Google-sheets – How to implement a script that creates a unique ID for a Google form submission

google sheetsgoogle-apps-scriptgoogle-forms

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!

Best Answer

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.

Related Solutions

Google Forms – Auto Increment ID

Just replace eventRange.getLastColum() + 1 with a number. Column A is 1, B is 2 etc.

So if you want the ID to be placed in column C, change the code to

var column = 3;

Google Sheets – Sort Function Moves Data to Bottom

Cells containing the empty string "" appear empty but are not (isblank returns FALSE for them). They get sorted ahead of nonempty strings, which is not what you want. To avoid this, replace

if(isblank(B1:B), "", datevalue(B1:B))

by the simpler

iferror(datevalue(B1:B))

The command iferror returns its (optional) second argument if there is an error evaluating the first argument; otherwise it leaves the cell blank. Blank cells are sorted to the bottom.

A potential drawback of the above is that misformatted dates are simply ignored, while you may want to see and fix them. This is something you can address separately; e.g., have a column with

if(isblank(B1:B), 0, if(iserror(datevalue(B1:B), 1, 0))

and sum over it to get the total number of nonempty but invalid date cells.

Another potential issue is that you are sorting a range containing arrayformula. The sort may move the formula to another row, creating a mess. It's safer to exclude the header row from sorting. Replacing

var data = JobList.getDataRange();

var data = JobList.getDataRange().offset(1,0);

would do it.

Best Answer

Related Solutions

Google Forms – Auto Increment ID

Google Sheets – Sort Function Moves Data to Bottom

Related Topic