We have a challenge with our online bookings form which integrates with Zapier and we hope the community can help with this.
we receive bookings via phone calls, emails and online booking forms.
All bookings are recorded in Google Sheets
Each booking is assigned a unique booking ID which is alphanumeric eg. ABC230101
We would like all bookings received from the online booking forms to automatically generate the next booking ID in the sequence i.e. ABC230102 and populate Column B in our Google Sheet.
We’ve looked at Formatter and Storage by Zapier however, none of these are working for us.
With Formatter, we tried the INDIRECT function from Excel/Google Sheets however, it seems this is an unknown function for Zapier.
Any help in resolving this would be much appreciated.
Page 1 / 1
Hey there, @MGL_Dev! I can see you were able to open a ticket with my teammates in support and wanted to share their recommendation in case it’s helpful:
Looking at your Zap, you need to increment the id number every time the Zap runs. Storage by Zapier would be possible for this.
You can use the Increment Value of Storage by Zapier and map it on the Order ID field. The MGL will be static, and you’ll just map the dynamic data from the Storage by Zapier.
Hi @MGL_Dev!This is a bit of a tricky one, since you have multiple sources for bookings!
I’m afraid Storage won’t work because the reference number consists of numbers AND letters. Increment only works on numbers.
Off the top of my head, here’s an idea.
Assuming your sheet will not be sorted/filtered and the bottom-most row will always be the one you want to increment, you could do something like this:
You trigger on the online form being submitted
You find the latest row in the spreadsheet (the best way to do this is to include a column that always contains the same value, so you can search for it bottom-up on the sheet)
See what that reference number was, then add 1 to it
Create the new row from the form submission, with the reference number being one higher than the previous one
Now, the details of how to accomplish this are where it gets a bit more complicated. A code step would be ideal to increment the reference number by 1 since you could do it all in one step (alas, Javascript and Python are not my forte).
Outside of that you could use a Regex step to separate the letters from the number, then a Perform Math Operation step (in the Formatter app) to increment it, then re-construct the pieces for your new row.
This method relies on there always being the same value in your Find By column:
You could create a separate Zap that triggers on new rows then updates your Find By column, to be 100% sure it’s filled out.
To separate the letters from the numbers (you would map the field from Google Sheets that you found in your search):
I used this: ( a-zA-Z]*)(d0-9]*)
Then you increment just the number by 1:
Then you bring them both together to create your new row:
I’ll leave it at that for now, since I know it’s a lot to process for something that sounds like it shouldn’t be too difficult. Again, a code step would be best if you know anyone who could help you combine my RegEx and Formatter steps above into a single step.
Hope this helps!
Thanks both, @christina.d and @nicksimard
Will be giving it a shot in the morning and feedback.
