【Google Apps Script】進捗状況が変わった日付を複数セルに記述したい

Question

前提・実現したいこと

進捗状況が変わった際のタイムスタンプをGoogle Apps Scriptで実行できるようにしたいです。
セルA2はデータ規則入力で選択するように設定しています。

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

その行に更新があった場合、指定のセルに更新日時を記入するところまではできたのですが、
特定のセルの内容が指定した内容に変わったこと、
また、複数のセルにタイムスタンプを実行するのを１つのScriptで設定することに手こずっています。

該当のソースコード

function insertLastUpdated() {
  var ss = SpreadsheetApp.getActiveSheet(); 
  var currentRow = ss.getActiveCell().getRow(); 
  var currentCell = ss.getActiveCell().getValue(); 
  var updateRange = ss.getRange('B' + currentRow) //更新日をいれる列をstringで指定。この場合はAT列
  Logger.log(updateRange);
  //更新日の記入
  if(currentRow>1){ 
    if(currentCell) { 
      updateRange.setValue(new Date());
    }
  }
}

Answer 1

GASの質問ですが、営業の案件管理のように見えるので、１つアイディアを提供します

今回の式だけでできます

列の見出しを、そのまま引用します。

１．sum() --> 行の中から最大の値を求める。「一番新しい日時はいつか？」
２．Match() --> 最大の値にマッチする位置を求める。「左から何番目か？」
３. Index() --> リストから n番目の　値を取る。「n列の項目名は？？」

=index(B1:E1,0,match(max(B2:E2),B2:E2,0))

入力ルールとして担当者さんに該当列でショートカットキー　CTR+ + :キーを押してもらいます。すると、本日の日時が入ります。

入力したデータをもとに進行状況を表示する方式のメリットは、”操作ミスによる誤りデータ”について、考慮しなくてよくなることです。

ユーザーがマウスを操作中に、ついうっかりして、指定したい進行状況と違うものを選んでしまったとき、

　例）”決定”を選ぶべきところを、1つ前の段階である”見積り”を選んでしまった場合。

以下の状況を考慮して処理を決めていく必要があります。

・すでに書いてあるタイムスタンプに上書きされてしまうのでは？
・案件途中の発生しなかった状態に記録が残ってしまうのでは？
（アポはとった→　見積りなしで失注になった）

また、案件管理であるあるなのが、「進行状態に新しい区分んを増やしたい」という事態です。
式で処理している場合、列を追加して、範囲を広くするだけなので、すぐに対応できます。

このような、過去の案件対応系のシステムを担当した経験から、今回のアイデアをご提供させていただきました。
参考になれば幸いです。

ちなみ、Google スプレッドのヘルプ : INDEX と MATCH を組み合わせて使用する方法が紹介されています。

https://support.google.com/docs/answer/3098242?hl=ja#null

Comments

[退会済みユーザー]

ご回答ありがとうございます！
おっしゃるとおり、営業の案件管理になります。

教えて頂いたかたちでやったらできました！
なのですが、これを実際に記入する営業は自分で日付を入れるのも面倒のようで、
（もちろん、ショートカットキーも使えないです・・・）
そのため進捗状況が動いた日付を自動で記入できたらいいなと思ったのです。

すごく便利な関数なので個人的に使おうと思いました。
Googleスプレッドのヘルプページもありがとうございます。
今後の勉強のため拝見致します。

Answer 2

「A列に並んでいる入力規則のデータを変えたとき、その行の、変更後の文字列と一致する列に、
変更日時を記録したい」ということだと理解しました。

（たとえばA2セルから「2(見積)」を選択したら、C列2行目のセルに、その時点の日時を記録する）（間違っていたらすみません。）


下記のコードでは、変更後の文字列に一致する列の列数を探し、
当該列に日時を記録するようにしています。

function insertLastUpdated() {
  var ss = SpreadsheetApp.getActiveSheet(); 
  var currentRow = ss.getActiveCell().getRow(); 
  var currentColumn = ss.getActiveCell().getColumn(); 
  var currentCell = ss.getActiveCell().getValue();

  var select = ss.getRange("E:E")  // ここに入力規則からの選択列を指定する。

  // 1行目の文字列データを格納する。
  var headerRange = ss.getRange("AU1:AY1") 
  var headerValues = headerRange.getValues()[0]
    // リストを変えた後の進捗状況文字列と同じ文字列の列数（0始まり）を探す
  var c = headerValues.indexOf(currentCell)
  
  // 見つからなかった場合（-1）は何もしない。
  if (c<0) return;
  
  //更新日時をいれる列を指定(B列以降)
  var updateRange = ss.getRange(currentRow, c + headerRange.getColumn());
  
  // 更新日時の記入
  if(currentRow > 1 && currentColumn === select.getColumn()) { 
    if(currentCell) { 
      updateRange.setValue(new Date());
    }
  }
}

Comments

[退会済みユーザー]

ご説明ありがとうございます！
おっしゃるとおりの解釈で問題ございません。
こちらのコードで新規作成スプレッドシートで試してみたら無事できました。
なのですが、実際に使用するシートでは進捗状況選択列がE列、1(アポ)～5(失注)がAU~AY列のため、

// 1行目の文字列データを配列として格納する。
var headers = ss.getRange("B1:F1").getValues()[0];

上記を
var headers = ss.getRange("AU1:AY1").getValues()[0];

に変更したのですが、上手く動きませんでした。
これは進捗状況選択列がA列からE列に移動したことで起きていると思ったのですが、
コードの検索をしても分からずお手数ですが教えて頂けますと幸いです。
よろしくお願い致します。

[退会済みユーザー]

コードを修正しました。

[退会済みユーザー]

ありがとうございます！
成功しました！