Iterate through rows in Google Sheets using Apps Script
When you're working with data in Google Sheets using Apps Script, a very common use case is taking some action for every row in the spreadsheet.
For example, if you are a teacher, each row in the spreadsheet might contain the grades obtained by students in a class along with their email address. You can automate the process of emailing each student using Apps Script. The script would take the information in each row and then take some action with it. In this example, the script would send each student an email with their grades.
Iteration involves processing a set of items one at a time.
In this tutorial, I will show you how to iterate through every row in a Google Sheets spreadsheet using Google Apps Script.
Let's say you have the following information in a sheet named Students. There are four columns in the spreadsheet:
Name: The student's name
Math grade: Their Math grade
English grade: Their English grade
Email: Their email address
The following code loads this data and then iterates through each row and logs the information contained in it.
//@OnlyCurrentDoc
function iterateThroughRows() {
var sheet = SpreadsheetApp.getActive().getSheetByName("Students");
var data = sheet.getDataRange().getValues();
data.forEach(function (row) {
Logger.log(row);
});
}How does the above code work?
First, we access the sheet called Students in the spreadsheet.
var sheet = SpreadsheetApp.getActive().getSheetByName("Students");Next, we load all of the data in the sheet into a variable called data. The data range of a sheet is the range that contains all the data in the sheet. The variable data is a two-dimensional array that is an array of rows where each row is in turn an array of columns.
Then we use a forEach loop to iterate through each row and log its contents. Since each row is an array of columns, you'll notice that the information logged is a string representation of an array value.
data.forEach(function (row) {
Logger.log(row);
});The forEach loop is one of the most useful array methods in AppsScript. It works in the following way:
You pass a function as a parameter to the
forEachmethod.The
forEachmethod will then call this function with each value in the array.The function then takes some action on each value when it is called. In this example, the function logs the value using the
Logger.log()method.
In the above code, I've in-lined the function parameter but you can also define the function separately and then pass it as a parameter to the forEach method.
function logRow(row) {
Logger.log(row);
}
data.forEach(logRow);When you run the function iterateThroughRows(), it will iterate through each row in the sheet called Students and log that information.
In your code, you should replace Logger.log(row); with the action you want to take on each row. This could be generating a report, performing some computation, or even sending an email. It depends on your use case.
Conclusion
In this tutorial, I showed you how to iterate through each row in a Google Sheets spreadsheet using the forEach loop in Apps Script.
Thanks for reading!