0

I am aware of the manual way (add 1,2 in the first 2 rows, mark them, drag the square on the mark-outline down to where u need the numbers) and a number of ways to create the numbers with formula's, as described eg. here: How to auto increment a field

But i need a way to create the numbers automatically as a formula would do, but also need the created numbers to not change after they are created, when one row is deleted. (So it would behave like a auto-increment field in a SQL database) , so i think can not be a formula directly, but would need to be a formula that creates a static value.

Can this be done at all?

Without having an idea how to do it, i could imagine a script that cuts the value and pastes it instead of the formula...

Or, probably more practical, use the script from here: Autoincrement in Google Sheets based on Google Form submission

But what would the trigger be?

1 Answer 1

1

What about if you add the the sequential numbers in advance?

  1. Add as many rows as you will need.
  2. On A1, add =ROW()
  3. Copy A1
  4. Select column A
  5. Paste
  6. Copy (again)
  7. Paste as value only
  8. If you need to have headers on the first row, select A1, and insert row above it or insert a cell with the option shift cells down.

Regarding what trigger to use, there are many options

  1. Record the above as a macro and with slight changes the above procedure could start on the selected cell instead of doing that on A1, then the macro could be run from the macros menu or by using a keyboard shortcut.
  2. Run the script from the code editor
  3. Create a custom menu
  4. Use a Edit trigger
  5. Use a Change trigger
  6. Use a Open trigger
  7. Use a Form submit trigger
  8. Other

1, 2 and 3 are good options to run the script on demand

4, 5, 6 and 7 will run when the respective event occurs. Additionally it's very common to have to use conditional statements to limit when changes, in this case adding the next number to the certain cell, should be applied.

8 You might use add-ons and third party apps.

Resources

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.