Zapier Loop Array with Nesting

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.

On a related note are you interested in learning more about APIs? APIs are the foundational building blocks that power modern software ecosystems like Zapier and the Demystify the API Crash Course will explain the fundamentals and give you a hands-on introduction to making your first API requests. Check out the course using the button below 🙂

Take Tyron’s Course on Demand

Explore the Demystify the API Crash Course

Nested Looping in Google Scripts

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 10second timeout limit imposed on all Zapier actions may then become the limiting factor if your programmed loop does not complete in timewhich 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  Google Sheet for the examples below. This sheet contains the First NameOrder #Email Address, and Phone Number of each customer along with the Delivery Today? checkbox field to designate whether their order will be delivered today.

Flow diagram showing how Google scripts can be used to achieve nested looping with Zapier
Google script and Zapier nested loop flow diagram

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.

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.

Overview of the zap that loops through the rows sent from Google Sheets
Overview of the zap that loops through the rows sent from Google Sheets

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.

"Catch Hook" action to receive the webhook from Google Sheets
“Catch Hook” action to receive the webhook from Google Sheets
/*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);
  
  }

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

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.

Zapier loop array action setup showing the inputs to this action
Zapier loop array action setup showing the configuration of the action
Zapier loop array action setup

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.

Email action setup showing the recipient address
Email action setup showing the population of the subject line and email body using the looping variables
Population of the email using the looping variables

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.

Holding of loop iterations in Zapier after exceeding rate limits
Holding of loop iterations after exceeding rate limits

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.

Email inbox containing emails sent from the Zapier loop array action
Email inbox containing emails sent from the Zapier loop array action

Nested Looping using Webhooks to Get Google Sheets Rows

Flow diagram showing how to use webhooks to achieve nested looping in Zapier
Nested looping with webhooks flow diagram

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.

Overview of nested looping using webhooks in Zapier to send an email for each Google Sheet row
Overview of nested looping using webhooks in Zapier to send an email for each Google Sheet row

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

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

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.

Using the timestamp from the webhook to lookup the spreadsheet row
Using the timestamp from the webhook to lookup the spreadsheet row

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 length of the array and the finished boolean value is set to True.

Input data for the Python code used to slice the arrays from Google Sheets
Input data for the Python code used to slice the arrays from Google Sheets
/*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}

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 progess 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.

Zapier filter to only allow progression if the nested Zapier loop array action has finished and the outer loop is not finished
Filter to allow progression once the nested loop has finished and the outer loop still has more iterations to do

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.

Post webhook setup to re-trigger the Zap to run if there are still more values to iterate through in Google Sheets
Webhook setup to re-trigger the Zap to run if there are still more values to iterate through in Google Sheets

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.

Overview of nested looping using webhooks in Zapier to update each row in a Google sheet
Overview of nested looping using webhooks in Zapier to update Google Sheets rows

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

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.

Zapier loop array action setup
Zapier loop array action setup
Updating the spreadsheet row identified by the loop iteration variable
Updating the "Delivery Today?" column in the spreadsheet row to True
Updating the “Delivery Today?” column in the spreadsheet row to True

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).

Filter to allow progression once the nested Zapier loop array action has finished and the outer loop still has more iterations to do
Filter to allow progression once the nested loop has finished and the outer loop still has more iterations to do

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.

Input data to the Python code used to calculate the end index of the next iteration
Input data to the Python code used to calculate the end index of the next iteration
/*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.

Webhook payload containing the information needed for the next loop iteration
Webhook payload containing the information needed for the next loop iteration

What’s Next?

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.

About The Author — Tyron Pretorius
Tyron Pretorius

Greetings fellow MO Pros! As a previous mechanical engineer turned marketing operations lead, I now fancy myself as a full-stack marketer capable of designing websites, writing SEO-optimized content, and doing all the tracking and automation on the backend. That being said my bread and butter is marketing operations and I love to get down and dirty with the Marketo API, Python, Zapier, and Google Scripts.

Related free workshops

The New Four Pillars of Marketing Operations

The New Four Pillars of Marketing Operations

Created with the assistance of DALL·E Introduction and Background In 2018, Edward Unthank introduced the four pillars of marketing operations, setting the stage for the management of effective marketing technology (Martech) functions. These pillars have since been the bedrock for many successful careers in marketing operations. My journey to redefine

Become a member
The Role of Front-End Martech Tools in the Age of Generative AI

The Role of Front-End Martech Tools in the Age of Generative AI

Introduction: Blending Human and Technical Perspectives in Martech Caption: Future gardens of martech will blend human and AI perspectives As a co-host of the Humans of Martech podcast, I’ve had the privilege of interviewing some of the brightest minds in marketing technology. This unique experience has provided a blend of

Become a member