2022/05/06に更新

【GASで便利に】シート内のすべての数式を表示する

スプレッドシート小技

シートに入力されている全ての数式を、別シートに出力するGoogle Apps Scriptです。数式のセル参照が連鎖していて、確認が大変だったので作ってみました。

こんな悩みを解決します


  • 数式を追うの疲れた・・・1箇所ずつセルにカーソル合わせるの大変!
  • 資料のチェックを頼まれたけど数式見るのが大変・・まとめて出力したい。

用意するもの


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

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


  • アクティブなシート内に入力されている数式を、別シートにすべて書き出すことができます。

使い方


1.スクリプトの実行

サンプルとして適当な成績表を用意しました。
合計得点と平均得点が計算されている、よく見かけるやつですね。

上のメニューにある「スクリプト実行」から「すべての数式を別シートに出力する」を実行します。

2.結果の確認

あたらしいシートが作成され、入力されていた数式が見えるようになりました。
Aさんの点数だけ手入力で、他の人の点数はrand()とfloor()で作られていたようです。
これは極端な例ですが、数式を可視化することで同僚が仕掛けたワナに気づけるかもしれませんね!

スクリプト


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

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

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

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

  // メニューに追加するボタン
  menu.addItem('すべての数式を別シートに出力する', 'export_formulas_to_new_sheet');

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

function export_formulas_to_new_sheet() {

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

  // 開いているアクティブシートのオブジェクトを取得
  const sheet = ss.getActiveSheet();

  // アクティブシートのデータが存在する範囲を取得
  let range = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());

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

  // 取り出した数式を格納する配列
  const formulas_text = [];

  // 数式を取り出していく
  for (let i = 0; i < formulas.length; i++) { // 行のループ

    // 各行も配列にする
    formulas_text[i] = [];

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

      if (formulas[i][j] != '') {

        // 数式がカラでなければ、「=」(イコール)を除去して配列に格納する
        formulas_text[i][j] = formulas[i][j].match(/^=(.*)/)[1];

      } else {
        // カラの場合は何も格納しない
        formulas_text[i][j] = '';
      }
    }
  }

  // 数式を書き込むシートを作成する
  const new_sheet = ss.insertSheet();

  // 新しく作ったシートの名前を変更する
  new_sheet.setName(sheet.getName() + '_数式');

  // 数式の書き込み範囲を指定する
  range = new_sheet.getRange(1, 1, formulas_text.length, formulas_text[0].length);

  // 数式の書き込みを実行
  range.setValues(formulas_text);

}

関連するアプリ


【GASで時短】数式エラーが出ているセルを通知する

数式でエラーが出ているセルを探してくれるGoogle Apps Scriptです。巨大なシートだとエラーに気づかないこともある・・・?ということで作ってみました。

【GASで便利に】数式が入っているセルに色を付ける

シート内の数式が入っているセルを探して、色を付けてくれるGoogle Apps Scriptです。他の人から引き継いだシートだと、数式がどこにあるのかわかりにくいので作ってみました。

【GASで時短】シート内の数式だけを削除する

セルに入力されている数式をすべて消し去るGoogle Apps Scriptです。数式を多用してデータを作ったが、再計算することはないので数値が直接入力されている状態にしたい時・・・などに。

【GASで時短】2つのシートの差分をチェックする

2つのシートを比較して、値や数式に差分があるかどうか教えてくれるGoogle Apps Scriptです。スプレッドシートでもdiffがしたい!という時にどうぞ。

【GASで時短】同じ背景色のセルを選択状態にする

選択したセルと同じ背景色のセルを選択状態にするGoogle Apps Scriptです。細かく色分けされたセルの色を変えたくなったときにどうぞ。

【GASで時短】同じ文字色のセルを選択状態にする

文字色が同じセルを選択状態にしてくれるGoogle Apps Scriptです。特定の文字色をまとめて変えたくなったときにどうぞ。

【GASで時短】同じフォントサイズのセルを選択状態にする

同じフォントサイズのセルを選択状態にしてくれるGoogle Apps Scriptです。一部のフォントサイズをまとめて変えたくなったときなどにどうぞ。

タグ一覧

最新のアプリ

人気のアプリ

myfunc.jp