Workarounds for the 500 google sheet lookup limit without having to separate sheets
I have run into the problem where the google sheet lookup allows you to lookup a maximum of 500 rows. My sheet has already exceeded 1000 so the start from bottom approach won’t work.
My Zap feels quite complex in what its trying to achieve, and seprating my business partners into separate sheets and separate zaps will cause duplicate emails if there locations are in different regions.
Can anyone help think of an apporach that will allow me to keep everything in one sheet or one zap?
Many thanks in advance.
Customer populates their requirements. They specify a location(s)
As most will specify more than 1 location, i need to separate the typeform data into line items
The lookup row uses a common field across the entire spreadsheet to ‘get’ the rows into the zap. But this is where all my agents exist
The code does some smart stuff to find the agents associated to the locations
This code de-duplciates
The loop iterates on all the agents in the sheet (this is why it all needs to be in one place)
Send email to the agents with the requirements. If it was separate sheets, the email would get fired each time where as now its nice and succinct.
Email back to the customer with summary of their requirements.
Page 1 / 1
Hi! Did you think of:
Changing the Lookup step for a “Get many rows” step
Replicate this block as much as needed: if you have 1,500-2,000 rows, make 4 of these blocks
Each time, change the “First Row” param (1, 501, 1001, 1501...)
Finally just patch the outputs together in your Code JS step. Just make sure to put a comma between each output, so the code will remain able to correctly turn this big string back into an array. Something like: agent_emails = {{GSheets_Step_1_Col_B}},{{GSheets_Step_2_Col_B}},{{GSheets_Step_3_Col_B}},{{GSheets_Step_4_Col_B}}
Hopefully this can help :)
@escalateur.com you legend, this worked!! It took a bit of effort to get the code right but once it worked, it worked perfectly.
Thank you so much!
My pleasure, thanks for letting me know
Awesome to see this solved—great teamwork! @escalateur.com, really appreciate you helping out here! 🧡
@jordan_nest, since you mentioned the code took a bit of effort to get it working, would you be open to sharing a screenshot (be sure to blur or remove any personal or sensitive information) of your final version? It could be really helpful for others who run into the same challenge. No pressure, just thought I’d ask!
My pleasure @SamB
// Retrieve input variables from the "input" object; default to empty arrays if undefined
let gsheetEmails1 = input.gsheetEmails1 || 1];
let gsheetEmails2 = input.gsheetEmails2 || 2];
let gsheetEmails3 = input.gsheetEmails3 || 3];
let gsheetLocations1 = input.gsheetLocations1 || 1];
let gsheetLocations2 = input.gsheetLocations2 || 2];
let gsheetLocations3 = input.gsheetLocations3 || 3];
// Ensure each input is an array (if not, wrap it in an array)
