GASで一つのシートに別複数シートの更新日を記入したい

Question

前提・ 実現したいこと

台帳管理で一つのファイルに複数シート存在していますが、頻繁には更新されていません。
そこで、GASを利用して、シートに更新があった場合に、更新管理シートに更新日が入力されて、一括管理できるようにしたいです。

ここに実現したいことを箇条書きで書いてください。

• 更新管理シートにAシート・Bシート・・・Zシート、それぞれの更新日を記入
• イメージは更新管理シートで以下のように一元管理できている状況になります。
• Aシート：2022-12-27
• Bシート：2022-12-01
• Zシート：2021-06-07

試したこと

同じシート内で更新があった際に、更新日が入力される方法は調べたら出てきたらのですが、上記については、見つからず質問になります。

Comments

[kuro788]

丸投げの質問ですみません！
下記のように、同じシート内であれば、更新日を確認できるのですが、
一つのシートで複数シートの更新日を確認するにはどうしたらいいかわからず困っています。

function insertLastUpdated() {
//アクティブなスプレッドシートの特定のシートを指定
var ss = SpreadsheetApp.getActive().getSheetByName();
//アクティブなセルの行数を取得
var currentRow = ss.getActiveCell().getRow();
//アクティブなセルの値を取得
var currentCell = ss.getActiveCell().getValue();

//更新日をいれる列をstringで指定、B+currentRowでB列のセルを指定できる
var updateRange = ss.getRange('B' + currentRow);
Logger.log(updateRange);

if(currentRow>1){
if(currentCell) {
updateRange.setValue(new Date());
}
}
}

Answer 1

下記のwriteEditInfo(e)関数を「編集時」のトリガーに設定してください。

トリガー設定

コード

function writeEditInfo(e) {
  const now = new Date(); // 編集日時
  const sheetName = e.source.getSheetName(); // 編集されたシート名を取得
  const cellName = e.range.getA1Notation(); // 編集されたセル名を取得
  const oldValue = e.oldValue; // 編集前の値を取得
  const newValue = e.value; // 編集後の値を取得

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('更新管理'); // 更新情報を記録するシートを取得

  sheet.appendRow([now, sheetName, cellName, oldValue, newValue]); // 最終行に行を追加し、情報を書き込む
}

解説

• トリガーで編集時（またはonEdit(e)）に設定すると、引数e（イベントオブジェクト）からさまざまな編集情報が取得できます。
  • 参考：【GAS】onEditの使い方、onedit(e) とは？【コピペで使えるサンプルコード】 | スプレッドシートでGASる
• 私はスプレッドシートの言語設定が英語のため日時が「12/28/2022」になってしまっていますが、日本語に設定されていましたらデフォルトで年月日の順番で表示されていると思います。
  • されていない場合や他の表示に変更したい場合は、Utilities.formatDate()メソッドを利用して、お好きに整形してください。
    • 参考：GASの日付を文字列フォーマットに変換する方法(formatDateメソッド) | AutoWorker〜Google Apps Script(GAS)とSikuliで始める業務改善入門
  • または、GASを使用せずに、Googleスプレッドシートの「カスタム日時」で表示形式を変更することも可能です。
    • 参考：スプレッドシートで数値の表示形式を設定する - パソコン - Google ドキュメント エディタ ヘルプ

---

最終更新日時のみ書き込むコード

Aシート、Bシート・・・Zシートの最終更新日を「更新管理」シートに記載することは可能でしょうか。
イメージとしては、新しいレコードが追加されのではなく、
Aシート：12/27
更新があったら、上記のレコードの更新日が上書きされて以下のようになる形です。
Aシート：12/28

STEP1

初回のみ、以下のwriteSheetNames()関数を実行すると、自動でシート名を羅列してくれます。

▼実行前

▼実行後

// 『最終更新日管理シート』に、自動でシート名（A〜Zシート）を出力する、初回のみ手動で実行させる関数
function writeSheetNames() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const lastDateSheet = ss.getSheetByName('最終更新日管理');

  // シート名を配列で取得（例：最初の2つのシートは無視して、3つ目＝配列で言うと2番目からが対象シート）
  const sheetNames = ss.getSheets().slice(2).map(sheet => sheet.getSheetName()); // ['Aシート', 'Bシート', 'Zシート']
  // 書き込む情報の2次元配列を生成
  const values = sheetNames.map(name => [name, '']);
  // [
  //   ['Aシート', ''],
  //   ['Bシート', ''],
  //   ['Zシート', '']
  // ]

  // 書き込む場所の範囲を取得
  const range = lastDateSheet.getRange(2, 1, sheetNames.length, 2);
  // シート名を書き込む
  range.setValues(values);
}

STEP2

以下のwriteLastEditDate(e)関数を「編集時」トリガーに設定してください。
最終更新日時のみ管理できるようになります。

// 『最終更新日管理シート』に、編集があったシートの最終更新日を、編集時に自動で出力する関数
function writeLastEditDate(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const lastDateSheet = ss.getSheetByName('最終更新日管理'); 
  const lastRow = lastDateSheet.getLastRow();

  // 編集されたシート名を取得
  const sheetName = e.source.getSheetName();
  // 編集日時を整形
  const now = Utilities.formatDate(new Date(), 'JST', "yyyy/MM/dd HH:mm:ss");

  // 編集されたシート名が、最終更新日管理シートの何行目にあるか、行番号を取得
  const rowNum = lastDateSheet.getRange(1, 1, lastRow, 1).getValues().flat().indexOf(sheetName) + 1;
  // 書き込むセルを取得
  const cell = lastDateSheet.getRange(rowNum, 2);
  // 更新日時を書き込む
  cell.setValue(now);
}

Comments

[kuro788]

回答ありがとうございます！
頂いた内容で設定することができました。

Aシート、Bシート・・・Zシートの最終更新日を「更新管理」シートに記載することは可能でしょうか。
イメージとしては、新しいレコードが追加されのではなく、
Aシート：12/27
更新があったら、上記のレコードの更新日が上書きされて以下のようになる形です。
Aシート：12/28

[Cocode]

回答を更新しました。

[kuro788]

ありがとうございます！！
実現したいことができました！