GoogleSpreadSheetをDBみたいにするAPIを作成しました。

GoogleSpreadSheetをDBみたいにしてみた。

背景

アプリのモックを作成するときに、サーバーサイドの開発環境が用意できなかったため、GASの勉強も兼ねて、GoogleSpreadSheetで代用してみました。

GoogleSpreadSheet内を検索

コメントで参照したときの補完機能も充実してたので書いてみました。

gssTableSearch.gs

/**
 * 全件検索した結果を取得します。
 * @param {string} sheetName シートの名称
 * @return {obj} 取得したデータ
 */ 
function getData(sheetName) {
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var data = sheet.getDataRange().getValues(); // シートの中のデータを行単位で配列として入る
  var key = data.splice(0, 1)[0];  // 一番上の行をキー値として配列に格納する

  var returnData = data.map(function(row) {
    var obj = {};
  
    row.map(function(value, index) {
      
      // キー名を設定して、配列に格納する（連想配列にする）
      obj[String(key[index])] = String(value);
      
    });
    
    return obj;
  });
  
  return returnData;
}

GoogleSpreadSheetを更新する

追加、更新、削除をやってみます。
主キー重複のチェックとかは、面倒だったので作ってません。

gssTableUpdate.gs

/**
 * シート内の表形式に合わせた、一行を作成します。(2行以上のJSONの場合、2行目以降は無視します)
 * （最後にアンダーバーつけると外部参照できなくできなくなるみたい。便利！)
 * @param {string} sheetName シートの名称
 * @param {string} postJson JSON形式の文字列 
 */ 
function createRow_(sheetName, postJson) {
  var keys = sheetName.getDataRange().getValues()[0];
  var row = [];
  
  keys.map(function(key) {
    row.push(postJson[key]);
  });
  
  return row;
}

/**
 * データを追加します。
 * @param {string} sheetName シートの名称
 * @param {string} postJson JSON形式の文字列 
 */ 
function insert(sheetName, postJson) {
  var row = createRow_(sheetName, postJson);
  sheetName.appendRow(row);
}

/**
 * データを更新します。
 * A列を主キーとして扱います。
 * @param {string} sheetName シートの名称
 * @param {string} postJson JSON形式の文字列
 */ 
function update(sheetName, postJson) {
  var row = createRow_(sheetName, postJson);
  var data = sheetName.getDataRange().getValues();
  
  for (var i = 0; i < data.length; i++) {
    if (data[i][0] === row[0]) {
      sheetName.getRange(i + 1, 1, 1, data[i].length).setValues([row]);
    }
  }
}

/**
 * データを削除します。
 * A列を主キーとして扱います。
 * @param {string} sheetName シートの名称
 * @param {string} postJson JSON形式の文字列 
 */ 
function remove(sheetName, postJson) {
  var row = createRow_(sheetName, postJson);
  var data = sheetName.getDataRange().getValues();
  
  for (var i = 0; i < data.length; i++) {
    if (data[i][0] === row[0]) {
      sheetName.deleteRow(i + 1);
    }
  }
}

API作成

GETリクエストと、POSTリクエストの処理を作ります。
POST時の"action"クエリの値によって、更新モードを分岐させます。

本当はPUT、DELETEメソッド使いたかったんだけど、公式ドキュメントのHTTPメソッドのところにdoGet()、doPost()の仕様しか見つからなかったので妥協。

main.gs

function doGet() {
  var data = gssTableSearch.getData("シート1")
  return ContentService.createTextOutput(JSON.stringify(data, null, 2))
  .setMimeType(ContentService.MimeType.JSON);
}

function doPost(e) {
  var sheetName = SpreadsheetApp.getActive().getSheetByName('シート1');
  var postJson = JSON.parse(e.postData.contents);
  
  switch(e.parameter.action) {
    case "insert":
      gssTableUpdate.insert(sheetName, postJson);
      
      break;
 
    case "update":
      gssTableUpdate.update(sheetName, postJson);  

      break;
 
    case "delete":
      gssTableUpdate.remove(sheetName, postJson);
      
      break;
 
    default:
      console.log("動作モードが指定されていません。");
      break;
  }
}

WebAPIとして公開する。

1. 上部のタブから、「公開」を選択
2. Webアプリケーションとして導入
3. プロジェクトバージョンを最新にして、「公開」をクリック

すごくわかりやすく書いてくれている人がいるため、参考にしました。
今から10分ではじめる Google Apps Script(GAS) で Web API公開

あとがき

GASは初めて触りましたが、API代わりにすることもできるのは衝撃でした。
JavaScriptと同じなので、個人的に書きやすいのもいいですね。
あとExcelのVBA信者から逃げることができるのも高評価

GitHubに上げました。GAS_WebAPI