3

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 complicated - 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:

spreadsheet 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.

spreadsheet

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.

how it should look

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.

turbonerd
  • 1,234
  • 4
  • 27
  • 63
  • Here are some options for you: https://stackoverflow.com/a/42757972 – mtkopone Aug 17 '17 at 08: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 Aug 17 '17 at 08:44

2 Answers2

8

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.com/sheets/api/v3/worksheets to access your data.

The URL to access the data is of the form:

https://spreadsheets.google.com/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.com/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.com/feeds/cells/1TbMrtJl01i-Q5YudlhdAB_E1LTYkkLswnql5LHyiIuk/1/public/basic?alt=json-in-script&callback=onDataLoaded"></script>
</html>
mtkopone
  • 5,955
  • 2
  • 27
  • 30
  • *EDIT* I didn't read the earlier bit of your post where you explained this answer, please ignore! This is exactly what I wanted - your code example worked out of the box (with my spreadsheet ID). I'll award the bounty when it's available in 19 hours - thank you. – turbonerd Aug 17 '17 at 12:46
  • Still works in 2021. You need to use the 'https://spreadsheets.google.com/feeds////public/full?alt=json' url and insert your sheet id which you find in the url of your sheet: https://docs.google.com/spreadsheets/d/SHEET_ID/edit#gid=0 – moritz Feb 11 '21 at 11:55
0

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

burky39
  • 65
  • 1
  • 9