[GUIDE] How to loop through *all rows* in Google Sheets

  • 20 July 2022
  • 2 replies
  • 435 views

Userlevel 7
Badge +12

This post will outline how to schedule Zapier to loop through *all rows* on a Google Sheet. *Bear in mind that Looping by Zapier currently has a limit of 500 iterations meaning this trick will only work for sheets with up to 500 rows of data. 

Step 1: Modify Your Sheet

First thing is first, you will need to modify your spreadsheet so Zapier has a way to figure out where your last column is and a way to look up each column while in the loop. Here is where ArrayFormula comes in handy! 
Add a column to your spreadsheet title ‘Row’. This column will fill itself with its own row number when there is a value in column A. If there is not a value in column A then the value will be “Blank Row”.

Formula in C2: =ArrayFormula(IF(ISBLANK(A2:A),"Blank Row",Row(A2:A)))

Step 2: Head back to Zapier and start setting up your zap

In this example, we will have Zapier run once a month on a scheduler (shown below)

Once you’ve got your scheduler set up, step 2 of your zapier integration will be to look up the first blank row on your spreadsheet. Set up the Lookup Spreadsheet Row action to search for the value “Blank Row”. This will tell our Zap where it can stop.  

Zapier step 3 is a Code by Zapier step. This is where the magic starts to happen. It takes the first blank row value outputted from the previous step and determines the number of rows to pass into the looping step. It assumes your spreadsheet has a header row thus the output starts at row 2. 

CODE FOR COPY

/* This assumes you have a header row. Loop will run from row 2 until first blank row */
let loopIterations = [];
for (var i=2;i<inputData.firstBlankRow;i++) {
loopIterations.push({row: i})
}
return {loopIterations}

The code step should output something like the following. in our example, we are going to loop through 3 rows (row 2, 3, & 4)

 


Next, set up a Looping by Zapier step passing in the output from the code block. 

 

Finally, within the loop, set up another Lookup Spreadsheet Row action. You’re still searching that row column but this time using the row index as the key. 



There you have it! Your zap is now set up to run on a scheduler to pull back up to the first 500 rows of data in a loop. You can then pass these row values into additional actions to send text messages, emails, & more!


**note, if you only need to loop up to 250 rows then you can remove the Looping by Zapier step and modify the code block to engage the loop.**
 

/* This assumes you have a header row. Loop will run from row 2 until first blank row */
let loopIterations = [];
for (var i=2;i<inputData.firstBlankRow;i++) {
loopIterations.push({row: i})
}
return loopIterations

 


2 replies

Userlevel 1

Thank you!  This solved my exact problem!

Nice walk through! Thank you for spending the time to create this!

Reply