Iterate through every cell in a range in Google Sheets using Apps Script

A common use case when working with data in Google Sheets using Apps Script involves taking some action on every cell in a range. For example, you might want to ensure that every cell in the range meets some criteria.

In this tutorial, I will show you how to iterate through every cell in a range in Google Sheets using Google Apps Script.

Since a spreadsheet is a two-dimensional grid of cells, the data in a range that is larger than a single cell is structured as a two-dimensional array in Apps Script (i.e., an array of rows where each row is an array of columns).

[
    [R1C1, R1C2, R1C3],
    [R2C1, R2C2, R2C3]
]

One way to iterate through every cell in a range is by using two nested forEach loops. The code below shows you how to do this and uses two nested forEach loops to log the data in every cell in a range.

//@OnlyCurrentDoc

function logDataInEveryCell() {
 var range = SpreadsheetApp.getActive().getRangeByName("SalesData");
 var values = range.getValues();
 values.forEach(function(row) {
   row.forEach(function(col) {
     Logger.log(col);
   });
 });
}

var range = SpreadsheetApp.getActive().getRangeByName("SalesData");

var values = range.getValues();

values.forEach(function(row) {
   row.forEach(function(col) {
     Logger.log(col);
   });
 });

In your code, you should replace Logger.log(col); with the action you want to take on each cell. This could be generating a summary statistic, performing some analysis or even putting together some HTML to send out an email from Google Sheets.

Conclusion

In this tutorial, I showed you how to iterate through every cell in a range in Google Sheets using two nested forEach loops.

Thanks for reading!