Google Sheets API with Ionic

I want to retrieve formula of cells from spreadsheet using google sheets api. I did the configurations of credential keys etc. Then I went for javascript setup as mentioned by google. But got error cannot find gapi , so I did this solution and it worked. But now I am getting error

Property 'sheets' does not exist on type 'typeof client'.

Have also gone through this article but wasn't much helpful. Following is my implementation.

Home.ts

readFromSpreadSheet() {
    gapi.client.sheets.spreadsheets.values.get({
      spreadsheetId: 'XXXIDXXX',
      range: 'Sheet1!A1:D7'
    }).then((response) => {
      var result = response.result;
      var numRows = result.values ? result.values.length : 0;
      console.log('${numRows} rows retrieved.');
    });
  }

client_secret.json

{"web":{"client_id":"XXX","project_id":"XXX","auth_uri":"XXX","token_uri":"https://accounts.google.com/o/oauth2/token","auth_provider_x509_cert_url":"https://www.googleapis.com/oauth2/v1/certs","client_secret":"XXXXX"}}

quickstart.html

<!DOCTYPE html>
<html>
  <head>
    <title>Google Sheets API Quickstart</title>
    <meta charset='utf-8' />
  </head>
  <body>
    <p>Google Sheets API Quickstart</p>

    <!--Add buttons to initiate auth sequence and sign out-->
    <button id="authorize-button" style="display: none;">Authorize</button>
    <button id="signout-button" style="display: none;">Sign Out</button>

    <pre id="content"></pre>

    <script type="text/javascript">
      // Client ID and API key from the Developer Console
      var CLIENT_ID = 'XXXX';
      var API_KEY = 'XXX';

      // Array of API discovery doc URLs for APIs used by the quickstart
      var DISCOVERY_DOCS = ["https://sheets.googleapis.com/$discovery/rest?version=v4"];

      // Authorization scopes required by the API; multiple scopes can be
      // included, separated by spaces.
      var SCOPES = "https://www.googleapis.com/auth/spreadsheets.readonly";

      var authorizeButton = document.getElementById('authorize-button');
      var signoutButton = document.getElementById('signout-button');

      /**
       *  On load, called to load the auth2 library and API client library.
       */
      function handleClientLoad() {
        gapi.load('client:auth2', initClient);
      }

      /**
       *  Initializes the API client library and sets up sign-in state
       *  listeners.
       */
      function initClient() {
        gapi.client.init({
          apiKey: API_KEY,
          clientId: CLIENT_ID,
          discoveryDocs: DISCOVERY_DOCS,
          scope: SCOPES
        }).then(function () {
          // Listen for sign-in state changes.
          gapi.auth2.getAuthInstance().isSignedIn.listen(updateSigninStatus);

          // Handle the initial sign-in state.
          updateSigninStatus(gapi.auth2.getAuthInstance().isSignedIn.get());
          authorizeButton.onclick = handleAuthClick;
          signoutButton.onclick = handleSignoutClick;
        });
      }

      /**
       *  Called when the signed in status changes, to update the UI
       *  appropriately. After a sign-in, the API is called.
       */
      function updateSigninStatus(isSignedIn) {
        if (isSignedIn) {
          authorizeButton.style.display = 'none';
          signoutButton.style.display = 'block';
          listMajors();
        } else {
          authorizeButton.style.display = 'block';
          signoutButton.style.display = 'none';
        }
      }

      /**
       *  Sign in the user upon button click.
       */
      function handleAuthClick(event) {
        gapi.auth2.getAuthInstance().signIn();
      }

      /**
       *  Sign out the user upon button click.
       */
      function handleSignoutClick(event) {
        gapi.auth2.getAuthInstance().signOut();
      }

      /**
       * Append a pre element to the body containing the given message
       * as its text node. Used to display the results of the API call.
       *
       * @param {string} message Text to be placed in pre element.
       */
      function appendPre(message) {
        var pre = document.getElementById('content');
        var textContent = document.createTextNode(message + '\n');
        pre.appendChild(textContent);
      }

      /**
       * Print the names and majors of students in a sample spreadsheet:
       * https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
       */
      function listMajors() {
        gapi.client.sheets.spreadsheets.values.get({
          spreadsheetId: 'XXXIDXXX',
          range: 'Class Data!A2:E',
        }).then(function(response) {
          var range = response.result;
          if (range.values.length > 0) {
            appendPre('Name, Major:');
            for (i = 0; i < range.values.length; i++) {
              var row = range.values[i];
              // Print columns A and E, which correspond to indices 0 and 4.
              appendPre(row[0] + ', ' + row[4]);
            }
          } else {
            appendPre('No data found.');
          }
        }, function(response) {
          appendPre('Error: ' + response.result.error.message);
        });
      }

    </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>
  </body>
</html>

No idea what to do with quickstart.html . I have only changed the CLIENT_ID & API_KEY

$ ionic info

cli packages: (/usr/local/lib/node_modules)

@ionic/cli-utils  : 1.19.2
ionic (Ionic CLI) : 3.20.0

global packages:

cordova (Cordova CLI) : 8.0.0

local packages:

@ionic/app-scripts : 3.1.10
Cordova Platforms  : none
Ionic Framework    : ionic-angular 3.9.2

System:

ios-deploy : 1.9.2
Node       : v8.11.1
npm        : 6.0.0
OS         : macOS High Sierra
Xcode      : Xcode 9.1 Build version 9B55

Environment Variables:

ANDROID_HOME : not set

Misc:

backend : pro

Please suggest steps to integrate google spreadsheet api with ionic or suggest some helpful articles.

Upvotes: 1

Answers (2)

Yasar Whizz

Reputation: 17

In my project I had used Ionic with React. I followed these steps.

in project directory run commands

npm install @types/gapi
npm install @types/gapi.client
npm install @types/gapi.client.sheets
npm install @types/gapi.auth2

In package.json use types and typeRoots

{ 
  // ... 
  "types":["gapi"], 
  "typeRoots":["node_modules/@types"], 
  // ...
}

In project_directory/src/index.html

<script async defer src="https://apis.google.com/js/api.js" type="text/javascript"/>

In .ts or tsx file type, var gapi=window.gapi;

this will work fine in ionic localhost development server for using in android you have to do certain steps more.

Upvotes: 1

Apogee

Reputation: 719

After trying EVERYTHING. I got the answer!

And it is:

Cordova apps are not supported by gapi.client

https://github.com/google/google-api-javascript-client/issues/334

Supported Environments:

https://developers.google.com/api-client-library/javascript/start/start-js#supported-environments

Upvotes: 1

Google Sheets API with Ionic

Answers (2)

Related Questions