【VBA】GASへの書き込み方の違い｜全消しと追記のみを実測

アルバイト情報を整形してスプレッドシートで共有するため、ExcelのVBAからGASへデータを送っていました。受け側のシートは、毎回すべて消して書き直す運用でした。

その後、送信を店舗ごとに1回ずつへ分け、最初に「消す」リクエストを1回だけ送り、そのあとで店舗ごとに追記する形にしました。受け側の書き込み方で結果がどう変わるのかを、書き込み方だけを変えて比べました。

通信まわりは、【VBA】GASへのPOSTが405になる原因と通信オブジェクト比較と【VBA】GAS宛POSTのStatus 0をサイズ別に実測で確かめました。この記事では、通信は WinHttp.WinHttpRequest.5.1 に固定します。

受け側の書き込み方を3つ比べる
再実行したとき・途中で失敗したとき
消すリクエストが飛んだとき
ヘッダーを毎回1行目に書く追記
所要時間
2本を同時に流したとき
まとめ

受け側の書き込み方を3つ比べる

どの方式も、5店舗×各10行（3列）を、店舗ごとに1回ずつPOSTします。行数はヘッダーの1行を含めて数えます。

方式	受け側の動き	VBAからの送り方
A 毎回消す	リクエストのたびにシートを全部消して、書き直す	店舗ごとにPOST
B 最初に1回消す	消すリクエストを先に1回だけ送り、あとは追記する	最初に `mode=clear`、そのあと店舗ごとに `mode=append`
C 追記のみ	消さずに、最終行の次へ追記する	店舗ごとにPOST（消すリクエストは送らない）

GAS側の要点は、次のコードです（キーは各自で設定してください）。

const KEY = 'ここにキーを入れる';

function out_(o) {
  return ContentService.createTextOutput(JSON.stringify(o))
    .setMimeType(ContentService.MimeType.JSON);
}

function doPost(e) {
  const p = e.parameter;
  if (p.key !== KEY) return out_({ error: 'Unauthorized' });
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(p.sheet);
  if (!sh) return out_({ error: 'Sheet not found' });
  const mode = p.mode || 'append';

  if (mode === 'clear') {
    sh.clearContents();
    return out_({ result: 'cleared' });
  }

  const body = JSON.parse(e.postData.contents);
  const headers = Object.keys(body[0]);
  const values = body.map(r => headers.map(h => r[h] ?? ''));

  if (mode === 'replace') {            // A：毎回消す
    sh.clearContents();
    sh.getRange(1, 1, 1, headers.length).setValues([headers]);
    sh.getRange(2, 1, values.length, headers.length).setValues(values);
  } else {                             // B・C：最終行の次へ追記
    sh.getRange(1, 1, 1, headers.length).setValues([headers]);
    sh.getRange(sh.getLastRow() + 1, 1, values.length, headers.length).setValues(values);
  }
  return out_({ result: 'ok', rows: values.length });
}

追記する位置は、getLastRow() が返す「内容のある最後の行」の次の行です（Class Sheet | Apps Script）。

検証環境は、Excel 16.0（64bit）、Windows 64bitです。受け側は、検証用のスプレッドシートに紐づけたGAS Webアプリです。別のバージョンやMac版では確かめていません。

再実行したとき・途中で失敗したとき

状況	A 毎回消す	B 最初に1回消す	C 追記のみ
5店舗を1回流す	11行（最後の店舗だけ）	51行（各店舗10行）	51行（各店舗10行）
同じデータをもう1回流す	11行	51行	101行（50行が重複）
前回分がある状態で、2店舗目まで送って止める	11行（前回分は消え、2店舗目の10行だけ）	21行（前回分は消え、1・2店舗目）	71行（前回50行＋今回20行）

VBAのイミディエイトウィンドウに、3つの書き込み方（毎回消す・最初に1回消す・追記のみ）の行数が3行で並んでいる

上の画像は、3つの方式を実際に流した結果です。数字は表と同じです。

店舗ごとに送る形でAを使うと、最後の店舗だけが残りました。1店舗ずつ送る形では、Aは使えません。

BとCは、再実行したときと途中で止まったときの結果が逆になります。Bは再実行しても行数が増えませんが、途中で止まると前回分が消えたままになります。Cは前回分が残りますが、再実行すると同じ行が重なります。

消すリクエストが飛んだとき

Bで最初の消すリクエストを送らずに2回目を流すと、前回分の下に積まれて101行（50行＋50行）になりました。

消すリクエストを間違ったキーで送ると、応答は Unauthorized を含むJSONで、HTTPステータスは200でした。シートは101行のままです。ステータスが200かどうかだけで成功と判断すると、消えていないことに気づけません。応答の本文も確認する必要があります。

ヘッダーを毎回1行目に書く追記

Bと同じ書き方（ヘッダーを毎回1行目に書いてから追記）には、既存のデータの持ち方によって結果が変わる場面がありました。

空のシートへ、ヘッダー付きで3行追記：4行（ヘッダー1行＋3行）
空のシートへ、ヘッダーなしで3行追記してから、別の店舗の3行を追記：6行
ヘッダーなしで3行入れたあとに、ヘッダー付きで別の店舗の3行を追記：6行。ただし、先の店舗の1行目がヘッダーに上書きされ、先の店舗は2行になった

ヘッダーのない状態でデータが入っているシートへ、この書き方で追記すると、1行目のデータが消えます。

所要時間

書き込み1回あたりの、GAS側の処理時間（doPost の中の時間）の中央値です。同じ条件で10回ずつ、方式の順番を毎回ずらして測りました。かっこ内は最小〜最大です（単位はミリ秒）。

既存の行数	追記（B・Cの1店舗分）	消す（Bの最初の1回）	消して書き直す（A）
0行	776（507〜1,269）	264（192〜472）	549（390〜806）
1,000行	805（623〜1,652）	261（189〜686）	456（364〜594）
10,000行	734（562〜1,281）	217（189〜581）	515（380〜706）

中央値は、どの行数でも「消す」「消して書き直す」「追記」の順に長くなりました。ただし、最小〜最大が重なる組み合わせもあります。同じ方式で既存の行数を変えても、中央値はほぼ変わらず、範囲も重なっていました。この範囲では、行数による差は読み取れませんでした。

追記が消して書き直すより長かった点は、doPost の中を区間に分けて測りました（既存の行数は0行と1,000行、各10回、中央値）。書き込みと消す処理は、どれも2〜6ミリ秒で終わりました。時間がかかっていたのは getLastRow を呼ぶ区間で、1回あたり225〜264ミリ秒です。追記は getLastRow を2回、消して書き直すは1回呼びます。doPost 全体の中央値は、追記が827・863ミリ秒、消して書き直すが518・519ミリ秒で、差は約310〜345ミリ秒でした。getLastRow 1回の時間と、同じくらいの大きさです。

各区間のあとに SpreadsheetApp.flush() を呼ぶと、書き込みの区間も210〜474ミリ秒になりました。なお、この区間の計測では、有効な結果が得られなかった呼び出しはやり直しています。

VBAから見た往復時間（通信込み）は、1.4〜2.1秒でした。5店舗を店舗ごとに送ると14.3秒、同じ50行を1行ずつ送ると129.9秒で、9.1倍です（どちらも1回ずつの計測）。

2本を同時に流したとき

排他制御を入れない追記では、2本を同時に送ると（100行ずつ）、片方の書き込みが消えることがありました。10回の実行を2回行い、どちらも5回で片方の100行が残りませんでした。2本の応答はどちらも成功で、開始行はどちらも2として返っていました。

スクリプトロック（LockService.getScriptLock()）で書き込みをはさみ、反映してから解放すると、10回すべてで200行が残りました。開始行は2と102に分かれ、ロックの待ち時間は70〜1,549ミリ秒でした。2本が完了するまでの時間は、中央値で排他なしが約3.8秒、排他ありが約4.7秒でした。排他あり版は、書き込みを反映してから解放するために SpreadsheetApp.flush() を呼んでいます。

VBAのイミディエイトウィンドウに、2本を同時に送ったときに200行がそろった回数が、排他なしと排他ありで2行ずつ並んでいる

上の画像は、別の実行の結果です。排他なしで200行そろった回数は4/10で、実行のたびに変わります。排他ありは10/10でした。

  if (mode === 'append_lock') {
    const lock = LockService.getScriptLock();
    lock.waitLock(30000);
    try {
      sh.getRange(1, 1, 1, headers.length).setValues([headers]);
      const start = sh.getLastRow() + 1;
      sh.getRange(start, 1, values.length, headers.length).setValues(values);
      SpreadsheetApp.flush();
    } finally {
      lock.releaseLock();
    }
    return out_({ result: 'ok', rows: values.length });
  }