Using Google Sheets as a JSON endpoint

Spreadsheets are a great way to capture complex information without having to spin up a database or build an application to manage things. As a result, they often become stores of valuable business information, and on occasion you want to get that information out of the spreadsheet and into a more formal application. Thankfully for users of Google Sheets, there is an easy way to accomplish that.

It turns out that every Google Sheet document has the ability to produce a JSON endpoint. Activating it is an easy process:

  • In your Google Sheets document, go to File and choose Publish to the web…
  • In Published content & settings, decide if you want to make the entire document available or just one specific sheet, then press Start publishing.
  • Once published, you’ll need to grab a copy of your spreadsheet ID. You can find it in the URL of the page. It’s the long string of characters that come after https://docs.google.com/spreadsheets/d/ .

With the spreadsheet ID copied, you can access the JSON feed by inserting it into the following structure:

https://spreadsheets.google.com/feeds/list/[SPREADSHEET_ID]/1/public/values?alt=json

Here’s an example: https://spreadsheets.google.com/feeds/list/1HR4WeATudZKmmLDXuo-qe9dt04Y98h4ebbv5rkzibZA/1/public/values?alt=json

Armed with your fancy new JSON URL, you can import the data using a normal AJAX request and use JSON.parse to get at the content. Example:


// Sample AJAX function
function fetchSpreadSheet() {
var url = 'https://spreadsheets.google.com/feeds/list/[SPREADSHEET_ID]/1/public/values?alt=json'
var  xmlhttp =  new  XMLHttpRequest();
console.log("starting request");
xmlhttp.onreadystatechange  =   function()  {
if  (this.readyState  ==  4  &&  this.status  ==  200) {
var  data = JSON.parse(this.responseText);
//doSomething(data);
console.log(data.feed.entry[0]['title']['$t']);
}
}
xmlhttp.open("GET", urlStats,  true);
xmlhttp.send();
fetchSpreadSheet();

Be warned, the structure of the Google Sheets JSON does take some getting use to, but the data is all there. Start with data.feed.entry for an array of your actual values.

Add a Comment

Your email address will not be published. Required fields are marked *