3

I am using Google Forms to acquire "per-X" feedback, and want to dynamically produce customised reports that are filtered based on the URI.

What I am looking to achieve is a unique URI per X that allows them to view only feedback relevant to that X (from a READ ONLY sheet). Consider X to be departments or members of staff or products if that is helpful. Note that multiple (comma-seperated) Xs can exist per row in the table.

Filter Views

I know this can be done using a filter view (which introduces an fview parameter to the URI) and is very very close to what I would like to achieve with there being unique URIs without touching the underlying data.

I also like that the URI is obfuscated somewhat to limit the ability of other "X"s to read other "X"s reports, though as the fview parameter is an incrementing integer it is possible to "guess" at the other URIs.

My main concern with filter views is that assuming the scenario of >100 "X"s, it would be extremely time consuming to create all filter views manually and is not something I could advocate (mail merge makes it easy to distribute URIs en-masse). Additionally, it does not cater for cases of comma-seperated values holding multiple vaules.

Current Progress

What I am looking to achieve is something akin to the following, where F is the column containing a CSV list of X's that apply:

=FILTER('Form Responses'!A2:Z, IF(FIND(<<Value from URI>>, 'Form Responses'!F2:F), TRUE, FALSE))

The closest I have been able to get is as follows, but this relies on the value being entered into the sheet itself rather than there being part of the URI:

=FILTER('Form Responses'!A2:Z, IF(FIND($C$1, 'Form Responses'!F2:F) >= 0, TRUE, FALSE))

Can this be done?

  • 1
    There is a way to query spreadsheets via URI commands, but the result is returned as a JSON, so you'd need to write a webapp for its display. Staying withing Sheets, the best I can think of is to automatically generate either a separate sheet or a separate spreadsheet for each view, each of them querying the master sheet. – user79865 Dec 17 '15 at 3:24
1

Short answer

Google Sheets URL parameters doesn't include one to modify filter views nor to modify spreadsheet content.

Work arounds

One alternative is to use Table Chart from Google Charts. Some content that could be directly related:

Another alternative is to create a Web Application by using Google Apps Scripts.

References

Related questions in Stack Exchange Web Applications

Google Spreadsheets URL Syntax and Display Options?

Related question in Stack Overflow

https://stackoverflow.com/questions/23446449/google-sheet-embed-url-documentation

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.