0

I want to be able to pull data from a google spreadsheet doc every 24hrs and use the values in my html page.

I have managed to get the JSON url for the cell I want to track, but I do not know how to get this JSON object into a javascript variable using the url.

I have searched around and tried using Jquery $.get() and $.getJSON() but I cant seem to get anything to work.

The url for the google spreadsheet data cell JSON is

https://spreadsheets.google.com/feeds/cells/1r56MJc7DVUVSkQ-cYdonqdSGXh5x8nRum4dIGMN89j0/1/public/values/R29C4?alt=json-in-script&callback=importGSS

I know this is probably simple but I am very new to working with JSON/ Javascript and have been struggling to work this out.

Thanks for any help :)

1 Answer 1

1

The data being returned is jsonp so you need to specify that in your Ajax request.

function getData() {
    return $.ajax({
        url: 'https://spreadsheets.google.com/feeds/cells/1r56MJc7DVUVSkQ-cYdonqdSGXh5x8nRum4dIGMN89j0/1/public/values/R29C4?alt=json-in-script&callback=importGSS',
        dataType: 'jsonp',
        jsonpCallback: 'importGSS'
    })
}

And while you can assign the data to, say, a global variable this will only get you so far - the Ajax process is asynchronous and you won't be able to access the data until the process has finished:

var obj;
getData().done(function (data) {
  obj = data;
});

// console.log(obj) here will return undefined as the process
// has not yet finished

Much better to grab the data and do something with it:

function doSomethingWithData(data) {
  console.log(data);
}

getData().done(function (data) {
  doSomethingWithData(data);
});

Or even simpler:

getData().done(doSomethingWithData);
Sign up to request clarification or add additional context in comments.

2 Comments

Now I have the value I want from the cell what would be the best way to put this value into a div in the HTML so that it automatically refreshes every hour? I was attempting to use .append() but I dont know how to keep the value up to date with the spreadsheet
You should ask a new question for that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.