Clarification on using the forEach() function

Note: The purpose of this was just to try and learn how the forEach function works so that I can try to apply it elsewhere.

I’m trying to learn how to use the forEach function on apps script, but it doesn’t work when I try to use it. I want to loop through the first 15 cells of column A and set the background to red.

I tried this but when I run it, it says "cell out of range"

 function onOpen() {
 
  var range = SpreadsheetApp.getActive().getRange("A1:A15");
  var values = range.getValues();
  values.forEach(function(row) {
    range.getCell(row,1).setBackground('red');
   
 });
 }

>Solution :

Modification points:

  • The arguments of the callback function of forEach are element, index, and array, respectively. In your script, the element (cell value) is used as the row number. I think that this is the reason for your current issue.

  • If you want to use the index as the row number, it is required to add 1. Because the start of row number is 1. Please be careful about this.

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

Modified script:

From:

  values.forEach(function(row) {
    range.getCell(row,1).setBackground('red');
   
 });

To:

values.forEach(function (element,row) {
  range.getCell(row + 1, 1).setBackground('red');
});

Note:

  • If you want to set the background color of cells "A1:A15", I think that the following script might be useful. But, from your question, I thought that your goal of this question might be to understand about forEach. So, I proposed the above modification.

      SpreadsheetApp.getActive().getRange("A1:A15").setBackground('red');

Reference:

Leave a Reply