Module 0325: Interacting with a Google Sheet from a command line interface script

It is convenient to be able to interact with a Google Sheet remotely using a script running on a local computer. This can automate updates, analyses and many other tasks involving a spreadsheet. Furthermore, this approach is also likely to apply to other Google Drive components like Doc.

3 The Program

1const fs = require(’fs’);
2const readline = require(’readline’);
3const {google} = require(’googleapis’);
4
5// If modifying these scopes, delete token.json.
6const SCOPES = [’https://www.googleapis.com/auth/spreadsheets.readonly’];
7// The file token.json stores the user’s access and refresh tokens, and is
8// created automatically when the authorization flow completes for the first
9// time.
10const TOKEN_PATH = ’token.json’;
11
12// Load client secrets from a local file.
13fs.readFile(’credentials.json’, (err, content) => {
14  if (err) return console.log(’Error␣loading␣client␣secret␣file:’, err);
15  // Authorize a client with credentials, then call the Google Sheets API.
16  authorize(JSON.parse(content), listMajors);
17});
18
19/**
20 * Create an OAuth2 client with the given credentials, and then execute the
21 * given callback function.
22 * @param {Object} credentials The authorization client credentials.
23 * @param {function} callback The callback to call with the authorized client.
24 */
25function authorize(credentials, callback) {
26  const {client_secret, client_id, redirect_uris} = credentials.installed;
27  const oAuth2Client = new google.auth.OAuth2(
28      client_id, client_secret, redirect_uris[0]);
29
30  // Check if we have previously stored a token.
31  fs.readFile(TOKEN_PATH, (err, token) => {
32    if (err) return getNewToken(oAuth2Client, callback);
33    oAuth2Client.setCredentials(JSON.parse(token));
34    callback(oAuth2Client);
35  });
36}
37
38/**
39 * Get and store new token after prompting for user authorization, and then
40 * execute the given callback with the authorized OAuth2 client.
41 * @param {google.auth.OAuth2} oAuth2Client The OAuth2 client to get token for.
42 * @param {getEventsCallback} callback The callback for the authorized client.
43 */
44function getNewToken(oAuth2Client, callback) {
45  const authUrl = oAuth2Client.generateAuthUrl({
46    access_type: ’offline’,
47    scope: SCOPES,
48  });
49  console.log(’Authorize␣this␣app␣by␣visiting␣this␣url:’, authUrl);
50  const rl = readline.createInterface({
51    input: process.stdin,
52    output: process.stdout,
53  });
54  rl.question(’Enter␣the␣code␣from␣that␣page␣here:␣’, (code) => {
55    rl.close();
56    oAuth2Client.getToken(code, (err, token) => {
57      if (err) return console.error(’Error␣while␣trying␣to␣retrieve␣access␣token’, err);
58      oAuth2Client.setCredentials(token);
59      // Store the token to disk for later program executions
60      fs.writeFile(TOKEN_PATH, JSON.stringify(token), (err) => {
61        if (err) console.error(err);
62        console.log(’Token␣stored␣to’, TOKEN_PATH);
63      });
64      callback(oAuth2Client);
65    });
66  });
67}
68
69/**
70 * Prints the names and majors of students in a sample spreadsheet:
71 * @see https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
72 * @param {google.auth.OAuth2} auth The authenticated Google OAuth client.
73 */
74function listMajors(auth) {
75  const sheets = google.sheets({version: ’v4’, auth});
76  sheets.spreadsheets.values.get({
77    spreadsheetId: ’1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms’,
78    range: ’Class␣Data!A2:E’,
79  }, (err, res) => {
80    if (err) return console.log(’The␣API␣returned␣an␣error:␣’ + err);
81    const rows = res.data.values;
82    if (rows.length) {
83      console.log(’Name,␣Major:’);
84      // Print columns A and E, which correspond to indices 0 and 4.
85      rows.map((row) => {
86        console.log(‘${row[0]}, ${row[4]}‘);
87      });
88    } else {
89      console.log(’No␣data␣found.’);
90    }
91  });
92}

4 Analysis

Lines 1 to 3 are including the necessary modules. So make sure the necessary modules are installed. NPM does ﬁnd and install most packages quite readily in case you forget to install some of the modules.

Line 6 speciﬁes a “scope”. Refer to Google scopes for a longer list and explanation. This scope can be changed to without .readonly to permit changes to the spreadsheet.

TOKEN_PATH is a constant so that various places of the program can refer to the “token” at the same path.

13 attempts to read a credential ﬁle. A credential ﬁle should exist as credential.json in the same folder as the script prior to running the script. This ﬁle essentially enables the script to sign in as whatever Google identity was used to create the API project, and under the name of the project.

On line 16, the subroutine authorize is called. It has two parameters. The ﬁrst one is the credential object that is parsed from the ﬁle credential.json, the second one is a callback function in case authorization is successful.

While it is unlikely that you need to modify the function authorize, it is nonetheless helpful to understand it. It attenpts to read a “token” ﬁle. If such a ﬁle exists, the token object represented by the ﬁle is used to authenticate using OAuth 2.0. Otherwise, a new token is acquired using the client ID and secret contained in the credential object.

In OAuth 2, when a server authorizes based on a speciﬁc credential, the server returns with an authorization code otherwise known as a “token.” If the script does not yet have the authorization code, then the subroutine getNewToken is called to get authorized. Otherwise, an existing token is read so that the client can reuse the same authorization code again. The callback subroutine is called with a single parameter that speciﬁes the authorization code.

Line 44 starts the subroutine to get a new token (aka “authornization code”). Again, it is unlikely that your script will need to modify this subroutine, but it is helpful to understand how it works.

line 45 interacts with an OAuth server to generate a unique URL. This URL must be visited using a browser. When this URL is visited, it allows the user to specify a Google account, and understand what kind of access is about to be granted to the script. Upon successful sign in or speciﬁcation of a Google account and agreement to the access of the resource, a code is presented.

This code should then be copied-and-pasted on the command line to complete the negotiation with the OAuth 2 server. The code is passed as the ﬁrst parameter to oAuth2Client.getToken. If the code matches what the OAuth server expects, then a token is generated by the getToken method of the oAuth2Client object. The token is then passed to a callback function.

The callback (second parameter of oAuth2Client.getToken) of this method then performs two important operation. First, it associates the token with the credential property of the oAuth2Client object so that from here on, the token is the key of accessing the resources. Second, it stores the otken as a ﬁle for further use.

With an OAuth2Client object and credential speciﬁed, the application speciﬁc callback is ﬁnally called. This subroutine starts on line 74. This is where the Google Sheets NodeJS API can be used.

On line 75, the main Google Sheets API object is created with the authentication object. This API object then used on the next line to get speciﬁc cells of a speciﬁc sheet of a speciﬁc spreadsheet using the get method. A callback is speciﬁed as the second argument to process the error or result of the operation.

5 Concepts

5.1 Credential

A credential ﬁle is a form of authentication without interactively supply a password.

Go to the Google Sheets API Guides page and click “enable the Google Sheets API” button to start or select a project and get the credential ﬁle. Note that this wizard also calls the credential ﬁle “client conﬁguration”, but that is what you need to download as the credential.

5.2 OAuth 2 Client

OAuth 2 is a client-server authentication mechanism. A “client” is an entity that requests authentication to a resource of a server. In this case, the script is the client. A client speciﬁes the following:

5.3 Token

A token is a crypto object created by an OAuth 2 server after authentication and access granting are completed. As such a token is the crypto representation of successful authentication and can be used to attempt to gain access to applications. A token is attached to an OAuth 2 client object once it is either read from local ﬁle system or generated by interactively authenticating and being granted access.