Do, For and While Loops in Google Sheets

There are 3 types of loops you can use when writing JavaScript inside the Apps Script editor in Google Sheets. Those are do,for and while loops. They each work slightly differently. For loops are our favorite construct when updating all the cells in a range. They offer outstanding performance and make for easily readable code.

There are other modern JavaScript looping constructs available, like ForEach. However, when you’re trying to update sheet cells, a ForEach loop doesn’t help. This is because the Range object used to get access to cells within a sheet programmatically doesn’t offer any methods that return a collection of cells, only a collection of values. So there is nothing for ForEach to iterate over and update. It’s possible, but you are really best only using ForEach to read cell values.

For loop example

Here’s an Apps Scrips function that gets a range of 11 cells from the current sheet and updates rows 2 to 10 with a timestamp using a for loop.

function for_loop() {
  var range = SpreadsheetApp.getActiveSheet().getRange("A1:A10");

  //for is the simplest most readable way to update cell values
  for (let row = 2; row <= 10; row++) {
    range.getCell(row,1).setValue(Utilities.formatDate(new Date(),"GMT","HH:mm:ss.S"));
  } 
}Code language: JavaScript (javascript)

Once we have the Range object from the active sheet, iterating over it, starting at row 2 and ending at row 10, and getting each individual cell takes only a few lines of code. It’s easy to change the start and end position of the loops (the rows we update) and the for loop handles creation of the variables that hold the current row number and increments that rows number for us.

While loop example

Here’s the same task written using a while loop. A while loop will check the condition inside the brackets and, if it is false, execute the code within the curly braces. It will then repeat that process until the condition inside the brackets becomes true. At which point, it will stop.

Using a while loop means you have to remember to deal with creating and your current row number variable outside the loop and incrementing it yourself within the loop (after your job is complete). If you forget, the loop may run forever.

 function while_loop() {
  var range = SpreadsheetApp.getActiveSheet().getRange("A1:A10");

  var current_row = 2;
  while (current_row <= 10) {
    range.getCell(current_row,1).setValue(Utilities.formatDate(new Date(),"GMT","HH:mm:ss.S"));
    current_row++;
  } 
}Code language: JavaScript (javascript)

Do While loop example

The third type of loop is the do while loop. This is like the while loop, but we specify the while condition outside the final curly brace. When this loop runs, it will do the code within the curly braces and then check the condition specified at the end and if that condition is false, repeat the process.

Again, like a while loop you need to create the variable used to hold your current row number and also remember to increment it after your task is complete, or the loop will run forever.

Here’s the task again, written using a do while loop.

function do_loop() {
  var range = SpreadsheetApp.getActiveSheet().getRange("A1:A10");
  var current_row = 2;

  do {
    range.getCell(current_row,1).setValue(Utilities.formatDate(new Date(),"GMT","HH:mm:ss.S"));
    current_row++;
  } while (current_row <= 10);
}Code language: JavaScript (javascript)

ForEach loops to read cells

If you’ve written modern JavaScript before, you’ve probably come across ForEach. Whilst this isn’t a strictly a loop, it can run custom a function against all the objects of an array. The problem with Google Sheets is that there are no objects available on the Range class that allow you to get an array of cell objects.

You can use getRange() to return an array of the values for a range. But you can’t use those values to update cells directly, they’re read only.

It’s possible to update cells using a ForEach, but it doesn’t make for readable or fast code.

Here’s the job above written using a ForEach loop.

function foreach_loop() {
  var range = SpreadsheetApp.getActiveSheet().getRange("A1:A10");
  var values = range.getValues();

  //using a foreach to write sheet values leads to confusing code
  values.forEach(function (val, ix, array) {
    if (ix > 0) {
      range.getCell(ix+1,1).setValue(Utilities.formatDate(new Date(),"GMT","HH:mm:ss.S"));
    }
  });
}Code language: JavaScript (javascript)

As you can see, we have to get the range, then get the values and then iterate over those values and use the index (ix) parameter that ForEach provides (which is zero based) and once again get the cells from the initial range before we can update them.

There are several un-necessary steps in this code that we can avoid by simply using a for loop instead. Also, ForEach has to execute a function against each object in the array, this is always going to be slower than simply iterating a loop.