Move "active" cell in Googles Sheet macro language

Question

I am trying to write a function that pulls the nth (in example 4th) column of data from a sheet starting at a certain row/column, whose address is defined as a variable. I tried using offset but the .getrange() to move the "active" cell to the nth position and make that cell the active cell but macro aborts. Has anyone done this? Due to nature of data, it is important that I move the "active" cell n columns. Subset of code below:

function PullSelectedData() {

//

var GeneralDataFirstLocation = "D4";   //data starting point cell D4

var ColumnsBetweenSeries = 4;          //want data from each of the 4 columns (e.g.; D4, H4, L4, P4....)

var ReturnedValue = 0;

//

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

var BondCheckSS = spreadsheet.getSheetByName("BONDCHECKSHEET");

//

  for (var i = 0; i <= 3; i++) {

      BondCheckSS.getRange(GeneralDataFirstLocation).Offset(0, ColumnsBetweenSeries * i).activate();

      ReturnValue = getActiveCell().getValue();

  }

}

Answer 1

Why it crashed

• The first issue is that Offset is not a function, so it should be offset, though your arguments for the function are correct, so that issue is resolved.
• The next issue is that getActiveCell is not a function on its own, it must be called on the sheet. For example, MySheet.getActiveCell()
• A third issue that doesn't cause your code to crash, but allows you to use the ColumnsBetweenSeries variable, is changing your loop to for (var i = 0; i < ColumnsBetweenSeries; i++) {

Unwanted behavior

At this point, your code now runs fine, it no longer crashes, but there is still an issue, it doesn't quite do what you want it to do. Here is what it looks like now

function PullSelectedData() {
  var GeneralDataFirstLocation = "D4"; //data starting point cell D4
  var ColumnsBetweenSeries = 4;        //want data from each of the 4 columns (D4, H4, L4, P4)
  var ReturnedValue = 0;

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var BondCheckSS = spreadsheet.getSheetByName("BONDCHECKSHEET");

  for (var i = 0; i < ColumnsBetweenSeries; i++) {
      BondCheckSS.getRange(GeneralDataFirstLocation).offset(0, ColumnsBetweenSeries * i).activate();
      ReturnedValue = BondCheckSS.getActiveCell().getValue();
  }
}

The main problem is that ReturnedValue gets overwritten every iteration of the loop. So we need to either add it to a total every iteration, or we can put them all in a list with which we can do further processing afterwards.

If we wish to add each cell's value to a total, then the line where you set ReturnedValue in the loop becomes the following

ReturnedValue += BondCheckSS.getActiveCell().getValue();

If we want to put it all in a list, we can add each of the elements to a list using the append function, MyList.append(valueToAppend), in which case, declaring ReturnedValue becomes the following:

var ReturnedValue = [];

and inside the loop, setting ReturnedValue becomes the following, which adds each value to the end of the list

ReturnedValue.append(BondCheckSS.getActiveCell().getValue());

Sum example

function PullSelectedData() {
  var GeneralDataFirstLocation = "D4"; //data starting point cell D4
  var ColumnsBetweenSeries = 4;        //want data from each of the 4 columns (D4, H4, L4, P4)
  var ReturnedValue = 0;

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var BondCheckSS = spreadsheet.getSheetByName("BONDCHECKSHEET");

  for (var i = 0; i < ColumnsBetweenSeries; i++) {
      BondCheckSS.getRange(GeneralDataFirstLocation).offset(0, ColumnsBetweenSeries * i).activate();
      ReturnedValue += BondCheckSS.getActiveCell().getValue(); // Notice the change from = to +=
  }
}

List example

function PullSelectedData() {
  var GeneralDataFirstLocation = "D4"; //data starting point cell D4
  var ColumnsBetweenSeries = 4;        //want data from each of the 4 columns (D4, H4, L4, P4)
  var ReturnedValue = []; // Notice change from 0 to []

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var BondCheckSS = spreadsheet.getSheetByName("BONDCHECKSHEET");

  for (var i = 0; i < ColumnsBetweenSeries; i++) {
      BondCheckSS.getRange(GeneralDataFirstLocation).offset(0, ColumnsBetweenSeries * i).activate();
      ReturnedValue.append( BondCheckSS.getActiveCell().getValue() ); // We now append each value to the list instead of overwriting it each time
  }
} // ReturnedValue is now filled with the values of D4, H4, L4 and P4, Ex: [10, 20, 30, 40]