2022/05/22に更新

【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('長い数式は無いか、メモが記入されているので問題ありません')
  }
}

関連するアプリ


【GASで便利に】スプレッドシートを開いたときにメッセージを表示する

スプレッドシートを開いたときにメッセージを表示するGoogle Apps Scriptです。更新履歴などのお知らせ、業務の引き継ぎ、注意事項などを表示するのに使えます。ユーザーによるスクリプトの動作承認は不要です。

【GASで便利に】スライドを開いたときにメッセージを表示する

スライドを開いたときにメッセージを表示するGoogle Apps Scriptです。更新履歴などのお知らせ、業務の引き継ぎ、注意事項などを表示するのに使えます。ユーザーによるスクリプトの動作承認は不要です。

【GASで便利に】ドキュメントを開いたときにメッセージを表示する

ドキュメントを開いたときにメッセージを表示するGoogle Apps Scriptです。更新履歴などのお知らせ、業務の引き継ぎ、注意事項などを表示するのに使えます。ユーザーによるスクリプトの動作承認は不要です。

【GASで品質向上】特定のセルが編集されたときにメッセージを表示する

特定のセルが編集されたときにメッセージを表示するGoogle Apps Scriptです。編集をさせたくないときは保護してしまえばよいのですが、編集後に「本当にその内容で大丈夫?」と振り返って欲しいときに使えるかもしれません。ユーザによるスクリプトの動作承認は不要です。

【GASで品質向上】特定のセルが編集されたときにメールで通知する

特定のセルが編集されたときにメールを送信するGoogle Apps Scriptです。大事な部分が更新された時にすぐに知りたいときに使えるかもしれません。

【GASで時短】スプレッドシートのコメントを全て削除する

スプレッドシートに書き込まれた全てのコメントを削除するGoogle Apps Scriptです。コメントの応酬を消し去りたいときにどうぞ。

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

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

タグ一覧

最新のアプリ

人気のアプリ

myfunc.jp