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

## やりたいこと

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

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

## やりかた

### GoogleSpreadSheetを作成する

`user`
![user.sheet](https://qiita-image-store.s3.amazonaws.com/0/1361/6093b1b8-18cf-7814-a820-39c5d5ceddbb.png)
  
`address`
![address.sheet](https://qiita-image-store.s3.amazonaws.com/0/1361/d10c8fef-80c5-ec67-897f-3e145cf9015f.png)

`itemIdList`
![itemIdList.sheet](https://qiita-image-store.s3.amazonaws.com/0/1361/f3617a7e-06d5-f539-d76f-9e46396836a6.png)

### GoogleAppsScriptを書く

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

![select_acript_editor](https://qiita-image-store.s3.amazonaws.com/0/1361/ee712c94-796d-5d2c-0522-fc161e66adbb.png)

下記の様な`GoogleAppsScript`を書く。

```js: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として、公開する

![pubWebApplication](https://qiita-image-store.s3.amazonaws.com/0/1361/94cc1f5a-8120-53a2-5120-b15c18c0acc0.png)

![pubDialog_one](https://qiita-image-store.s3.amazonaws.com/0/1361/fbefedea-2cea-6345-0b65-d076bb70f4d8.png)


![pubDialog_two](https://qiita-image-store.s3.amazonaws.com/0/1361/b24b4b06-b053-ea30-57e2-8adba062711b.png)

### 実際に叩いてみる

![accessPubAPI](https://qiita-image-store.s3.amazonaws.com/0/1361/456342ed-f35b-c0d1-a585-72ba52b9e236.png)

やったー！

## さいごに

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

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

---

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

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

```js: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;
}
```

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