Google Sheets Links – How to Create a URL for a Specific Cell in Google Sheets

google sheets

I am a teacher who is trying to create QR codes for book reviews to be placed on books in the library. I have collected all of my students' book reviews via a Google Form, so that their answers are on a Google Sheets (one column, about 100 rows). Instead of creating a QR code from the reviews and limiting the characters for the length, I'd rather have each review be a separate URL and use that address to create the QR code instead. Instead of creating 100+ different web pages or sheets in Google, if there was a way to create a link directly to a specific cell in the sheet that would be incredibly helpful.

I tried to use the query formula (limit and offset) after publishing the web page and also not publishing it, but I get an error each time.
I tried adding the range/cell using this URL: https://docs.google.com/a/apsva.us/spreadsheets/d/1agxRLfypqjknOHPGPq_zRqJNvSRso-0b8XOH3P67a1E/pubhtml?gid=0&single=true&range=A1

but it comes up with the error page saying:

Sorry, the file you have requested does not exist.

Please check the address and try again.

I'm not sure if I typed it in wrong or what.

The code I used originally was: /gviz/tq?tq=limit 1 offset 32

It actually is working today to give me the limited row, but there are a lot of extraneous characters included (this is the actual link: https://docs.google.com/a/apsva.us/spreadsheets/d/1agxRLfypqjknOHPGPq_zRqJNvSRso-0b8XOH3P67a1E/gviz/tq?tq=limit%201%20offset%2032)

Screen Shot of above URL

Is there a way (or an easier way) to do this?

Is there any way to keep the formatting of the Google Sheet?

Best Answer

Google Query Language will return a JSON string. To get the cell value and format as you see in the spreadsheet, instead of /gviz/tq?tq= and the query string, use /pubhtml?gid= and the "right" parameters.

The easiest way is to get the URL resulted of publishing the spreadsheet to the web. Then...

Add range=A1 to the URL of the published spreadsheet, where A1 is the cell that you want to display.

I.E. for https://docs.google.com/spreadsheets/d/1NIIqpRSYCXT0OlvDgXa0g4GVxmrnvQOSn_L-HcgPqVA/pubhtml?gid=0&single=true

to display only the cell B13, the resulting URL is:
https://docs.google.com/spreadsheets/d/1NIIqpRSYCXT0OlvDgXa0g4GVxmrnvQOSn_L-HcgPqVA/pubhtml?gid=0&single=true&range=B13

to remove the header, add chrome=false:

https://docs.google.com/spreadsheets/d/1NIIqpRSYCXT0OlvDgXa0g4GVxmrnvQOSn_L-HcgPqVA/pubhtml?gid=0&single=true&range=B13&chrome=false

Note:
Use ? before the first parameter.
Use & when adding the second and subsequent parameters.

References
Publish a document, spreadsheet, presentation, or drawing - Docs editors Help
Cómo publicar en Web un rango - Ejemplos para los foros (How to publish a range in the Web - Examples for forums, self citation)

Related Topic