CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
RextheJoker
RextheJoker

Reputation: 29

Get values from multiple non-adjacent cells

I try to get values (as an array) from multiple non-adjacent cells in Google Sheet.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var arr = ['C1', 'C2', 'C4'];
  var val = ss.getRangeList(arr).getValues();
  Logger.log(val);
}

I got the following :

TypeError: ss.getRangeList(...).getValues is not a function".

Your help is much appreciated.

Upvotes: 2

Views: 1844

Answers (1)

Tanaike
Tanaike

Reputation: 201418

How about this modification?

Modification points:

  • Unfortunately, there is not method of getValues in Class RangeList. I think that this is the reason of your issue.
  • In order to retrieve the values from the cells of ['C1', 'C2', 'C4'], it is required to use the method of getRanges. By this, getValues can be used.

When these are reflected to your script, it becomes as follows.

Modified script:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var arr = ['C1', 'C2', 'C4'];
  var val = ss.getRangeList(arr).getRanges().map(range => range.getValues());  // Modified
  Logger.log(val);
}
  • In this case, each element in the array of val is 2 dimensional array.

Note:

  • In your case, each cell is a single cell. So I think that the following script might also be able to be used.

      var val = ss.getRangeList(arr).getRanges().map(range => range.getValue());

  • Or, in order to retrieve the values from the multiple non-adjacent cells, I created RangeListApp of Google Apps Script library. When this is used, your script is as follows.

      var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = ss.getActiveSheet().getSheetName();
  var arr = ['C1', 'C2', 'C4'];
  var rangeList = arr.map(e => `${sheetName}!${e}`);
  var val = RangeListApp.getSpreadsheet(ss).getRangeList(rangeList).getValues().map(e => e.values);
  Logger.log(val);

References:

Upvotes: 5

Related Questions