I am trying to write a simple plugin for WordPress using PHP/JS. The plugin has only one objective, to create a widget. For those of you unfamiliar with the WordPress plugin implementation, it's not too plicated - I can use JS/PHP as normal. For the purpose of this question therefore, a single PHP/HTML file may give the answer I'm looking for.

I have a publicly-accessible Sheet with the following sharing settings:

All I want to do now is retrieve the value of a single cell on a single worksheet. The worksheet is called Live Summary and the cell is E20.

I have gotten this working using oAuth 2.0 authentication. However, I don't want the user to have to authenticate to see this information.

I want my website to display the value of this cell at all times, as if it were a feature of the website or as if I were pulling it from the MySQL database that WordPress is installed on.

I've read about using certain GET endpoints from the Google Sheets API but for the life of me I don't even know where to start if I'm not using oAuth tokens.

Could someone please a) tell me if this possible and, if it is, b) point me in the right direction to get started?

Preferably using JavaScript - but I could cope with PHP too.

I am trying to write a simple plugin for WordPress using PHP/JS. The plugin has only one objective, to create a widget. For those of you unfamiliar with the WordPress plugin implementation, it's not too plicated - I can use JS/PHP as normal. For the purpose of this question therefore, a single PHP/HTML file may give the answer I'm looking for.

I have a publicly-accessible Sheet with the following sharing settings:

All I want to do now is retrieve the value of a single cell on a single worksheet. The worksheet is called Live Summary and the cell is E20.

I have gotten this working using oAuth 2.0 authentication. However, I don't want the user to have to authenticate to see this information.

I want my website to display the value of this cell at all times, as if it were a feature of the website or as if I were pulling it from the MySQL database that WordPress is installed on.

I've read about using certain GET endpoints from the Google Sheets API but for the life of me I don't even know where to start if I'm not using oAuth tokens.

Could someone please a) tell me if this possible and, if it is, b) point me in the right direction to get started?

Preferably using JavaScript - but I could cope with PHP too.

• javascript
• google-sheets-api

• Here are some options for you: stackoverflow./a/42757972 – mtkopone Commented Aug 17, 2017 at 8:25
• Thanks for that @mtkopone, that's a useful link. I've checked through all of those resources though and I'm no closer to my main problem - I don't know how to (or, if it's even possible - it must be, surely?!) access the APIs without authorizing. Or perhaps I need to use some different kind of authorization which is authenticated once by a "bot" so that all users can access the information. – turbonerd Commented Aug 17, 2017 at 8:44

2 Answers 2

Here's how you can make it work without OAuth.

1) Make your sheet public to everyone on the web. (As you already have.)

2) Publish it: In the Google Sheets UI, navigate to File > Publish on the web...

3) Choose type Link, Entire Document, Web Page and click Publish.

After that you can (still) use the legacy API documented in https://developers.google./sheets/api/v3/worksheets to access your data.

The URL to access the data is of the form:

https://spreadsheets.google./feeds/<WHAT_KIND_OF_DATA>/<YOUR_SHEET_KEY>/<YOUR_SPREADSHEET_SHEET:)>/public/full?alt=json

So, for example, data for a sheet I have published (containing only the text "I'm E20" at E20) can be found at:

https://spreadsheets.google./feeds/cells/1TbMrtJl01i-Q5YudlhdAB_E1LTYkkLswnql5LHyiIuk/1/public/full?alt=json

There are also options to only publish a subset of the data, which might be better suited for you. But i hope this gets you forward.

EDIT: Next steps:

The next problem might be CORS. You can bypass it with e.g. JSON-P. Here's a fully working JSON-P solution to loading the content of the E20-cell on my sheet:

<html>
<body>
  <pre id='data'></pre>
</body>
<script>
  const onDataLoaded = (data) => {
    const e20Content = data.feed.entry.find((entry) => entry.title.$t == 'E20').content.$t
    document.getElementById('data').innerHTML = e20Content
  }
</script>
<script src="https://spreadsheets.google./feeds/cells/1TbMrtJl01i-Q5YudlhdAB_E1LTYkkLswnql5LHyiIuk/1/public/basic?alt=json-in-script&callback=onDataLoaded"></script>
</html>

I have been successfully using this approach for several years but I now find it doesn't seem to work. ken b