How to Reference Cells in Google Apps Scripts

Question

I'm transitioning from knowing VBA to Scripts for spreadsheet management. I created a function to flag cells that contain certain words within my spreadsheet.

How do I reference specific cells as it relates the overall code:

The issue is with the following being undefined.

setBackgroundColor("#99CC99")

Would this be appropriate?:

values[i][0].setBackgroundColor("#99CC99")

Code below:

function foo(){
  var range = SpreadsheetApp.openById("1oh5wXFi4YM1bDvfF27pu1qHDKQEhn02drtfsrRgxArQ").getSheetByName("General").getRange("D:D");
var values = range.getValues();
for(var i in values){
  if(values[i][0].match("Rensselaer")!=null){
     setBackgroundColor("#99CC99")
  } 
  if(values[i][0].match("Albany")!=null){
     setBackgroundColor("#99CC99")
  }
  if(values[i][0].match("Saratoga")!=null){
     setBackgroundColor("#99CC99")
  }
}
}

Like the VBA code you came from, there is a Spreadsheet class, a Sheet class, and a Range class. There are also native language constructs, such as VB's Array (and Apps Script's JavaScript Array). Make sure you read the Apps Script method documentation, and perhaps work through some Javascript tutorials. Using the editor's autocomplete functionality will greatly help you understand what is happening and what class your variables are. Note `for(var ... in someArray)` is not [a recommended practice](https://stackoverflow.com/questions/500504/why-is-using-for-in-with-array-iteration-a-bad-idea) — tehhowch, Apr 18 '18 at 18:57
You do the proper thing - work on an array of the Range's values - as opposed to repeatedly call over the interface via `getCell` or similar just to check the value. To reference the cell whose value is `values[r][c]`, note that the Javascript array is 0-base and the Range is 1-base indexed. So A1 = [0][0]. https://developers.google.com/apps-script/reference/spreadsheet/range#getCell(Integer,Integer) — tehhowch, Apr 18 '18 at 18:59

Rubén · Accepted Answer · 2018-04-19T03:48:39.273

setBackgroundColor(color) is a method of the Class Range, in other words, first you should get a range object then you could call that setBackgroundColor(color) for that object

values[i][0] isn't an appropriate way to reference a range because this variable holds cell content not the cell itself.

There are a several ways to reference a range. The basics forms are A1 Notation, and row and column numbers.

A very simple example

function example1(){
  var range = SpreadsheetApp.getActiveRange();
  range.setBackgroundColor('red');
}

Recently the Class RangeList was introduced so now is possible to speedup the algorithms to edit non-adjacent cells.

function example2() {
  var sheet = SpreadsheetApp.getActiveSheet()
  var redList = [];
  var vls = sheet.getRange('A1:A' + sheet.getLastRow()).getValues();
  for(var i = 0; i < vls.length; i++){
    if(vls[i][0]=== 'red') redList.push('A'+ (i + 1));
  }
  var redRangeList = sheet.getRangeList(redList).setBackground('red');
}

Please note that the for loop doesn't make calls to the Google Apps Script classes and methods, it use pure JavaScript which make it faster than other alternatives. Then with a single call the color of several non-adjacent cells is set.

Cooper · Answer 2 · 2018-04-18T20:08:12.863

You might like to try it this way:

  function foo(){
  var ss=SpreadsheetApp.openById('1oh5wXFi4YM1bDvfF27pu1qHDKQEhn02drtfsrRgxArQ');
  //var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('General');
  //var sh=ss.getActiveSheet();
  var range=sh.getRange(1,4,sh.getLastRow(),1);
  var values=range.getValues();
  for(var i=0;i<values.length;i++){
    if(values[i][0].match(/Rensselaer|Albany|Saratoga/)){
      sh.getRange(i+1,4).setBackgroundColor("#99CC99");
    } 
  }
}

This works too:

function foo(){
  var ss=SpreadsheetApp.openById('1oh5wXFi4YM1bDvfF27pu1qHDKQEhn02drtfsrRgxArQ');
  //var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('General');
  //var sh=ss.getActiveSheet();
  var range=sh.getRange(1,4,sh.getLastRow(),1);//gets the smallest range that has data in it
  var values=range.getValues();
  var colors=range.getBackgrounds();//Creates the correct size area with the starting colors
  for(var i=0;i<values.length;i++){
    if(values[i][0].match(/Rensselaer|Albany|Saratoga/)){//you can add more alternatives easily
      colors[i][0]='#99cc99';
      //sh.getRange(i+1,4).setBackgroundColor("#99CC99");
    } 
  }
  range.setBackgrounds(colors);
}

Have questions?

Try reading the Google Apps Script Documentation.

How to Reference Cells in Google Apps Scripts

2 Answers2