If you have done any looping within Zapier you have most likely been frustrated by the 500 iteration limit imposed on the Zapier loop array action. Never fear this post will show you how you can loop to your heart’s content using nested looping in Zapier.
Psssst another way to circumvent the 500 iteration limit is to use JavaScript or Python in a “Code by Zapier” action to do the looping, as shown in the Zapier Nested Looping using Webhooks & Python post. However, the 10-second timeout limit imposed on all Zapier actions may then become the limiting factor if your programmed loop does not complete in time, which means we’re back to nested looping 🙂 .
N.B. That Zapier imposes a 200 actions per 10 minutes limit on every zap so if you are running into this rate-limit issue then contact Zapier support to increase it.
This post will use the Customer Database Large Google Sheet for the examples below. This sheet contains the First Name
, Order #
, Email Address
, and Phone Number
of each customer.
Nested Looping Through Array in Zapier using Google Scripts
The first method for achieving nested looping in Zapier actually does not use nested looping in Zapier at all. Instead, a nested for loop in a Google Script is used to iterate through all the rows in your Google Sheet and send them in batches of 500 rows to Zapier using a webhook. Zapier then receives these batches of 500 rows using the “Catch Hook” action so that this data can be used in the Zapier loop array action.
N.B. While I am using Google Scripts you can apply the same logic I use here to any system external to Zapier e.g. scheduled scripts in any programming language, your marketing automation platform, your customer data platform, etc, where you can do the nested looping outside of Zapier to send a maximum of 500 rows at a time to Zapier for processing.
The Google Script sends the rows in batches of 500 because this is the maximum number of iterations that any Zapier loop array action can do at once and so the zapier_iteration_limit
variable used in the JavaScript code below is set to 500.
N.B. Before diving in below, you can take a look at the Zapier Import Multiple Rows from Google Sheets post to get a detailed walk-through of how to send multiple rows from Google Sheets to Zapier using webhooks.
Javascript Code for Outer Loop
The outer for loop of the JavaScript code runs from the first row of the Google Sheet, incrementing by 500 for each iteration, until it exceeds the number of rows in the sheet. Each iteration of the outer for loop:
- Resets the 4 strings variables for names, orders, emails, and phones to be empty
- Calls the nested for loop to populate these 4 strings with row values
- Sends a webhook to Zapier with these 4 strings in the payload
Note that the webhook URL used below is obtained by copying the “Custom Webhook URL” value from the “Set up Trigger” section in the “Catch Hook” action.
/*Zapier Loop Array with Nesting JavaScript Outer Loop*/
for (var i=1; i <= rows.length; i=i+zapier_iteration_limit){
names_string="";
orders_string="";
emails_string="";
phones_string="";
[[Nested For Loop Populates String Variables]]
var ts = new Date().toLocaleString()
var url = "https://hooks.zapier.com/hooks/catch/65051/bykfncv/"
var options = {
"method": "post",
"headers": {},
"payload": {
"Timestamp": ts,
"Names": names_string,
"Orders": orders_string,
"Emails":emails_string,
"Phones":phones_string
}
};
var response = UrlFetchApp.fetch(url, options);
}
Javascript Code for Nested Loop
The nested for loop runs from 0, incrementing by 1 for each iteration, until it has iterated through 500 rows or it has iterated through the last row of the sheet. Each iteration of the nested for loop takes the 4 column values within a row and appends them using the *
character to the corresponding string variable for that column.
The conditional statement in the nested for loop ensures that once the variables have been populated for the first time the next time they are being populated an asterisk will be used to separate the value(s) already in the variable from the new value being appended.
/*Zapier Loop Array with Nesting Javascript Nested Loop*/
for (var j=0;(j<zapier_iteration_limit) &&( (i+j) <=rows.length);j++){
if (names_string==""){
names_string = rows[i+j][0];
orders_string = rows[i+j][1];
emails_string = rows[i+j][2];
phones_string = rows[i+j][3];
}
else{
names_string = names_string+"*"+rows[i+j][0];
orders_string = orders_string+"*"+rows[i+j][1];
emails_string = emails_string+"*"+rows[i+j][2];
phones_string = phones_string+"*"+rows[i+j][3];
}
}
Trigger Zapier to Loop Through Array
This Google Script can then be attached to a button (see the Zapier Import Multiple Rows from Google Sheets post) so that every time the button is clicked the JavaScript code above will run. For each iteration of the outer for loop, Zapier will receive the webhook containing the 4 string variables (each containing a maximum of 500 row values) and split them with the *
delimiter.
The individual row values split out by this delimiter can then be used inside actions for each iteration of the Zapier loop array action. For example, the image below shows how the looping variables from the Zapier loop array action can be used to populate the recipient address, subject line, and body of the customer email notification.
Zapier Loop Through Array Action Output
The “Send Email” action from the Gmail app was used instead of the “Email by Zapier” action because the “Email by Zapier” action kept returning the error “Sorry! That is too many emails sent. Please try Mandril, Mailgun, Gmail, or others for bulk email.” causing all iterations of the loop to be held.
While the Gmail app is better for sending email within a loop it still can only handle about 10 loop iterations before it runs into rate limit issues from my testing. Therefore, if you want to send email in bulk using a loop then I would look into the Mandril or Mailgun apps suggested by Zapier.
As shown in the image below, the order in which the different loop iterations complete in Zapier is not sequential so bear this in mind if you are performing tasks in a loop that need to complete one after the other. If you require the actions to be completed sequentially then take a look at the “Executing Loop Actions Sequentially” section in the Zapier For Each Loop Quick Start Guide post.
Nested Looping Through Array using Webhooks
If your external system outside of Zapier cannot implement the outer loop for nesting then the outer loop can be implemented in Zapier using webhooks. The external system will trigger the Zap to run by sending a webhook to the “Catch Hook” trigger causing the Zap to run through its actions using the information contained in the incoming webhook.
At the end of the Zap, if there is still more iteration that needs to be done then the “POST Hook” step will send a webhook to the “Catch Hook” trigger containing the new set of information for the next iteration. This process is repeated until all iterations of the outer loop are complete and the Zap stops at the penultimate filter step meaning the “POST Hook” webhook is not sent and the Zap is not restarted.
N.B. If you want a simpler example of how to use webhooks for looping in Zapier, one that does not involve nested looping, then check out the Marketo Program Cloning Via API post.
Javascript Code to Loop Through Rows
N.B. The next few paragraphs build on the Zapier Google Sheets Lookup Value post which shows you how to import a large number of rows into Google Sheets. So if you need greater detail after reading the paragraphs below then take a look at this post to clear things up 🙂
The first part of the Javascript code uses a for loop to iterate over every row in the Google Sheet and if the Delivery Today?
column is True
then the for loop takes the 4 column values within a row and appends them using the *
character to the corresponding string variable for that column.
The conditional statement in the nested for loop ensures that once the variables have been populated for the first time the next time they are being populated an asterisk will be used to separate the value(s) already in the variable from the new value being appended.
/*Zapier Loop Array with Nesting JavaScript for Webhook Nesting Part 1*/
for (var i=1;i<rows.length;i++){
if(rows[i][4]==true){
if (names_string==""){
names_string = rows[i][0];
orders_string = rows[i][1];
emails_string = rows[i][2];
phones_string = rows[i][3];
}
else{
names_string = names_string+"*"+rows[i][0];
orders_string = orders_string+"*"+rows[i][1];
emails_string = emails_string+"*"+rows[i][2];
phones_string = phones_string+"*"+rows[i][3];
}
}
}
Javascript Code to Create Arrays & Trigger Zap
The second part of the Javascript code then creates 5 variables for each column in the first empty row of the Concatenated Columns sheet and then populates these variables, and hence this first empty row, with the variable values. Once this is done a webhook is sent to the URL of the “Catch Hook” trigger in Zapier so that this Zap can then iterate over all the row values stored in the concatenated strings stored in the columns.
/*Zapier Loop Array with Nesting JavaScript for Webhook Nesting Part 2*/
var submissions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Concatenated Columns");
var row = submissions.getLastRow() + 1;
var timestamp = submissions.getRange(row, 1);
var names = submissions.getRange(row, 2);
var orders = submissions.getRange(row, 3);
var emails = submissions.getRange(row, 4);
var phones = submissions.getRange(row, 5);
timestamp.setValue(ts);
names.setValue(names_string);
orders.setValue(orders_string);
emails.setValue(emails_string);
phones.setValue(phones_string);
var url = "https://hooks.zapier.com/hooks/catch/65051/byka0c9/"
var options = {
"method": "post",
"headers": {},
"payload": {
"timestamp": ts,
"length": rows.length,
"index_start":0
}
};
Looking Up Arrays in Zapier
Once the Zap has been triggered the “Lookup Spreadsheet Row” action is used to lookup the timestamp value, which was passed in the incoming webhook, in the “Timestamp” column of the “Concatenated Columns” sheet.
Slicing Arrays into Packages of 500 Rows
The 4 concatenated string variables are then brought into a “Code by Zapier” action where Python code is used to create arrays that will be iterated through in the “Create Loop from Text” action. The Python code turns each of the string variables into arrays and then only stores a portion of the array from the starting index to the ending index.
The starting index is updated for each iteration of the outer loop when the “Catch Hook” trigger receives a new incoming webhook. The end index will either be the start_index plus the Zapier loop array iteration limit of 500 or if this sum exceeds the length of the array then the end index will be the length of the array and the finished
boolean value is set to True
.
/*Zapier Loop Array with Nesting Python Array Slicing*/
start_index = int(float(input['start_index']))
limit = int(input['limit'])
length = int(float(input['length']))
if (start_index+limit) >= length:
end_index = length
finished = True
else:
end_index = start_index+limit
finished = False
names = input['names_raw'].split('*')[start_index:end_index]
orders = input['orders_raw'].split('*')[start_index:end_index]
phones = input['phones_raw'].split('*')[start_index:end_index]
emails = input['emails_raw'].split('*')[start_index:end_index]
return{'end_index':end_index, 'names':names, 'orders':orders, 'phones':phones, 'emails':emails, 'finished':finished}
Nested Looping in Zapier to Send Emails
The “Create Loop From Text” action and “Send Email for Each Customer” actions (set up the same as detailed in the “Nested Looping in Google Scripts” section above) then send an email to each customer within the sliced arrays returned from Python.
A filter is then used in Step 6 to ensure that the Zap will only progress once the Zapier loop array action has reached its last iteration (i.e. Loop Iteration Is Last
is true) and if the finished
boolean variable set by the Python code is false.
Since the Zap progressed through the filter in Step 6 that means that the Zap needs to be run again to process the next batch of sliced arrays. The “POST Hook” action will now send the lookup timestamp, the length of the concatenated string variables, and the starting index to use in the next iteration (which is the ending index of the current iteration) to the URL of the “Catch Hook” trigger in Step 1.
Nested Looping using Webhooks to Edit Google Sheets Rows
If instead of getting multiple rows from Google Sheets, you want to update a large number of rows (>500) then you can use nested looping in Zapier once more. The structure of the zap is the same as outlined in the “Nested Looping using Webhooks to Get Google Sheets Rows” where webhooks are used to achieve the nested looping.
Javascript to Trigger Nested Looping in Zapier
The Zap is once again triggered by a Google script sending a webhook to the “Catch Hook” trigger in Zapier. The payload of the webhook contains the number of rows to be updated, the starting index (2 in this case since row 1 contains the sheet headers), and the ending index. Since the Zapier loop array action can only do 500 iterations at once and we are starting from row number 2 the end index will be 501.
/*Zapier Loop Array with Nesting JavaScript to Send Webhook*/
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Customer Database Large");
var rows = sheet.getDataRange().getValues();
var url = "https://hooks.zapier.com/hooks/catch/65051/bydbbeq"
var options = {
"method": "post",
"headers": {},
"payload": {
"length": rows.length,
"index_start":2,
"index_end":501
}
};
Nested Looping Through Array in Zapier to Set Rows
The starting and ending indexes are then used in the set up of the “Create Loop From Numbers” action so that Zapier will loop through the interim numbers and use each number to update the Delivery Today?
column to true within the row identified by this number.
N.B. The updating of the Google Sheets rows is pretty slow.
Restarting Nested Looping in Zapier
A filter is then used to ensure that the Zap will only progress once this nested loop has updated all the rows for this iteration of the outer loop (i.e. Loop Iteration Is Last
is true) and if there are still more rows that need to be updated (i.e. Loop Iteration Last
is still less than the number of rows needing updating).
Since the Zap has progressed through the filter that means we need to calculate a new ending index for the next iteration of the outer loop. This is done using logic in Python code to set the next ending index to the current ending index plus 500 or if this sum is greater than the number of rows that need updating then set the value to the final row number.
/*Zapier Loop Array with Nesting Python End Index Calculation*/
last_index = int(input['last_index'])
limit = int(input['limit'])
length = int(float(input['length']))
if (last_index+limit) > length:
end_index = length
else:
end_index = last_index+limit
return{'end_index':end_index}
Finally, the zap is restarted by using the “POST Hook” action to send the starting index (which is equal to the ending index of the current iteration), ending index, and number of rows needing updating to the URL of the “Catch Hook” trigger in Step 1.
What’s After Nested Looping in Zapier?
Congrats on getting through this post! It was dense, it was heavy but once the lactic acid fades away the exhilaration of loop-de-looping like an F-15 fighter pilot will make it all worth it!
Now that you are ready to leave the nest (sorry I couldn’t help it…), here are some posts that you might be interested in.
- Enhance your newfound nested looping skills by taking off the training wheels and swapping out the nested Zapier loop array action with a loop in Python as done in the Zapier Nested Looping using Webhooks & Python post.
- Recap on some of the ways we imported data into Google Sheets in this post and learn about the best options for importing data based on the number of rows you need to import in the Zapier Google Sheets Quick-Start Guide.
- Recap on the 3 inbuilt Zapier loop array actions that are available in Zapier in the Zapier For Each Loop Quick-Start Guide.
As always if you are finding this content useful (or you are just enjoying the periodic witty banter) and you haven’t yet subscribed, then hit that shiny, pink subscribe button at the top of the page 🙂