CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Zach Biggs
Zach Biggs

Reputation: 1

Adaptable spreadsheet data gathering

I have a google script program that gets information from a google spreadsheet. The variables get their data from specific cells. My question is how can I make my coding adaptable so if rows or columns are added or subtracted the code will know where to look to get the right information? ie the emailaddresses(var AthEmailData) and Names (AthPresData)

Here is an example,

//Athens Variables
  var dataA = sheet.getRange(10, 2);
  var totalA = dataA.getValue();
  var AthPresData = sheet.getRange(3, 8)
  var AthPres = AthPresData.getValue();
  var AthEmailData = sheet.getRange(3, 9);
  var AthEmail = AthEmailData.getValue();

Upvotes: 0

Views: 71

Answers (1)

Tanaike
Tanaike

Reputation: 201398

  • You want to retrieve the values of AthPres and AthEmail, even if the ranges of sheet.getRange(3, 8) (H3) and sheet.getRange(3, 9) (I3) are changed.

If my understanding is correct, how about using NamedRange? In the case that NamedRange is used, even when the range which is set as the named range is changed, the range of NamedRange is automatically modified and the value can be retrieved using NamedRange. I think that there might be several answers for your situation. So please think of this as one of them.

The flow is as follows.

  1. Set "H3" and "I3" to NamedRange.
    • "H3" and "I3" are used from your question.
  2. Retrieve values using the NamedRange.

Sample script:

1. Set NamedRange

At first, please set the NamedRange using this script. Of course, you can also set "H3:I3" as one NamedRange.

function myFunction1() {
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = s.getActiveSheet();
  var AthPresData = sheet.getRange(3, 8); // H3
  var AthEmailData = sheet.getRange(3, 9); // I3
  s.setNamedRange("AthPresData", AthPresData); // Set "H3" to NamedRange as "AthEmailData"
  s.setNamedRange("AthEmailData", AthEmailData); // Set "I3" to NamedRange as "AthEmailData"
}

2. Retrieve values

After set the NamedRange, you can retrieve values using the NamedRange using this script. Even if the ranges from "H3" and "I3" to others, you can retrieve the values using the NamedRange.

function myFunction2() {
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var names = ["AthPresData", "AthEmailData"];
  var namedRanges = s.getNamedRanges();
  for (var i = 0; i < namedRanges.length; i++) {
    for (var j = 0; j < names.length; j++) {
      if (namedRanges[i].getName() == names[j]) {
        var value = namedRanges[i].getRange().getValue();
        Logger.log("%s, %s", names[j], value)
      }
    }
  }
}

Note:

  • This is a simple sample script. So please modify it for your situation.
  • You can also manually set NamedRange on Spreadsheet.

References:

If I misunderstand your question, please tell me. I would like to modify it.

Upvotes: 1

Related Questions