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
  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"

Other Items in js
Page Views

This page has been viewed 465 times.

Search Code
Search Code by entering your search text above.

This is my test area for webdev. I keep a collection of code snippits here, mostly for my reference. Also if i find a good site, i usually add it here.


Random Quote
There is no way to happiness. Happiness is the way.