When creating a new row in Google Sheets, how do I get it to add the next number in a sequence in a specific column?

When a new order comes through from Shopify I need to add it to a Google Sheet as we have to send a sales report CSV every day to our regulatory body

One of the details we need to send is a Service ID number

Basically, what I want to do is give a customer the next number on the list (i.e. if they buy 1 product they get the next number, and if they buy 4 products they get the next 4 numbers)

How do I get the Zap (or a setting natively within google sheets) to add the next number in a column when a new row is added (i.e. 002 after 001 etc.)

I’m assuming I need to do this in a create row zap and not an update row zap, as I can’t see how an update row zap would do it dynamically (i.e. update the next row)

Also, is Google Sheets even the right product to be doing this in?  Should I be using something like Airtable for example?

Hope that makes sense

Thanks

RJSP