はじめに

この記事は株式会社LabBase テックカレンダー Advent Calendar 2022の二日目です。我らがCTOのミズノさんの記事はこちら

こんにちは、株式会社LabBase（旧株式会社POL）でソフトウェアエンジニアをしている上久保です。昨年10月にLabBaseに入社して以降、主にフロントエンド開発をしていてReact/TypeScriptを書くことが多いですが、最近ではRustでバックエンド開発なんかもしたりしています。

今回はGASについて少し書いてみようと思います。最近業務でGoogle Drive上のスプレッドシートの内容をJSON形式に加工して出力するプログラムを利用していたのですが、この既存のプログラムは対象のスプレッドシートの数が多く処理にとても時間がかかってしまっていました。

またGASには利用プランに関わらず6分の実行時間制限があるため、その度にプログラムを手動で再実行して続きのスプレッドシートから変換処理を再開する手間もあり、改善に取り組むことにしました。

GASの実行時間制限の抜け道

いろいろ調べていくと、実は6分という実行時間制限はあくまで「１つのGASが最大6分しか実行出来ない」ということでした。そのため時間のかかる処理を行う場合は複数のGASに分割してそれぞれ外部から実行するようにすれば実行時間制限の問題はクリアできそうです。

幸いGASでは以下の画像のようにhtmlファイルを用意してWebアプリを公開することができるので、フォルダ内のスプシを全て取得し、その数だけWebアプリ側でfor文を回してGASを実行しJSON形式に変換しファイル出力する方針で開発してみることにしました。

実装内容

方針が定まったところで、実際のコードを一部抜粋して紹介します。

まずindex.htmlではWebアプリの画面とそこに仕込むJSを書いています。
html部分にはスプレッドシートのfileIdを受け取る要素、処理実行開始ボタン、結果表示というシンプルな要素しか存在せず、勿論CSSなども（面倒なので）ありません。

scriptタグの中には実行開始ボタンを押したときに呼ばれるexecute関数とその中でGASを呼び出すrun関数が書かれています。execute関数の中ではファイル数分だけfor文を回していて、この数だけGASが並列実行されます。

run関数の中では、google.script.run.convertFileToJSON(id)という部分でGASのconvertFileToJSON関数をidを引数に呼び出しています。この間に挟まっているwithSuccessHandlerとwithFailureHandlerでGAS呼び出し成功/失敗時に実行する処理を記述することができます。GASは同時に実行可能なスクリプト数の上限が決まっていて、それを超えると呼び出しに失敗するような仕様となっています。
そのためこのようにGAS呼び出し失敗時に再実行するようにプログラムを書けば、あたかも「マルチスレッドプログラミングでのポーリング処理」のようにスクリプトが実行可能になるまで待ち、空き次第実行するように動作させることができます。ただし適切なsleep時間を設定しないと不必要にGASを呼び出す処理が走り、プログラムが重くなるor途中で異常終了してしまうので注意が必要です。

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <form>
      <p>
        <div>処理対象のスプレッドシートのfileIdをファイルで指定してください</div>
        <input id="file" type="file" name="file"/>
      </p>
    </form>
    <button onClick="execute()">処理実行</button>
    <p>
      <div>処理結果</div>
      <div id="fileNum"></div>
      <div id="resultMsg"></div>
    </p>
    <script>
      const sleep = ms => new Promise(res => setTimeout(res, ms));
      let count = 0;
 
      async function execute() {
        // 研究室スプシのIDリストファイルの読み込み
        const input = document.getElementById('file');
        if (input.files.length == 0) {
          alert("読み込むファイルを設定してください！");
          return;
        }
        const file = input.files[0];
        const reader = new FileReader();
        reader.onload = () => {
          const idList = reader.result.split(',');
          const LIST_LENGTH = idList.length;
          const fileNum = document.getElementById('fileNum');
          fileNum.innerHTML =  `全${LIST_LENGTH}ファイル中`;
          for (let i=0;i<LIST_LENGTH;i++) {
            run(idList[i]);
          }
        };
        reader.readAsText(file);
      }

      async function run(id) {
        const RERUN_INTERVAL_MSEC = 5000;// スクリプト失敗時に再実行するまでの待機時間
        google.script.run.withSuccessHandler(function(e){
          // 処理が完了したらcountをインクリメント
          count += 1;
          const finishCount = document.getElementById('resultMsg');
          finishCount.innerHTML = `${count}ファイル変換完了`;
        })
        .withFailureHandler(async function(e){
          // 処理が失敗したら一定時間sleepして再実行
          await sleep(RERUN_INTERVAL_MSEC);
          run(id);// 再帰
        })
        .convertFileToJSON(id);
      }
    </script>
  </body>
</html>

次にGASの説明です。スプレッドシートの中身からJSONを生成するロジックについては主題ではないので省略します。ここで必ず必要なのはdoGet関数で、これを書くことでindex.htmlを生成してブラウザに表示します。htmlファイル側から呼び出されるconvertFileToJSONはスプレッドシートを読み込んでJSONを生成、結果をファイルに出力するだけのプログラムです。また、こちらも省略していますが、業務上JSON生成時にスプレッドシートの内容にバリデーションをかけており、その結果をログとして欲しいのでtry catch文で例外処理をしています。

main.js

// 便宜上main.jsにしていますがGASなので正しくはmain.gsになります。

function doGet() {
  // index.htmlを生成してブラウザに表示
  return HtmlService.createHtmlOutputFromFile('index');
}

function convertFileToJSON(fileId) {
  try{
    const file = SpreadsheetApp.openById(fileId);
    const sheet = file.getSheetByName("シート1");

    if (sheet == null) return `https://docs.google.com/spreadsheets/d/${fileId}\tシート1がありません`;
    
    // スプレッドシートの内容からJSONを生成
    const json = makeJsonFromSheet(sheet);
    const jsonLen = json.length;
    
    if (jsonLen == 0) return `https://docs.google.com/spreadsheets/d/${fileId}\t生成するJSONがありませんでした`;
    
    const saveFileName = `output_${fileId}_${file.getName()}.json`;
    const saveFile = DriveApp.createFile(saveFileName,json);
    // JSONファイルの保存先フォルダのIDを指定
    const folderId = "XXXXXXXXXX";
    DriveApp.getFolderById(folderId).addFile(saveFile);
    DriveApp.getRootFolder().removeFile(saveFile);
    return;
  }catch(e){
    return `https://docs.google.com/spreadsheets/d/${fileId}\t異常終了しました。原因：${e}`;
  }
}

function makeJsonFromSheet(sheet) {
  // ロジックを記述
  return jsonData;
}

結果

業務で使っているスプレッドシート約2000ファイルを20分弱で処理できるようになりました。元のプログラムでは実行時間ごとに再実行して合計20時間かかっていたので60倍高速になっています。やったね！

その他の工夫

上記の高速化には並列実行が一番効いていることは明らかですが、他にも改良した点があります。それはファイルI/O回数を極力減らすことです。
一般的に同じデータでもそれがメモリ上にあるかスプレッドシートなどの外部記憶領域上にあるかで読み込み/書き出し速度は数百倍違うと言われています。GASが遅いと感じたときにはまずファイルI/O周りから改善を検討するのが良いと思います。
スプレッドシート内の各行の値をループ処理で参照する場合でも、ループ外で最初に値をまとめて取得して配列の変数に格納してから適宜参照した方が良いでしょう。

まとめ

今回は並列処理によってGASの実行時間制限を気にせず大量の処理を高速に行う方法を解説しました。この記事を読んだみなさんにとって何かしらの参考になれば幸いです。

次回のアドベントカレンダーは@yiwiさんです！よろしくお願いします。

はじめに

GASの実行時間制限の抜け道

実装内容

結果

その他の工夫

まとめ

参考情報