get x cell in range google sheets app script

Question

Using a google sheets apps script, Is it possible to get the X cell in a range of cells as shown below:


var app = SpreadsheetApp;
var ss = app.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

var cells = sheet.getRange("D6:BF100"); // the range

for (var x = 0; x < 100; i++) {
var cell = cells[row][x]; // this is the line that doesn't work

if (someCondition(x)) {
      cell.setBackground("red");
}
}

I don't want to change the colors for every single cell, just the ones that have the correct condition.

I know it is possible to do something like this:

var cell = sheet.getRange(row+i);

But this method takes a very long time to complete the for loop as there are hundreds of cells that need to be scanned. I would rather only use getRange once with the entire range (instead of just one cell at a time) and then from that range (it should make a 2d array right?) set the cell values. I think that would be a lot faster.

Is there a different way to do it faster, or do I just need to do this: var cell = cells[row][x]; a different way.

Thanks!

Have you read best practices ? See tag info page for more details. — TheMaster
@TheMaster yes I have read it. I was looking through mainly the optimizing one, and unfortunately in their example they are only getting the range of a single cell. — WhiteFire356
Do you mean this? They're getting a whole range and not a single cell — TheMaster

BaconSodas BaconSodas · Accepted Answer · 2020-05-13T20:05:15

It looks like your for loop is constructed incorrectly. You increment the variable i that isn't defined or used anywhere. Also, you use the variable row without defining it.

It takes forever because it has to query your sheet each time you call cells.getRange() Instead, you can query once using cells.getValues() to get the whole range, and then change the cells color that meet the condition one at a time.

Try the following;

var app = SpreadsheetApp;
var ss = app.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

var cells_range = sheet.getRange("D6:BF100");
var cells_values = cells_range.getValues();

for (var col = 1; col <= cells_values[0].length; col++) {
  for (var row = 1; row <= cells_values.length; row++) {
    if (someCondition(cells_values[row][col])) {
      cells_range.getCell(row, col).setBackground("red");
    }
  }
}

get x cell in range google sheets app script

2 Answers