this content was originally posted here.

load google sheet data into json string with jquery

Just discovered this the other day, you can actually export google sheets data into json format and load it directly into your site, how good is that. A free mini basic database!

Initially i was looking into ways to store json data easily, so that i dont have to manually write the data and have an easy way of adding and removing data. This seems like a good solution.

Here is what you need to do:

  1. Go to sheets.google.com and create a new blank sheet

  2. Add some kind of data
  3. Publish the Sheet to the Web: File / Publish to the Web

  4. Copy the key looking part of the Link, like this: 1BfQlexhe9K1HjkBbSZpcrEEjJsS9FRn3DFh_Uo_gY3k

    Here is my test url, the bolded bit is the key you can paste into the box below to generate your link: https://docs.google.com/spreadsheets/d/1BfQlexhe9K1HjkBbSZpcrEEjJsS9FRn3DFh_Uo_gY3k/edit#gid=0

  5. Paste it into here to generate the json link

    Google Sheets JSON URL Generator

  6. Now you have a fully working json data feed for your page.
  7. Load it in with jquery and show the data
  8. $.getJSON("https://spreadsheets.google.com/feeds/list/1BfQlexhe9K1HjkBbSZpcrEEjJsS9FRn3DFh_Uo_gY3k/od6/public/values?alt=json", function(data) {
      //also note that it will get rid of spaces in titles so "A Price" becomes "aprice" so better to use one word
      console.log(data.feed.entry[0]['gsx$titleone']['$t']);
    });	
  9. Check the console and you should see some kinda data from the sheet
  10. Note: if new data is not showing up you will need to add the ajax no cacge to your doc ready.

If you check the console log it will show the text "Data One"

Demo