3

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?

Improve this question
3
  • 1
    Hi Rachael and welcome on Web Applications !! Would you care to share the formula with us and the error message, so that we can have a better understanding of what's happening.
    – Jacob Jan Tuinstra
    Jun 12, 2015 at 14:25
  • @Rachel The URLs aren't to public files. Consider to create a demo of what you are trying without any sensitive data and share it with only with who has the link or public with the web or add screenshots.
    – Rubén ♦
    Jun 12, 2015 at 15:49
  • 1
    Ah, my apologies. I didn't think about the privacy settings when using Google Apps through my school account. I've included a screen shot for the second link above, but won't be able to create a public copy of the document until I can access my home computer later tonight.
    – Rachael
    Jun 12, 2015 at 16:03

1 Answer 1

Reset to default
5

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)

Improve this answer

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.