書式が「経過時間」のセルから任意の時間を引き算するボタンを作製したい。

Question

Googleスプレッドシートにおいて、書式が「経過時間」であるセルから任意の時間を引き算するボタンを設置しようと思い、以下のスクリプトを作成したのですが、結果が「#NUM!」と出てしまいます。
また、以下のコードではB8セルから引き算がもしできたとしても、もともとB8セルに入力していた計算式や書式が崩れてしまいます。

もし可能なら引き算もでき、なおかつ元のセルの計算式も残しつつ経過時間という書式を保持したいです。

有識者の知識をお借りしたいです。
どうぞご教授をよろしくお願い致します。

function minus23() {
  var sheet = SpreadsheetApp.getActiveSheet()
  sheet.getRange(8, 2).setValue(sheet.getRange(8, 2).getValue() - "23:00")
}

情報の追加

B8セルのもともとの計算式：=C6-B6+"23:00"
（便宜上23:00を足していますが、実際に使用したいのは1:00を足したり1:00を引いたりしたものです。また、C6,B6には日時が記入されています。）

試したコード①

function minus23() {
  var sheet = SpreadsheetApp.getActiveSheet()
  sheet.getRange(8, 2).setValue((sheet.getRange(8, 2).getValue() - 23))
}

スクリプト実施後：-2209082400023

試したコード②

function minus23() {
  var sheet = SpreadsheetApp.getActiveSheet()
  sheet.getRange(8, 2).setValue((sheet.getRange(8, 2).getValue() - "23:00"))
}

スクリプト実施後：#NUM!

試したコード③

function minus23() {
  var sheet = SpreadsheetApp.getActiveSheet()
  sheet.getRange(8, 2).setValue((sheet.getRange(8, 2).getValue() - 82800)/3600)
}

スクリプト実施後：-613634023

理想としましては、セルに表示される数字だけ変わり、スクリプトによる計算後ももともとの計算式は残ったままでC6,B6などの日時を変えるとそれに伴いB8の時間が変わると嬉しいです。
厳しい場合は、=C6-B6+"23:00"の計算結果を別セルに表示させて、スクリプトによる計算はそこで行うといったものでもとても嬉しいですm(_ _)m

Comments

[Lhankor_Mhy]

『以下のコードではB8セルから引き算がもしできたとしても、もともとB8セルに入力していた計算式や書式が崩れてしまいます』とは、具体的には何が起きましたか？　「どのようなコードにしたらどのように計算式や書式が崩れたか」をご提示ください。

[nikukyu]

返答が遅くなってしまい申し訳ありません。情報のほう追加させていただきました。よろしくお願い致します。

[Lhankor_Mhy]

『もともとの計算式』とは『=C6-B6+"23:00"』という理解で合っていますか？ ということは、ボタン押下前は数式表示「=C6-B6+"23:00"」・値表示「=C6-B6+"23:00"の結果」、ボタン押下後は数式表示「=C6-B6+"23:00"」・値表示「=C6-B6+"46:00"の結果」、にしたいという理解であってますか。さらにそうだとすると、表示されている数式と表示される値を矛盾する形にしたいという希望である、と理解してかまいませんか？

[nikukyu]

返答ありがとうございます。質問が分かりづらく申し訳ありません...。Lhankor_Mhy様の理解で合っています。質問では意図していなかったことですが、ボタン押下後の数式表示、値表示ともに「=C6-B6+"46:00"」でも大丈夫です。質問の意図としては「=C6-B6＋（あるいは−）」という部分の数式をボタン押下後も残したかったので、"23:00"の部分だけでもスクリプトで計算で加減できるならそのようなものでも嬉しいです。よろしくお願い致しますm(_ _)m

Answer 1

GASはあまり詳しくないのですが、時刻型のセルの値から"23:00"という文字列を引き算しているところが気になります。
シート上ではセルに似たような記述をすることで時間計算できるようですが、マクロでも同じように冗長な解釈をしてくれるものなのかどうなのか。

下記に似たような疑問で解決された記事がありましたのでご紹介します。
⇒Google Apps Scriptの時刻の計算

上記では、時刻に対して秒単位の数値で計算されているようです。
23時間減算したいのなら、23(時間)*60(分)*60(秒)を引くということになると思います。

手元に確認環境がないので検索結果だけでのアドバイスになりますが、ご確認ください。
参考になれば幸いです。

（追記）

前回はweb検索結果からのアドバイスのみでしたが、今回実際に検証環境ができましたので追記させていただきます。

前回、「秒換算してみては？」というようなアドバイスをさせていただいておりましたが、どうもそんな簡単な話でもなかったようでした。すみません。

---

まずシート上の入力値について
「経過時間」書式が設定されているセルを「数値型」書式に変更してみると、入力値が数値として表示されました。
この数値はどうやら日付単位の値になっているようです。
例：1:00:00(1時間) ⇒　0.0416666666666667 ≒ 1/24日

次にマクロ内で日付型変数の計算を行った結果を見てみると、こちらはどうやらミリ秒で結果が返されているようです。
例：10:00:00 - 6:00:00(日付型で計算) ⇒　14400000 ＝ 4 * 60 * 60 * 1000(4時間のミリ秒値)

これらを考慮して、マクロで計算した結果（ミリ秒）を経過時間書式の単位（日）に変換してあげれば計算ができそうです。

よくわからないところ

実際に上記の考え方でマクロを組んでみたところ、どうしても1日分のズレがでてしまいました。
テストではセルの値を30:00:00としており、ここから23時間引いた7:00:00という結果を得たかったのですが、どうしてもセルの取得値が6:00:00扱いとなってしまい-17:00:00という結果になってしまいます。

そこで6:00:00と入力したセルと30:00:00と入力したセルをそれぞれ用意し、Date型変数に取得して内容を比較してみたところ、
6:00:00と入力したセル：1899年12月30日 06:00:00
30:00:00と入力したセル：1899年12月31日 06:00:00
となっていました。

日付型変数を年月日指定なしでnew Date(0,0,0,23,0,0)のように作成すると1899年12月31日 23:00:00という値がセットされるので、てっきり基準日は1899年12月31日 0:00:00だと思っていたのですが、セルの日時は1899年12月30日 0:00:00が基準となっているようです。

上記も考慮して正しい値で計算できるようになったマクロが以下になります。

function minus23() {
  var sheet = SpreadsheetApp.getActiveSheet()
  
  var dtRet = new Date();   //計算結果

  var dtCell = new Date();  //セル値
  var dt23 = new Date(0,0,0-1,23,0,0);    //23時間(基準日の-1日を考慮)

  //セル値の取得
  dtCell = sheet.getRange(2, 2).getValue();

  //時間計算
  dtRet = dtCell - dt23;
  
  //Browser.msgBox(dtCell, Browser.Buttons.OK_CANCEL)
  //Browser.msgBox(dt23, Browser.Buttons.OK_CANCEL)
 
  sheet.getRange(2, 2).setValue(dtRet/1000/60/60/24); //ミリ秒⇒日変換
  
}

（追記２）

質問本文に追記されていた「最終的にやりたいこと」に目が行っておりませんでした。
マクロで計算結果を出したいのではなく、マクロを使ってセルに数式をセットされたかったのですね。

これはもうミリ秒とかも関係なく、単純な話です。
数式をセットしてあげればよいだけですので、数式の文字列を作成し、それをセルにセットしてあげれば実現できると思います。

function minus23() {
  var sheet = SpreadsheetApp.getActiveSheet()
  var strFormula = '=C6-B6+"' + '23:00' + '"'
  sheet.getRange("A1").setFormula(strFormula)
  sheet.getRange("A2").setValue(strFormula)
}

上記ではA1セルにsetFormulaで、A2セルにsetValueで数式を設定しています。

setFormulaを使用した方が数式をセットしているということが明示されていてコードとしてわかりやすいのでお勧めしますが、実際にはどちらでも同じ結果が得られると思いますので好みの問題かと思います。

参考になれば幸いです。

Comments

[nikukyu]

返答が遅くなってしまい、申し訳ありません。
上記の内容を実施してみました。
書式が時間であるセルから23時間を秒数に引いたものを3600で割ってみて引いた時間を出そうとしたのですが、うまくいきませんでした...

function minus23() {
var sheet = SpreadsheetApp.getActiveSheet()
sheet.getRange(8, 2).setValue((sheet.getRange(8, 2).getValue() - 82800)/3600)
}

B8セルには31:00という時間が入っており、上記を試した結果、-613634023という計算結果になりました...

[ExcelVBAer]

ミリ秒ですよ？

[nikukyu]

追記の回答ありがとうございます。
質問が分かりづらくて申し訳ありませんでした..(T_T)
最終目標としましては、マクロによる計算も行い、その後も「=C6-B6＋（あるいは−）」という部分の数式を残したいというものでした。
しかし、解答してくださったミリ秒の計算はとても勉強になりました。ありがとうございました。
今回はLhankor_Mhy様の解答をベストアンサーにさせていただきますが、jawa様にも本当に感謝しています。
独学で勉強しておりまだまだ至らないところが多々あり、質問も分かりづらかったことをお詫びします。
フォローさせていただきますので、また機会がありましたらよろしくお願いします
m(_ _)m

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

Answer 2

function minus23() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var test = sheet.getRange(8, 2).getFormulaR1C1().replace(/"(\d\d):00"/, function(_, p1){
    return "\"" + (Number(p1) + 23) + ":00\"";
  });
  sheet.getRange(8, 2).setFormulaR1C1(test);
}

動作確認しました。

コメントを受けて追記

応用するのはそれほど難しくないのではないかと思いますが……

function minus23() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var test = sheet.getRange(8, 2).getFormulaR1C1().replace(/"(-*\d*\d):00"/, function(_, p1){
    return "\"" + (Number(p1) - 23) + ":00\"";
  });
  sheet.getRange(8, 2).setFormulaR1C1(test);
}

Comments

[nikukyu]

解答ありがとうございます。
動作確認を行ったところ、まさに求めていたものでした！！
質問が分かりづらかったのにもかかわらず、噛み砕いてくださりありがとうございましたm(_ _)m

このようなアプローチの方法があることも学ぶことができ、本当に感謝しています。
このコードを用いて作成したかったものを早速作ってみたいと思います！ありがとうございました。

[nikukyu]

すみません、現在の+ 23　のところを- 23にしたものも作成しましたが、 + "0:00"　が最小でした。
ここからさらに- 23　のボタン押下後、- "23:00" あるいは　+ "-23:00"　とすることは可能でしょうか...