GASで数式だけを別シートへコピーする

翌月用の表を作るとき、計算式は残したい一方、前月の入力値は引き継ぎたくない場合があります。範囲を普通にコピーすると値や書式も対象になるため、コピーする種類を数式に限定します。

GASのcopyToでは、SpreadsheetApp.CopyPasteType.PASTE_FORMULAで数式だけを貼り付けられます。コピー先を空いている練習用シートに固定し、実行前の確認と書き込みを分けると、対象の範囲を間違えたまま進むことを避けられます。

数式と計算結果は別のもの

=B2*C2が1000を返すセルでは、数式は=B2*C2、計算結果は数値の1000です。PASTE_VALUESでコピーすると結果の値が入り、後からB2やC2を変えてもそのコピー先は再計算しません。

公式のCopyPasteTypeには、値、数式、書式などの貼り付け方が分かれています。contentsOnly:trueと書くだけでは、数式だけに限定する指定とは異なります。

練習では同じファイル内に「数式原本」と「数式コピー」を作ります。原本のD2:D3へ数式を入れ、コピー先のD2:D3は空欄にします。式を入れるB列・C列も練習用の架空の数値を用意し、元ファイルは残してください。

コピー先の空欄を確認してから実行する

以下は原本の2セルがどちらも数式であることを確認します。コピー先は値だけでなく数式も調べ、=""のように表示が空の式を見逃さないようにします。

function copyPracticeFormulas(book, apply) {
  const from = book.getSheetByName('数式原本');
  const to = book.getSheetByName('数式コピー');
  if (!from || !to || from.getSheetId() === to.getSheetId()) {
    throw new Error('原本とコピー先を確認してください');
  }
  const source = from.getRange('D2:D3');
  const target = to.getRange('D2:D3');
  const formulas = source.getFormulas();
  if (formulas.some(row => row[0] === '')) throw new Error('原本に数式がない行があります');
  const used = target.getValues().some(row => row[0] !== '') ||
    target.getFormulas().some(row => row[0] !== '');
  if (used) throw new Error('コピー先に値か数式があります');
  const plan = {from: from.getName(), to: to.getName(), range: 'D2:D3', formulas: formulas};
  if (apply === true) {
    source.copyTo(target, SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  }
  return plan;
}

function previewFormulaCopy() {
  const APPLY = false;
  const book = SpreadsheetApp.getActiveSpreadsheet();
  console.log(JSON.stringify(copyPracticeFormulas(book, APPLY)));
}

APPLYがfalseのままなら計画の表示までです。「数式原本」から「数式コピー」のD2:D3が対象になっているかを確認します。コピーを試す場合だけ、練習用ファイルでtrueに変えて実行してください。実行後にもう一度走らせると、既に数式があるため止まります。

相対参照の移動を確認する

copyToの貼り付け先は左上の位置が基準になります。式の相対参照は、コピー元とコピー先の位置に応じた確認が必要です。この例は同じD2:D3へ貼るので、練習用の各シートでB列・C列を参照する結果を確認します。

参照を固定したい場合は$B$2のような絶対参照との違いも確かめます。別シート名を含む式が、コピー先のデータへ自動で切り替わるとは限りません。数式の文字と計算結果の両方を見て、どのデータを計算しているかを確認してください。

原本に数式と固定値が混ざる範囲を、そのままこの関数へ渡す前提にはしていません。固定値を残すのか、空にするのかを決め、必要な範囲を分けます。貼り付け後に都合よく固定値だけ消す方法では、途中失敗時の状態を追いにくくなります。

コピーはバックアップの代わりにならない

数式だけのコピーには、入力値、共有設定、表示形式などは含まれません。ファイル全体を戻したい場合はスプレッドシートの定期バックアップと目的を分けます。

ローカルでは、代用範囲でコピー前の条件と、許可した場合にだけPASTE_FORMULAが渡ることを確認しました。Google上の数式の参照調整や計算結果は未確認です。セルと範囲の読み書きを基に、小さな練習表で結果を照合してから広げてください。