Increasing order number in Google Sheets

​
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]*)([0-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!