GoogleSpreadSheetをGoogleAppsScript使用して、JSON APIにする

やりたいこと

管理ツール作るのめんどくさいし、どうせマスターデータしか管理するものないので、この際Google大先生におんぶにだっこしてもらいたい。

• マスターデータをGoogleSpreadSheetで管理する。
• その内容をJSONに変換して出力する。
• APIとして使いたいので、URLも同時に発行する。

やりかた

GoogleSpreadSheetを作成する

user

address

itemIdList

GoogleAppsScriptを書く

次にGoogleAppsScriptを書くためにスクリプトエディタを開く。

下記の様なGoogleAppsScriptを書く。

main.gs

var book = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheet/ccc?key=<ここにGoogleSpreadsheetのID>');

function doGet(e) {

  var json = convertSheet2JsonText(book.getSheetByName('user'), book.getSheetByName('itemIdList'))[0];
  json.address = convertSheet2JsonText(book.getSheetByName('address'))[0];

  return ContentService.createTextOutput(JSON.stringify(json)).setMimeType(ContentService.MimeType.JSON);
}

function convertSheet2JsonText(sheet, arrayObjSheet) {
  
  var startKeyRowIndex = 2;
  var startKeyColumnIndex = 2;
  var startValueRowIndex = 3;
  var startValueColumnIndex = 2;

  var jsonArray = [];
  var keyRow = sheet.getRange(startKeyRowIndex, startKeyColumnIndex, 1, sheet.getLastColumn() - startValueColumnIndex + 1).getValues()[0];
  var valueRows = sheet.getRange(startValueRowIndex, startValueColumnIndex, sheet.getLastRow() - startKeyRowIndex, sheet.getLastColumn() - startValueColumnIndex + 1).getValues();
  for(var i = 0; i < valueRows.length; i++) {
    var json = {};
    for(var j = 0; j < keyRow.length; j++) {
      json[keyRow[j]] = valueRows[i][j];
    }
    if (arrayObjSheet) {
      json.array = arrayObjSheet.getRange(i + startValueRowIndex, startValueColumnIndex, 1,  arrayObjSheet.getLastColumn() - 1).getValues()[0];
    }
    jsonArray.push(json);
  }
  
  return jsonArray;
}

APIとして、公開する

実際に叩いてみる

やったー！

さいごに

ツッコミどころとか改良点とかは、まだまだアリそうだけど、ひと通りやりたいコトは出来そうな感じ。
GoogleAppsScriptは、なんとなく書けばなんとなく動くし色々揃ってるっぽい。
(Logger.log(obj)とか書くと、ログ出力してくれるのでデバッグも簡単だった。)

URLが発行されるし、簡単な管理ツールだけでなくて、一時的なモック作るのもこのやり方が一番簡単かな〜って、思った。

---

補足：速度も気にする場合

複雑なJSONを出力するとき、結構レスポンスタイムが遅いので気になる。
対策として、一度JSONファイルと出力して、APIではそのファイルを返すことにする。

main.gs

var fileName = 'hogefuga-master.json';
var folderId = '<GoogleドライブのフォルダID>';

function doGet(e) {
  return ContentService.createTextOutput(DriveApp.getFolderById(folderId).getFilesByName(fileName).next().getBlob().getDataAsString()).setMimeType(ContentService.MimeType.JSON);
}

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu('マスタ更新', [{name: 'JSON出力', functionName: 'updateMasterData'}]);
}

function updateMasterData() {

  var book = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheet/ccc?key=<ここにGoogleSpreadsheetのID>');

  var json = convertSheet2JsonText(book.getSheetByName('user'), book.getSheetByName('itemIdList'))[0];
  json.address = convertSheet2JsonText(book.getSheetByName('address'))[0];  

  var targetFolder = DriveApp.getFolderById(folderId);
  var targetFile = targetFolder.getFilesByName(fileName);
  if(targetFile.hasNext()) {
    targetFolder.removeFile(targetFolder.getFilesByName(fileName).next()).createFile(fileName, JSON.stringify(json), ContentService.MimeType.JSON); 
  } else {
    targetFolder.createFile(fileName, JSON.stringify(json), ContentService.MimeType.JSON);
  }
}

function convertSheet2JsonText(sheet, arrayObjSheet) {

  var startKeyRowIndex = 2;
  var startKeyColumnIndex = 2;
  var startValueRowIndex = 3;
  var startValueColumnIndex = 2;

  var jsonArray = [];
  var keyRow = sheet.getRange(startKeyRowIndex, startKeyColumnIndex, 1, sheet.getLastColumn() - startValueColumnIndex + 1).getValues()[0];
  var valueRows = sheet.getRange(startValueRowIndex, startValueColumnIndex, sheet.getLastRow() - startKeyRowIndex, sheet.getLastColumn() - startValueColumnIndex + 1).getValues();
  for(var i = 0; i < valueRows.length; i++) {
    var json = {};
    for(var j = 0; j < keyRow.length; j++) {
      json[keyRow[j]] = valueRows[i][j];
    }
    if (arrayObjSheet) {
      json.array = arrayObjSheet.getRange(i + startValueRowIndex, startValueColumnIndex, 1,  arrayObjSheet.getLastColumn() - 1).getValues()[0];
    }
    jsonArray.push(json);
  }

  return jsonArray;
}

体感速度で結構変わったので、満足した(｀・ω・´)