プログラミング

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

投稿日: yuis

Googleの提供するサービスへAPIアクセスが可能なSheets APIと、そのライブラリであるgoogleapisをnode.jsで使用し、Googleスプレッドシートのシートやセルの読み込み、セルの書き込み・アップデートなどを行う方法について紹介します。

Sheets APIとgoogleapisでGoogleスプレッドシートを操作する

Google sheets APIのAPIを有効化、作成、設定

node.jsなどからcredentialsのjsonファイルやAPIキーを通してsheets APIを操作可能にする前に、sheets APIをGoogleアカウントから有効化し、APIキーを新規作成、oauthの設定を行う必要があります。

プロジェクトを作ってなければ作成する

なお、以下のステップでは以下を参考にしています。

Create a project and enable the API  |  Google Workspace for Developers

Google Cloud Platform (GCP)にアクセスし、プロジェクトを選択します。

Google Cloud Platform (GCP)

プロジェクトがまだ作成されていない場合は新規に作成をします。

Google Cloud Platform (GCP)でプロジェクトを新規作成する

sheets APIを有効化

sheets APIをアカウントで有効化します。
画面左のメニューを開き、 API and services・APIとサービス > library・ライブラリー へと進みます。

sheets APIをアカウントで有効化する

次の画面上で”sheets”などと適当に検索して、Google sheets APIを探します。見つかったらそれをクリック、先に進み有効化をします。
以下の画像では”manage”・管理となっていますが、有効化がされていない状態であれば enable・有効化 などの表示となるはずです。

sheests APIを有効化

OAuth 2.0を設定

次にOAuth 2.0を設定します。
OAuth 2.0を設定することで、自分のGoogleアカウントを含めた任意または指定のGoogleアカウントにAPIへのアクセス許可を求めることができるようになります。
自分または関係者またはユーザーのアカウントとAPIを紐付ける仕組みがOAuthであり、それをここで有効化、設定します。

なお、以下のステップでは以下を参考にしています。

Create credentials  |  Google Workspace for Developers

画面左のメニューを開き、 API and services・APIとサービス > OAuth consent screen へと進みます。

GCPでAPI and services・APIとサービス ><noscript><img src=

ステップ1のOAuth consent screenではapp name・名前、メールアドレスなどを任意のものに手早く入力します。

ステップ2のscopes・スコープでは Google Sheets API の /auth/spreadsheets を追加します。

GCPでGoogle Sheets APIのscopes・スコープを追加、設定

ステップ3のテストユーザー追加では、テストとして使用を想定するGoogleアカウントのメールアドレスを追加します。

sheets apiでテストユーザー追加

OAuth client ID credentialを作成

OAuthをnode.jsで使用するためのクライアントID、APIキーのjsonファイルを生成します。
Credentialsページから画面上部からCredentialsを新規作成します。

アプリケーションタイプはdesktop app・デスクトップアプリケーションを選択します。
作成完了後、jsonファイルの保存をします。このファイルを後にnode.jsプログラムで読み込みます。

OAuthをnode.jsで使用するためのクライアントID、APIキーのjsonファイルを生成する

OAuthをnode.jsで使用するためのクライアントID、APIキーのjsonファイルを生成する

node.jsでgoogleapisからGoogle sheets APIを実行する

nodeにgoogleapisライブラリをインストールします。

npm install googleapis@39 --save

なお、私の環境のnode.jsおよびライブラリと依存関係のバージョンは以下となります。

> 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",
  }
}

以下ページの Step 2: Set up the sample > sheets/quickstart/index.js のnode.jsプログラムをコピーアンドペーストしてファイルに保存します。

Node.js quickstart  |  Sheets API  |  Google Developers

上記のステップで保存されたjsonファイルをnode.jsに渡します。
“fs.readFile(‘credentials.json'”の部分にファイルパスを指定します。

nodeでファイルを実行します。
コンソールにURLが表示されるので、それをクリックし認証を行います。

URLの例: accounts.google.com/o/oauth2/v2/auth…

コンソールにURLが表示されるので、それをクリックし認証を行う

認証が完了するとtoken.jsonファイルにトークンがキャッシュされ、以降はtoken.jsonを削除しない限りは認証無しでプログラムが実行できるようになります。

以下のようにコンソールに出力がされれば、正常にAPIを通してスプレッドシートのセルを取得できています。

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

googleapisからGoogle sheets APIで書き込み

上記では読み込み・readの作業をガイドの通り行いました。以下にシートの書き込み・アップデート(write)についてのコードを紹介します。

サンプルデータとして、Googleの公式サンプルシートを使用します。

Example Spreadsheet

シートをコピーし、自分のアカウントに保存し、編集可能にします。

スプレッドシートでシートをコピー

コピーのシートのシートIDを選択します。
例えば以下のURLとシートIDのような組み合わせになります。

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

以下に全体としてのプログラムを紹介します。 (以下一部引用)
以下を適時書き換えてください。

sheet_id .. シートID
oauth_json .. 上記で保存したoauth 2.0のクライアントIDのAPIキーのjsonファイル (例えば”client_secret_”などで始まる)


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'];

の部分です。

上記のGoogleの公式ガイドには

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

と、read権限しか無いスコープが指定されています。この状態では以下に紹介するようなパーミッションエラーとなりますので、書き込み・アップデート作業をする場合にはここを必ず上記のものに変えてやる必要があります。

なお、スコープも上記のステップの通り auth/spreadsheets が追加されている必要があります。また、スコープの変更後にはoauthのキーを再発行すると安全かもしれません。

上記のコードでは、シート “Class Data” の “B2:C3” の2×2のセル4つに対して、多次元配列 [["hoge", "hogehoge"],["fuga", "fugafuga"]] を渡しその値にそれぞれのセルを更新・アップデートし変更を加えることをしています。

上記のコードのnodeで実行することで、以下(1)のようなスプレッドシートが、以下(2)のようにアップデートされます。

(変更前)

googleapisでスプレッドシートを書き込み・アップデート(変更前)

(変更後)

googleapisでスプレッドシートを書き込み・アップデート(変更後)

エラー: Error: Insufficient Permission (googleapis)

以下より、よくあるエラーについて紹介します。

googleapisでの実行で以下のエラーが起こる場合があります。

Error: Insufficient Permission

このエラーは上記の通り、スコープの追加、設定が正しく行われていない場合か、コードのSCOPES定数(oAuth2Client.generateAuthUrl -> scope)に渡しているパーミッションが不適切である場合に主に起こります。

スタックオーバーフローによると、oauthのURL認証の際に、URLにスコープのURLが含まれているため、これを最終チェックとして用いると良いことが示されています。

Visiting https://www.googleapis.com/oauth2/v1/tokeninfo?access_token=xxxxxx is a good way to verify
curl – Why is Google Calendar API (oauth2) responding with ‘Insufficient Permission’? – Stack Overflow

参考:

python – Google API (Sheets) API Error code 403. Insufficient Permission: Request had insufficient authentication scopes – Stack Overflow
curl – Why is Google Calendar API (oauth2) responding with ‘Insufficient Permission’? – Stack Overflow
javascript – Google Cloud OAuth2 scopes not updating – Stack Overflow

参考

その他、Google Sheets APIをnode.jsで使う場合の参考・レファレンスを以下に紹介します。

sheets api samples・サンプル:

Basic Writing  |  Sheets API  |  Google Developers

sheets api guide・ガイド (node.js, javascript, python, php, java, ruby etc.):

Reading & Writing Cell Values  |  Sheets API  |  Google Developers

googleapisにおけるnode.js参考:

Method: spreadsheets.values.get  |  Sheets API  |  Google Developers
googleapis documentation

Google sheets APIにおけるscopes・スコープ:

Authorize Requests  |  Sheets API  |  Google Developers

Sheets APIをcurlなどから使う場合:

Basic Reading  |  Sheets API  |  Google Developers
Basic Writing  |  Sheets API  |  Google Developers

node-google-spreadsheetを使う

上記までの紹介ではnode.jsのSheets APIのライブラリとしてGoogleが公式に提供するgoogleapisを使用しましたが、その他にもサードパーティのライブラリがあります。その一つがnode-google-spreadsheetです。

Github:

theoephraim/node-google-spreadsheet: Google Sheets API (v4) wrapper for Node.js

node-google-spreadsheetについてはgoogleapisを使用する前に少し触っていましたが、読み込みはできたのですが書き込みで躓いてしまいました。

取れる時間も多くないので、googleapisの使用に移行しましたが、
以下にnode-google-spreadsheetの使うための環境設定とAPI追加手順、node-google-spreadsheetのリファレンス・参考文献およびシートの読み込み・readをする例のサンプルプログラムを紹介します。

google sheets apiのnode-google-spreadsheetを使う

まずディレクトリとnpm initでpackage.jsonを作成し、node-google-spreadsheetをインストールします。

npm i google-spreadsheet

なお、私の環境のnode.jsおよびライブラリと依存関係のバージョンは以下となります。

> 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",
  }
}

サービスアカウントを作成します。
jsonファイルを保存します。

サービスアカウントを作成する

以下サンプルプログラムになります。

以下を適時書き換えて、実行をします。

sheet_id .. スプレッドシートのID; 例: 1jMCd0flts..
client_email .. 保存したjsonファイルのclient_emailプロパティ
private_key .. 保存したjsonファイルのprivate_keyプロパティ


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);    
})();

参考:

Authentication
Overview
GoogleSpreadsheetRow
GoogleSpreadsheetCell

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です