Search code examples
google-sheetsgoogle-sheets-api

How to get google sheets from this url that has "/e" in it (ultimate goal is to get the json)?

I'm trying to get the json from a googlesheet

the sheet is published at:

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

None of the examples I've seen for getting the google sheet key have a url with the "/e" in it.

The examples I've seen are of the form

https://spreadsheets.google.com/feeds/list/GOOGLE-SHEET-KEY/default/public/full?alt=json

I'm thinking the key should be "2PACX-1vQ7VXz3uE1SkR_7JyU5FdzLkdIvM_hUjUfHBSSS5xshCL0ZmmE_JVxc3rqI5p1sNGUZWS7SMVrlIcyf", but that doesn't work.

And I've tried using the stuff with and without the "/e", but none of them work for getting the json for the sheet.

Maybe something silly going on, so any help appreciated.

Solution

  • To get Google Sheet data as JSON you use the url:

    https://spreadsheets.google.com/feeds/list/GOOGLE-SHEET-KEY/od6/public/values?alt=json

    You can only get the Google Sheet Key if you have access to the document (i.e. If you are the creator or if you have a shareable link)

    When you publish the document to the web, you get a 'published to web' URL similar to this:

    enter image description here

    But having this URL doesn't give you the Google Sheet Key

    • If you are the creator of the spreadsheet, you simply have to go to the page where you edit your document.

    enter image description here

    In the URL of the page, the Google Sheet Key is present between:

    https://docs.google.com/spreadsheets/d/ and /edit#gid=0

    (#gid=< any number > ...varies with different sheets)

    • If you are not the creator of the spreadsheet you need to get the 'shareable link' URL obtained by clicking the 'Share button' and clicking 'Get shareable link'... That will give you the shareable link

    enter image description here

    In the link Google Sheet Key is present between:

    https://docs.google.com/spreadsheets/d/ and /edit?usp=sharing

    Note: Incase you get an error "We're sorry. This document is not published." on loading the JSON page, it means the document hasn't been published to the web.