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.
Note: Iteration involves processing a set of items one at a time.
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]
]Note: Please see the tutorial on reading from and writing to a range in Google Sheets using Apps Script for more information on how to work with ranges using Google Apps Script.
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);
});
});
}Note: The above code assumes that your sheet has a named range called SalesData.
How does the above code work?
First, we access the named range called SalesData in the Google Sheets spreadsheet.
var range = SpreadsheetApp.getActive().getRangeByName("SalesData");Next, we load all of the data in the range into a variable called values. The variable values is a two-dimensional array that is an array of rows where each row is in turn an array of columns.
var values = range.getValues();Finally we use an outer forEach loop to iterate through each row. Inside this forEach loop, we use another nested forEach loop to iterate through each column in the row and log the data contained in it.
values.forEach(function(row) {
row.forEach(function(col) {
Logger.log(col);
});
});The forEach loop is one of the most useful array methods in AppsScript. Please see the tutorial on forEach loops for more information on how to use them.
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!