【Googleスプレッドシート】共有しているユーザーの最終確認日時を自動で入力したい

Question

前提

GoogleフォームとGoogleスプレッドシートを連携させて、フォームから送信された情報がスプレッドシートに自動入力されるようにしています。
複数のスタッフが閲覧できるようにスプレッドシートは複数のアカウントに共有しています。

実現したいこと

現在、スタッフA、スタッフB、スタッフC、スタッフDさんの4人にスプレッドシートを共有しており、各スタッフの最終閲覧日時を自動でセル内に記載することは可能でしょうか？（指定したセルに記載できると助かります。）
また、確認の基準は、シートを開いた時点で　確認済　と表示できると良いです。

「確認」という列を設けて、スタッフが手動で◯印を入力するなどが手軽だと思うのですが、記入忘れを防ぎたいので自動入力ができたらと考えています。

お手数をおかけいたしますがご教示いただけますと幸いです。
よろしくお願いいたします。

Answer 1

GsuiteやGoogleworkplaceなら、共有しているスプレッドシートの右上に折れ線グラフみたいなボタンがあるので、そちらを開くことで日々のユニーク閲覧者数や誰が最後にいつ開いたかが取得できています。
一番簡単なのは、その機能を使って確認する方法です。

GASでシートに書き出す場合は、onOpen のシンプルトリガーではなくて、手動でスプレッドシート起動時にトリガーをセットしてあげれば、e.user.getEmail() で 開いたユーザーが取得できます。

https://developers.google.com/apps-script/guides/triggers/events

Comments

[papinianus]

質問者じゃなくてすみませんが伺いたいです。
トリガーってオーナーじゃなくても発動するのでしょうか？
（今回四人なので、セットアップすればいいだけ、のようにも思いますが、ご存知であれば）

[sawa]

papinianus様
私も昔は使う人にトリガーをセットしてもらってた気がするんですが、現状ではインストーラブルトリガーでもスクリプトのオーナーを実行者として、他の人が起動したり、編集したりでも発動するようです。

https://developers.google.com/apps-script/guides/triggers/installable

今回のパターンだと、スプレッドシート起動時に発動するトリガーをセットしていれば、Gsuiteユーザーじゃない場合でも開いた日時をシートに書き出すことは可能です。user部分に関してはフリーのGmailアカウントだと勝手に取得は無理ですが、Gsuiteの同じ組織内であれば取得可能であることを確認しております。（Gsuiteの権限ロールは関係なく、作成者が管理者でなくてもいけます）

Answer 2

const q309630_onOpen = (e) => {
    const formSheetName = "シート1";
    const targetColumnName = "対応状況";
    const formSheet = SpreadsheetApp.getActive().getSheetByName(formSheetName);
    const dat = formSheet.getDataRange().getValues();
    const targetColumn = dat[0].reduce((a,c,i) => c === targetColumnName ? i : a,-1);
    if(targetColumn === -1) return;
    const userEmail = e.user.getEmail();
    formSheet.getRange(dat.length, targetColumn, 1, 1).setValue(userEmail);
}

• onOpen をトリガー設定すると動作するっぽいので、サンプルを書きました。
  が、最終行の「対応状況」が空かを判定してない(というか仕様が分からないので判定すべきかも分からない)ですし、そもそも最終行に入れるべきかも分からない(例えばチェックするまでに 2 つ以上の回答が来た状況でどうするのか)ですし、開いたときに"フォームの回答"シートが開かれているかも確認してないですし(確認して弾くことはできますが、一旦弾いてしまうともう二度と onOpen は発火しないので)、はっきり言って使いものにならないでしょうね。
  仕様がはっきりしたら別質問立ててください。

Comments

[mst0]

>質問の感じからすると、共有相手は編集しなくてよいのかなと考えました。これが正しいとしたら私だった>らwebアプリケーションとして公開、にして、リンクが開かれたときに確認済を書き込みますね。
>誰が開いたかを特定する必要があるのでしょうか。
>また、どのようなデータのどこに確認済と入れるか、も不明です。

今回、お問い合わせ管理票としてスプレッドシートを活用したいと考えているため、
共有しているユーザーは、対応状況を追記や編集する必要があります。

「誰が開いたかを特定する」目的は、お問い合わせ対応にヌケ・モレがないかを確認するためです。

「どのようなデータのどこに確認済と入れるか」については、Googleフォームとスプレッドシートを連携した際にフォーム側で設定した問い合わせ項目が横一列に表記されますが、その一番右側の列に「対応状況」という項目を手動で追加して各担当が入力できるようになればと考えています。