- Published on
Sheets APIとgoogleapisでGoogleスプレッドシートの読み込み・書き込み
- Authors
- Name
- Shou Arisaka nyapp.buzz/shou
- short bio
- Z世代の情報技術者。Next.jsで自作SNSを個人開発中。
Googleの提供するサービスへAPIアクセスが可能なSheets APIと、そのライブラリであるgoogleapisをnode.jsで使用し、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 DevelopersGoogle Cloud Platform (GCP)にアクセスし、プロジェクトを選択します。
Google Cloud Platform (GCP)プロジェクトがまだ作成されていない場合は新規に作成をします。

sheets APIを有効化
sheets APIをアカウントで有効化します。 画面左のメニューを開き、 API and services・APIとサービス > library・ライブラリー へと進みます。
次の画面上で"sheets"などと適当に検索して、Google sheets APIを探します。見つかったらそれをクリック、先に進み有効化をします。 以下の画像では"manage"・管理となっていますが、有効化がされていない状態であれば enable・有効化 などの表示となるはずです。

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 へと進みます。

OAuth 2.0を設定していきます。

ステップ1のOAuth consent screenではapp name・名前、メールアドレスなどを任意のものに手早く入力します。
ステップ2のscopes・スコープでは Google Sheets API の /auth/spreadsheets を追加します。

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

OAuth client ID credentialを作成
OAuthをnode.jsで使用するためのクライアントID、APIキーのjsonファイルを生成します。 Credentialsページから画面上部からCredentialsを新規作成します。アプリケーションタイプはdesktop app・デスクトップアプリケーションを選択します。 作成完了後、jsonファイルの保存をします。このファイルを後にnode.jsプログラムで読み込みます。


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...

認証が完了すると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" の2x2のセル4つに対して、多次元配列 [["hoge", "hogehoge"],["fuga", "fugafuga"]]
を渡しその値にそれぞれのセルを更新・アップデートし変更を加えることをしています。
上記のコードのnodeで実行することで、以下(1)のようなスプレッドシートが、以下(2)のようにアップデートされます。
(変更前)

(変更後)

エラー: 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 Developerssheets api guide・ガイド (node.js, javascript, python, php, java, ruby etc.):
Reading & Writing Cell Values | Sheets API | Google Developersgoogleapisにおけるnode.js参考:
Method: spreadsheets.values.get | Sheets API | Google Developers googleapis documentationGoogle sheets APIにおけるscopes・スコープ:
Authorize Requests | Sheets API | Google DevelopersSheets 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.jsnode-google-spreadsheetについてはgoogleapisを使用する前に少し触っていましたが、読み込みはできたのですが書き込みで躓いてしまいました。
取れる時間も多くないので、googleapisの使用に移行しましたが、 以下にnode-google-spreadsheetの使うための環境設定とAPI追加手順、node-google-spreadsheetのリファレンス・参考文献およびシートの読み込み・readをする例のサンプルプログラムを紹介します。

まずディレクトリと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 GoogleSpreadsheetCell15歳でWordPressサイトを立ち上げ、ウェブ領域に足を踏み入れる。翌年にはRuby on Railsを用いたマイクロサービス開発に着手し、現在はデジタル庁を支えたNext.jsによるHP作成やSaaS開発のプロジェクトに携わりながら、React.js・Node.js・TypeScriptによるモダンなウェブアプリの個人開発を趣味でも行う。
フロントエンドからバックエンドまで一貫したアジャイルなフルスタック開発を得意とし、ウェブマーケティングや広告デザインも必要に応じて担当、広告運用・SEO対策・データ分析まで行う低コストかつ高品質な顧客体験の提供が好評。
国内外から200万人を超える人々に支えられ、9周年を迎えるITブログ「yuipro」の開発者、デザイナーでありライター。現在ベータ段階の自作SNS「nyapp.buzz」を日本一の国産SNSとするべく奮闘中。