Using Google Sheets As A Json Endpoint

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.