SpreadSheetを簡易databaseにしたAPIができないか
調べたらできそうだったので下記の資料を参考に実装してみました。
参考にさせていただいた資料
Google Sheets にデータを追加、更新、削除する Web API をサクッと作る
https://qiita.com/takatama/items/e5cb83012d14c0094a79
【GAS】スプレッドシート内の全シートへのリンク一覧を作る
https://qiita.com/okNirvy/items/d1a2f4918cff8e63dcac
Google Apps ScriptのdoPostでJSONなパラメータのPOSTリクエストを受ける
https://qiita.com/shirakiya/items/db22de49f00710478cfc
JSのOOP
oopでjsを書く練習をしたかったため、functionでオブジェクトを定義するoopで実装しました。
実装するAPIの仕様
- GET で指定したシートの全レコードを取得する
- POST で指定したシートに新しい行を挿入する。
（削除なども実装できそうでしたが今回は割愛します。）
SpreadSheetDatabaseの実装
スプレッドシート管理オブジェクト「SpreadSheetDatabase」の実装は下記のとおり行いました。
targetSheetNameで操作対象シートオブジェクトを初期化して、レコードを取得、追加できるようにします。
/**
* スプレッドシート管理
* @param targetSheetName:string
*/
function SpreadSheetDatabase(targetSheetName) {
const _this = this;
// targetSheetNameでシートを初期化
const book = SpreadsheetApp.getActive();
const sheet = book.getSheets().find(sheet => sheet.getName() === targetSheetName);
if(!sheet){
throw new Error('シートが存在しません');
}
// 1行目はカラムキー行
_this.columnKeys = function () {
return allRows().splice(0, 1)[0];
};
// 2行目以降はレコード行
_this.allRecords = function () {
const records = allRows();
return records.slice(1, records.length);
};
// 全行取得
function allRows() {
return sheet.getDataRange().getValues();
};
// 行の挿入
_this.insertRow = function(row) {
if(!Array.isArray(row)){
return;
}
return sheet.appendRow(row);
};
}
SpreadSheetAPIControllerの実装
APIコントローラーオブジェクト「SpreadSheetAPIController」の実装は下記のとおり行いました。
handleGet 、handlePostをpublicメソッドとして持ち、それぞれリクエストされたシートのSpreadSheetDatabaseオブジェクトを用いてデータを操作します。
（eオブジェクトからdataを取得する個所などの共通モジュールをAppUtilsにまとめています。）
/**
* APIコントローラー
*/
function SpreadSheetAPIController() {
const _this = this;
let db;
/**
* 初期化
* @param e:request変数
* @param method:string
*/
function initDB(e, method='GET') {
let sheet = getTargetSheetNameFromData(
parseDataWithMethodName(e, method)
);
// 指定スプレッドシートの管理インスタンスを生成
db = makeTargetSpreadSheetDatabase(
sheet
);
}
/**
* @method GET
*/
_this.handleGet = function (e) {
initDB(e, 'get');
return AppUtils.convertRowsToAPIResult(
db.allRecords(),
db.columnKeys()
);
}
/**
* @method POST
*/
_this.handlePost = function(e) {
initDB(e, 'post');
db.insertRow(
AppUtils.convertPostDataToRow(
AppUtils.parsePostData(e),
db.columnKeys()
)
);
}
/** シート名が見つからないかった際の使用シート名 */
const DEFAULT_SHEET_NAME = 'シート1';
/**
* 指定されたシート名を取得
* @param data:object
* @return sheet:string
*/
function getTargetSheetNameFromData(data) {
return (data && data.sheet) ? data.sheet : DEFAULT_SHEET_NAME;
}
/**
* 指定されたmethod名に対応してdataを取得
* @param e:request変数
* @param method:string
* @return data:object
*/
function parseDataWithMethodName(e, method = 'GET') {
if(method.toLowerCase() == 'get') {
return AppUtils.parseGetParams(e);
}
if(method.toLowerCase() == 'post') {
return AppUtils.parsePostData(e);
}
}
/**
* 指定したスプレッドシートの管理インスタンスを作成
* @return db:SpreadSheetDatabase
*/
function makeTargetSpreadSheetDatabase(sheet) {
return new SpreadSheetDatabase(sheet);
}
}
/**
* utilities
*/
const AppUtils = {
parseGetParams(getE) {
return getE.parameter;
},
parsePostData(postE) {
return JSON.parse(postE.postData.getDataAsString());
},
convertPostDataToRow(postData, keys) {
return keys.map(key=>{
const value = postData[key];
if (value instanceof Date) {
return Utilities.formatDate(value, 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss');
}
if (typeof value === 'object') {
return JSON.stringify(value);
}
return value;
});
},
convertRowsToAPIResult(rows, keys) {
const result = rows.map(row => {
const obj = {};
row.map((item, index) => {
obj[String(keys[index])] = String(item);
});
return obj;
});
return result;
},
}
Response の実装
ContentsServiceのcreateTextOutput (https://developers.google.com/apps-script/reference/content/content-service) を使い、APIの結果のjsonを返します。
失敗時は、Abortでerror.messageを受け取ってResponseを作ります。
GASの実装は初めてだったのですが、Web Appを作る場合、doGet、doPostの返り値として任意statuscodeを持つHTTPResponseを作ることができないようでした。
つまり、400エラー500エラーをAPIが任意に送信できないようです。
（商用サービスの実装でなく、学習用の簡易のDatabaseとする目的のためなら問題はないと思います。）
/**
* @param data:object
* @return output:TextOutput
*/
function Response(data) {
return ContentService.createTextOutput(JSON.stringify(data, null, 2))
.setMimeType(ContentService.MimeType.JSON);
}
/**
* @param error:Error
* @param etc:any
* @return output:TextOutput
*/
function Abort(error, etc) {
const errorResponse = {
error: {
message:error.message,
}
}
errorResponse.etc = etc;
return Response(errorResponse);
}
doGet、doPostの実装
const contoller = new SpreadSheetAPIController();
function doGet(e) {
try{
let result = contoller.handleGet(e);
return Response(result);
} catch(error) {
return Abort(error);
}
}
function doPost(e) {
contoller.handlePost(e);
}
デプロイ後のAPIの実行結果
GET
- doGetが実行され、全行が取得されます。
curl -L -X POST https://script.google.com/macros/s/GASのWebサービスのデプロイ時のURL/exec
[
{
"task": "a",
"status": "b",
"etc": "c"
},
{
"task": "牛乳をあげる",
"status": "in_progress",
"etc": ""
}
]
- POST
doPostが実行され、新規行が追加されます。
curl -X POST -H "Content-Type: application/json" -d '{"status": "AAA", "task":"BBB"}' https://script.google.com/macros/s/GASのWebサービスのデプロイ時のURL/exec
<!DOCTYPE html><html><head><link rel="shortcut icon" href="//ssl.gstatic.com/docs/script/images/favicon.ico"><title>エラー</title><style type="text/css" nonce="hzMivqfkAYlouW/btFBSyw">body {background-color: #fff; margin: 0; padding: 0;}.errorMessage {font-family: Arial,sans-serif; font-size: 12pt; font-weight: bold; line-height: 150%; padding-top: 25px;}</style></head><body style="margin:20px"><div><img alt="Google Apps Script" src="//ssl.gstatic.com/docs/script/images/logo.png"></div><div style="text-align:center;font-family:monospace;margin:50px auto 0;max-width:600px">スクリプトが完了しま したが、何も返されませんでした。</div></body></html>
最終的な実装
以上です。