How to Create a Sequential ID when Google Form Submitted to Google Sheets
Learn how to create a sequential ID for Google Form submissions in Google Sheets using Google Apps Script. This tutorial covers both methods: using a dedicated sheet and utilizing document properties.
In a previous video, I showed you how to add a unique id. When you have a form response submitted, we have just a simple form, just a name, and we want in this third column, a unique id. Well, that was the blast video. In this video, I'm gonna show you how to create a sequential id. So we have ID here In this third column, we wanna add an ID every time that we enter a name. So what we need to do is go to extensions app script . Over here we have this function , new id, and this is the old one that has this utilities docket, UID. And this is gonna give us a crazy sort of long, unique ID every single time, but we wanna just have a sequential Id. Make sure also before we start that you have a trigger here. Add trigger . Choose which function new ID from spreadsheet on form, submit, make Automate Form Response to Confirmation Email Generate Short Random Codes or IDs
sure that trigger is created. And that's so we can put in a name here. And we have a unique id, but again, we want to make a sequential ID instead. So let's go to our app script . Go to editor . I. And see what we can do. There are actually two ways to do this. We're going to create a new sheet called id and in a one I'm gonna put the number zero. This is, um, one way to do it, but I will show you another way. After this, so we'll have a zero and a one on id. Let's go here and we are not gonna get the id. It's going to be spreadsheet app dot get active spreadsheet , get sheet by name. Id get range A one and get value. This is id. Iterate Numbers with a Simple Apps Script Figure Out Frequency of Numbers Get Sheet ID and Tab GID Into Your Google Sheet Find Your Spreadsheet ID in Google Sheets
We need to set the value ID plus one, and then we're going to go back to the exact same range and. Share , uh, reset that value, set value to ID plus one command s to save this. So now our ID is getting the range of the row we're in, just so that we can set the value ID plus one, whatever the ID is in a one. It will add one to it and then. Set that value and then we're going to reset that value here. Id plus one, so let's see how that works. Let's go to submit another response. Let's put another name in. Andrew. Again, submit. Go back to our sheet and now it's one. And now on id. It's one. So let's see if it actually adds one each and every time Emily is submitting now. Iterate Numbers with a Simple Apps Script How to Auto Increment Numbers in Google Sheets
And then change it to two. There you go. That is one way to do it. . If you don't want this ID sheet here, we can save this in properties. So instead of ID is equal to this value, I'm gonna create a new one here. I'm gonna comment this out. Variable ID equals property service dot git. Now we have three options. We have document, script, or user properties . For this particular case, I'm gonna get the document properties and we're going to GI property. We're gonna end up creating one soon, but we're gonna call this. In single quotes form id, so this is what we would get, but if that's null or zero, we're gonna get nothing here. So if there is no ID or the idea is null , then I want to set property. Auto Increment Invoice Numbers So The Sheet Remembers!
So I'm gonna do set property. And we're going to set the same name, form id, and we're gonna put a zero there as the string again now this, and just do that. And then. Get our variable id, so we're setting it at zero and then we're getting it. If there is no id, if there is, or if it's null here. So this ID plus one is gonna be whatever is in this saved property, add one. But instead of setting the value to the sheet, we're going to go back here and set this. Value here to form ID to ID plus one save this and now let's add a sequential ID should be one. Let's do Frank Submit I zero one because I think it thinks it is a string . Show Sheet Tabs Based on Edit Auto Increment Invoice Numbers So The Sheet Remembers!
We need to wrap parse int parse int around here. And it'll be on a 10 to get the id, and that should change that. So let's try this again. Submit another response, Frank. Again. Click submit. And let's see, and we get one. And now let's do it again and see if we get two frankly again, yet again. Oh, we have to wrap it around this one as well up here. Parse in so that each time we get it, it will be, a number , not a string . So we're saving. Basically what's happening is that this property services saves anything as a string , and when we retrieve it, we need to change it to an integer. So let's try again, Linda. Submit Linda again. And there's Linda. And there's Linda again. So now we have our sequential id, and this will keep going, Linda, Auto Increment Invoice Numbers So The Sheet Remembers!
yet again, submit another one, mark. And they just keep going up and up and up sequentially. And you can set this sequence starting anywhere you want. If you want to set the property here. As not zero, maybe you wanna start it at 1000? Thanks for watching this video. Here's how to set a sequential id. We learned the property service here. Hope you enjoyed. Auto Increment Invoice Numbers So The Sheet Remembers!