参考: Google Sheets のデータを JSON 形式で取得する Web API をサクッと作る
Google Sheets にデータを追加、更新、削除する Web API を、10分くらいでサクッと作ります。
Spreadsheet の1列目(A列) は uuid にして、2列目（B列）以降は好きなデータ項目を書いていきます。
ここではToDo管理アプリをイメージして task, status にしました。
使い方
追加 (POST)
method を指定せずデータを POST します。uuid 以外の項目をデータとして渡します。
$ curl -X POST -F 'task=牛乳をあげる' -F 'status=in_progress' <発行したURL>
更新 (UPDATE)
method に update を指定します。URL に &method=update をつけるか、データに -F 'method=update' を追加します。uuid を含む全ての項目をデータとして渡します。
$ curl -X POST -F 'uuid=<UUID>' -F 'task=牛乳を売る' -F 'status=in_progress' <発行したURL>?method=update
削除 (DELETE)
method に delete を指定します。URL に &method=delete をつけるか、データに -F 'method=delete' を追加します。uuidをデータとして渡します。
$ curl -X POST -F 'uuid=<UUID>' <発行したURL>?method=delete
手順
Script Editor でコードを書く
シート上部のメニュー Tools > Script Editor... を選び、Code.gs に以下のコードを記入します。ここではシート名がSheet1の場合で記述していますので、適宜変更してください。
const UUID = 'uuid';
const LOCK_TIMEOUT_MILLIS = 500;
/**
* オブジェクトをシートの行に変換する
*
* @param {SpreadsheetApp.Sheet} sheet - シート。一行目の見出しに、オブジェクトの属性名を記載しておく。
* @param {Object} obj - 行に変換するオブジェクト。
* @return {string[]} - オブジェクトから変換された行。
*/
function createRow_(sheet, obj) {
const keys = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
return keys.map(key => {
const value = obj[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;
});
}
/**
* オブジェクトをシートに追加する
*
* @param {SpreadsheetApp.Sheet} sheet - シート。一行目の見出しに、オブジェクトの属性名を記載しておく。
* @param {Object} obj - スプレッドシートに追加するオブジェクト。
* @return {Object} - 追加されたオブジェクト。uuid属性が追加される。
*/
function appendRow_(sheet, obj) {
obj[UUID] = Utilities.getUuid();
const row = createRow_(sheet, obj);
// appendRowはatomicなのでlockは取得しない。
// https://developers.google.com/apps-script/reference/spreadsheet/sheet?hl=en#appendRow(Object)
sheet.appendRow(row);
return obj;
}
/**
* シートに追加されたオブジェクトを更新する
*
* @param {SpreadsheetApp.Sheet} sheet - シート。一行目の見出しに、オブジェクトの属性名を記載しておく。
* @param {Object} obj - 更新後のオブジェクト。スプレッドシートに追加されたときのuuidを属性に持つ。
* @return {Object} - 追加されたオブジェクト。uuid属性が追加される。
*/
function updateRow_(sheet, obj) {
const row = createRow_(sheet, obj);
const values = sheet.getDataRange().getValues();
values.some((value, index) => {
const uuid = value[0];
if (uuid === row[0]) {
sheet.getRange(index + 1, 1, 1, value.length).setValues([row]);
return true;
}
});
return obj;
}
/**
* シートに追加されたオブジェクトを削除する
*
* @param {SpreadsheetApp.Sheet} sheet - シート。一行目の見出しに、オブジェクトの属性名を記載しておく。
* @param {Object} obj - 更新後のオブジェクト。スプレッドシートに追加されたときのuuidを属性に持つ。
* @return {Object} - 追加されたオブジェクト。uuid属性が追加される。
*/
function deleteRow_(sheet, obj) {
const row = createRow_(sheet, obj);
const lock = LockService.getDocumentLock();
lock.waitLock(LOCK_TIMEOUT_MILLIS);
try {
const values = sheet.getDataRange().getValues();
values.some((value, index) => {
const uuid = value[0];
if (uuid === row[0]) {
sheet.deleteRow(index + 1);
return true;
}
});
} finally {
lock.releaseLock();
}
return obj;
}
function doPost(e) {
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
if (e.parameter.method) {
const method = e.parameter.action.toLowerCase();
if (method === 'update') {
updateRow_(sheet, e.parameter);
} else if (method === 'delete') {
deleteRow_(sheet, e.parameter);
}
} else {
appendRow_(sheet, e.parameter);
}
}
Web app として公開する
Google Sheets のデータを JSON 形式で取得する Web API をサクッと作る と同じです。
補足
ポイント
UUIDを追加時に払い出すことで、更新時、削除時に行を特定できるようにしています。UUIDの生成にはUtilities.getUuid()を利用します。複数クライアントから同時アクセスがあった場合に、Idの重複を避けるためです。
追加に使っている Sheet.appendRow() は同時に実行しても競合しない atomic なメソッドです。
appendRow(Object) | Class Sheet | Apps Script | Google Developers
https://developers.google.com/apps-script/reference/spreadsheet/sheet#appendRow(Object)
その一方、削除では Sheet.getDataRange().getValues() を使ってどの行を削除するかを調べている間に同時アクセスがあり、対象の行数が変化する恐れがあるため、Lock を使っています。
Lock Service | Apps Script | Google Developers
https://developers.google.com/apps-script/reference/lock
日付を表すDate型の属性は、Utilities.formatDate()を使って日本時間に変換して保続しています。
formatDate(date, timeZone, format) | Class Utilities | Apps Script | Google Developers
https://developers.google.com/apps-script/reference/utilities/utilities#formatdatedate,-timezone,-format
また、属性がObject型の場合は、JSON.stringify()でJSON文字列にして保存しています。doGet()で情報を取得するときに、気を付けてください。
HTTP Method に DELETE や PUT は使えないの？
GAS には doGet(e), doPost(e) しかないようです。仕方がないのでパラメーター method で update, delete を指定しています。
返却されるレスポンス
残念ながら、きれいなレスポンスを返す方法はありません。なのでレスポンスは何も返していません。なので、実行すると以下のエラーが返却されます。
<!DOCTYPE html><html><head><link rel="shortcut icon" href="//ssl.gstatic.com/docs/script/images/favicon.ico"><title>エラー</title><style type="text/css">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><div><img src="//ssl.gstatic.com/docs/script/images/logo.png"></div><center>スクリプトが完了しましたが、何も返されませんでした。</center></body></html>
レスポンスがきれいにならない理由:
- UrlFetchApp.HTTPResponse クラスを使いたいけど、直接コンストラクタを使えない。
- ContentService.createTextOutput() で返却すると、クライアント側がリダイレクトに追随する必要がある。