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

database file: /home/kruxor/Dropbox/ssl.kruxor.com/root/kruxor.com/db/db.db
comments_dbfile: /home/kruxor/Dropbox/ssl.kruxor.com/root/kruxor.com/db/comments.db ($db_comments)
views_dbfile: /home/kruxor/Dropbox/ssl.kruxor.com/root/kruxor.com/db/views.db
$dbfood_file: /home/kruxor/Dropbox/ssl.kruxor.com/root/kruxor.com/db/food.db
db_linx: /home/kruxor/Dropbox/ssl.kruxor.com/root/kruxor.com/linx/db/db.db
Classes Loaded:
class/answers.class.php
class/code.class.php
class/comments.class.php
class/content.class.php
class/core.class.php
class/crud.class.php
class/crumbs.class.php
class/featured.class.php
class/food.class.php
class/functions.class.php
class/images.class.php
class/import.class.php
class/links.class.php
class/questions.class.php
class/template.class.php
class/tv.class.php
class/user.class.php
class/utilities.class.php
class/views.class.php
class/widget.class.php
Class Extend Loaded: class/extends/expenses.extend.php
Class Extend Loaded: class/extends/gary_cummins_timeline.extend.php
Class Extend Loaded: class/extends/moo.extend.php
Class Extend Loaded: class/extends/osrs_item_data.extend.php
Class Extend Loaded: class/extends/osrs_slayer.extend.php
Class Extend Loaded: class/extends/osrs_slayer_monsters.extend.php
Class Extend Loaded: class/extends/searches.extend.php
Core Database Connected: expenses.db
expenses add_to_menu:
Class Extend Auto Initialized as: expenses
Core Database Connected: gary_cummins_timeline.db
gary_cummins_timeline add_to_menu:
Class Extend Auto Initialized as: gary_cummins_timeline
Core Database Connected: moo.db
moo add_to_menu:
Class Extend Auto Initialized as: moo
Core Database Connected: osrs_item_data.db
osrs_item_data add_to_menu:
Class Extend Auto Initialized as: osrs_item_data
Core Database Connected: osrs_slayer.db
osrs_slayer add_to_menu:
Class Extend Auto Initialized as: osrs_slayer
Core Database Connected: osrs_slayer_monsters.db
osrs_slayer_monsters add_to_menu:
Class Extend Auto Initialized as: osrs_slayer_monsters
Core Database Connected: searches.db
searches add_to_menu:
Class Extend Auto Initialized as: searches
$user_logged_in:
user access level:
pages:
$p1:view
$p2:code
$p3:659ri
$p4:load-google-sheet-data-into-json-string-with-jquery
$p5:
$p6:
$p:/view/code/659ri/load-google-sheet-data-into-json-string-with-jquery/
Using Template: view-code.html