メモで入力されている内容を取得する自作関数について値を更新させたい

概要

Googleスプレッドシートにて、入力されているメモの内容を取得する自作関数を作成したい。
getNotes()を使うとメモを取得することが出来るがメモ内容が更新されても自作関数の値が更新されない。

該当のソースコード

Google
1function getMemo(cellInf) {
2  // 現在アクティブなスプレッドシートを取得
3  var ss = SpreadsheetApp.getActiveSpreadsheet();
4  // 現在開いているシートを取得
5  var sheet = ss.getActiveSheet();
6  // そのシートにある対象のセル位置を取得
7  var range = sheet.getRange(cellInf);
8  
9  // そのセルに設定されているメモをセルごとに取得する
10  var results = range.getNotes();
11
12  //メモ内容を返却する
13  return results;
14}

試したこと

セル内に=getMemo(cellInf)とすることで入力内容の取得はできるが、メモ内容の更新をした後、セルの内容は更新されず、トリガーの設定、ファイルの開き直しをしても値の更新が起こらない。スクリプトエディタの上書き、更新で値の更新がされるが実運用には向かないため、値が更新されるための別の方法を模索中です。

よろしくお願いいたします。

行動規範の内容に同意します

回答2件

ベストアンサー

考えたこと

onChange トリガーで、全てのセル関数を見て関数を再設定することを考えました。
(この前提として、onChange で関数を参照して、あるセル(仮に B1)に =getMemo("A1") とかかれていたとしても、B1 に A1 のメモをそのまま setValue すると関数が消えるので次回以降、同様の監視ができなくなります)
まずは関数を全セットすればいいと思いました。
このとき、onChange はどのシートを編集したかすらわからないので、全てのシートを見ないといけないことに気付きました。
次に、同じ関数を上書き保存しても、再計算が起こらないことに気付きました。
これは、セルから関数を消してすぐに同じ関数を書いた(編集したが同じ値にした)場合も更新がかからないので、そういう動作なのだと思います。
さらに、一旦、空白値を書き込み、関数を再セットしても何も起こりませんでした。
flush をしないといけないのだなと思いました。
flush をすると無限にローディングが繰り返されるので、見辛いですし、累積の実行時間が長くなりすぎてアカウントを ban されそうなので中断しました。
どうなっても責任とれませんが、下記に共有します。(q362999 をトリガーに設定します)

javascript
1function q362999(e) {
2    if(e.changeType !== "OTHER") return;
3    SpreadsheetApp.getActive().getSheets().forEach(s=>{
4        const funcs = s.getDataRange().getFormulas();
5        funcs.forEach((r,i)=>{
6            r.forEach((c,j) => {
7                if(!c.startsWith('=getMemo')) return;
8                s.getRange(i+1,j+1).setValue(""); // これを flush することで、変更が連鎖するが、本当にこれでずっと動いて問題ないかは分からない
9                SpreadsheetApp.flush();
10                s.getRange(i+1,j+1).setFormula(c);
11            });
12        });
13    });
14}
15function getMemo(cellInf) {
16    // 現在アクティブなスプレッドシートを取得
17    var ss = SpreadsheetApp.getActiveSpreadsheet();
18    // 現在開いているシートを取得
19    var sheet = ss.getActiveSheet();
20    // そのシートにある対象のセル位置を取得
21    var range = sheet.getRange(cellInf);
22
23    // そのセルに設定されているメモをセルごとに取得する
24    var results = range.getNotes();
25
26    //メモ内容を返却する
27    return results;
28}

ユーザがGoogleAppsに慣れていない

Gooogle Apps に慣れる必要はないと思います。よくあるパターンはエクセルになれているからスプレッドシートを採用するのではないかと思います(エクセルになれているわけではないなら、用途に応じて他のツールを探すのも一案だと思います。kintone とか notion とか)。

一方で、エクセルと違うメリット(同時に編集して互いに編集状況をどこからでも参照できる)もデメリット(関数の動作が違う)もあるので、運用の変更はあってもいいんじゃないかと。

運用でやりたいことが見えないので具体的には難しいですが、ノートを使わない(一定の書式の枠外のセルに記入する)、ノートをつけるような使いかたをする人向けには閲覧用のシートや画面を提供する(importrangeを使うなど)といった運用が考えられます。

投稿2021/10/16 18:59

papinianus

総合スコア12705

-.-as

2021/10/16 23:04

詳細に手順まで記していただきありがとうございます。試してみたいと思います。 Google共有ドライブ上での多数での閲覧・編集でノート（←はじめはコメントでの運用でしたがAPIが見つけられずメモに変更依頼）を使うことも決定済み事項のため、こちらサイドでは大掛かりな運用変更を促すことは難しいです。また、月次シートに人名/row、日/colがセットされている状態なので、枠外セルも厳しいです。やりたいことは、月次シート（組織別）×20からTodayのcolを拾って、セルに入力値がある場合にはコメントを拾って、統計シートに一覧にして表示するといった内容です。コメント以外はQuery関数やHlookup関数で拾えるんですが、、、レスポンスの問題もあって、統計シートでまとめて処理するよりは、各シートの非表示領域（列）に自作関数で当日分のノート出してQuery関数で処理してしまおうとしたのですがうまくいかず。長期かけて改善案or別案模索していこうと思います。ありがとうございました。

行動規範の内容に同意します