0

I'm developing an Atom package that needs to read all data from a public readonly spreadsheet in Google Sheets, but I'm unsure about what to do.

I know I can use OAuth or an API key, but there are problems with each of these options. For OAuth, I need to setup an URI handler, and even though Atom has an URI handler (atom://package-name), it's not easily available in Linux, and Google only allows https:// handlers. Now, about the API key, it can be used for testing, but it can't be published within the source code, which is necessary no publish the package.

So what to do? Just emphasizing that the only thing that needs to be accessed is a public readonly spreadsheet, so I guess no special access rights are required.

PiFace
  • 526
  • 3
  • 19
  • 1
    Although I'm not sure whether I could correctly understand about your situation, how about using the web publish? The permission is read only. When Spreadsheet is published to Web, the URL like `https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml` is retrieved. By modifying this like `https://docs.google.com/spreadsheets/d/e/2PACX-###/pub?gid=0&output=csv`, you can directly retrieve the data without using the API key and access token as the CSV data. But if you cannot publish the Spreadsheet to web, this method cannot be used. At that time, I have to apologize for my unuseful proposal. – Tanaike Apr 02 '20 at 00:57
  • 1
    Is your sheet public? Would you mind downloading it as a csv and then reading from it ? Then, this [other Stack Overflow answer](https://stackoverflow.com/a/55720172/12835757) might help. Otherwise, if you just want to retrieve the data and **the sheet is public** then [this tutorial](https://www.labnol.org/code/20004-google-spreadsheets-json) might help you. Please, let me know if your sheet is public as that makes things way easier, otherwise I believe there are workarounds. – Mateo Randwolf Apr 02 '20 at 09:11
  • 1
    Yes, as stated in the question, the sheet is public (public and readonly). I'll try what you both suggested and comment here. – PiFace Apr 02 '20 at 17:18
  • 1
    So, the tutorial did work, it's basically a more detailed version of the first comment. Post it as an answer so I can mark it as accepted :) – PiFace Apr 02 '20 at 18:48
  • That sounds great, thanks ! – Mateo Randwolf Apr 06 '20 at 14:27

2 Answers2

1

Solution

Public sheets do not require the use of the Google Sheets API to extract their json infromation from them nor they require the use of oAuth 2. You can simply achieve this by using a json GET request to them.

To get the json data from a public sheet you can use jQuery: 

$.getJSON(SHEET URL, function(data) {
  //data is the JSON string
});

Here is a tutorial with more information about how to achive this.

If you do not want to get the json with jQuery you can also use plain JavaScript as shown in this Stack Overflow question.

NOTE : This will only work if your sheet is public. To achieve this go to the sharing permissions and make it Public. You also will need to publish the sheet so that you can access the json data. Go to File -> Publish to the web -> Publish.

Mateo Randwolf
  • 2,823
  • 1
  • 6
  • 17
  • This approach no longer works and shows message "The Sheets v3 API has been turned down. Further information can be found at: https://cloud.google.com/blog/products/g-suite/migrate-your-apps-use-latest-sheets-api" – Chirag Oct 14 '21 at 02:42
0

If you've enabled "Anyone with the link" sharing and the currently active sheet is the one you want to export, the url in the address bar can be transformed from this:

  • https://docs.google.com/spreadsheets/d/UNIQUE_KEY_HERE/edit#gid=UNIQUE_SHEET_ID

To this:

  • https://docs.google.com/spreadsheets/d/UNIQUE_KEY_HERE/export?id=UNIQUE_KEY_HERE&gid=UNIQUE_SHEET_ID&format=tsv

Single-sheet formats (requires gid parameter): tsv, csv and ods

Multi-sheet formats (gid is ignored): xlsx and zip (html)

PDF seems to use a different endpoint with POST instead of a GET so is the only Download format that can't be used with this URL.

ColinM
  • 13,367
  • 3
  • 42
  • 49