4

I need to create a unique ID upon submission of a form which will be used as a project number for tracking. The number needs to be unique but not based on rows or other changeable data because completed projects are kicked to an archive sheet where they will be on a different row and therefore will change both that project's number and the number of all other projects below it on the spreadsheet.

I found the answer to Can I add an autoincrement field to a Google Spreadsheet based on a Google Form?, but the script is giving me errors, so maybe I need some advice on making it work.

I'm a very beginner user with no script writing experience but I cannot find a formula that doesn't rely on rows or otherwise would retain a number throughout different sheets. Any help is appreciated!

  • Hi Kelly, welcome to web apps. Could you please include a link to a sample sheet in your question for us to look at. – Tom Horwood Oct 21 '13 at 22:41
1

The problem is in the first line of the script. Your form ID is the special ID that identifies the form. You can see it in the address of the form (it's the bit in bold)

docs.google.com/forms/d/17rxZ5vzy0OcxibXV6aRpso7Xm_Vs0FpxMLgbchjO998/viewform.

You accidentally used the form name instead.

That being said, I should have just used the Spreadsheet.getFormUrl() in the original answer. I'll change that now, then you won't have to worry about the ID at all.

  • Ha! thanks, that's very helpful. I used the updated script and get this error: TypeError: Cannot read property "values" from undefined (line 9, file "Code"). The line that it is highlighting is: var uniqueID = getUniqueID(e.values); – Kelly Jensen Oct 23 '13 at 13:28
  • How are you running the script? You need to specifically set a trigger for On form submit on the actual spreadsheet, not the form. – Tom Horwood Oct 24 '13 at 3:27
  • It is set up on the spreadsheet as a trigger for On form submit but so far has not generated any numbers although it is no longer generating any errors. I'm not really sure how what is happening. Thank you for your continued help! – Kelly Jensen Oct 24 '13 at 16:01
  • Actually, continuing to have a values error just showing up on the tab. – Kelly Jensen Oct 24 '13 at 16:05
  • If there is any way to do a paste values only script, that would also solve my problem because I can run an array formula on the spreadsheet that accepts the form to generate a number, then have that info pasted into a spreadsheet and not have to worry about deleting rows since that form spreadsheet wouldn't need to be touched. – Kelly Jensen Oct 25 '13 at 19:42
1

If your form is not being submitted by two people at the same time, you could consider using the current time as the unique ID.

Here is the JavaScript to get the current time in milliseconds (since 1970):

var d = new Date();
var n = d.getTime(); // e.g. n = 1382489068127

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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