(Update 2021) Google Sheets API – Read and Write Data

Anthony B. December 31, 2020 0 Comments

Table of Contents

Steps
- JExcel Setup
- Google Sheets Setup
Code

Steps #

JExcel Setup #

Documentation is located at jexcel.net.

Google Sheets Setup #

Make sure you set the following properties to make the code work.

SpreadsheetID
API Key
Client ID
Scope

Everything else should work as is listed in the code below.

Code #


    
<html>
  <head>
    <script src="https://bossanova.uk/jexcel/v4/jexcel.js"></script>
    <script src="https://jsuites.net/v3/jsuites.js"></script>
    <link rel="stylesheet" href="https://jsuites.net/v3/jsuites.css" type="text/css" />
    <link rel="stylesheet" href="https://bossanova.uk/jexcel/v4/jexcel.css" type="text/css" />
  </head>

  <div id="spreadsheet"></div>
      <!--
        BEFORE RUNNING:
        ---------------
        1. If not already done, enable the Google Sheets API
           and check the quota for your project at
           https://console.developers.google.com/apis/api/sheets
        2. Get access keys for your application. See
           https://developers.google.com/api-client-library/javascript/start/start-js#get-access-keys-for-your-application
        3. For additional information on authentication, see
           https://developers.google.com/sheets/api/quickstart/js#step_2_set_up_the_sample
      -->
      <script>
      var ssID = 'xxx';
      var data;

      function update(value, range) {
        var params = {
            // The ID of the spreadsheet to update.
            spreadsheetId: ssID,  // TODO: Update placeholder value.

            // The A1 notation of the values to update.
            range: range,  // TODO: Update placeholder value.

            // How the input data should be interpreted.
            valueInputOption: 'RAW',  // TODO: Update placeholder value.
        };

        var valueRangeBody = {
          // TODO: Add desired properties to the request body. All existing properties
          // will be replaced.
          "values": [[value]]
        };

        var request = gapi.client.sheets.spreadsheets.values.update(params, valueRangeBody);
        request.then(function(response) {
          // TODO: Change code below to process the `response` object:
          console.log(response.result);
        }, function(reason) {
          console.error('error: ' + reason.result.error.message);
        });
      }

      function get() {
        var params = {
          // The ID of the spreadsheet to retrieve data from.
          spreadsheetId: ssID,  // TODO: Update placeholder value.

          // The A1 notation of the values to retrieve.
          range: 'xxx',  // TODO: Update placeholder value.

          // How values should be represented in the output.
          // The default render option is ValueRenderOption.FORMATTED_VALUE.
          //valueRenderOption: '',  // TODO: Update placeholder value.

          // How dates, times, and durations should be represented in the output.
          // This is ignored if value_render_option is
          // FORMATTED_VALUE.
          // The default dateTime render option is [DateTimeRenderOption.SERIAL_NUMBER].
          //dateTimeRenderOption: '',  // TODO: Update placeholder value.
        };

        var request = gapi.client.sheets.spreadsheets.values.get(params);
        request.then(function(response) {
          // TODO: Change code below to process the `response` object:
          populateSheet(response);


          //data = response.result;
        }, function(reason) {
          console.error('error: ' + reason.result.error.message);
        });
      }

      function populateSheet(response) {

          var rows = response.result.values.slice(0);
          var cols = rows.shift();
          var colObj = new Array();

          for(var i=0; i<cols.length; i++) {
              colObj.push({ type: 'text', title:cols[i], width:120 });
          }

          jexcel(document.getElementById('spreadsheet'), {
              data:rows,
              columns: colObj,
              onchange: function(table, cell,col,row,newValue,oldValue)  {
                var range;
                var rowLtr = parseInt(row)+1;
                var colLtr = columnToLetter(parseInt(col)+1);
                range = colLtr + rowLtr;

                update(newValue, range);

              }
          });
      }

      function columnToLetter(column) {
        var temp, letter = '';
        while (column > 0)
        {
          temp = (column - 1) % 26;
          letter = String.fromCharCode(temp + 65) + letter;
          column = (column - temp - 1) / 26;
        }
        return letter;
      }

      function initClient() {
        var API_KEY = 'xxx';  // TODO: Update placeholder with desired API key.

        var CLIENT_ID = 'xxx';  // TODO: Update placeholder with desired client ID.

        // TODO: Authorize using one of the following scopes:
        //   'https://www.googleapis.com/auth/drive'
        //   'https://www.googleapis.com/auth/drive.file'
        //   'https://www.googleapis.com/auth/drive.readonly'
        //   'https://www.googleapis.com/auth/spreadsheets'
        //   'https://www.googleapis.com/auth/spreadsheets.readonly'
        var SCOPE = 'https://www.googleapis.com/auth/spreadsheets';

        gapi.client.init({
          'apiKey': API_KEY,
          'clientId': CLIENT_ID,
          'scope': SCOPE,
          'discoveryDocs': ['https://sheets.googleapis.com/$discovery/rest?version=v4'],
        }).then(function() {
          gapi.auth2.getAuthInstance().isSignedIn.listen(updateSignInStatus);
          updateSignInStatus(gapi.auth2.getAuthInstance().isSignedIn.get());
        });
      }

      function handleClientLoad() {
        gapi.load('client:auth2', initClient);
      }

      function updateSignInStatus(isSignedIn) {
        if (isSignedIn) {
          get();
        }
      }

      function handleSignInClick(event) {
        gapi.auth2.getAuthInstance().signIn();
      }

      function handleSignOutClick(event) {
        gapi.auth2.getAuthInstance().signOut();
      }
      </script>


      <script async defer src="https://apis.google.com/js/api.js"
        onload="this.onload=function(){};handleClientLoad()"
        onreadystatechange="if (this.readyState === 'complete') this.onload()">
      </script>
      <button id="signin-button" onclick="handleSignInClick()">Sign in</button>
      <button id="signout-button" onclick="handleSignOutClick()">Sign out</button>
      <button id="update-button" onclick="update()">Update</button>

    </html>