Passing URL parameter(s) to Google Sheets

Question

Is it possible to include parameters in a URL referencing a shared Google Sheet?

A typical URL will look like this: https://docs.google.com/spreadsheets/d/1c-sxi4cwZXUXTHJSU-Nzl1lAwPCs123l46UKQgABCp0/edit#gid=1455048586

What I'm trying to do is include a parameter, something like: https://docs.google.com/spreadsheets/d/1c-sxi4cwZXUXTHJSU-Nzl1lAwPCs123l46UKQgABCp0/edit#gid=1455048586?msg=Hello

I then would hope to retrieve the value of msg with the doGet(e) function which includes the following line, just to prove it works.

SpreadsheetApp.getUi().alert(e.parameter.msg);

If the following solution is the best alternative, I'll change course: Google spreadsheet reading parameters from its own URL

Surefire

I'm afraid the answer will be the same as in September 2014, you just can't add custom parameters to a spreadsheet URL since there is no way to retrieve it. – Genovese

Answer 1

Adding parameters to spreadsheet URL will not do anything for doGet function. The function doGet does not run when someone opens the spreadsheet. To use it, the script must be deployed as a web app, after which this web app is given its own URL, different from spreadsheet URL. To that URL you can add URL parameters:

https://script.google.com/macros/s/..../exec?msg=Hello

and then e.parameter.msg in doGet will indeed be "Hello", as described in URL parameters article.

Dowry

Got it, thanks! I now can use the values passed in the parameters to filter a google sheet. The only remaining issue relates to opening the sheet within this same script. Presently, I include a link which directs me to the sheet, but ideally, I'd like to open the sheet programatically. Can this be done? – Surefire

I think so; the page served by a web app can include client-side JavaScript that can open a new window, etc. See developers.google.com/apps-script/guides/html/best-practices (and this is really a separate question) – Dowry

Thanks very much for your help, I've got the script working just fine now! – Surefire

@ThomasAliberti do you mind sharing your code? I want to do something similar. Thanks in advance! – Indianapolis

So how do you pass and process those parameters? – Remde

Answer 2

You can do this with a form:

1. Create a form, link it to the spreadsheet, and add the questions representing your fields (you basically need just a correct number of question cause through url you can submit any data to the form and it will be passed just as text to the spreadsheet and only then processed (formatted) by the spreadsheet. You can pass any text values to any fields overcoming form data validation. Just make all your questions Short text or Long text if what)
2. Enable "Allow response editing" in the form settings
3. Submit any response, and click "Edit response"
4. Submit edited response again. This will keep the edit_2 part but convert viewform to formResponse
5. Copy the URL of that page. It will look like this:

https://docs.google.com/forms/d/e/formID/formResponse?edit2=some_edit_id

6. Then go to the form and get a pre-filled link. It will look like this:

https://docs.google.com/forms/d/e/formID/viewform?usp=pp_url&entry.1684891990=your_value

7. Add &entry.1684891990=your_value part from step 6 to the URL from step 5. Your result will look like this:

https://docs.google.com/forms/d/e/formID/formResponse?edit2=some_edit_id&usp=pp_url&entry.1684891990=your_value

This is the URL you can use to submit values to the spreadsheet

Remde