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.
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
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.
Do While loop example
The third type of loop is the
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.
ForEach loops to read cells
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.
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.