If you use spreadsheets to help staff or team members collect/collate data for database entries, yes I know it’s bad but sadly everyone I know still deals with this somewhere in their workflow when managing websites for clients. Then this trick will vastly improve your productivity when creating said data entry spreadsheets or importing the data to your MySQL tables.
This works in Google Sheets, not Microsoft Excel, because Microsoft Excel is horrible and anyone who isn’t using cloud based office tools is crazy. This snippet will simply auto increment a field, typically you want this to be your “id” field as data is entered into rows in your sheet.
As data is entered in the row and more importantly the next row, this little function will increment the corresponding rows id field automatically. This will make it much much easier to import into MySQL via an import event.
This snippet needs to be placed in the first field of the first row that you want to be getting auto increment values for. Typically this is A1, or if you are using headings as MySQL table field references, than you can paste it into A2. Then your field A1 would/should be something like “id”.
Auto Increment With Column Headings
/* A2: When using field/column headings in your spreadsheet */ =ARRAYFORMULA(SEQUENCE(MATCH(2,1/(B2:B<>""),1),1,1,1))
Here is a screenshot of how and where it should be used.
Auto Increment Id Field Excel Googlesheet Mysql Database Import A2
Auto Increment Without Column Headings
Some people will obviously not want field/column headings. So here is the code and example for that as well. Assuming you want to use the very first field, column, row for id’s.
/* A1: When there are NO field or column headings in your spreadsheet */ =ARRAYFORMULA(sequence(match(1,1/(B:B<>""),1),1,1,1))
Auto Increment Id Field Excel Googlesheet Mysql Database Import A1
That should save anyone who uses spreadsheets to import data to MySql tables and databases a bunch of formatting time and remove some complexity to your import queries.
Enjoy 🙂
