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.

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

Screenshot of a Google Sheets spreadsheet containing information about student grades.

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);
 });
}

When you run the function iterateThroughRows(), it will iterate through each row in the sheet called Students and log that information.

Screenshot of the execution log written by the script.

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!


Have feedback for me?

I'd appreciate any feedback you can give me regarding this post.

Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!