Google Forms - Drop Down List from Spreadsheet Using Apps Script

alright so in this video I'm gonna tackle a problem that a few people asked me to do basically I'm gonna try to populate Google Forms drop-down box using data from Google sheets now this is not something I usually do honestly so if I have a situation like this I would use a web app but I'm gonna tackle this and hopefully that will work out for you right so the reason I don't really do this in Google sheets and forums is because there is really no great way of doing this but anyhow I'm gonna show you how you could do something like this so what I'm gonna do I have a blank spreadsheet right here I'm gonna go to Google Forms and make a new brand new Google Form this is my form I'm gonna go here and rename this I'm gonna say data from SS from spreadsheet it entered that pretty much saved that now in this form basically we're gonna have a drop-down box so I'm gonna add a new question and then this question is gonna be a drop-down so we're gonna start with a drop-down option I'm gonna do drop-down and here we're gonna select what the question is so the question is gonna be what is your favorite fruit and we'll do some options so we'll do like apple orange banana anyways the whole point is that we don't want to type this we want to somehow get this data from here from our spreadsheet so frankly I'm just gonna remove all that stuff so now I want to be able to just update this list using the information in our spreadsheet so I'm gonna go here let's rename the spreadsheet I'm gonna call this form data maybe we'll spell it right although that makes absolutely no difference so a couple of things I'm gonna have to do number one I'm gonna have to decide how do I write the script so we can either create a script on Google Forms side or we can create this on Google Spreadsheets side so I'm gonna make that on Google sheets side now one thing I'm going to do what I'm gonna just copy exactly this question and I'm gonna paste it as a column label right here and here I'm gonna type the options that I'm gonna provide apples bananas oranges etc maybe I'll make that bold but that doesn't matter rename this tab to data so I'm gonna do tools script editor from the spreadsheet side this is my script I'm gonna rename this populate form again this names don't make any difference whatsoever it's just a name for the project save that now there are a couple of things I need to do I need to get the ID for my spreadsheet and the ID for my form so I'm gonna create a couple of global variables here I'm gonna do var SS ID that will be our spreadsheet ID and it will just be text and then I'm gonna do var form ID and that's also gonna be text so let me go get those IDs so the spreadsheet ID will be right here so I'm gonna copy the whole link then I'm gonna change it a little bit so I'm gonna paste that link over here so the ID is really not the whole thing so it's what's after this like D slash thing C so I'm gonna remove all that part and then I'm gonna also remove this slash the rest of the stuff so that's the ID for my spreadsheet let me go grab the ID for my form go back to this paste the ID get rid of this stuff in here and then just go ahead and remove this stuff in here as well so I got the ID for my form ID for my spreadsheet now I'm gonna try to open those too so I'll do a couple of variables here I'll do VAR WS so data so that's our data worksheet and I'll open my spreadsheet spreadsheet app dot so we're gonna do open and we're gonna open by ID the ID is this SSID and in that spreadsheet I'm gonna do get sheet by name the worksheet that I'm gonna open is this one called data I'm gonna copy that go here and use that in codes so that's my worksheet name now I'm gonna create another variable that will be form and we're gonna use form app we're gonna open by ID again the ID is our form ID so that should open our for now let's go inside of our function and see what we're gonna do with these things now first of all I want to just figure out how do I update this drop-down list options from here before even I get to my spreadsheet so I'm gonna go to this form that's the variable dot and in this form let's see what we have see we have all of this like add item and all this delete get all this get methods see there's this get ID and there is get item by ID now we have to get the item by its ID so if the items basically in a form are pretty much everything in the form it could be in drop down it can be something else so we need to figure out which one it is so to figure out which one it is first of all let's just instead of figuring out let's just do the regular method let's just say get item by ID and let's just do one so we'll do far item equals to that so let's try to just log that and see what we get so logger.log item save that let's try to run this so we're gonna run this it will ask us for permissions so we'll review those and give permissions allow let's view our logs so it says null so basically we got nothing as the item let's try to do ID zero so look at a log again now so what if we do ID 2 so it seems like we got nothing so I'm gonna have to troubleshoot this to see what's going on and just get items to see if we get anything in return so we'll do VAR items it cools that and then I'll log that out items so out of curiosity are you getting anything so log and see we got now then we got item item so we do have two items probably the ID is just not one or two or three so we have to figure out what that ID is now if we do item zero that will be the first item in the first item I get ID that will give us what the IDS so I'm gonna run that apparently that was the ID and if we do the second one which is probably our drop-down actually so one because in a write a second one is number one log this will be the number so assuming that that's a very big number it gives us that integer with that weird notation so I'm gonna make a very quick text out of this so I'm gonna do two string so that's the item ID copy it and go back and change this to that ID of the item remove this stuff and let's now lock this again so I'm gonna run this you view the locks and now it's the item now for this item I'm gonna do get title so I'm gonna run this look at our logs and oh so that was not the right ID so that was actually this one apparently I needed the first one so I'm gonna have to go back and get my items and find out what's the ID for the first item and then we'll come back to this later on so run that the log that's the ID go back and update this I'm gonna comment these lines for now I don't want to use them so hopefully now when I run this we'll see that the title is gonna be well the right one what is your favorite fruit which is actually the one we need to update we want to update this one cool now if I do this get title I get the question if I do get type that will most likely be the type of this so this is a list apparently that's good now I'm gonna do this see when you do dot now because it's a list it has to be this as list item so I'm gonna do as list item and then we're gonna do dot and now see these are some things we can do with this so we can get some things you can get ID but more importantly I want to do this set stuff see set choice values and then it says string and that's what I'm gonna do I'm gonna do set choice values and then it's gonna be values and it seems like it's an array I don't need to log this anymore and remove all that log stuff now I need this values so that values is going to be an array let's actually create that on a separate so I'm gonna do var values and it's just a variable name you can call this anything just whatever you call it needs to go here too so that's gonna be values and it's an array so array basically is a comma separated list of stuff I'm gonna say apples oranges Tomatoes those three so those are gonna be my values I'm gonna try to set those values here let's run this and see what happens so I'm gonna run this no errors let's go look at our form look at that we just updated our form with those three veils apples oranges and tomatoes now I want to see if I had to change this list do I need to do anything else other than just provided a different array and run the same thing so I'm gonna remove for example apples and leave oranges and tomatoes let's run this again and see what happens and cool that updates no problems so now we figured out how this is gonna work so basically what I need to do I need to provide the ID of the thing I'm updating and I need to provide an array of values so let's convert that to a function so we can use it right so I'm gonna copy it go down here paste and I'm gonna create a function I'm gonna call this update drop-down and for that we're gonna need ID and we're gonna need values which is the array so ID is gonna be this and the values is gonna be the array like this and this we don't need good so now I'm gonna remove all of that stuff and now we need to figure out how do we get the ID so the way we're gonna get the ID is by using the question name and the question is what is your favorite fruit so I'm gonna copy that go back here and first let's save it in a variable var title equals to that question so we're going to find the items I d using the question if you remember when I did this items it gets me the list of items and then what it is it basically it's an array of items so what I can do because it's an array I can basically just iterate through that array and run through every single one of that so I'm gonna do items dot map so what I'm gonna do I'm gonna map and I'm gonna show you why I'm doing this so I'm gonna map that array to create another array and I'm gonna save the result in a new variable I'm gonna call this variable titles plural so that's gonna hold all the titles and you'll see what that means so I'm gonna create a function here that's gonna be our callback for this array if you don't understand this map all this array methods I have a lot of videos covering all those or ray methods it's a whole list of videos it's not something I can really color within a few seconds so if you want to understand those in depth watch those videos otherwise you can just write the same code so this function is gonna accept the actual item and each item in a list remember we had those things like get titles get this get that that's pretty much what we're gonna have so we're gonna do items and see we have all of this stuff so each item whatever that item is and we can do get title for that item so I'm just gonna return the title of the item like this and now if I just log the titles let's see what happens so I'm gonna run this function I'm gonna look at the log and see what it is it's basically a list of all the titles we have so we have this is what is your favorite fruit this is the second one which is this untitled question good so now if we search in this titles to find this question we should be able to find which item is the right item and the way we can do that we can take this titles which is an array and do this index off method and by the way this index off is case sensitive so we need to make sure that this is exactly the same with upper case lower case everything matches so we're gonna search for that title in this so if I run this view the log see it says 0 0 means it's in a first position and it was it was the first one and it was the second one good so we have this that's gonna give us the position of the title that we're after because we have the position we should be able to go so let's create variable position so that position is going to be the position of this title that we're looking for now once we found a position then we can pick up that item from this items list in that position so our actual item should be equal to items array and in that items array we will pick the position that we found in this list so that will give us the actual item and this item is gonna be this drop-down now once we have the drop-down we're gonna do that item dot and we're gonna do as list item because well it's a drop-down we probably don't need to do that because we kind of do it here so I probably don't even have to do as list items in this case I could just do get ID because that's really what I'm after I want to find what's the ID for that item so I'll do var item ID equals to that that will get us the ID and now that we have the ID we basically need to have the ID remember here and we need the array to set the array which means that now if I do some values here and I'll do an array so I'll just do something simple something like that we have our values we should be able to run this update drop-down function to update the list and we need to provide that the item ID which we were able to find using the title of the element and the values which is gonna be this array so save this run it go back now we have this updated so that works cool so now basically I just need this values and I need the title I'm gonna cut that move this up put this on top here because nowhere else I'm using those values so I'll just do my variables here on top and what I'm gonna do now I'm just gonna convert this to a function again so I'm gonna create this function this is gonna be update drop-down using title that's the drop-down let's be very specific update drop-down using title so now we're gonna accept a title and we're gonna accept a a ray of values and then I'm gonna get rid of all of those so now basically I converted that to a function to this is gonna be my second function moving through the next one so I'm gonna do a new function this is the function where I'm gonna now start getting this data from the spreadsheet I'm putting that in this list so I'm gonna do a function main and in this main function I want to first go to that spreadsheet this one so I have already this worksheet data that's excellent so I'm gonna get the data from here so the first thing I want to do so my thinking is I want to create this not to work for just one thing I one is to work for multiple things if necessary so if we have multiple dropdowns I want to just add another column for the next question so actually let me do something like that let's make another one here say another question and that's gonna be a drop down to and I'm gonna copy that question go here paste it there make it bold and then have some options here cool so I have the options for that question so my first thing is gonna be I'm gonna go to the spreadsheet I'm gonna get all of these labels from this first row and then find how many questions we have and need to update right so I'm gonna go here and do get range so in that worksheet we're gonna get a range and that range is basically going to be my entire first row that is gonna start Row 1 column 1 and then the number of rows is gonna be 1 and the number of columns is gonna be if I just take the data and do get last column that should give me the column and then we're gonna read this to an array so we're gonna do get values and we're gonna save it to a variable I'm gonna save for labels let's just log this out to see what this looks like so far so labels I'm gonna run that function main let's take a look so as you can see it's an array of a race so it's an array that has an array which is basically those labels now to make sure I just get one array I'm gonna get the 0 position and actually I'm just not gonna do it here I'll just do it right here in the end so this way we're not gonna get to a raise we'll just get one array that has the list of items so we have an array that has the first question and the second question cool so we have our questions now I'm gonna loop through that array so I'm gonna do labels dot and I'm gonna do for each look I'm gonna do a callback function and that's gonna end here and this function is gonna accept label each time so each label that's gonna be basically first time this than this so what we need to do when we get the label for the first one we need to go and get all these values for that label so first of all let's just log out the label and we also need to log at the same time as we lock the label all the values over here and the way we're gonna do that we need to first figure out which column this information is coming in so from so but that we're gonna do actually by adding the second argument here so I'm gonna do this argument I or yeah is fine so I'm gonna do this I so you can see what happens with that I so this function callback actually accepts a secondary optional argument which is the iterator number or index whatever you're gonna call this so let me just run this so you can see what it looks like so now I'm logging the label and I'm logging the I let's see what that is see it says that's the first question that's the label then we get 0 that's the first one then another question we get 1 then it's gonna do 2 3 4 so that is gonna give us some sort of position now because that I is a position I can now use that in my advantage so I'm gonna do WS data and get range and the range is gonna start so this is where I'm gonna grab this I'm gonna get it from the second row first column in this case now that first column is going to be first column only when it's the first question then it's gonna be the second column but remember the iterator is gonna give us 0 1 2 what I need is 1 2 3 so what I'm gonna do I'm gonna do I plus 1 so I is gonna be 0 plus 1 is the first column that's gonna give us that then we need the number of rows we need to go down so in this case it's gonna be these 3 down so which I could do many different ways I guess but what I'm gonna do I'm just gonna get the last column for now so I'm gonna do that and I'm gonna do get last actually less row of the whole worksheet now keep in mind that that's gonna be for right now even for this one but that's fine we'll leave that so that's gonna be the last row we need to do minus 1 because well it's gonna be this minus 1 to get to 1 less than that although I don't think that's important at this point so we're gonna do minus 1 and then number of columns is just gonna be 1 so we're gonna take that and we're gonna get values let's just take a look and see what this looks like so I'm gonna do VAR options equals to that I'm gonna zoom out a little bit let's just log those options let's see what it looks like so logger.log options and I'm gonna run this let's take a quick look so see it says what is your favorite question then it's basically apples bananas oranges and it's an array of a race which we will have to fix and then another question we have yes no and we have this third one because we got three anyways because we got the last row now first I want to make sure this is not an array of a race it's just an array so it should be just Apple bananas oranges without this bracket so for that we will just take this get values and we'll map it to a new array and we'll do a function call back and for each oh that will stand for option we're gonna return the Oh in position zero like this now I'm gonna start moving things to new lines because this is getting too long and it's hard to read so I'm gonna do like this I'm gonna do get values we're gonna map it that's the same I just want you to be able to see this so and then we're gonna return the option at position zero so let's just save and log this out to see what this looks like so you can see it's pretty much the same now we don't have array of a race it's just basically an array without values in it we still have yes/no and this empty thing in the end I'm gonna ignore it for now and try to just use this information to build my form stuff in here so basically if you remember I made this function and this function what it needs is the title which I'm getting right here and it needs a values array which I have right here so I should be able to use that in my advantage here so let me actually remove that log and remove this log and instead let's just replace it with our function right here and we'll pass the title will be the label in this case and values will be the options so let's run this code I want to see what happens with that blank extra thing in the end I don't know if this still here this blank will it actually handle it automatically or do we have to clean those up that's what I'm curious about so I'm gonna run this let's go check it out so apparently it did add that third option as a blank which we don't want but it did work so we have apples bananas oranges we have yes to know for another question which is that let's try to now fix that situation with our options so I'm gonna comment this line that does the forum thing so what do we need to do we need to get rid of all the blanks out of our options lists so that means after the map I'm gonna add another filter here and in my filter I'm basically going to again have a callback function and for each option will only return the ones that are not equal to blank something like that and I'll do I guess strict so what happened there of course I forgot to use the variable let's run this Oh actually I should have probably done that so that should remove everything that's blank I'm gonna save it run it let's go back and check it out see we got yes or no without that blank extra thing and we have apples oranges and bananas now let's go back and change stuff here pineapples grapes berries I don't know whatever that's gonna be yes no maybe something like that so now let's go and run the same thing go back to this see we got all of those cool now we got all of these so that updates our dropdowns just fine that's good that works possibly the only thing we should do is do some error hand link in case we don't find maybe the question because right now if we accidentally type something that we don't find in a list or maybe go here and type new question which really doesn't exist let's see if we get an error which I assume we do there it is see we go here and we try to update basically using that title and what happens is that we are not able to find that item with that name and we get this error and at this point what's gonna happen when we don't find it and we do this index of this position is gonna be negative one when you don't find something it's a negative one and when it's negative one position we probably don't want to do all this stuff below so we just want to ignore it so we'll say let's add an if statement we'll say if that position is equal to negative one so we'll just say if it's not equal to negative one then let's do this so we're gonna run all of this stuff below only when we find that title I'm gonna tap this in maybe move it to the same line is this stuff there it is so now we have a little bit of error handling so we're gonna say if it's not found then we're gonna just ignore it so if I save this I should be able to have this extra stuff here that's not relevant so I'll just change this a little bit so we see some updates something like this I'm gonna run this again and again I was running that main function so it was on it so let's go back here take a look grapes berries oranges pineapples see now I don't have to deal with that error message because we just ignore it if it's not in the list cool so that's our drop-down updater the only thing I'm gonna do here I'm gonna add a button for somebody to click on to update the records so I'm gonna go here do insert and drawing we're gonna add a little text box I'm gonna say update form we'll do some colors and stuff I'm not gonna spend too much time on this I'm sure you can do this better than I can so I'll just do some background color on this that's good enough for me I'm not gonna make it - pretty good so that's that save and close that's the button now I'm gonna assign this button to this main function so I'm gonna click on this click on that assign script and then the name of the function is main okay that's that so now every time I want to update something I'm just gonna go here pineapples oranges etc I'm gonna add blueberries hopefully I didn't miss type anything if I did that's fine I'm gonna run this go back to my eye form there it is blueberries and all this stuff so there you go that's something that will work it's definitely not the most efficient way of doing this because every time I run this function I go and iterate for each item through the entire list here in the form and find the stuff definitely not the most optimal way but for something small this would work and it should be fine thanks for watching please subscribe and I'll see you next one

Channel: Learn Google Sheets & Excel Spreadsheets

Views: 376,067

Keywords: Google Forms, Drop Down, List, Spreadsheet, sheets, Script, Apps Script, tutorial

Id: o3AL7ASI_cA

Length: 34min 38sec (2078 seconds)

Published: Fri May 10 2019