0

I've been building a sheet for work and I want an auto incremented ID, which I can later use to FILTER or VLOOKUP the results back into a dashboard.

I've found a "tutorial" online, regarding auto increments and form responses. Tutorial from FormFuze

Basically it comes down to the following code:

function incrementID(){
  const ss = SpreadsheetApp.openById('##')
  const sheet = ss.getSheetByName('CASUS_DB')

  const lastRow = sheet.getLastRow()
  const previousRow = lastRow === 0 ? 0 : lastRow -1
  const lastAutoIncrementID = sheet.getRange(previousRow, 1).getValue() || 0

  sheet.getRange(lastRow, 1).setValue(lastAutoIncrementID + 1)
}

Now there are 2 problems. First it doesn't add a new number AFTER the last one, but keeps repeating the samen line over and over. Which is strange, because everytime I run it, it should get the last row, which should be 1 more than the last time it ran and add 1.

I've managed to "fix" the code, but since I'm learning sheets for work, I want to make sure I did it correctly and I want to understand why the first didn't work, since it came from a tutorial.

Also if people have better solutions, let me know!

"The fixed code"

function incrementID(){
  const ss = SpreadsheetApp.openById('1-pI5xFMAGnN7XcJ5R2fEUzDnlAROghWZOXPCf272X9M')
  const sheet = ss.getSheetByName('CASUS_DB')

  const lastRow = sheet.getLastRow()
  const previousRow = lastRow === 0 ? 0 : lastRow
  const lastAutoIncrementID = sheet.getRange(previousRow, 1).getValue() || 0

  sheet.getRange(lastRow+1, 1).setValue(lastAutoIncrementID + 1)
}

Thanks in advance!

Jan de Pauw
  • 1

1 Answers1

0

You could try with a formula in A2 that makes a sequence of numbers according to the values of Column B (you can change it to another column that always needs to be filled):

=SEQUENCE(COUNTA(B2:B))

(PS: it works as an array, delete any previous value of A)

Martín
  • 7,849
  • 2
  • 3
  • 13