ChatGPTでGoogleAppsScriptのコードを生成して、Json形式の簡易APIを作成した話

# 概要
* 通信を含むアプリ開発を勉強として作成してみていた
* GoogleスプレッドシートのデータをJsonで取得する形でAPIを実装することにした
* 無料で使えて要件にマッチするサービスがなかった
* ChatGPTにGoogleAppsScriptのコードを書かせて解決
* さらにパフォーマンスチューニングまで

# GoogleスプレッドシートのデータをJsonで取得できるAPIを作る
Googleスプレッドシートの1行目をキーとして、Json化してAPI化するサービスというのはいくつかあるようで
自分は最初[SSSAPI](https://sssapi.app/)というサービスを使ってみました。

開発序盤は良かったのですが、無料の場合3つまでしかAPIが作成できないというのが、今回の要件にマッチしませんでした…

(SSSAPI自体はめちゃくちゃ便利で、スプレッドシートのURLを貼り付けるだけでAPI化してくれます。
ただ、今回の場合4つAPIが欲しく、またコストもかけたくなかったので、他を検討する必要が出てきました。)

# ChatGPTに書かせる
ChatGPTの説明は省きますが、GASのコードを書かせることにしました。
![スクリーンショット 2023-02-26 19.02.54.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/46278/a01f5408-49b3-7a09-976c-05782c39f0d7.png)
```js:1発目の生成コード
function doGet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(1, 1, sheet.getLastRow(), 2);
  var values = range.getValues();
  
  var data = [];
  for (var i = 0; i < values.length; i++) {
    var row = {};
    row['column1'] = values[i][0];
    row['column2'] = values[i][1];
    data.push(row);
  }
  
  var response = ContentService.createTextOutput();
  response.setMimeType(ContentService.MimeType.JSON);
  response.setContent(JSON.stringify(data));
  
  return response;
}
```

これでも十分すごいのですが、1行目をキーにできていないので、以下みたいに言ってみると
![スクリーンショット 2023-02-26 19.05.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/46278/5c7c02c8-f590-63b6-1c3c-ab16069ffeb4.png)
```js:1行目をキーにする
function doGet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getDataRange();
  var headers = dataRange.getValues()[0];
  var numRows = dataRange.getNumRows();
  var numCols = dataRange.getNumColumns();
  var dataArray = [];
  
  for (var i = 1; i < numRows; i++) {
    var row = {};
    for (var j = 0; j < numCols; j++) {
      row[headers[j]] = dataRange.getCell(i+1,j+1).getValue();
    }
    dataArray.push(row);
  }
  
  var response = ContentService.createTextOutput();
  response.setMimeType(ContentService.MimeType.JSON);
  response.setContent(JSON.stringify(dataArray));
  
  return response;
}
```

ほぼ完成！

これでアプリの要件は満たしたようなものです。
その他自分でも手を入れつつ、いい感じに動くようになりましたが、一つ問題が。。

# パフォーマンスチューニングをChatGPTにやらせる
![スクリーンショット 2023-02-26 19.21.41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/46278/49d0f8ea-707c-bd33-ccaa-a9f2fff22a38.png)
遅い。

おそらくスプレッドシートの取得は結構時間がかかり、
平均7秒くらいかかってしまうAPIになってしまいました…

パフォーマンスのこともChatGPTにやらせてみるかとダメ元でやってみると

![スクリーンショット 2023-02-26 19.09.23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/46278/bf1b3307-8f51-0200-72fd-eea3b419245a.png)
4つほど提示してくれました。

1. バルク読み込み
2. キャッシュを利用する
3. Advanced Sheets Service を使う
4. Sheets API を直接使う

キャッシュで行ってみるか、ということで、コードを生成させて
![スクリーンショット 2023-02-26 19.10.34.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/46278/4124c5d2-4d46-2e0e-ec8d-48c7c6c9975a.png)
```js:キャッシュをする
function getDataAsJson() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var cache = CacheService.getScriptCache();
  var cached = cache.get("json_cache");
  if (cached != null) {
    return cached;
  }

  var dataRange = sheet.getDataRange();
  var headers = dataRange.getValues()[0];
  var numRows = dataRange.getNumRows();
  var numCols = dataRange.getNumColumns();
  var dataArray = [];

  for (var i = 1; i < numRows; i++) {
    var row = {};
    for (var j = 0; j < numCols; j++) {
      var cellValue = dataRange.getCell(i + 1, j + 1).getValue();
      if (cellValue !== "") {
        row[headers[j]] = cellValue;
      }
    }
    dataArray.push(row);
  }
  
  var json = JSON.stringify(dataArray);
  cache.put("json_cache", json, 21600); // 6時間でキャッシュを破棄する
  return json;
}
```

これを参考に自分のGASプロジェクトに組み込んだところ
![Fp4U2D_aAAESAUr.jpeg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/46278/bccc9db2-c0d2-c4d1-1379-da7b03c48cec.jpeg)


20倍速くなりました！
キャッシュなので当然といえば当然ですが、GASはたまに書くことはあっても、ガッツリ調べて、みたいな経験はないので、そもそも機能として知らなかったですし、それを提示してくれたおかげなので、ChatGPT様様です。

# 締め
業務でも使えるかもですが、趣味で使うレベルではかなり工数削減できますし、ChatGPTとのうまいコミュニケーションスキルを身につけると良さそうと感じました。(結構普通にコードレビューしている感覚で作業が進んだので、それほど違和感なく進むと思いました。)

以下最終的な生成コードです。(自分では全く手を入れていない)
```js:最終成果物
function doGet(request) {
  var cache = CacheService.getScriptCache();
  var cacheKey = "cache-key";
  var cachedData = cache.get(cacheKey);
  if (cachedData) {
    return ContentService.createTextOutput(cachedData).setMimeType(ContentService.MimeType.JSON);
  }
  
  var spreadsheetId = "{スプレッドシートのID}";
  var sheetName = "Sheet1";
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var dataArray = formatData(spreadsheet, sheetName);
  
  var cacheExpirationInSeconds = 3600; // 1 hour
  var jsonData = JSON.stringify(dataArray);
  cache.put(cacheKey, jsonData, cacheExpirationInSeconds);
  return ContentService.createTextOutput(jsonData).setMimeType(ContentService.MimeType.JSON);
}

function formatData(spreadsheet, sheetName) {
  var dataRange = spreadsheet.getSheetByName(sheetName).getDataRange();
  var headers = dataRange.getValues()[0];
  var numRows = dataRange.getNumRows();
  var numCols = dataRange.getNumColumns();
  var dataArray = [];

  for (var i = 1; i < numRows; i++) {
    var row = {};
    for (var j = 0; j < numCols; j++) {
      var value = dataRange.getCell(i + 1, j + 1).getValue();
      if (value !== "") {
        row[headers[j]] = value;
      }
    }
    dataArray.push(row);
  }

  return dataArray;
}
```

# 補足
上の最終成果物を作成するのは結構苦労していて、言ってないことを勝手にやってくれたりして、わかりにくいコードを生成しちゃったりするので、こういったことはできるか？くらいまでにとどめて、自分で書き換えながら使うのがいいと思いました。
(この処理はメソッドに入れて、みたいなやり取りをすると、かなりの確率でうまくいきませんでした)