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

概要

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

GoogleスプレッドシートのデータをJsonで取得できるAPIを作る

Googleスプレッドシートの1行目をキーとして、Json化してAPI化するサービスというのはいくつかあるようで
自分は最初SSSAPIというサービスを使ってみました。

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

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

ChatGPTに書かせる

ChatGPTの説明は省きますが、GASのコードを書かせることにしました。

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行目をキーにできていないので、以下みたいに言ってみると

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にやらせる

遅い。

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

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

4つほど提示してくれました。

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

キャッシュで行ってみるか、ということで、コードを生成させて

キャッシュをする

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プロジェクトに組み込んだところ

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

締め

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

以下最終的な生成コードです。(自分では全く手を入れていない)

最終成果物

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;
}

補足

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