Sheets APIとgoogleapisでGoogleスプレッドシートの読み込み・書き込み

npm install googleapis@39 --save

> node -v
v13.10.1

{
  "name": "tmp_dev",
  "version": "1.0.0",
  "description": "",
  "main": "tmp_dev.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "googleapis": "^39.2.0",
  }
}

Name, Major:
Alexandra, English
Andrew, Math
Anna, English

URL: https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0
ID: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms

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

// If modifying these scopes, delete token.json.
// const SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']; 
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets']; 

const TOKEN_PATH = 'token.json';

fs.readFile(<<oauth_json>>, (err, content) => {
    if (err) return console.log('Error loading client secret file:', err);
    // Authorize a client with credentials, then call the Google Sheets API.
    authorize(JSON.parse(content), main);
});

function authorize(credentials, callback) {
    const { client_secret, client_id, redirect_uris } = credentials.installed;
    const oAuth2Client = new google.auth.OAuth2(
        client_id, client_secret, redirect_uris[0]);

    // Check if we have previously stored a token.
    fs.readFile(TOKEN_PATH, (err, token) => {
        if (err) return getNewToken(oAuth2Client, callback);
        oAuth2Client.setCredentials(JSON.parse(token));
        callback(oAuth2Client);
    });
}

function getNewToken(oAuth2Client, callback) {
    const authUrl = oAuth2Client.generateAuthUrl({
        access_type: 'offline',
        scope: SCOPES,
    });
    console.log('Authorize this app by visiting this url:', authUrl);
    const rl = readline.createInterface({
        input: process.stdin,
        output: process.stdout,
    });
    rl.question('Enter the code from that page here: ', (code) => {
        rl.close();
        oAuth2Client.getToken(code, (err, token) => {
            if (err) return console.error('Error while trying to retrieve access token', err);
            oAuth2Client.setCredentials(token);
            // Store the token to disk for later program executions
            fs.writeFile(TOKEN_PATH, JSON.stringify(token), (err) => {
                if (err) return console.error(err);
                console.log('Token stored to', TOKEN_PATH);
            });
            callback(oAuth2Client);
        });
    });
}

function main(auth) {
    const sheets = google.sheets({ version: 'v4', auth });

    sheets.spreadsheets.values.update({
        spreadsheetId: <<sheet_id>>,
        range: 'Class Data!B2:C3',
        valueInputOption: "USER_ENTERED",
        resource: {
            values: [
                ["hoge", "hogehoge"],
                ["fuga", "fugafuga"]
            ]
        }
    }, (err, result) => {
        if (err) {
            // Handle error
            console.log(err);
        } else {
            console.log('%d cells updated.', result.updatedCells);
        }
    });

}

const SCOPES = ['https://www.googleapis.com/auth/spreadsheets']; 

const SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'];

Error: Insufficient Permission

npm i google-spreadsheet

> node -v
v13.10.1

{
  "name": "tmp_dev",
  "version": "1.0.0",
  "description": "",
  "main": "tmp_dev.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "google-spreadsheet": "^3.2.0",
    "googleapis": "^39.2.0",
  }
}

const { GoogleSpreadsheet } = require('google-spreadsheet');

const doc = new GoogleSpreadsheet('<<sheet_id>>');

let rows = []; 

(async function(){

    await doc.useServiceAccountAuth({
        client_email: <<client_email>>,
        private_key: <<private_key>>,
    });
    
    await doc.loadInfo();
    const sheet = doc.sheetsByIndex[0];

    rows = await sheet.getRows();
    
    console.log(rows);    
})();