test script of google sheet api.

google_sheet_api.js

const fs = require('fs');
const readline = require('readline');
const google = require('googleapis');
const googleAuth = require('google-auth-library');

// If modifying these scopes, delete your previously saved credentials
// at ~/.credentials/sheets.googleapis.com-nodejs-quickstart.json
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'];
const TOKEN_DIR = (process.env.HOME || process.env.HOMEPATH ||
    process.env.USERPROFILE) + '/.credentials/';
const TOKEN_PATH = TOKEN_DIR + 'sheets.googleapis.com-nodejs-quickstart.json';
// replace with issued code when access to generated URL from get_url_for_code function.
const CODE = 'xxxxxxxxxx';

function exec_proc_with_credentials(file_path, callback){
  fs.readFile(file_path, (err, content) => {
    if (err) {
      console.log('Error loading client secret file: ' + err);
      return;
    }
    callback(JSON.parse(content));
  });
}

function exec_proc_via_oauth2client(token_path, oauth2Client, callback){
  fs.readFile(token_path, function(err, token) {
    if (!err) {
      oauth2Client.credentials = JSON.parse(token);
      callback(oauth2Client);
    }
  });
}

function getOauth2Client(credentials) {
  const clientSecret = credentials.installed.client_secret;
  const clientId = credentials.installed.client_id;
  const redirectUrl = credentials.installed.redirect_uris[0];
  const auth = new googleAuth();
  const oauth2Client = new auth.OAuth2(clientId, clientSecret, redirectUrl);
  return oauth2Client;
}

// this function is only required for preparation phase.
function get_url_for_code(credentials){
  const oauth2Client = getOauth2Client(credentials);
  const authUrl = oauth2Client.generateAuthUrl({
    access_type: 'offline',
    scope: SCOPES
  });
  console.log('Authorize this app by visiting this url: ', authUrl);
}

// this function is only required for preparation phase.
function storeCredentialToken(credentials) {
  const oauth2Client = getOauth2Client(credentials);
  oauth2Client.getToken(CODE, (err, token) => {
    if (err) {
      console.log('Error while trying to retrieve access token', err);
      return;
    }
    _storeToken(token);
  });
}

// this function is only required for preparation phase.
function _storeToken(token) {
  try {
    fs.mkdirSync(TOKEN_DIR);
  } catch (err) {
    if (err.code != 'EEXIST') {
      throw err;
    }
  }
  fs.writeFile(TOKEN_PATH, JSON.stringify(token));
  console.log('Token stored to ' + TOKEN_PATH);
}

function main(credentials) {
  const oauth2Client = getOauth2Client(credentials);

  const aceess_to_sheet = (oauth2Client) => {
    const sheets = google.sheets('v4');
    sheets.spreadsheets.values.get({
      auth: oauth2Client,
      spreadsheetId: 'xxxxxxxxxxxx',
      range: 'item!B2:E3',
    }, (err, response) => {
      if (err) {
        console.log('The API returned an error: ' + err);
        return;
      }
      const rows = response.values;
      rows.forEach((row) => {
        console.log('%s, %s', row[0], row[1]);
      });
    });
  };
  exec_proc_via_oauth2client(TOKEN_PATH, oauth2Client, aceess_to_sheet);
}

//exec_proc_with_credentials('client_secret.json', get_url_for_code);
//exec_proc_with_credentials('client_secret.json', storeCredentialToken);
exec_proc_with_credentials('client_secret.json', main);