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

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

こんな悩みを解決します

• スプレッドシートを引き継いだけど数式がどこに入ってるのかわかりにくい・・・

用意するもの

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

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

• セルに数式が入力されている場合、そのセルに色を付けてわかりやすくしてくれます。

使い方

1.スクリプトの実行

サンプルとして適当な成績表を用意しました。
合計得点と平均得点は数式によって計算されています。

上のメニューにある「スクリプト実行」から「数式が入力されているセルを色付けする」を実行します。

2.結果の確認

合計と平均の部分に色が付きました。
今回の例ではスクリプトを使うまでもないですが、「オリジナルデータかと思いきやimportrange()されていた・・」ということも稀によくあるので、念の為にチェックしてもよいかもしれませんね。

シート内に数式が存在しない場合は「このシート内には数式が存在しないようです」と教えてくれます。

スクリプト

/**
 * このスクリプトの説明、使い方はこちら。
 * https://myfunc.jp/items/00082/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() {

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

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

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

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

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

  // 数式の有無を判断するためのカウンタ
  let counter = 0;

  // 1セルずつ数式の有無をチェックしていく
  for (let i = 0; i < formulas.length; i++) { // 行のループ

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

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

        range = sheet.getRange(i + 1, j + 1); // 該当セルの範囲オブジェクトを取得
        range.setBackground('darkviolet'); // セルの背景色をセット
        range.setFontColor('white'); // セルの文字色をセット
        counter++; // カウンタを増やす

      }
    }
  }

  if (counter == 0) {
    ui.alert('このシート内には数式が存在しないようです');
  }
}

関連するアプリ