はじめに
Node.jsでGoogleスプレッドシートを読み書きする。
使うもの：
- Node.js v10.16.0
- Google Sheets API v4
- Visual Studio Code
Google Sheets API を使えるようにする
Node.js Quickstart | Sheets API | Google Developers
上記サイトを参考に、まず Google Sheets API を使えるようにする。「Enable the Google Sheets API」のボタンを押してしばらく待つと、Google Cloud PlatformにQuickstartという名前のプロジェクトが作成され、Client IDやシークレットと共に設定ファイルをダウンロードできるようになる。
↓クリックしてしばらく待った後の画面。
ダウンロードしたcredentials.jsonは、Node.jsプロジェクトのルートフォルダなど扱いやすい場所に置く。ダウンロードしそこねた場合は、Developer Console→APIとサービス→認証情報からダウンロードできる。
ソース管理しているフォルダに配置した場合は、credentials.jsonをソース管理対象外にして、うっかりコミットすることを防いでおく。
ライブラリのインストール
VS Code のターミナル、またはWindowsコマンドウィンドウで以下を実行する。
npm install googleapis
Visual Studio Code で readline を使えるようにする
Quickstartのサンプルでは、OAuthトークンをreadlineで受け取ろうとしている。しかし、Visual Studio Codeのコンソールは出力専用なので、VS Codeでスクリプトを実行すると入力の受け取りができない。このままではデバッグできないので、launch.jsonの設定を変え、コンソールを外部のものにする必要がある。
launch.jsonを開くには、メニューの「デバッグ」→「構成を開く」を選ぶ。
するとlaunch.jsonが開くので、configurationsの中に以下を追加する。
"console": "externalTerminal"
これでデバック実行時にコマンドプロンプトが起動するようになり、コンソール入力の受け取りが可能になる。
サンプルの実行
Quickstartに載っているサンプルをコピーし、プロジェクトルートのindex.jsに貼り付ける。これでサンプルを実行できる状態になった。
↓実行できる状態のフォルダ
メニューの「デバッグ」→「デバッグの開始」で実行すると、Windowsの黒いコンソール画面が起動し、認証のためのURLが表示されるので、それをコピーしてブラウザに貼り付ける。
Quickstartのアプリを許可するアカウントを選ぶ。
Quickstartのアプリは先ほど作ったばかりでGoogleが安全と確認していない。そのためセキュリティ警告が表示されるが、詳細を表示して「Quickstartに移動」を選ぶ。
この後、スプレッドシートの読み取りの許可を与えるかどうか確認されるので、「許可」を選択する。許可が終わると、アプリ側へ返すためのコードが表示されるので、これをコピーする。
コピーしたコードをコンソールに貼り付けると、スプレッドシートの内容が読み取られてコンソールに表示される。
これでQuickstartの実行は成功である。
ちなみに元データとなったスプレッドシートは、Googleがサンプルとして提供しているものを使っている。
https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
OAuth認証を得るまでのサンプル
Quickstartのものをそのまま使っても良いが、コールバック地獄が個人的に好きではないので、同期的にスクリプトを書けるようにしたものを以下に掲載する。spreadsheet.jsのinit()メソッドを呼び出せば、必要な場合にOAuthトークンをリクエストし、リクエスト済みであれば、保存したトークンを読み込むようにしている。
const fs = require("fs");
const readline = require("readline");
const {google} = require("googleapis");
const SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]; // 読み書き可
const CREDENTIALS_PATH = "credentials.json"; // アプリ側の認証情報
const TOKEN_PATH = "token.json"; // トークン保存場所
let oAuth2Client = null; // API実行に必要な認証情報
/**
* モジュール初期化。
* token.jsonがあればそれを読込み、無ければユーザーに認証を求める。
*/
module.exports.init = async function() {
// 認証
let credentialContent = fs.readFileSync(CREDENTIALS_PATH);
let credentials = JSON.parse(credentialContent);
oAuth2Client = new google.auth.OAuth2(
credentials.installed.client_id
, credentials.installed.client_secret
, credentials.installed.redirect_uris[0]);
if (!fs.existsSync(TOKEN_PATH)) {
await getNewToken();
}
let tokenContent = fs.readFileSync(TOKEN_PATH);
let token = JSON.parse(tokenContent);
oAuth2Client.setCredentials(token);
};
/**
* 新しいトークンを取得する
*/
async function getNewToken() {
const authUrl = oAuth2Client.generateAuthUrl({
access_type: "offline",
scope: SCOPES});
console.log("このURLへアクセスしてアプリを承認してください：", authUrl);
const rl = readline.createInterface({
input: process.stdin,
output: process.stdout
});
return new Promise(function(resolve, reject) {
rl.question("承認後に表示されたコードを入力してください：", async (code) => {
rl.close();
let {tokens} = await oAuth2Client.getToken(code);
// tokenを保存する
fs.writeFileSync(TOKEN_PATH, JSON.stringify(tokens));
console.log("トークンを以下のファイルへ保存しました。", TOKEN_PATH);
});
});
}
スプレッドシートIDの取得方法
読み書きするスプレッドシートは、スプレッドシートIDというもので特定する。
ブラウザでスプレッドシートを開いたときのURLの、/d/から/editの間がスプレッドシートIDとなる。これをコピーしてスクリプトで使用する。
範囲(range)の指定方法
基本的にExcelと同じ。
例：
Sheet1!A1:B2-
Sheet1!A:AA列すべてのセル -
Sheet1!1:21～2行目のすべてのセル -
Sheet1!A5:AA5以降のA列すべてのセル -
A1:B2シート名を省略した場合、最初の可視なシートが対象になる。 -
Sheet1Sheet1シートすべてのセル
セルの取得
sheets.spreadsheets.values.get()を使う。
Method: spreadsheets.values.get | Sheets API | Google Developers
以下のスプレッドシートから取得する場合のサンプル。
const {google} = require("googleapis");
const SPREADSHEET_ID = "xxxxx";
let oAuth2Client = null; // API実行に必要な認証情報
module.exports.getSheetData = async function(){
const sheets = google.sheets({version: "v4"});
const param = {
spreadsheetId: SPREADSHEET_ID,
range: "シート1",
auth : oAuth2Client
};
let response = await sheets.spreadsheets.values.get(param);
let data = response.data;
console.log(JSON.stringify(data));
}
↓実行結果(変数dataの中身)
{
"range":"'シート1'!A1:Z1000"
,"majorDimension":"ROWS"
,"values":
[
["日付","商品","個数"]
,["2019/12/1","りんご","10"]
,["2019/12/2","バナナ","20"]
,["2019/12/3","みかん","30"]
]
}
データは2次元配列になっていて、日付も数値も文字列として取得される。
セルの更新
単純な更新
Method: spreadsheets.values.update | Sheets API | Google Developers
↓サンプル
const {google} = require("googleapis");
const SPREADSHEET_ID = "xxxxx";
let oAuth2Client = null; // API実行に必要な認証情報
module.exports.addData = async function(){
const sheets = google.sheets({version: "v4"});
const param = {
spreadsheetId: SPREADSHEET_ID,
range: "シート1!A5",
valueInputOption: "USER_ENTERED",
auth : oAuth2Client,
resource : {
values : [["2019/12/4", "もも", "40"]]
}
};
await sheets.spreadsheets.values.update(param);
};
↓実行結果
表の末尾にデータを追加する
Method: spreadsheets.values.append | Sheets API | Google Developers
const {google} = require("googleapis");
const SPREADSHEET_ID = "xxxxx";
let oAuth2Client = null; // API実行に必要な認証情報
module.exports.appendData = async function(){
const sheets = google.sheets({version: "v4"});
const param = {
spreadsheetId: SPREADSHEET_ID,
range: "シート1!A1", // 表を探索する場所を指定する。
valueInputOption: "USER_ENTERED",
insertDataOption : "INSERT_ROWS",
auth : oAuth2Client,
resource : {
values : [["2019/12/4", "もも", "40"]]
}
};
await sheets.spreadsheets.values.append(param);
};
実行結果は、A5にupdateした場合と同じ。
InsertDataOption
新しいデータをどうやって挿入するかのオプション。
-
OVERWRITE- 単純に既存データの末尾のセルに新しいデータを書き込む。 -
INSERT_ROWS- 新しいデータを書き込む前に行が挿入される。そのため、既存データの位置がその分下がる。
既存の値を残しつつ上書きする
更新値にnullを指定した場合、その部分は既存データが残って上書きされない。
module.exports.overwrite = async function(){
const sheets = google.sheets({version: "v4"});
const param = {
spreadsheetId: SPREADSHEET_ID,
range: "シート1!A2",
valueInputOption: "USER_ENTERED",
auth : oAuth2Client,
resource : {
values : [[null, "ゴリラ", "99"]] // 1列目は上書きしない
}
};
let response = await sheets.spreadsheets.values.update(param);
};
↓実行結果
