別シートを編集した日付を表示するGAS

スプレッドシートにて、シート２にCSVデータを貼り付け、シート1に必要なデータをXLOOKUPして管理するシートを運用したいのですが、シート1のA1セルにCSVデータを張り付けた日時(〇月〇日〇時〇分まで、【06/07 22:00】と表示したい)を表示させたいです。

自力で下記の作成はできましたが、これではシート1に何かしら入力しないと日付の変更がされませんでした

function myFunction() {
SpreadsheetApp.getActiveSheet()
.getRange('A1')
.setValue(
'更新: ' + Utilities.formatDate(new Date(), 'JST', 'MM/dd HH:mm')
)
}

可能な限り上記を再利用して表示可能にできませんでしょうか。

YAmaGNZ

2024/06/07 22:22

SpreadsheetApp.getActiveSheetではなくシート名を指定してシートを取得すればいいのではないですか？「GAS シート名　指定」とでも検索すればいろいろと情報が出てくるかと思います。

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

回答1件

ベストアンサー

引用テキストシート1に何かしら入力しないと日付の変更がされません

シート1に何かを入力するとA1セルの日付が変更されているのであれば、
関数にシートの「編集時」または「変更時」のトリガーを設定してあるのだと思います。

トリガーの種類は、
トリガー起動時に編集されたセル範囲を受け取れるように「編集時」のトリガーにしておいてください。

可能な限り上記を再利用して表示可能に

必要な修正は次のとおりです。

まずは、シート2が編集されたかどうかを確認するために
トリガーに設定した関数の引数（関数宣言時の括弧内の変数）を(e)として
編集イベントを変数に受け取るようにしておき、
e.rangeで編集されたセル範囲を取得し、
さらに続けて.getSheet()でシートを取得、
さらに続けて.getName()でシート名の文字列を取得して
それがシート2という文字列と一致するときに
次の処理に進むようにifの条件文を加えておきます。

シート2が編集されたことを確認できたら、
元のコード（更新日時の記入）を現在開いているシートに記入するのではなく
シート1を特定して記入するように修正します。

具体的には、

SpreadsheetApp.getActiveSheet()

と現在開いているシートを直接取得している部分を、

SpreadsheetApp.getActiveSpreadsheet() // まずはスプレッドシート全体を取得し
.getSheetByName('シート1') // さらに続けてシート1 という名前のシートを取得

として、スプレッドシート全体から特定のシートを取得するように変更します。

このようにすると画面上はシート2が開かれた（シート2がActiveSheetの）状態で、
CSVをコピペした（セル範囲が編集された）ときに
シート1のA1セルに日時が記入されるようになります。

js
1// この関数をシートの「編集時」のトリガーに設定していることが前提
2function myFunction(e) {
3  // シートにCSVを貼り付けるなどセルの編集があったか確認
4  if (e) { // トリガー起動のとき
5    if (e.range.getSheet().getName() === 'シート2') { // シート2が編集されたとき
6      // シート1のA1セルに更新時の日時を記入
7      SpreadsheetApp.getActiveSpreadsheet() // スプレッドシート全体を取得
8        .getSheetByName('シート1') // シート1という名前のシートを取得
9        .getRange('A1') // セル範囲を指定
10        .setValue( // 更新日時を記入
11          '更新: ' + Utilities.formatDate(new Date(), 'JST', 'MM/dd HH:mm')
12        )
13    }
14  }
15}

可能な限りの再利用ということでしたが、
実際には、トリガーを設定せずに（削除した上で）、
関数名を変更して
function myFunction(e) {
を
function onEdit(e) {
とすることでもセルの編集時に起動するようになります。
例えば次のように書くことができます。

js
1// この関数はトリガーを設定する必要がない（関数名が簡易なトリガー）
2function onEdit(e) {
3  // シートにCSVを貼り付けるなどセルの編集があったか確認
4  if (e) { // 簡易なトリガー起動のとき
5    if (e.range.getSheet().getName() === 'シート2') { // シート2が編集されたとき
6      // シート1のA1セルに更新時の日時を記入
7      e.source // 編集されたセルを含むスプレッドシート全体を受け取る
8        .getSheetByName('シート1') // シート1という名前のシートを取得
9        .getRange('A1')
10        .setValue(
11          '更新: ' + Utilities.formatDate(new Date(), 'JST', 'MM/dd HH:mm')
12        )
13    }
14  }
15}