◄︎ Gregor's Portfolio Site
08 Oct 2017

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:

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);
xmlhttp.open("GET", urlStats, true);

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.