Using chained select is a great way to convert an Excel file into a series of dropdowns that change depending on the user’s selections. Not sure what I mean? Check this video that explains it better than me.

This approach requires an Excel (or CSV) file you upload when designing the form. But with the Google Sheets add-on, you can go one step further and use a Google sheet instead. And the best part is that you can even configure the field to be automatically updated when the Sheet is modified!

To link a chained select to Google Drive follow these steps:

1.- Configure the Google sheet add-on as explained in steps 1-3 of this tutorial: https://allinoneforms.rednao.com/knowledge-base/configuring-the-google-sheet-add-on/

2.- Add a chained select field in your form and in the ‘Source’ setting Select ‘Google Sheet’

3.- Select a google sheet file and a google sheet tab(this will list all the Google sheets you had shared as explained in the tutorial of step 1)

And that’s it! Now the chained select will create the dropdowns using the selected Google sheet.

Sync your Google Sheet with your chained select automatically

If you want your chained select to be updated every time your Google sheet is modified, follow these steps:

1.- Check “Sync automatically”

2.- Click on “View trigger code” and copy the displayed code.

Important: Google needs to be able to contact your site and notify that an update is available. This means that you might need to do some additional network configuration to make it work in a localhost or vpn site.

3.- Open your Google sheet and go to Extensions/Apps Script

4.- A new window will be open. Replace the default code with the code that you copy in step 2.

5.- Click on save project

6.- In the left menu, click on triggers

7.- Click on “Add Trigger”.

8.- In the popup make sure the “Choose which function to run” is set to “atEdit” and the “Select event type” is set to “On edit”. Then click on save.

That’s it! Now the chained select will be changed when the Google sheet is updated.

To use the google sheet add-on and send your entries directly to a sheet you need to do these things:

1.- Getting your google sheet configuration file

2.- Making a google sheet accessible to the plugin

3.- Configure a form to work with google sheet

4.- Configuring a form to send the entries to a sheet

1.- Getting your google sheet configuration file

The first step is getting your google sheet configuration file, this is the most complex step but if you follow the step-by-step guide below you can get it without issues:

1.- Go to https://console.cloud.google.com/ (you will need a google account)

2.- Click on ‘Select a project and then on ‘New Project’

3.- Define a project name, it can be whatever you want. Then click on ‘Create’. You will be directed to a new page

4.- Make sure your project is selected in the top bar

5.- Go to API & Services/ Library

6.- Click on the Google Drive API

7.- And then click on “Enable”

8.- Do the same for the google sheet API

9.- Now go to API & Services / Credentials

10.- Click on “Create Credentials” / “Service Account”

11.- Define a service account name (any name is ok) and click on “Done”

12.- Click on the service account that was created

13.- Copy the email (this email will be used later)

14.- Go to keys and then click on “Add key” / “Create new key”

15.- Select “JSON” and click on “Create”

16.- After clicking create your browser will download a file with the extension “json”

2.- Making a google sheet accessible to the plugin

The second step is to configure a sheet or folder to be accessible to the plugin, to do so do the following steps:

1.- Go to your google drive account( https://drive.google.com/drive )

2.- Right click in a folder or sheet and click on share

3.- Paste the email that you copied in step 13 of the google sheet configuration and click in “Send”

3.- Configure a form to work with google sheet

Now that you have your configuration file and have access to some sheets in your account you just need to link a form to your Google sheet, to do so follow these steps

1.-Open a form (in the aio designer) and go to Settings/Google Sheets

2.- In the JSON Config box paste the content of the file that you downloaded in step 15 of the google sheet configuration part. (You can open this file in any text editor like notepad)

3.- Click on “Verify and save configuration”

4.- If everything was done correctly you will receive a success message.

4.- Configuring a form to send the entries to a sheet

1.- Check the “On submission send entry to google sheet” box

2.- Select a sheet (if you don’t find any is because you didn’t give access to any sheet/folder or the folder that you gave access to doesn’t have any sheet).

3.- Map the field in your form to columns in your google sheet. In the example below I mapped the columns b, c, and d to the fields year, brand, and model respectively (column a is always used to save the entry id).

That’s it! Now your entry will be automatically sent to google drive after a submission.