【検証】GASでHtmlServiceダイアログの進捗表示を実装する｜6分の実行時間上限も回避できた

はじめに

前回、VBAのMsgBoxに近い完了通知がSpreadsheetApp.getUi().alert()で再現できることを確認しました。あわせて、HtmlServiceを使えばモーダルダイアログを作り込めること、ただし制約があることにも触れていました。今回はこのHtmlServiceダイアログを実際に使って、VBAのUserForm・プログレスバーに近い進捗表示を実装し、検証します。

前回のもう一つの発見が、GASには実行時間の上限（通常6分）があり、1000件のデータ処理でこれに引っかかってタイムアウトしたことでした。今回検証する「ダイアログ＋チャンク分割」方式は、進捗表示を実装するついでに、この6分の壁への対処法としても機能することが実機検証で分かりました。本記事はこの2つのテーマを軸に進めます。

注意： 本記事のコードは、実際にGoogleスプレッドシート上で動作検証を行っています。所要時間の実測値やタイムアウトの有無など、検証で判明した内容をそのまま反映しています。

前回までのおさらい

今回は、この「6分でタイムアウトした」問題を別の切り口から掘り下げます。

実現方針の全体像

GASでの進捗表示には、大きく分けて2つのアプローチが考えられます。

【ダイアログ方式（今回検証）】
ボタン押下 → HtmlServiceのモーダルダイアログを表示
└─ ダイアログ内のJavaScriptが google.script.run で少量ずつ（チャンク単位）処理を呼び出す
1チャンク完了 → 進捗バーを更新 → 次のチャンクを呼ぶ、を繰り返す
★ チャンクごとに新しいスクリプト実行になるため、6分の上限がリセットされる

【セルベース方式（次回予告）】
Office Scripts第6回と同じく、制御シートのセルに進捗を書き込む方式
1回のループが最後まで回り続けるため、単体では6分の壁を回避できない
→ 次回は時間主導型トリガーを連鎖させることでこの壁を越えられるか検証する

今回は前者の「ダイアログ方式」を実装・検証します。セルベース方式は次回、トリガー連鎖という別のアプローチとあわせて扱います。

準備：制御シートにC1セルを追加する

gas-01のサンプルスプレッドシートには、すでに図形ボタン設置用の「制御」シートがあります。ここに1セル追加します。

この「C1で手動指定、未入力なら自動計算」という設計は、Office Scripts第6回のチェック間隔指定と同じ発想です。

準備：Apps ScriptエディタにHTMLファイルを追加する

GASのプロジェクトは、スクリプトファイル（.gs）とは別に「HTMLファイル」を持てる構成になっています。VBAのUserForm作成に相当する操作ですが、手順がまったく異なるため、具体的に解説します。

1. スプレッドシートのメニューから「拡張機能」→「Apps Script」を選択してスクリプトエディタを開く
2. 左側のファイル一覧にある「＋」アイコンをクリックする
3. 表示されるメニューから「HTML」を選択する（「スクリプト」ではなくHTMLを選ぶのがポイント）
4. ファイル名を聞かれるので、拡張子なしでProgressDialogと入力してEnter（保存すると自動的にProgressDialog.htmlとして作成される）
5. 作成された空のHTMLファイルの中身を、後述のコードに置き換える
6. 既存のスクリプトファイルにも、後述の3関数（showProgressDialog・initTransfer・processChunk）を追加する
7. 保存アイコンでプロジェクト全体を保存する

補足： HtmlService.createHtmlOutputFromFile('ProgressDialog')という呼び出しは、拡張子なしのファイル名を指定する仕様です。ファイル名を変える場合は、コード側の引数も同じ名前に合わせてください。

保存後は、図形ボタンへの関数割り当てを、これまでの実行関数（arrayMatch）から、ダイアログを開く関数（showProgressDialog）に変更する必要があります。

Step 1：ダイアログの基本実装

チャンク分割方式は、以下の3つの関数で構成します。

行・列マッピングの検出処理（findValueInRow・getMaxInRow・getLastRow）はgas-01のヘルパー関数をそのまま流用します。

// ボタン・メニューからはこちらを呼ぶ（arrayMatchの直接実行ではなくダイアログを開く）
function showProgressDialog() {
  const html = HtmlService.createHtmlOutputFromFile('ProgressDialog')
    .setWidth(420)
    .setHeight(170);
  SpreadsheetApp.getUi().showModalDialog(html, 'データ転記');
}

// ダイアログ表示直後に呼ばれ、開始行・総件数・チャンクサイズを返す
function initTransfer() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const prefSh = ss.getSheetByName('貼付元');
  const sName  = prefSh.getRange('K1').getValue();
  const workSh = ss.getSheetByName(sName);
  const ctrlSh = ss.getSheetByName('制御');

  const wtCol = findValueInRow(workSh, 2, '▼');
  const wRow  = workSh.getRange('G1').getValue();
  const workShEndR = getLastRow(workSh, wtCol);
  const totalRows  = workShEndR - wRow + 1;

  // ★ 前回実行分のHIT件数が残らないよう、開始時に必ずリセットする
  CacheService.getUserCache().put('transfer_hcount', '0', 600);

  // チャンクサイズを自動計算（詳細はStep4）
  const manualSize = ctrlSh.getRange('C1').getValue();
  const chunkSize = (typeof manualSize === 'number' && manualSize > 0)
    ? Math.min(1000, Math.max(1, Math.round(manualSize)))
    : Math.min(1000, Math.max(20, Math.ceil(totalRows / 20)));

  return { startRow: wRow, totalRows: totalRows, chunkSize: chunkSize };
}

// 1チャンク分（chunkSize件）だけ処理して進捗を返す
function processChunk(startRow, chunkSize) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const prefSh = ss.getSheetByName('貼付元');
  const sName  = prefSh.getRange('K1').getValue();
  const workSh = ss.getSheetByName(sName);

  const ptCol = findValueInRow(prefSh, 2, '▼');
  const wtCol = findValueInRow(workSh, 2, '▼');
  const pMCol = getMaxInRow(prefSh, 2);
  const wMCol = getMaxInRow(workSh, 2);
  const pRow  = prefSh.getRange('G1').getValue();
  const workShEndR = getLastRow(workSh, wtCol);
  const prefShEndR = getLastRow(prefSh, ptCol);

  const pCol = [];
  for (let i = 1; i <= pMCol; i++) pCol.push(findValueInRow(prefSh, 2, i));
  const wCol = [];
  for (let i = 1; i <= wMCol; i++) wCol.push(findValueInRow(workSh, 2, i));

  const tgetRng = prefSh.getRange(pRow, ptCol, prefShEndR - pRow + 1, 1).getValues();

  const cache = CacheService.getUserCache();
  let lngHcount = Number(cache.get('transfer_hcount') || 0);

  const endRow = Math.min(startRow + chunkSize - 1, workShEndR);

  for (let tgetTmpR = startRow; tgetTmpR <= endRow; tgetTmpR++) {
    const tmpStr = workSh.getRange(tgetTmpR, wtCol).getValue();

    let matchRng = -1;
    for (let r = 0; r < tgetRng.length; r++) {
      if (tgetRng[r][0] === tmpStr) { matchRng = r + pRow; break; }
    }
    if (matchRng === -1) continue;

    const myArray = [];
    for (let i = 0; i < pMCol; i++) myArray.push(prefSh.getRange(matchRng, pCol[i]).getValue());
    for (let i = 0; i < wMCol; i++) workSh.getRange(tgetTmpR, wCol[i]).setValue(myArray[i]);

    lngHcount++;
  }

  cache.put('transfer_hcount', String(lngHcount), 600);

  return { nextRow: endRow + 1, done: endRow >= workShEndR, hCount: lngHcount };
}

ダイアログ側（クライアントJavaScript）は、initTransfer()で受け取った開始行からprocessChunk()を繰り返し呼び出し、1回終わるたびに進捗バーを更新して次のチャンクを呼びます。ProgressDialog.htmlの完全なコードは、後述の「サンプルファイルについて」からコピーできるスプレッドシートに含まれています。

Step 2：★6分の実行時間上限を回避できるか検証

gas-01では、1000件のデータをセル単位のループで処理したところ6分でタイムアウトしました。今回のチャンク分割方式で同じ1000件を処理したところ、結果は次のとおりです。

CHUNK_SIZE=200（5回に分けて処理）で1000件を実行し、6分30秒を超えてもタイムアウトエラーは発生せず、正常に完走した。

チャンクごとにprocessChunk()が独立したスクリプト実行として呼び出されるため、1回あたりの実行時間が短ければ、トータルの処理時間が6分を超えてもタイムアウトしないことが実機検証で確認できました。gas-01の「セル単位ループで6分に達するとタイムアウトする」という制約に対する、実務的な回避策になります。

Step 3：チャンクサイズと処理速度のトレードオフ

チャンクサイズ（CHUNK_SIZE）を変えて、1000件のデータで所要時間を比較しました。

更新回数を増やす（＝チャンクサイズを小さくする）ほど、所要時間が明確に伸びています。CHUNK_SIZE=10（1000件処理・100回呼び出し）とCHUNK_SIZE=1（100件処理・100回呼び出し）はどちらも呼び出し回数が同じ100回ですが、所要時間には倍近い差があります。これは、行の処理コストとは別にgoogle.script.runの呼び出し1回あたりに一定の固定オーバーヘッドがかかっていることを示しています。

Office Scripts第6回でもセルへの読み書き頻度と処理速度のトレードオフがありましたが、GASのダイアログ方式では通信そのもののオーバーヘッドがさらに大きく効いてくるという違いがあります。細かく更新すればするほど見た目はなめらかになりますが、やりすぎると処理全体が大きく遅くなる点に注意が必要です。

Step 4：チャンクサイズの自動調整

固定値をそのまま使うと、データが少ない場合は更新回数が足りず、多い場合は通信オーバーヘッドで極端に遅くなります。そこで、Office Scripts第6回のcheckInterval自動計算と同じ考え方で、総件数からチャンクサイズを自動計算しつつ、制御シートのC1セルで手動指定も可能にしました。

// チャンクサイズを自動計算（全体で約20回更新される間隔が目安）
// 「制御」シートのC1に数値が入っていればそれを優先（手動指定）
const manualSize = ctrlSh.getRange('C1').getValue();
const chunkSize = (typeof manualSize === 'number' && manualSize > 0)
  ? Math.min(1000, Math.max(1, Math.round(manualSize)))
  : Math.min(1000, Math.max(20, Math.ceil(totalRows / 20)));

Office Scripts第6回では「全体で約50回更新」を目安にしていましたが、GASのダイアログ方式は通信オーバーヘッドがより大きいため、目安の更新回数を約20回に抑えています。

実際にこの自動計算で検証した結果は以下のとおりです。

いずれも実用上問題のない速度・更新頻度でした。

Step 5：細かい作り込み（経過時間表示・キャンセル時の進捗保持）

ここまでで最低限の進捗表示・キャンセルは動きますが、実務で使うレベルに仕上げるため、以下の2点を追加で作り込みました。

経過時間の表示

ダイアログが開いた直後を起点にDate.now()で計測し、進捗更新のたびに「◯:◯◯経過」の形で末尾に表示します。クライアント側だけのローカル計算のため、追加の通信は発生せず負荷はほぼありません。

表記は当初「処理時間◯:◯◯」としていましたが、100%完了時にテキストが折り返される問題があったため、より短い「◯:◯◯経過」に変更しました。

キャンセル時に直前の進捗が消える問題の修正

キャンセルボタンを押した直後は、実行中の1チャンクをそのまま完走させます。素朴に実装すると、この完走を待たずに「キャンセルしました」とだけ表示され、それまでの処理件数・HIT件数が失われてしまいました。

対処として、直近の進捗（処理件数・HIT件数・パーセント）を変数に保持しておき、次のチャンクを呼ぼうとしたタイミングでcancelledフラグを確認し、保持しておいた最新の進捗込みでキャンセルメッセージを表示する方式に変更しました。

完全なコードは、後述の「サンプルファイルについて」からコピーできるスプレッドシートに含まれています。

できること・できないことの整理

よくある質問・エラー対処

Q. ダイアログは表示されるが、キャンセルボタンや進捗バーが表示されない

→ ProgressDialog.htmlの<script>部分だけを置き換えたことで、上部の<div>（バー・ボタンのHTML）が失われている可能性があります。付録のコードでファイル全体を置き換えてみてください。それでも直らない場合は、ダイアログ上で右クリック→「検証」（F12）でConsoleタブを開き、JavaScriptのエラーが出ていないか確認してください。

Q. HIT件数の表示が実行のたびにリセットされず、前回の値が残る

→ HIT件数を保持するCacheServiceのキャッシュを、実行開始時（initTransfer）にリセットする処理が抜けていたのが原因です。本記事のコードはinitTransfer冒頭でリセットするよう修正済みです。

Q. 「制御」シートが存在しないとエラーになる

→ initTransferが「制御」シートのC1セルを参照する仕様のため、シート名が一致していないとエラーになります。シート名を変更する場合はコード内の参照も見直してください。

まとめ

• GASのHtmlServiceを使えば、VBAのUserForm・プログレスバーに近い見た目の進捗ダイアログを作り込める
• クライアント側JavaScriptからgoogle.script.runで処理をチャンク分割して呼び出す方式にすると、チャンクごとに新しいスクリプト実行になるため、gas-01で判明した6分の実行時間上限を回避できる（1000件・6分30秒超で完走を確認）
• チャンクサイズを小さくするほど進捗表示はなめらかになるが、google.script.runの通信オーバーヘッドで処理全体は遅くなるトレードオフがある
• チャンクサイズは、Office Scripts第6回のcheckIntervalと同じ考え方で総件数から自動計算できる（GASでは通信オーバーヘッドが大きいため、目安の更新回数は少なめに設定するのがよい）
• キャンセル時に直前の進捗を失わないためには、完走中のチャンクの結果を待ってから中断メッセージを表示する工夫が必要

次回予告

今回はダイアログ方式で6分の実行時間上限を回避できましたが、Office Scripts第6回のようなセルに進捗を書き込むだけのシンプルな方式は、1回のループが回り続けるため単体では同じ壁を越えられません。次回は、時間主導型トリガーを連鎖させることで、セルベースの進捗表示でも6分の実行時間上限を回避できるかを検証します。

サンプルファイルについて

この記事で使用したテスト用スプレッドシートは、Googleドライブ上のファイルへの共有リンクからご利用いただけます。GASのスクリプトはスプレッドシートに紐づいた状態（コンテナバインド型）で保存されているため、ご自身のGoogleドライブにコピーを作成すれば、スクリプトごとそのまま複製されます。

サンプルスプレッドシートを開く（コピーして使用）

↑ リンク先ファイルのURL末尾を/copyにした「コピー用リンク」にしています。クリックするだけでご自分のGoogleドライブにスプレッドシートのコピーが複製されるようになっています。末尾/copyを外してアクセスした場合は、「閲覧のみ」となりますのでコピーを作成してご利用ください。

今回追加したProgressDialog.htmlについて補足します。 コンテナバインド型のスクリプトは、.gsファイルだけでなくHTMLファイルも含めてスプレッドシートに紐づいた状態で保存されます。そのため、上記のコピー用リンクからスプレッドシートを複製すれば、ProgressDialog.htmlも本文中の手順でゼロから作り直す必要はなく、そのままコピーされた状態で使えます。「準備：Apps ScriptエディタにHTMLファイルを追加する」の手順は、自分のスプレッドシートに一から組み込みたい場合の説明として読んでいただければ十分です。

関連記事

• 【検証】生成AIはVBAをGoogle Apps Scriptに変換できるか？Office Scriptsとの違いも解説
• 【Office Scripts】UserForm代替パターン：進捗表示はセルで、キャンセルは実行ボタンで実装する

当サイトの記事で使用したVBAなどのサンプルをDLできます

この記事のサンプルへのリンクはこちらです！

ダウンロードページへは下のカードをクリックすればジャンプできます。
よろしければご利用ください！

サンプルのダウンロードはこちらから

【Excel VBA】サンプルファイル_ダウンロードのページ ・最新の登録データが一番上になっています。・目次クリックでジャンプできますのでご利用ください。 ダウンロード一覧（1/7ページ目）新しい登録順です 記事ごとに使っ…