preface

With the fourth version of the Google Tables API, there is a faster reading API for table data, but there is still a need for authorization to read data, even for tables published to the web. So we tried a lot of things and went through a lot of pitfalls in making this upgrade, so here’s a quick summary.

plan

First I found the API for reading table data:

Developers.google.com/sheets/api/…

The interface requires the following parameters (mandatory parameters) :

{
    "spreadsheetId": ""."range": ""."access_token": ""
}
Copy the code

Anyone who has used spreadsheetId knows that spreadsheetId and Range are table ids and tab-ranges. But an access_token is not a fixed thing, so I looked at two ways Google can get an Access_token (both based on OAuth 2.0) :

  1. Using the user’s own account, simply speaking, is to notify the user authorization, and then the user can get the user’s confirmationaccess_tokenRead the table data for which the user has read permission.
  2. Use the service account, share the form with the Google account, and then use the server to log in to the service account and get the service accountaccess_tokenRead table data.

I used the first method when I started, but the following shortcomings occurred:

  1. Need to write a dead account, each time use this account for authorization.
  2. access_tokenThe validity period is very short (usually two hours), and manual authorization is required at intervals, requiring human intervention.
  3. If my account is deleted one day, I need to reset the account and generate a new project configuration.

Later, I saw that Google suggested using the service account (although API KEY can also be used) in server authorization, thus realizing the authorization without user participation on the server side.

implementation

Create a project

Create a project in the developer console

Link:console.developers.google.com/projectcrea…

After filling in the necessary information, click Create (note that the project ID cannot be changed).

2. Open Google Sheet API service

After the account is created, it will jump to the dashboard of the project. In the following view, search Google Sheet API directly in the search box. If the service account created is not enabled, it has no permission to read the table data.

Click “Enable” on the API page to enable the Sheet API function of the project.

Create a service account

After opening the Sheet API, we will jump to the configuration page of the Sheet API. Now we will create a service account for this project.

First click “Credentials” according to the tag, then click “credentials in API and Services” to create a service account.

Here we choose to create a “service account key “;

Generate credentials

Click the above “service Account key” to jump to the key creation page. We select a new service account and fill it according to our own requirements, as shown below.

I’ve chosen the JSON format to make it easy for Node.js (this time as a practice) to read;

After clicking create, we will get a JSON file. Note that the json file must be saved, and the subsequent authorization depends on it.

Then we start coding to implement the read function;

5. Start coding

To implement node.js, you first need to install googleapis. The following code is the authorization part.

npm install googleapis --save
Copy the code

const {google} = require('googleapis');

// The following JSON file is the one we downloaded in step 4 above.
const {client_email: email, private_key: key} = require('./service-account.json');

// Read permission is required
const scopes = ['https://www.googleapis.com/auth/spreadsheets.readonly'];

// Store the token information in the redis cache. For now, I will store it in the local variable
let token = null;

module.exports = {
    authorize() {
        return new Promise((resolve) = > {
            // Check whether there is a token file
            if (token) {
                const {access_token, expiry_date} = token;
                // See if it has expired + 1 minute
                if (expiry_date > (new Date().getTime() + 60 * 1000)) {
                    returnresolve(access_token); }}// reauthorize
            let jwtClient = new google.auth.JWT(email, null, key, scopes);
            Access_token access_token access_token access_token access_token
            jwtClient.authorize().then(data= > {
                // Save token to global variable
                token = data;
                / / returns a token
                resolve(data.access_token)
            }).catch((a)= > {
                resolve(' '); }); }); }};Copy the code

Then we obtain the token information through the above authorize. Finally, we use the Ajax client to call the interface of the table to realize the data reading.

Here is an implementation of reading table data:

module.exports = async function getSheetData({spreadsheetId, worksheets}) {
    const axios = require('axios');
    const qs = require('qs');
    const auth = require('.. /auth/jwtclient');
    const serviceEmailAddr = require('.. /auth/service-account').client_email;
    const token = await auth.authorize();
    const url = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values:batchGet`;
    return await axios
        .get(url, {
            params: {access_token: token, ranges: worksheets},
            paramsSerializer(params) {
                return qs.stringify(params, {arrayFormat: 'repeat'.encodeValuesOnly: true});
            }
        })
        .then(resp= > resp.data.valueRanges)
        .catch(error= > {
            if (error.response.status === 404) {
                return Promise.reject('The form does not exist, please check the form ID is correct');
            }
            if (error.response.status === 403) {
                return Promise.reject('No permission to access, please share form with:' + serviceEmailAddr);
            }
            if (error.response.status === 400) {
                return Promise.reject(error.response.data.error.message.replace('Unable to parse range'.'Please check if the following form exists'));
            }
            Promise.reject(error.message);
        });
};
Copy the code

To ensure privacy, I did not upload the JSON file of the project configuration;

Reference implementation: github.com/gslnzfq/dev…

Six, test,

If Google returns 403 when reading the data, it’s probably because you didn’t share the form with the service account. Check the service account:

Console.developers.google.com/iam-admin/s…

Select your project above and you will see the email on the right. Share your Google form with your email address to access it.

Reference documentation

  • Console.developers.google.com/projectcrea…
  • Developers.google.com/identity/pr…
  • Developers.google.com/sheets/api/…
  • Developers.google.com/sheets/api/…
  • Developers.google.com/apis-explor…