Using Google Sheets API to fetch the value of a single cell from a publicly accessible spreadsheet

Question

Using Google Sheets API to fetch the value of a single cell from a publicly accessible spreadsheet

Currently, I am working on developing a simple WordPress plugin using PHP and JavaScript. The main goal of this plugin is to create a widget. If you are not familiar with how WordPress plugins work, don't worry - the implementation is not too complex since regular JS/PHP can be used. In fact, the solution to my question may lie within a single PHP/HTML file.

I have a publicly-accessible Google Sheet with specific sharing settings:

https://i.sstatic.net/vCIbo.png

What I aim to do now is fetch the value stored in a particular cell on a designated worksheet. The targeted worksheet is named Live Summary and the cell of interest is E20.

https://i.sstatic.net/8lgRL.png

Although I have managed to retrieve this data using oAuth 2.0 authentication, it is not ideal for users to authenticate just to view this information.

My objective is to seamlessly display the contents of this cell on my website at all times, almost as if it were directly sourced from the MySQL database where WordPress resides.

https://i.sstatic.net/tRSJN.png

I have come across suggestions about utilizing certain GET endpoints from the Google Sheets API, but without utilizing oAuth tokens, I am uncertain about where to begin.

If feasible, could someone please: a) confirm whether this is achievable, and b) provide guidance on how to kickstart this process?

Preferably, I would like to use JavaScript for this task, though PHP would also suffice.

javascript google-sheets-api

Answer 1

Answer №1

Follow these steps to make it work without OAuth.

1) Ensure your sheet is public for anyone on the web to access, as you have already done.

2) Publish it by going to File > Publish on the web in the Google Sheets UI.

3) Select Link, Entire Document, Web Page as the type and click Publish.

Once that's done, you can still utilize the legacy API detailed in https://developers.google.com/sheets/api/v3/worksheets to retrieve your data.

The URL format to access the data should be like this:

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

For instance, if you want data from a sheet I've published (which has "I'm E20" at cell E20), you can find it here:

You also have the option to publish only specific data, which may better suit your needs. Nevertheless, I hope this helps you move forward.

UPDATE: Additional Steps:

You might encounter CORS as the next challenge. You can circumvent it using JSON-P. Below is a functional JSON-P solution to load content from the E20-cell in 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>

Answer 2

Follow these steps to make it work without OAuth.

1) Ensure your sheet is public for anyone on the web to access, as you have already done.

2) Publish it by going to File > Publish on the web in the Google Sheets UI.

3) Select Link, Entire Document, Web Page as the type and click Publish.

Once that's done, you can still utilize the legacy API detailed in https://developers.google.com/sheets/api/v3/worksheets to retrieve your data.

The URL format to access the data should be like this:

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

For instance, if you want data from a sheet I've published (which has "I'm E20" at cell E20), you can find it here:

You also have the option to publish only specific data, which may better suit your needs. Nevertheless, I hope this helps you move forward.

UPDATE: Additional Steps:

You might encounter CORS as the next challenge. You can circumvent it using JSON-P. Below is a functional JSON-P solution to load content from the E20-cell in 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>

Answer 3

Answer №2

After years of effectively utilizing this method, I have discovered that it is no longer yielding the desired results. - Ken B

Answer 4

After years of effectively utilizing this method, I have discovered that it is no longer yielding the desired results. - Ken B

Using Google Sheets API to fetch the value of a single cell from a publicly accessible spreadsheet

Answer №1

Answer №2

Similar questions

Implementing a more efficient method for incorporating UUIDs into loggers

Guide on attaching an event to every dynamically created element in JavaScript

Running JavaScript function from AJAX response containing both HTML and JavaScript code

Synchronizing validation processes across both front-end and back-end applications

To ensure a rectangular image is displayed as a square, adjust its side length to match the width of the parent div dynamically

Tips for preserving the integrity of square brackets while extracting data from JSON

JavaScript News Scroller for Horizontal Display

obtain information from a Java servlet on a web page

What is the best way to implement dynamic filtering in Nest JS?

Monitoring changes to localstorage in AngularJS

Is there a way to align two canvases perfectly on top of each other?

Resuming AJAX requests after being aborted

Undefined is returned when exporting an item from the function called 'this'

Issue encountered when integrating JavaScript into Django view form update

"Trouble with kendo drop-down list: onclick event not triggering when text is changed

Using addClass and removeClass functions in JQuery when selecting a radio button

Tips for linking attributes to a function using ES6

What is the best way to adjust the size of carousel images within a box element?

Is it possible to design a collapsible element that gives a sneak peek of its content before fully expanding?

Vue.js parent component not receiving data emitted by child component