スプレッドシートに複数シートある場合で、更新時に自動で更新日付を入れるGAS

Question

前提

スプレッドシートで、G,H列のどちらかデータが更新されたら、I列に更新日が自動で入るというのをGASで行いたいと考えています。
（例）セルG11またはH11のデータが更新されたら、セルI11に更新日が自動で入る。
トリガーは設定済です。

発生している問題・エラーメッセージ

1シートだけであれば作成できたのですが、複数シートの場合は全てのシートに反映されてしまいます。
単一シートごとに反映できるようにしたいのですがやり方がわからず、質問させていただきました。ご教示頂ければ幸いです。
シートが10枚あり、シート2のセルG11を更新すると、セルI11に日付が入ります。しかし、シート1のセルI11にも日付が入ってしまう。

該当のソースコード

```/*指定列に日付を自動入力するスクリプト*/

function inputDate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //現在触っているファイルを取得
  var sheet = ss.getSheetByName('1'); //対象のシート名を選択( ()部分にシート名記載)
  var currentRow = sheet.getActiveCell().getRow(); //アクティブなセルの行番号を取得
  var currentCol = sheet.getActiveCell().getColumn(); //アクティブなセルの列番号を取得
  var currentCell = sheet.getActiveCell().getValue(); //アクティブなセルの入力値を取得
  var updateRange = sheet.getRange('I' + currentRow); //どの列に更新日時を挿入したいか。この場合はI列
  Logger.log(updateRange); //更新日の記入
  if(currentRow > 10 && currentCol == 7) { //11行目以降かつG列の変更を参照とする 
    if(currentCell) {
    updateRange.setValue(new Date());
    }
  }
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //現在触っているファイルを取得
  var sheet = ss.getSheetByName('1'); //対象のシート名を選択( ()部分にシート名記載)
  var currentRow = sheet.getActiveCell().getRow(); //アクティブなセルの行番号を取得
  var currentCol = sheet.getActiveCell().getColumn(); //アクティブなセルの列番号を取得
  var currentCell = sheet.getActiveCell().getValue(); //アクティブなセルの入力値を取得
  var updateRange = sheet.getRange('I' + currentRow); //どの列に更新日時を挿入したいか。この場合はI列
  Logger.log(updateRange); //更新日の記入
  if(currentRow > 10 && currentCol == 8) { //11行目以降かつH列の変更を参照とする 
    if(currentCell) {
    updateRange.setValue(new Date());
    }
  }
}
{
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //現在触っているファイルを取得
  var sheet = ss.getSheetByName('2'); //対象のシート名を選択( ()部分にシート名記載)
  var currentRow = sheet.getActiveCell().getRow(); //アクティブなセルの行番号を取得
  var currentCol = sheet.getActiveCell().getColumn(); //アクティブなセルの列番号を取得
  var currentCell = sheet.getActiveCell().getValue(); //アクティブなセルの入力値を取得
  var updateRange = sheet.getRange('I' + currentRow); //どの列に更新日時を挿入したいか。この場合はI列
  Logger.log(updateRange); //更新日の記入
  if(currentRow > 10 && currentCol == 7) { //11行目以降かつG列の変更を参照とする 
    if(currentCell) {
    updateRange.setValue(new Date());
    }
  }
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //現在触っているファイルを取得
  var sheet = ss.getSheetByName('2'); //対象のシート名を選択( ()部分にシート名記載)
  var currentRow = sheet.getActiveCell().getRow(); //アクティブなセルの行番号を取得
  var currentCol = sheet.getActiveCell().getColumn(); //アクティブなセルの列番号を取得
  var currentCell = sheet.getActiveCell().getValue(); //アクティブなセルの入力値を取得
  var updateRange = sheet.getRange('I' + currentRow); //どの列に更新日時を挿入したいか。この場合はI列
  Logger.log(updateRange); //更新日の記入
  if(currentRow > 10 && currentCol == 8) { //11行目以降かつH列の変更を参照とする 
    if(currentCell) {
    updateRange.setValue(new Date());
    }
  }
}

Answer 1

不具合の原因＆欠点

• 質問者様のコードは、どのシートでもいいので12行目以上のセルGかHが編集されたら、シート名「1」と「2」のシートに更新日時が書き込まれる処理を書かれています。
  • var sheet = ss.getSheetByName('2'); //対象のシート名を選択( ()部分にシート名記載)　←このようにシート名を指定してシートを取得しているからです。
  • シート名を指定してシートを取得するのではなく、「編集されたシート」を取得するようにしましょう。
• .getActiveCell()を使ってしまうと、スクリプトエディタ画面からinputDate()関数を実行した場合でも、対象のセルだったら更新日時が書き込まれてしまいます。スプレッドシートを編集して値を更新したわけではないのに…。
  • onEditトリガーを設定した関数の第一引数には、イベントオブジェクトが自動で渡されます。
  • そのイベントオブジェクトから編集情報を取得することができます。そちらを利用するといいでしょう。

コード例

対象のシート名に共通の文字列がある場合

「対象シート」という文字列を含む名前のシートだけが、更新日時書き込みの対象となるように処理しています。

function writeUpdateDate(e) {
  // 編集されたシートを取得
  const sheet = e.source.getActiveSheet();

  // 編集されたシート名が「対象シート」という文字列を含んでいるかどうか判定
  const isTargetSheet = sheet.getSheetName().includes('対象シート');

  // 含んでいなかったら（非対象のシートだったら）、ここで処理を終了。以降の処理は実行されない
  if (!isTargetSheet) return;

  // 編集されたセルの行番号を取得
  const activeRow = e.range.getRow();
  // 編集されたセルの列番号を取得
  const activeCol = e.range.getColumn();

  // 行が12行目以上、かつ、列が7番目または8番目の場合
  if (activeRow > 11 && (activeCol === 7 || activeCol === 8)) {
    // 更新日時を書き込みたいセルを取得
    const targetCell = sheet.getRange('I' + activeRow);
    // 日時を書き込む
    targetCell.setValue(new Date());
  }
}

対象のシート名を指定する場合

対象のシートの名前に関連性がない場合は、↑のコードは使いにくいと思いますので別の方法を以下にご提案します。
対象のシート名を配列に格納して、編集されたシート名が、その配列の中にあるかどうか判定する方法です。

function writeUpdateDate(e) {
  // 編集されたシートを取得
  const sheet = e.source.getActiveSheet();

  // 編集されたシートのシート名を取得
  const sheetName = sheet.getSheetName()
  // （更新日時書き込みの）対象のシート名
  const targetSheetNames = ['対象シート１', '対象シート２'];
  // 編集されたシートが、対象のシートかどうか判定
  const isTargetSheet = targetSheetNames.includes(sheetName);

  // 非対象のシートだったら、ここで処理を終了。以降の処理は実行されない
  if (!isTargetSheet) return;

  // 編集されたセルの行番号を取得
  const activeRow = e.range.getRow();
  // 編集されたセルの列番号を取得
  const activeCol = e.range.getColumn();

  // 行が12行目以上、かつ、セルが7番目または8番目の場合
  if (activeRow > 11 && (activeCol === 7 || activeCol === 8)) {
    // 更新日時を書き込みたいセルを取得
    const targetCell = sheet.getRange('I' + activeRow);
    // 日時を書き込む
    targetCell.setValue(new Date());
  }
}

シートのインデックス番号で指定する場合

追加でご質問いただいた分です。

• .getIndex()でシートのインデックス番号を取得します。
• シートのインデックス番号は0ではなく、1から始まるので注意です。

function writeUpdateDate(e) {
  // 編集されたシートを取得
  const sheet = e.source.getActiveSheet();

  // 編集されたシートのインデックス番号を取得
  const sheetIndex = sheet.getIndex();
  // 対象シートのインデックス番号を格納した配列。
  // シートのインデックスは1から開始します。私の画像の例では、1が非対象、2,3が対象
  const targetSheetIndexes = [2, 3];
  // 編集されたシートが、対象のシートかどうか判定
  const isTargetSheet = targetSheetIndexes.includes(sheetIndex);

  // 非対象のシートだったら、ここで処理を終了。以降の処理は実行されない
  if (!isTargetSheet) return;

  // 編集されたセルの行番号を取得
  const activeRow = e.range.getRow();
  // 編集されたセルの列番号を取得
  const activeCol = e.range.getColumn();

  // 行が12行目以上、かつ、セルが7番目または8番目の場合
  if (activeRow > 11 && (activeCol === 7 || activeCol === 8)) {
    // 更新日時を書き込みたいセルを取得
    const targetCell = sheet.getRange('I' + activeRow);
    // 日時を書き込む
    targetCell.setValue(new Date());
  }
}

Comments

[tkSounds7506]

丁寧なご説明ありがとうございます。
対象のシート名を指定する場合　にて実施しようと思いましたが、シート名が変わる為、シート番号に置き換えたいnのですが再度教えていただけませんか？
(getSheets()['0','1','2'];かなと思ったのですが、うまくいかず...

[Cocode]

了解です。コードを書いてみます。

[Cocode]

回答を更新しました。ご確認ください。

[tkSounds7506]

確認しました、ありがとうございます。
こちらをトリガーを設定して動作させたところ、すでに入っている別のものも同時に実行されてしまいます。
回避する手段はありませんか？元々シート10枚で初回使用時のみ使用するものです。
以下3種類です。
①/*回数選択用データ値貼り付け処理*/
function copy_contentsOnly_first(){
var ash = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ash.getSheets()[0]
var range = sheet.getRange('X11:AI35');
var target = sheet.getRange('K11:V35');
range.copyTo(target, {contentsOnly:true});
}
②/*番号セルに値貼り付け処理*/
function copy_contentsOnly_finish(){
var ash = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ash.getSheets()[0]
var range = sheet.getRange('A11:E35');
var target = sheet.getRange('A11:E35');
range.copyTo(target, {contentsOnly:true});
}
③/*全シートの指定列を非表示にするスクリプト*/
function hideColumns_allsheets_retu_hihyouji() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
// 指定されたインデックスより始まる連続した列を非表示_hideColumns(columnIndex, numColumns)
sheet.hideColumns(10, 27);
}

[Cocode]

私のコード、①のコード、②のコード、③のコード
それぞれどんなトリガーを設定されていますかー？

[Cocode]

私のコードにしかトリガー設定してないのに、他のも動いちゃう感じですか？

[tkSounds7506]

おっしゃる通りです。
Cocode様のコードにのみトリガー設定していますが、他のも動いてしまいます。①②③にはトリガー設定せず、図形をボタンにして動作させています。

[Cocode]

実験してみます。

[Cocode]

図形でボタンを作ってhideColumns_allsheets_retu_hihyouji()を割り当てて検証してみましたが、私のコードの関数が実行されることでボタンに割り当てたスクリプトまで実行されるようなことはありませんでした。
不具合が再現できませんでした。

私のコードは他の関数と連動するような記述はしていないので、現在ご提示いただいている情報だけでは原因は分かりませんね…。
何か他に特別なことはされていませんでしょうか？

[tkSounds7506]

シートが10枚あるので、各コードは[0]、[1]〜[9]まで同じものが記載されています。なぜか[0]のシートだけは反映されず、[1]〜[9]のみ反映されます。
これでしょうか？

[Cocode]

> なぜか[0]のシートだけは反映されず、[1]〜[9]のみ反映されます。

反映というのは、writeUpdateDate()関数が実行されたら①②③のスクリプトも同時に実行されてしまうのが[1]〜[9]のシートのみということでしょうか？

でも①②③のコードは[0]のシートでしか処理しないコードなので、他のシートも処理しちゃうの変ですね。

[Cocode]

一度、図形ボタンに割り当てているスクリプト全部解除した上で、対象シートの対象セルを編集することでwriteUpdateDate()関数を実行させてみてください。
それでも不具合が起きるようでしたら、スクリプトエディタ内（コードの書き方など）に問題があると思います。

[tkSounds7506]

反映というのは、writeUpdateDate()関数が実行されたら①②③のスクリプトも同時に実行されてしまうのが[1]〜[9]のシートのみということでしょうか？
→その通りです。

でも①②③のコードは[0]のシートでしか処理しないコードなので、他のシートも処理しちゃうの変ですね。
→コードはシート番号を[1]〜[9]に変えただけのものが同じfunction内に存在します。
③/*全シートの指定列を非表示にするスクリプト*/
function hideColumns_allsheets_retu_hihyouji() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[1];
// 指定されたインデックスより始まる連続した列を非表示_hideColumns(columnIndex, numColumns)
sheet.hideColumns(10, 27);
}
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[2];
// 指定されたインデックスより始まる連続した列を非表示_hideColumns(columnIndex, numColumns)
sheet.hideColumns(10, 27);
}
上記コードが0〜9まで連続しています

[Cocode]

↑不具合の原因とはちがうとは思いますが、そのコードの書き方かなりよろしくないです…そこも修正した方がいいですね。（コード全部提示していただければ、そこの修正方法もご提案できます）

お願い①
・スプレッドシートのスクショ
・トリガーページのスクショ（設定ウィンドウも）
・コード全部
これらを、質問を編集して提示していただくことは可能でしょうか？
私の方で、全く同じものをつくって実験してみたいです。不具合を再現できないことには、原因究明も難しいので。

お願い②
> 一度、図形ボタンに割り当てているスクリプト全部解除した上で、対象シートの対象セルを編集することでwriteUpdateDate()関数を実行させてみてください。
↑こちらも実験お願いします。原因がどこに潜んでいるのか特定するのに役立ちます。

実際のスプレッドシートを共有して触らせていただくのが手っ取り早いのですが、teratailではそういうわけにはいきませんもんね…。

他の方の回答へのコメント欄でも「トリガーかけると他の関数も自動実行されてしまい困っています。」とおっしゃっているので、原因は元から潜んでいるのかと思われます。

[tkSounds7506]

お願い②
> 一度、図形ボタンに割り当てているスクリプト全部解除した上で、対象シートの対象セルを編集することでwriteUpdateDate()関数を実行させてみてください。
→こちらを先に実施しましたが、結果は変わらず...。

[tkSounds7506]

質問を新規追加しました。ご確認頂ければ幸いです。

[Cocode]

確認させていただきます！お手数をおかけしました。

[Cocode]

あれ？投稿されてないみたいです〜〜！

[Cocode]

あ、新規投稿ですね！失礼しました。

[tkSounds7506]

他のアカウントでオフライン操作をすると、オンラインに復帰してもトリガー発動せず日付が入力されないようです。オーナー権限だと問題なく反映されます。
何とか回避したいのですがこちらは仕様でしょうか？

Answer 2

シートを指定してgetActiveCell()してもアクティブシートのセルを取得している気がしますね。
やり方を少し変えてアクティブセルを取得してのアクティブセルのシートの名前、列、行で判定してはどうでしょう

function inputDate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //現在触っているファイルを取得
  var sheet = ss.getActiveSheet(); //アクティブシートを取得
  var sheetName = sheet.getName(); //アクティブシートのシート名を取得
  var currentRow = sheet.getActiveCell().getRow(); //アクティブなセルの行番号を取得
  var currentCol = sheet.getActiveCell().getColumn(); //アクティブなセルの列番号を取得
  var currentCell = sheet.getActiveCell().getValue(); //アクティブなセルの入力値を取得
  var updateRange = sheet.getRange('I' + currentRow); //どの列に更新日時を挿入したいか。この場合はI列
  Logger.log(updateRange); //更新日の記入
  if(sheetName == "1" || sheetName == "2" && currentRow > 10 && currentCol == 7 || currentCol == 8) { //シート名が1か2で11行目以降かつG列かH列の変更を参照とする 
    if(currentCell) {
    updateRange.setValue(new Date());
    }
  }
}

Comments

[tkSounds7506]

ご教示いただきありがとうございます。トリガーかけると他の関数も自動実行されてしまい困っています。
var ash = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ash.getSheets()[0]
上記で始まるものが他に3つありますが、回避できないものでしょうか？