Googleスプレッドシートにnode.js(google API)を使って文字を書き込みたい

###実現したいこと

Googleスプレッドシートにnode.jsで文字を書き込みたいです。Googleスプレッドシートから読み込むことはできるのですが書き込む処理がわかりません。apiはappendを使っています。エラーメッセージが長くなっています。少し削りました。

エラーメッセージ

$ node app.js
{ Error: Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.
    at createError (/home/vagrant/node_modules/axios/lib/core/createError.js:16:15)
    at settle (/home/vagrant/node_modules/axios/lib/core/settle.js:18:12)
    at IncomingMessage.handleStreamEnd (/home/vagrant/node_modules/axios/lib/adapters/http.js:201:11)
    at emitNone (events.js:91:20)
    at IncomingMessage.emit (events.js:185:7)
    at endReadableNT (_stream_readable.js:974:12)
    at _combinedTickCallback (internal/process/next_tick.js:80:11)
    at process._tickCallback (internal/process/next_tick.js:104:9)
  config: 
   { adapter: [Function: httpAdapter],
     transformRequest: { '0': [Function: transformRequest] },
     transformResponse: { '0': [Function: transformResponse] },
     timeout: 0,
     xsrfCookieName: 'XSRF-TOKEN',
     xsrfHeaderName: 'X-XSRF-TOKEN',
     maxContentLength: 2147483648,
     validateStatus: [Function],
     headers: 
      { Accept: 'application/json, text/plain, */*',
        'Content-Type': 'application/x-www-form-urlencoded',
        'User-Agent': 'google-api-nodejs-client/1.6.1',
        'Content-Length': 11 },
     method: 'post',
     url: 'https://sheets.googleapis.com/v4/spreadsheets/19Z3nQt9lY1SMw3drTR0ly-rRsLRkTjEUJjV9_vl9d18/values/sheet1!A4:append',
     paramsSerializer: [Function],
     data: 'hello world',
     params: { valueInputOption: 'RAW', key: 'auth' } },
  request: 

<-- エラー削った部分 -->

   _header: 'POST /v4/spreadsheets/スプレッドシートID/values/sheet1!A4:append?valueInputOption=RAW&key=auth HTTP/1.1\r\nAccept: application/json, text/plain, */*\r\nContent-Type: application/x-www-form-urlencoded\r\nUser-Agent: google-api-nodejs-client/1.6.1\r\nContent-Length: 11\r\nHost: sheets.googleapis.com\r\nConnection: close\r\n\r\n',
     _headers: 
      { accept: 'application/json, text/plain, */*',
        'content-type': 'application/x-www-form-urlencoded',
        'user-agent': 'google-api-nodejs-client/1.6.1',
        'content-length': 11,
        host: 'sheets.googleapis.com' },
     _headerNames: 
      { accept: 'Accept',
        'content-type': 'Content-Type',
        'user-agent': 'User-Agent',
        'content-length': 'Content-Length',
        host: 'Host' },

<-- エラー削った部分 -->

        method: 'post',
        url: 'https://sheets.googleapis.com/v4/spreadsheets/スプレッドシートID/values/sheet1!A4:append',
        paramsSerializer: [Function],
        data: 'hello world',
        params: [Object] },
     request: 
      ClientRequest {
        domain: null,
        _events: [Object],
        _eventsCount: 6,
        _maxListeners: undefined,
        output: [],
        outputEncodings: [],
        outputCallbacks: [],
        outputSize: 0,
        writable: true,
        _last: true,
        upgrading: false,
        chunkedEncoding: false,
        shouldKeepAlive: false,
        useChunkedEncodingByDefault: true,
        sendDate: false,
        _removedHeader: [Object],
        _contentLength: null,
        _hasBody: true,
        _trailer: '',
        finished: true,
        _headerSent: true,
        socket: [Object],
        connection: [Object],
        _header: 'POST /v4/spreadsheets/スプレッドシートID/values/sheet1!A4:append?valueInputOption=RAW&key=auth HTTP/1.1\r\nAccept: application/json, text/plain, */*\r\nContent-Type: application/x-www-form-urlencoded\r\nUser-Agent: google-api-nodejs-client/1.6.1\r\nContent-Length: 11\r\nHost: sheets.googleapis.com\r\nConnection: close\r\n\r\n',
        _headers: [Object],
        _headerNames: [Object],
        _onPendingData: null,
        agent: [Object],
        socketPath: undefined,
        timeout: undefined,
        method: 'POST',
        path: '/v4/spreadsheets/スプレッドシートID/values/sheet1!A4:append?valueInputOption=RAW&key=auth',
        _ended: true,
        _redirectable: [Object],
        parser: null,
        res: [Object] },
     data: { error: [Object] } },
  code: 401,
  errors: 
   [ { message: 'Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.',
       domain: 'global',
       reason: 'unauthorized' } ] }

ソースコード

app.js
1const fs = require('fs');
2const readline = require('readline');
3const {google} = require('googleapis');
4
5
6const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
7
8
9const TOKEN_PATH = 'token.json';
10
11// Load client secrets from a local file.
12fs.readFile('credentials.json', (err, content) => {
13  if (err) return console.log('Error loading client secret file:', err);
14    authorize(JSON.parse(content), Listwrite);
15});
16
17function authorize(credentials, callback) {
18  const {
19    client_secret,
20    client_id,
21    redirect_uris
22  } = credentials.installed;
23  const oAuth2Client = new google.auth.OAuth2(
24    client_id, client_secret, redirect_uris[0]);
25
26
27  fs.readFile(TOKEN_PATH, (err, token) => {
28    if (err) return getNewToken(oAuth2Client, callback);
29    oAuth2Client.setCredentials(JSON.parse(token));
30    callback(oAuth2Client);
31  });
32}
33
34function getNewToken(oAuth2Client, callback) {
35  const authUrl = oAuth2Client.generateAuthUrl({
36    access_type: 'offline',
37    approval_prompt: 'force',
38    scope: SCOPES,
39  });
40  console.log('Authorize this app by visiting this url:', authUrl);
41  const rl = readline.createInterface({
42    input: process.stdin,
43    output: process.stdout,
44  });
45  rl.question('Enter the code from that page here: ', (code) => {
46    rl.close();
47    oAuth2Client.getToken(code, (err, token) => {
48      if (err) return console.error('Error while trying to retrieve access token', err);
49      oAuth2Client.setCredentials(token);
50      // Store the token to disk for later program executions
51      fs.writeFile(TOKEN_PATH, JSON.stringify(token), (err) => {
52        if (err) console.error(err);
53        console.log('Token stored to', TOKEN_PATH);
54      });
55      callback(oAuth2Client);
56    });
57  });
58}
59
60/**
61 * Prints the names and majors of students in a sample spreadsheet:
62 * @see スプレッドシートID
63 * @param {google.auth.OAuth2} auth The authenticated Google OAuth client.
64 */
65
66function Listwrite(spreadsheetId, range, valueInputOption, _values) {
67    let values = [
68      [
69        1
70      ],
71       // Additional rows ...
72    ];
73    values = _values;
74    let resource = {
75      values,
76    };   
77    const sheets = google.sheets('v4');
78    
79    sheets.spreadsheets.values.append({
80      auth: 'auth',
81      spreadsheetId: 'スプレッドシートID',
82      range: 'sheet1!A4',
83      valueInputOption: 'RAW',
84      resource: "hello world",
85    }, (err, result) => {
86      if (err) return console.log(err);
87      console.log(`${result.updates.updatedCells} cells appended.` + 'OK');
88      resolve(result);
89    });
90}

試したこと

公式サイトとか見てやったのですができませんでした。

補足情報

node.js v6.11.1
googleAPI v4

miyabi-sun

2019/02/12 06:18 編集

1行目に「Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project」とあり、認証で拒否されているようですが、認証用のトークンファイルはどの程度まで準備をすすめましたか？この辺が解決すればスルッと全部動くと思うので、credentials.jsonやtoken.jsonファイルの中身を確認しながら進めてください。