Most of the time when we are calling in data we access a live google sheet, but there may be occasions where a google sheet, or part thereof, has been published and you want to grab the data from there.

Reasons:

You may have no access to the live google sheet but you have the published url

It is your google sheet, which, for the most part, you want to keep private, but there are some elements you have published

Another possible use case is as above, but you want to make a call to some data (e.g. a version number) without having to use a web app to get at it

Google sheets can be published to output in various ways, the normal being to a web page and you would see a url something like this:

https://docs.google.com/spreadsheets/d/e/2PACX-1vRSX_HGTUJ4owRT4DHUyfGwadIArcWHR0zpmCXTOhqCUihO/pubhtml?gid=1624&single=true

It is possible to modify such a url to generate output in csv, tsv, pdf, xlsx, and ods format as well. There are also other parameters available such as defining a range. We are interested in outputting a range to csv, this example will demonstrate such, with the range being set in the app.

We modify the above url to look like this:

https://docs.google.com/spreadsheets/d/e/2PACX-1vRSX_HGTUJ4owRT4DHUyfGwadIArcWHR0zpmCXTOhqCUihO/pub?gid=1624&single=true&output=csv

You will see I have removed the "html" from "pubhtml" and added "&output=csv" to the end. In the app we will add "&range=" and the range e.g. A2:A4, so we eventually end up with our complete url of:

https://docs.google.com/spreadsheets/d/e/2PACX-1vRSX_HGTUJ4owRT4DHUyfGwadIArcWHR0zpmCXTOhqCUihO/pub?gid=1624&single=true&output=csv&range=A2:A4

Calling this url with a web component will return the data in the range A2:A4, you can then choose to display it as is, or create a list to work with

BLOCKS