スプシのセルを1つずつ処理するとどれだけ遅い？GASで1万セルを読み書きして比較してみた

sequenceDiagram
    participant GAS as GAS
    participant Sheets as Google Sheets

    loop セルごとに繰り返す
        GAS->>Sheets: getValue()で1セルを読む
        Sheets-->>GAS: 1セルの値を返す
        GAS->>GAS: 値を加工する
        GAS->>Sheets: setValue()で1セルへ書く
        Sheets-->>GAS: 書き込み完了
    end

sequenceDiagram
    participant GAS as GAS
    participant Sheets as Google Sheets

    GAS->>Sheets: getValues()で範囲をまとめて読む
    Sheets-->>GAS: 二次元配列を返す
    GAS->>GAS: 配列をメモリ上で加工する
    GAS->>Sheets: setValues()でまとめて書く
    Sheets-->>GAS: 書き込み完了

const BENCHMARK_CONFIG = Object.freeze({
  rows: 2000,
  columns: 5,
  sourceSheetName: 'BENCH_SOURCE',
  targetSheetName: 'BENCH_TARGET',
  resultSheetName: 'BENCH_RESULTS',
});

/**
 * 検証用の3シートと、2,000行×5列のダミーデータを準備する。
 * 同名シートの内容は初期化されるため、検証専用ファイルで実行する。
 */
function setupBenchmark() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = getOrCreateSheet_(
    spreadsheet,
    BENCHMARK_CONFIG.sourceSheetName,
  );
  const targetSheet = getOrCreateSheet_(
    spreadsheet,
    BENCHMARK_CONFIG.targetSheetName,
  );
  const resultSheet = getOrCreateSheet_(
    spreadsheet,
    BENCHMARK_CONFIG.resultSheetName,
  );

  // 新規シートの初期行数が2,000未満でも検証できるように拡張する
  ensureSheetSize_(
    sourceSheet,
    BENCHMARK_CONFIG.rows,
    BENCHMARK_CONFIG.columns,
  );
  ensureSheetSize_(
    targetSheet,
    BENCHMARK_CONFIG.rows,
    BENCHMARK_CONFIG.columns,
  );

  sourceSheet.clear();
  targetSheet.clear();
  resultSheet.clear();

  const values = createBenchmarkValues_();

  // セットアップ時間は測定対象外。一括でデータを作成する。
  sourceSheet
    .getRange(1, 1, BENCHMARK_CONFIG.rows, BENCHMARK_CONFIG.columns)
    .setValues(values);

  resultSheet.getRange('A1:H1').setValues([[
    '測定日時',
    '種別',
    'メソッド',
    '行数',
    '列数',
    'セル数',
    '実行時間(ms)',
    'チェック値',
  ]]);

  SpreadsheetApp.flush();
}

function createBenchmarkValues_() {
  return Array.from({ length: BENCHMARK_CONFIG.rows }, (_, index) => {
    const rowNumber = index + 1;

    return [
      `P-${String(rowNumber).padStart(5, '0')}`,
      rowNumber,
      rowNumber % 2 === 0 ? '完了' : '未処理',
      rowNumber * 100,
      rowNumber % 3 === 0,
    ];
  });
}

function getOrCreateSheet_(spreadsheet, sheetName) {
  return spreadsheet.getSheetByName(sheetName)
    ?? spreadsheet.insertSheet(sheetName);
}

function ensureSheetSize_(sheet, requiredRows, requiredColumns) {
  const currentRows = sheet.getMaxRows();
  const currentColumns = sheet.getMaxColumns();

  if (currentRows < requiredRows) {
    sheet.insertRowsAfter(currentRows, requiredRows - currentRows);
  }

  if (currentColumns < requiredColumns) {
    sheet.insertColumnsAfter(
      currentColumns,
      requiredColumns - currentColumns,
    );
  }
}

function benchmarkGetValue() {
  const sheet = getBenchmarkSheet_(BENCHMARK_CONFIG.sourceSheetName);
  const range = sheet.getRange(
    1,
    1,
    BENCHMARK_CONFIG.rows,
    BENCHMARK_CONFIG.columns,
  );

  const startedAt = Date.now();
  let checksum = 0;

  for (let row = 1; row <= BENCHMARK_CONFIG.rows; row++) {
    for (let column = 1; column <= BENCHMARK_CONFIG.columns; column++) {
      const value = range.getCell(row, column).getValue();
      checksum += valueToNumber_(value);
    }
  }

  const elapsedMs = Date.now() - startedAt;
  appendBenchmarkResult_('読み取り', 'getValue', elapsedMs, checksum);
}

function benchmarkGetValues() {
  const sheet = getBenchmarkSheet_(BENCHMARK_CONFIG.sourceSheetName);
  const range = sheet.getRange(
    1,
    1,
    BENCHMARK_CONFIG.rows,
    BENCHMARK_CONFIG.columns,
  );

  const startedAt = Date.now();
  const values = range.getValues();
  let checksum = 0;

  for (const rowValues of values) {
    for (const value of rowValues) {
      checksum += valueToNumber_(value);
    }
  }

  const elapsedMs = Date.now() - startedAt;
  appendBenchmarkResult_('読み取り', 'getValues', elapsedMs, checksum);
}

function benchmarkSetValue() {
  const sourceSheet = getBenchmarkSheet_(BENCHMARK_CONFIG.sourceSheetName);
  const targetSheet = getBenchmarkSheet_(BENCHMARK_CONFIG.targetSheetName);
  const values = sourceSheet
    .getRange(1, 1, BENCHMARK_CONFIG.rows, BENCHMARK_CONFIG.columns)
    .getValues();
  const targetRange = targetSheet.getRange(
    1,
    1,
    BENCHMARK_CONFIG.rows,
    BENCHMARK_CONFIG.columns,
  );

  targetRange.clearContent();
  SpreadsheetApp.flush();

  const startedAt = Date.now();

  for (let row = 1; row <= BENCHMARK_CONFIG.rows; row++) {
    for (let column = 1; column <= BENCHMARK_CONFIG.columns; column++) {
      targetRange
        .getCell(row, column)
        .setValue(values[row - 1][column - 1]);
    }
  }

  // 保留中の書き込みを反映してから測定を終了する
  SpreadsheetApp.flush();

  const elapsedMs = Date.now() - startedAt;
  appendBenchmarkResult_('書き込み', 'setValue', elapsedMs, '');
}

function benchmarkSetValues() {
  const sourceSheet = getBenchmarkSheet_(BENCHMARK_CONFIG.sourceSheetName);
  const targetSheet = getBenchmarkSheet_(BENCHMARK_CONFIG.targetSheetName);
  const values = sourceSheet
    .getRange(1, 1, BENCHMARK_CONFIG.rows, BENCHMARK_CONFIG.columns)
    .getValues();
  const targetRange = targetSheet.getRange(
    1,
    1,
    BENCHMARK_CONFIG.rows,
    BENCHMARK_CONFIG.columns,
  );

  targetRange.clearContent();
  SpreadsheetApp.flush();

  const startedAt = Date.now();

  targetRange.setValues(values);

  // setValue版と同じ条件で、書き込み完了までを測る
  SpreadsheetApp.flush();

  const elapsedMs = Date.now() - startedAt;
  appendBenchmarkResult_('書き込み', 'setValues', elapsedMs, '');
}

function getBenchmarkSheet_(sheetName) {
  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName(sheetName);

  if (!sheet) {
    throw new Error(
      `「${sheetName}」シートがありません。setupBenchmark()を実行してください。`,
    );
  }

  return sheet;
}

function valueToNumber_(value) {
  if (typeof value === 'number') {
    return value;
  }

  if (typeof value === 'boolean') {
    return value ? 1 : 0;
  }

  if (value instanceof Date) {
    return value.getTime();
  }

  return String(value).length;
}

function appendBenchmarkResult_(category, method, elapsedMs, checksum) {
  const resultSheet = getBenchmarkSheet_(BENCHMARK_CONFIG.resultSheetName);

  resultSheet.appendRow([
    new Date(),
    category,
    method,
    BENCHMARK_CONFIG.rows,
    BENCHMARK_CONFIG.columns,
    BENCHMARK_CONFIG.rows * BENCHMARK_CONFIG.columns,
    elapsedMs,
    checksum,
  ]);

  console.log(`${category} ${method}: ${elapsedMs} ms`);
}

1巡目：getValue → getValues → setValue → setValues
2巡目：getValues → getValue → setValues → setValue
3巡目：setValues → setValue → getValues → getValue