【GASで品質向上】長い数式が入っているセルにメモを入れてもらう

一定の文字数以上で構成された数式が入っているセルを探し、そのセルにメモが入っているかどうかをチェックするGoogle Apps Scriptです。長い数式はわかりにくいので説明を書いてもらうために作りました。

こんな悩みを解決します

• 他人が書いた長い数式って読むの大変・・・説明ぐらい残しといてくれよ・・・
• なんだこの数式・・・意味不明・・・ってこれ書いたの自分じゃん・・・なんで解説残してないんだよ・・・

用意するもの

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

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

• 設定した文字数以上の数式に対して、メモ（説明）を書くように促すことができます。

使い方

1.スクリプトの実行

適当なデータを用意しました。合計や平均等は数式で計算されています。

さっそくチェックしてみましょう。
上のメニューにある「スクリプト実行」から「長い数式にメモがあるかどうか調べる」を実行します。

2.結果の確認

「以下のセルに長い数式が入力されていますが、説明（メモ）がありません」と出てしまいました。
F3セルはaverage()ではなく、なぜか足し算と割り算で計算しているようですね。

3.メモを追加して再度実行

長い数式は、メモを追加すれば警告が出なくなります。
超適当なメモを記録して、スクリプトを再度実行してみます。

「長い数式は無いか、メモが記入されているので問題ありません」と表示されました。
メモの内容まではチェックできませんが、スクリプトにできるのはここまでです。

スクリプト

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

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

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

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

  // メニューに追加するボタン
  menu.addItem('長い数式にメモがあるかどうか調べる', 'check_memo_for_long_formula');

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

function check_memo_for_long_formula() {

  // 警告を出したい数式の文字数を設定
  const threshold = 20;

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

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

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

  // セルに入っている数式をすべて取得
  const formulas = range.getFormulas();

  // 閾値超えの数式を格納する配列を準備
  const result = [];

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

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

      // 数式が設定したしきい値より長い場合
      if (formulas[i][j].length > threshold) {

        let range = sheet.getRange(i + 1, j + 1); // 該当セルのrangeオブジェクトを取得
        let cell = range.getA1Notation(); // セルのアドレスをA1形式で取得
        let memo = range.getNote(); // セルのメモを取得

        // メモが1文字も入力されていない場合は警告を出す対象とする
        if (memo.length == 0) {

          // 結果格納用の配列に、セル番地と数式を入れる
          result.push(cell + ' : ' + formulas[i][j]);

        } else {

          // メモが1文字以上記載されていれば飛ばす
          continue;
        }
      }
    }
  }

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

  if (result.length > 0) {
    ui.alert('以下のセルに長い数式が入力されていますが、説明（メモ）がありません\n\n' + result.join('\n'));
  }else{
    ui.alert('長い数式は無いか、メモが記入されているので問題ありません')
  }
}

関連するアプリ