はしがき
この前遊びでちょっとしたワークフローサービスみたいなのを作ったときの話。
MySQL等の本物DBは扱いが面倒くさいのと、デプロイしても無料でデータベースを使いたいという理由からGoogle スプレッドシートを無理やりDB運用しました。
Google スプレッドシートだとデータベースへのアクセス・確認と直接のデータ操作が容易でUIもきれいなので非エンジニアでも使えるDBとして個人的には結構有用かと思います。
色々な記事を参考にしながら作りましたが、バージョンの違いや認証方法の違いなどで苦労しつつ、サービスアカウントを使った、一番簡単な方法で実装できたかと思います。
環境
フレームワーク: Node.js
API: Google APIs
やり方
Google Cloudでの作業
Google Cloudにアクセスしてコンソールを開き、新規プロジェクトを作成してください。
ナビゲーションメニューからAPIとサービスに進んで、Google Sheets APIを有効にしてください。
続きまして、APIとサービス>認証情報に進んでください。
認証情報を作成からサービスアカウントを選んでください。
適当なIDを付けたあと、このサービスアカウントにプロジェクトへのアクセスを許可するで編集者ロールを付けてあげてから作成してください。
作成後、作ったサービスアカウントを選択して鍵からキーを追加>新しい鍵を作成に進んでください。
形式はそのままJSONにしてください。作成すると自動的にファイルがダウンロードされます。これは後で使うものです。
このJSONファイルはその名の通り鍵です。絶対に外部へ流出しないようにしてください。
Google Cloudでの作業は以上ですが、最後にサービスアカウントのIDをコピーしてください。
Google スプレッドシートでの作業
DB用のスプレッドシートを作ります。
適当に命名した後、共有から先程コピーしたサービスアカウントのIDで共有してください。
Google スプレッドシートでの作業はこれだけです。
APIのインストール&コーディング
プロジェクトディレクトリでターミナルからGoogle APIsをインストールします。
npm install googleapis
先ほどダウンロードされたJSONファイルを任意のフォルダ(publicとか)に投げます。
導入したいjsファイルで以下のコードを挿入します。
// モジュール関係/定数定義
const KEYFILE_PATH = "[先ほどのJSONファイルのパス]";
const { google } = require("googleapis");
// SPREADSHEET_IDはスプレッドシートのURLの~/spreadsheets/d/[ココ]/edit~の[ココ]の部分です。
const SPREADSHEET_ID = "[スプレッドシートのID]"
//認証関係(Google スプレッドシート用です)
function googleAuthenticate(){
console.log("||Start Authentication||")
try {
// ここで認証。
const auth = new google.auth.GoogleAuth({
keyFile: KEYFILE_PATH,
scopes: ['https://www.googleapis.com/auth/spreadsheets'],
});
console.log("||Authentication successful||");
// ここでGoogle Sheets APIを返しています。なので関数名とはちょっと矛盾しています(笑)。
return google.sheets({version: "v4", auth});
} catch(error) {
console.log(error);
}
}
以上がGoogle スプレッドシートを操作するために必要な認証関係です。
サンプルコード
以下で操作のサンプルコードを紹介します。
関数以外のコードはExpressのgetやpostの中(?)に書かないとエラーを吐きます。
requireを使うのがだめなようなのですが、今回はExpress環境で動くので良いことにさせてください。
↓今回のサンプルデータベース
Google SheetsをDB化するにあたって取得したデータからはレコードの行番号を取得できないのでF列に=ROW()を入れることで行番号を無理やり取得できるようにしています。工夫ポイントです(^^)
データ取得(Select)
async function getData(sheet){
// Google Sheets APIのインスタンス化
const sheets = googleAuthenticate();
try {
// データ取得
const result = await sheets.spreadsheets.values.get({
spreadsheetId: SPREADSHEET_ID, // スプレッドシートのID
range: `${sheet}!A2:F` // 取得する範囲指定
});
return result.data;
} catch (error) {
console.log(error);
}
}
const data = await getData("シート1");
console.log(data);
実行結果
||Start Authentication||
||Authentication successful||
{
range: "'シート1'!A2:F1000",
majorDimension: 'ROWS',
values: [
[ '1', '渋沢 栄一', '91', 'Male', '実業家', '2' ],
[ '2', '津田 梅子', '64', 'Female', '女子教育家', '3' ]
]
}
Google Sheets APIの戻り値はオブジェクトです。
データはvaluesに2次元配列でレコードごとに格納されています。
なのでデータだけを取り出したければconsole.log(data.values);になります。
また、要素はすべて文字列になります。数値などは適宜キャストする必要があります。
データ書き込み(Insert)
// この関数は最終行のレコードを取得するための関数です。
const getLastData = async (sheet) => {
const data = await getData(sheet);
if(data.values === undefined){
return null;
}
return data.values[data.values.length - 1];
}
async function addData(sheet, name, age, sex, job){
// Google Sheets APIのインスタンス化
const sheets = googleAuthenticate();
try {
// IDインクリメントのために最終行のレコードを取得
const lastData = await getLastData(sheet);
// インクリメント処理
let incrementId;
if(lastData === null){
incrementId = 1; // データがまだなければIDは1
}else{
incrementId = parseInt(lastData[0]) + 1; //最終レコードのID + 1
}
// データ追加(append)
const result = await sheets.spreadsheets.values.append({
spreadsheetId : SPREADSHEET_ID,
range: `${sheet}!A1`, // テーブルの左上端を指定。appendだと最後に挿入してくれます。
valueInputOption: "USER_ENTERED", // 入力データの解釈方法
insertDataOption: "INSERT_ROWS", // 入力データの挿入方法
// 追加するデータ
resource:{
values: [[incrementId, name, age, sex, job, "=ROW()"]]
}
})
console.log("||New record saved||");
console.log(result.data);
} catch (error) {
console.log(error);
}
}
// シート名, 名前, 年齢, 性別, 職業
await addData("シート1", "野口 英世", 51, "Male", "細菌学者");
const data = await getData("シート1");
console.log(data);
実行結果
||Start Authentication||
||Authentication successful||
||Start Authentication||
||Authentication successful||
||New record saved||
{
spreadsheetId: '1npUO3tdGDw73HwUGBt93OffZQv9H_lLI2TmGtzZ9aiY',
tableRange: "'シート1'!A1:F3",
updates: {
spreadsheetId: '1npUO3tdGDw73HwUGBt93OffZQv9H_lLI2TmGtzZ9aiY',
updatedRange: "'シート1'!A4:F4",
updatedRows: 1,
updatedColumns: 6,
updatedCells: 6
}
}
||Start Authentication||
||Authentication successful||
{
range: "'シート1'!A2:F1001",
majorDimension: 'ROWS',
values: [
[ '1', '渋沢 栄一', '91', 'Male', '実業家', '2' ],
[ '2', '津田 梅子', '64', 'Female', '女子教育家', '3' ],
[ '3', '野口 英世', '51', 'Male', '細菌学者', '4' ]
]
}
valueInputOption: というのはデータを入力した際に数式(=SUM()や=ROW())があった際にどう処理するかというものです。"USER_ENTERED"の場合、そのまま数式として処理されます。"RAW"とすると文字列として処理されます。
今回"USER_ENTERED"で"=ROW()"をインデックス5番に指定しているので、5列目(F列)に動的な行番号が記録されます。
データ書き換え(Update)
新紙幣のメンツのなかに旧紙幣の野口 英世さんが混ざってしまいました。
ということで野口 英世さんから北里 柴三郎さんに書き換えましょう。
// この関数はIDから特定のデータを取得するための関数です。
const getTargetedIdData = async (sheet, id) => {
try {
const data = await getData(sheet);
let target = [];
data.values.forEach((item) => {
if(parseInt(item[0]) === parseInt(id)) {
console.log(item);
console.log(item[0]);
target.push(item);
}
})
return target;
} catch(error) {
console.log(error);
}
}
async function updateData(sheet, id, name, age, sex, job){
// Google Sheets APIのインスタンス化
const sheets = googleAuthenticate();
try {
const targetData = await getTargetedIdData(sheet, id);
// データ書き換え
const result = await sheets.spreadsheets.values.update({
spreadsheetId : SPREADSHEET_ID,
range: `${sheet}!A${targetData[0][5]}`, // 対象レコードのF列に格納した行番号を指定
valueInputOption: "USER_ENTERED", // 入力データの解釈方法
// 書き換えるデータ。書き換えなくて良いところはnullを指定します。
resource:{
values: [[null, name, age, sex, job, null]]
}
})
console.log("||Record updated||");
console.log(result.data);
} catch (error) {
console.log(error);
}
}
// シート名, ID, 名前, 年齢, 性別, 職業
await updateData("シート1", 3, "北里 柴三郎", 78, "Male", "微生物学者, 教育者");
const data = await getData("シート1");
console.log(data);
この書き換え処理をするときに行番号が必要になってきます。
range: `${sheet}!A${targetData[0][5]}`
targetDataには書き換え対象のレコードが2次元配列で格納されており、そのレコードのF列(インデックス番号でいう5番)に格納されている行番号を取得してます。
この場合だとrange: `${sheet}!A${targetData[0][5]}`の変数代入後はrange: `シート1!A4`となります。
実行結果
||Start Authentication||
||Authentication successful||
||Start Authentication||
||Authentication successful||
[ '3', '野口 英世', '51', 'Male', '細菌学者', '4' ]
3
||Record updated||
{
spreadsheetId: '1npUO3tdGDw73HwUGBt93OffZQv9H_lLI2TmGtzZ9aiY',
updatedRange: "'シート1'!A4:E4",
updatedRows: 1,
updatedColumns: 4,
updatedCells: 4
}
||Start Authentication||
||Authentication successful||
{
range: "'シート1'!A2:F1001",
majorDimension: 'ROWS',
values: [
[ '1', '渋沢 栄一', '91', 'Male', '実業家', '2' ],
[ '2', '津田 梅子', '64', 'Female', '女子教育家', '3' ],
[ '3', '北里 柴三郎', '78', 'Male', '微生物学者, 教育者', '4' ]
]
}
新紙幣メンツが揃いましたね。
以上がサンプルコードです。
Deleteの方法は公式ドキュメントなどを参考にしながら作ってみてください(作れって言われたら作ります)。
ちなみに、このサンプルコードは見れば分かる通りGoogle Auth認証を処理ごとに何度も繰り返すようになっています。
無駄な認証リクエストをなくしたい場合は、ご自身でのコードの修正をお願いします(_ _)。
まとめ
最後の最後に適当なところが残っていますが、だいたいこんな感じです。v4でサービスアカウントを使って実装する方法がなかなかインターネットを調べても見つからず苦労しました。本当にお遊び程度ならデータの管理も楽ちんで、お金もかからないので、もしよかったら使ってみてください。
他のデータの取得方法などについては公式ドキュメントをご参照ください。
参考