Google spreadsheet reading parameters from its own URL

1
votes

I would like to open a Google Spreadsheet with a few parameters.

Can I add these few parameters in the URL to open the spreadsheet, and then in the spreadsheet load these into some cells?

The spreadsheets in question are privately shared, and the users using the URLs is granted permission to edit.

2
google-apps-scriptgoogle-sheets

2 Answers

3
votes

Sandy Good provided an interesting answer and that workflow is indeed the only possible one but there is a major issue in that a webapp deployed this way won't be able to "open" your spreadsheet in the common sense of "open", ie open in a browser window to work on it normally.

At least not without a user action like a click on a link that would open the sheet in a new tab. Finally you would have a 2 steps process:

  1. Launch the webapp via an URL with parameters that would write some values in the spreadsheet without seeing it and

  2. Open the spreadsheet using a link shown in step 1... retrieving the values that step 1 wrote in some cell

Very cumbersome and nothing would prevent users to go directly to the spreadsheet without using your special URL...

All in all , it would be useless.

So I'm afraid the real answer it simply "no, not possible"

2
votes

Here is a table of triggers that are available to the different Google Products:

Available Types of Triggers

In a Stand Alone app, you can run a doGet() function. The doGet() function runs automatically. There is an e.parameter option available to doGet(e) that will parse URL search string settings. But a doGet() function can't be added to a Sheet.

The URL parameters that are available are listed in this documentation:

URL Parameters

But, those URL Parameters are for Web Apps and Google Sites Gadgets, not for a Google Sheet.

You could have an intermediary Google Apps Script that you send the URL Request to, and then the Apps Script processes the URL Search String parameters, and then opens up the spreadsheet.

So, instead of using the Google Sheet URL to open the spreadsheet, you'd be using a URL of the deployed web app. Then the user would need to click a link to open the spreadsheet.

Once the web app parsed the URL Search string values, you would need to write some kind of script that did something like write those values into your spreadsheet using the Spreadsheet Service