【GASで時短】全シートの数式を再セットする

入力されている数式を再度セットしなおすGoogle Apps Scriptです。数式に誤りがないのに何らかの原因で動かない・・・というときに。

こんな悩みを解決します

• なぜかわからないが、数式エラーが起こっている。ダブルクリックしてエンターするとエラーが消えるので数式自体には問題がない。

用意するもの

特にありません。今お使いのスプレッドシートに組み込めます。

このスクリプトで出来ること

• スプレッドシートの全シートのすべての数式を、再度セットすることができます。

使い方

1.スクリプトの実行

上のメニューにある「スクリプト実行」から「全シートの数式を再セットする」を実行します。

2.結果の確認

完了すると「数式の再セットが完了しました！」と表示されます。
エラーを再現するのが面倒だったので、説明のスクリーンショットは少なめです。ごめんなさい。

「数式が正しいのにエラー」は、他シートへの参照を含むシートをcopyToした時に発生するようです。毎回起きるわけではないのですが・・・。
1セルずつダブルクリックするのが面倒なので作成しました。

スクリプト

/**
 * このスクリプトの説明、使い方はこちら。
 * https://myfunc.jp/items/00096/index.html
 */

function onOpen() {
  // スプレッドシートを開いたときに実行される関数

  // UIの取得
  const ui = SpreadsheetApp.getUi()

  // メニューの表示名
  const menu = ui.createMenu('スクリプト実行');

  // メニューに追加するボタン
  menu.addItem('全シートの数式を再セットする', 'reset_formulas');

  // メニューを画面に追加する
  menu.addToUi();
}


function reset_formulas() {

  // アラートを表示するためにUIを取得
  const ui = SpreadsheetApp.getUi();

  // 開いているスプレッドシートオブジェクトを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // すべてのシートオブジェクトを取得する
  const sheets = ss.getSheets();

  // 1シートずつループする
  for (let i = 0; i < sheets.length; i++) {

    // シートにデータがない場合はスキップする
    if(sheets[i].getLastRow() == 0){
      continue;
    }

    // シート内のデータが存在する範囲を取得
    let range = sheets[i].getRange(1, 1, sheets[i].getLastRow(), sheets[i].getLastColumn());

    // 範囲内の数式を取得
    let formulas = range.getFormulas();

    // 1セルずつ数式を再セットしていく
    // setFormulasでまとめてセットすると数式エラーが出た場合に該当セルがわからないので1セルずつセットする
    for (let j = 0; j < formulas.length; j++) { // 行のループ

      for (let k = 0; k < formulas[j].length; k++) { // 列のループ

        if (formulas[j][k] != '') { // 数式としてなにか文字列が入っていれば

          range = sheets[i].getRange(j + 1, k + 1); // 該当セルの範囲オブジェクトを取得
          range.setFormula(formulas[j][k]); // 数式をセットする

        }
      }
    }
  }

  // 完了時にメッセージを表示する
  ui.alert('数式の再セットが完了しました！');

}

関連するアプリ