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 plete 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!

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 plete 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!

• javascript
• google-apps-script
• google-sheets

• Have you read best practices ? See tag info page for more details. – TheMaster Commented May 13, 2020 at 19:03
• @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 Commented May 13, 2020 at 19:16
• Do you mean this? They're getting a whole range and not a single cell – TheMaster Commented May 14, 2020 at 3:45

2 Answers 2

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");
    }
  }
}

This is just my own answer to help users who might get to this question.

Once you have a range of cells, like this:

var range = sheet.getRange("your range");

You can do this to get a specific cell:

var cell = range.getCell(row, col);