Posted in js
11298
6:20 am, August 31, 2018
 

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"

HTML

<p class="codepen" data-height="265" data-theme-id="light" data-default-tab="js,result" data-user="kruxor" data-slug-hash="BazNrwr" style="height: 265px; box-sizing: border-box; display: flex; align-items: center; justify-content: center; border: 2px solid; margin: 1em 0; padding: 1em;" data-pen-title="Test JSON Google Sheets">
  <span>See the Pen <a href="https://codepen.io/kruxor/pen/BazNrwr">
  Test JSON Google Sheets</a> by Luke (<a href="https://codepen.io/kruxor">@kruxor</a>)
  on <a href="https://codepen.io">CodePen</a>.</span>
</p>
<script async src="https://static.codepen.io/assets/embed/ei.js"></script>

Javascript

$.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']);
  
  console.log(data);
  
});

See the Pen Test JSON Google Sheets by Luke (@kruxor) on CodePen.

View Statistics
This Week
212
This Month
1254
This Year
994

No Items Found.

Add Comment
Type in a Nick Name here
 
Other Items in js
jqueryui date selector with examples and code with custom date formatting fancybox youtube showing video links in a lightbox - updated with fixed code parts fix for Uncaught (in promise) Error: reCAPTCHA placeholder element must be empty enable tinymce on a target textarea by id Generate Random Whole Numbers with JavaScript Function Generate Random Fractions with JavaScript jquery document ready make clickable element with clickable class using getElementById and innerHTML to change the html of content no jquery preserve tabs in textarea when tab key is pressed jqueryui includes css and js using vue and json data jqueryui date selector with examples and code jquery accordion fancybox youtube showing video links in a lightbox jquery jqueryui vue script includes get select option form value with jquery show the year with js jquery clone and append elements stacktable jQuery plugin for stacking tables on small screens load content with jquery find all elements add class jquery digital clock with jquery using regex with replace to replace all instances of something in a string random string generator guid set and check a cookie using js cookie validate email address from string check length of element jquery access hacker news json firebase api via jquery testing chartjs clipboard copy js tiny mce editor tinymce do something later with settimeout or loop with setinterval validate form data using javascript to check required html elements mithril testing parallax js scroll testing change the water colour in google maps for an already initialised map scrollbar replacement simplebar load google sheet data into json string with jquery change favicon with jquery add this Slick Slider Carousel change the window title flems embed in url detect window scroll position jquery truncate string using jquery round number with js google map with overlay data
Related Search Terms
Search Code
Search Code by entering your search text above.
Welcome

This is my test area for webdev. I keep a collection of code here, mostly for my reference. Also if i find a good link, i usually add it here and then forget about it. more...

Subscribe to weekly updates about things i have added to the site or thought interesting during the last week.

You could also follow me on twitter or not... does anyone even use twitter anymore?

If you found something useful or like my work, you can buy me a coffee here. Mmm Coffee. ☕

❤️👩‍💻🎮

🪦 2000 - 16 Oct 2022 - Boots
Random Quote


Me
Random CSS Property

align-content

The CSS align-content property sets the distribution of space between and around content items along a flexbox's cross-axis or a grid's block axis.
align-content css reference