質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
86.02%
Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

Q&A

解決済

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

tkSounds7506
tkSounds7506

総合スコア3

Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

2回答

0グッド

0クリップ

400閲覧

投稿2022/11/18 06:53

前提

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

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

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

該当のソースコード

GoogleAppsScript

1```/*指定列に日付を自動入力するスクリプト*/ 2 3function inputDate() { 4 var ss = SpreadsheetApp.getActiveSpreadsheet(); //現在触っているファイルを取得 5 var sheet = ss.getSheetByName('1'); //対象のシート名を選択( ()部分にシート名記載) 6 var currentRow = sheet.getActiveCell().getRow(); //アクティブなセルの行番号を取得 7 var currentCol = sheet.getActiveCell().getColumn(); //アクティブなセルの列番号を取得 8 var currentCell = sheet.getActiveCell().getValue(); //アクティブなセルの入力値を取得 9 var updateRange = sheet.getRange('I' + currentRow); //どの列に更新日時を挿入したいか。この場合はI列 10 Logger.log(updateRange); //更新日の記入 11 if(currentRow > 10 && currentCol == 7) { //11行目以降かつG列の変更を参照とする 12 if(currentCell) { 13 updateRange.setValue(new Date()); 14 } 15 } 16 var ss = SpreadsheetApp.getActiveSpreadsheet(); //現在触っているファイルを取得 17 var sheet = ss.getSheetByName('1'); //対象のシート名を選択( ()部分にシート名記載) 18 var currentRow = sheet.getActiveCell().getRow(); //アクティブなセルの行番号を取得 19 var currentCol = sheet.getActiveCell().getColumn(); //アクティブなセルの列番号を取得 20 var currentCell = sheet.getActiveCell().getValue(); //アクティブなセルの入力値を取得 21 var updateRange = sheet.getRange('I' + currentRow); //どの列に更新日時を挿入したいか。この場合はI列 22 Logger.log(updateRange); //更新日の記入 23 if(currentRow > 10 && currentCol == 8) { //11行目以降かつH列の変更を参照とする 24 if(currentCell) { 25 updateRange.setValue(new Date()); 26 } 27 } 28} 29{ 30 var ss = SpreadsheetApp.getActiveSpreadsheet(); //現在触っているファイルを取得 31 var sheet = ss.getSheetByName('2'); //対象のシート名を選択( ()部分にシート名記載) 32 var currentRow = sheet.getActiveCell().getRow(); //アクティブなセルの行番号を取得 33 var currentCol = sheet.getActiveCell().getColumn(); //アクティブなセルの列番号を取得 34 var currentCell = sheet.getActiveCell().getValue(); //アクティブなセルの入力値を取得 35 var updateRange = sheet.getRange('I' + currentRow); //どの列に更新日時を挿入したいか。この場合はI列 36 Logger.log(updateRange); //更新日の記入 37 if(currentRow > 10 && currentCol == 7) { //11行目以降かつG列の変更を参照とする 38 if(currentCell) { 39 updateRange.setValue(new Date()); 40 } 41 } 42 var ss = SpreadsheetApp.getActiveSpreadsheet(); //現在触っているファイルを取得 43 var sheet = ss.getSheetByName('2'); //対象のシート名を選択( ()部分にシート名記載) 44 var currentRow = sheet.getActiveCell().getRow(); //アクティブなセルの行番号を取得 45 var currentCol = sheet.getActiveCell().getColumn(); //アクティブなセルの列番号を取得 46 var currentCell = sheet.getActiveCell().getValue(); //アクティブなセルの入力値を取得 47 var updateRange = sheet.getRange('I' + currentRow); //どの列に更新日時を挿入したいか。この場合はI列 48 Logger.log(updateRange); //更新日の記入 49 if(currentRow > 10 && currentCol == 8) { //11行目以降かつH列の変更を参照とする 50 if(currentCell) { 51 updateRange.setValue(new Date()); 52 } 53 } 54} 55

以下のような質問にはグッドを送りましょう

  • 質問内容が明確
  • 自分も答えを知りたい
  • 質問者以外のユーザにも役立つ

グッドが多くついた質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

気になる質問をクリップする

クリップした質問は、後からいつでもマイページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

下記のような質問は推奨されていません。

  • 間違っている
  • 質問になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

適切な質問に修正を依頼しましょう。

回答2

3

ベストアンサー

不具合の原因&欠点

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

コード例

イメージ説明

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

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

javascript

1function writeUpdateDate(e) { 2 // 編集されたシートを取得 3 const sheet = e.source.getActiveSheet(); 4 5 // 編集されたシート名が「対象シート」という文字列を含んでいるかどうか判定 6 const isTargetSheet = sheet.getSheetName().includes('対象シート'); 7 8 // 含んでいなかったら(非対象のシートだったら)、ここで処理を終了。以降の処理は実行されない 9 if (!isTargetSheet) return; 10 11 // 編集されたセルの行番号を取得 12 const activeRow = e.range.getRow(); 13 // 編集されたセルの列番号を取得 14 const activeCol = e.range.getColumn(); 15 16 // 行が12行目以上、かつ、列が7番目または8番目の場合 17 if (activeRow > 11 && (activeCol === 7 || activeCol === 8)) { 18 // 更新日時を書き込みたいセルを取得 19 const targetCell = sheet.getRange('I' + activeRow); 20 // 日時を書き込む 21 targetCell.setValue(new Date()); 22 } 23}
対象のシート名を指定する場合

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

javascript

1function writeUpdateDate(e) { 2 // 編集されたシートを取得 3 const sheet = e.source.getActiveSheet(); 4 5 // 編集されたシートのシート名を取得 6 const sheetName = sheet.getSheetName() 7 // (更新日時書き込みの)対象のシート名 8 const targetSheetNames = ['対象シート1', '対象シート2']; 9 // 編集されたシートが、対象のシートかどうか判定 10 const isTargetSheet = targetSheetNames.includes(sheetName); 11 12 // 非対象のシートだったら、ここで処理を終了。以降の処理は実行されない 13 if (!isTargetSheet) return; 14 15 // 編集されたセルの行番号を取得 16 const activeRow = e.range.getRow(); 17 // 編集されたセルの列番号を取得 18 const activeCol = e.range.getColumn(); 19 20 // 行が12行目以上、かつ、セルが7番目または8番目の場合 21 if (activeRow > 11 && (activeCol === 7 || activeCol === 8)) { 22 // 更新日時を書き込みたいセルを取得 23 const targetCell = sheet.getRange('I' + activeRow); 24 // 日時を書き込む 25 targetCell.setValue(new Date()); 26 } 27}
シートのインデックス番号で指定する場合

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

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

javascript

1function writeUpdateDate(e) { 2 // 編集されたシートを取得 3 const sheet = e.source.getActiveSheet(); 4 5 // 編集されたシートのインデックス番号を取得 6 const sheetIndex = sheet.getIndex(); 7 // 対象シートのインデックス番号を格納した配列。 8 // シートのインデックスは1から開始します。私の画像の例では、1が非対象、2,3が対象 9 const targetSheetIndexes = [2, 3]; 10 // 編集されたシートが、対象のシートかどうか判定 11 const isTargetSheet = targetSheetIndexes.includes(sheetIndex); 12 13 // 非対象のシートだったら、ここで処理を終了。以降の処理は実行されない 14 if (!isTargetSheet) return; 15 16 // 編集されたセルの行番号を取得 17 const activeRow = e.range.getRow(); 18 // 編集されたセルの列番号を取得 19 const activeCol = e.range.getColumn(); 20 21 // 行が12行目以上、かつ、セルが7番目または8番目の場合 22 if (activeRow > 11 && (activeCol === 7 || activeCol === 8)) { 23 // 更新日時を書き込みたいセルを取得 24 const targetCell = sheet.getRange('I' + activeRow); 25 // 日時を書き込む 26 targetCell.setValue(new Date()); 27 } 28}

投稿2022/11/18 08:50

編集2022/11/18 16:57
Cocode

総合スコア2245

spoofy_dragon, tkSounds7506😄を押しています
tkSounds7506👍を押しています

良いと思った回答にはグッドを送りましょう。
グッドが多くついた回答ほどページの上位に表示されるので、他の人が素晴らしい回答を見つけやすくなります。

下記のような回答は推奨されていません。

  • 間違っている回答
  • 質問の回答になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

このような回答には修正を依頼しましょう。

回答へのコメント

tkSounds7506

2022/11/18 15:28 編集

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

2022/11/18 16:52 編集

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

2022/11/18 16:57

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

2022/11/19 02:04

確認しました、ありがとうございます。 こちらをトリガーを設定して動作させたところ、すでに入っている別のものも同時に実行されてしまいます。 回避する手段はありませんか?元々シート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

2022/11/19 07:19

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

2022/11/19 08:18 編集

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

2022/11/19 10:30

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

2022/11/19 11:43

実験してみます。
Cocode

2022/11/19 12:14

図形でボタンを作ってhideColumns_allsheets_retu_hihyouji()を割り当てて検証してみましたが、私のコードの関数が実行されることでボタンに割り当てたスクリプトまで実行されるようなことはありませんでした。 不具合が再現できませんでした。 私のコードは他の関数と連動するような記述はしていないので、現在ご提示いただいている情報だけでは原因は分かりませんね…。 何か他に特別なことはされていませんでしょうか?
tkSounds7506

2022/11/20 00:55

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

2022/11/20 02:01

> なぜか[0]のシートだけは反映されず、[1]〜[9]のみ反映されます。 反映というのは、writeUpdateDate()関数が実行されたら①②③のスクリプトも同時に実行されてしまうのが[1]〜[9]のシートのみということでしょうか? でも①②③のコードは[0]のシートでしか処理しないコードなので、他のシートも処理しちゃうの変ですね。
Cocode

2022/11/20 02:07

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

2022/11/20 07:24 編集

反映というのは、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

2022/11/20 08:17

↑不具合の原因とはちがうとは思いますが、そのコードの書き方かなりよろしくないです…そこも修正した方がいいですね。(コード全部提示していただければ、そこの修正方法もご提案できます) お願い① ・スプレッドシートのスクショ ・トリガーページのスクショ(設定ウィンドウも) ・コード全部 これらを、質問を編集して提示していただくことは可能でしょうか? 私の方で、全く同じものをつくって実験してみたいです。不具合を再現できないことには、原因究明も難しいので。 お願い② > 一度、図形ボタンに割り当てているスクリプト全部解除した上で、対象シートの対象セルを編集することでwriteUpdateDate()関数を実行させてみてください。 ↑こちらも実験お願いします。原因がどこに潜んでいるのか特定するのに役立ちます。 実際のスプレッドシートを共有して触らせていただくのが手っ取り早いのですが、teratailではそういうわけにはいきませんもんね…。 他の方の回答へのコメント欄でも「トリガーかけると他の関数も自動実行されてしまい困っています。」とおっしゃっているので、原因は元から潜んでいるのかと思われます。
tkSounds7506

2022/11/20 13:54 編集

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

2022/11/20 14:52

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

2022/11/20 16:08

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

2022/11/20 16:09

あれ?投稿されてないみたいです〜〜!
Cocode

2022/11/20 16:37

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

2022/12/13 05:39

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

1

シートを指定して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()); } } }

投稿2022/11/18 08:08

編集2022/11/18 08:08
bebebe_

総合スコア352

tkSounds7506😄を押しています

良いと思った回答にはグッドを送りましょう。
グッドが多くついた回答ほどページの上位に表示されるので、他の人が素晴らしい回答を見つけやすくなります。

下記のような回答は推奨されていません。

  • 間違っている回答
  • 質問の回答になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

このような回答には修正を依頼しましょう。

回答へのコメント

tkSounds7506

2022/11/18 14:31

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

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
86.02%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問

同じタグがついた質問を見る

Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。