CODEWITHSUNDEEP
javascriptjsonjsonpgoogle-sheets-api
Elster
Elster

Reputation: 3

Trouble understanding how to implement Google Sheets API

I'm still learning web dev, hence my amateurish lingo, but I really need to be able to pull some data from a Google sheet and represent it visually in a Javascript-animated canvas object.

Unfortunately, as usual, the Google Sheets API documentation is not written with people like me in mind! The basic GETer application worked perfectly on my local Python server but now throws up an error since I tested it on a hosted website. I assume this is a JSONP issue since the error starts with 'Load denied by X-Frame-Options', but there doesn't seem to be any mention in the documentation of how to make the request as JSONP. I've made the spreadsheet public so there should be no need for an authentication step.

Here's the code, with IDs etc removed:

 <script>         

      var CLIENT_ID = 'my_client_id';

      var SCOPES = ["https://www.googleapis.com/auth/spreadsheets.readonly"];

            function checkAuth() {
        gapi.auth.authorize(
          {
            'client_id': CLIENT_ID,
            'scope': SCOPES.join(' '),
            'immediate': true
          }, handleAuthResult);
      }

 /**
       * Handle response from authorization server.
       *
       * @param {Object} authResult Authorization result.
       */
      function handleAuthResult(authResult) {

        if (authResult && !authResult.error) {
          // Hide auth UI, then load client library.

          loadSheetsApi();
        } else {
          // Show auth UI, allowing the user to initiate authorization by
          // clicking authorize button.
    console.log('nope');
        }
      }

      /**

      /**
       * Load Sheets API client library.
       */
      function loadSheetsApi() {
        var discoveryUrl =
            'https://sheets.googleapis.com/$discovery/rest?version=v4';
        gapi.client.load(discoveryUrl).then(trackDonations);
      }

      /**
       * Print values in the donations column
       */
      function trackDonations() {
        gapi.client.sheets.spreadsheets.values.get({
          spreadsheetId: 'my_sheet_id',
          range: 'Form Responses 1!C2:C',
        }).then(function(response) {
          var range = response.result;
          if (range.values.length > 0) {
              console.log(range.values);
            $('.sum').text(range.values[range.values.length-1]);
          } else {
            prompt('No data found.');
          }
        }, function(response) {
          prompt('Error: ' + response.result.error.message);
        });
      }



    </script>
    <script src="https://apis.google.com/js/client.js?onload=checkAuth">

Perhaps I'm missing something stupid, but would be very grateful for some guidance! Thanks!

Upvotes: 0

Views: 423

Answers (1)

Mike Dent
Mike Dent

Reputation: 11

If you're working with a spreadsheet, it might be easier to use AppsScript.

From the spreadsheet, go to Tools > Script Editor. This page shows how to load the data into an array https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getDataRange() and then serve it as jsonp https://developers.google.com/apps-script/guide/content#serving_jsonp_in_web_pages If you then 'Deploy as web app' (the cloud icon), they will give you a url to reference in your code.

It's similar to using the rest api, but gives you more control over how the response works.

Upvotes: 1

Related Questions