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
-
気になる質問をクリップする
クリップした質問は、後からいつでもマイページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
クリップを取り消します
-
良い質問の評価を上げる
以下のような質問は評価を上げましょう
- 質問内容が明確
- 自分も答えを知りたい
- 質問者以外のユーザにも役立つ
評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。
質問の評価を上げたことを取り消します
-
評価を下げられる数の上限に達しました
評価を下げることができません
- 1日5回まで評価を下げられます
- 1日に1ユーザに対して2回まで評価を下げられます
質問の評価を下げる
teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。
- プログラミングに関係のない質問
- やってほしいことだけを記載した丸投げの質問
- 問題・課題が含まれていない質問
- 意図的に内容が抹消された質問
- 過去に投稿した質問と同じ内容の質問
- 広告と受け取られるような投稿
評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。
質問の評価を下げたことを取り消します
この機能は開放されていません
評価を下げる条件を満たしてません
質問の評価を下げる機能の利用条件
この機能を利用するためには、以下の事項を行う必要があります。
- 質問回答など一定の行動
-
メールアドレスの認証
メールアドレスの認証
-
質問評価に関するヘルプページの閲覧
質問評価に関するヘルプページの閲覧
+2
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を使用した方が数式をセットしているということが明示されていてコードとしてわかりやすいのでお勧めしますが、実際にはどちらでも同じ結果が得られると思いますので好みの問題かと思います。
参考になれば幸いです。
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
checkベストアンサー
+1
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);
}投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
15分調べてもわからないことは、teratailで質問しよう！
- ただいまの回答率 88.33%
- 質問をまとめることで、思考を整理して素早く解決
- テンプレート機能で、簡単に質問をまとめられる
質問への追記・修正、ベストアンサー選択の依頼
Lhankor_Mhy
2017/12/06 12:27
『以下のコードではB8セルから引き算がもしできたとしても、もともとB8セルに入力していた計算式や書式が崩れてしまいます』とは、具体的には何が起きましたか？ 「どのようなコードにしたらどのように計算式や書式が崩れたか」をご提示ください。
nikukyu
2017/12/10 23:37
返答が遅くなってしまい申し訳ありません。情報のほう追加させていただきました。よろしくお願い致します。
Lhankor_Mhy
2017/12/11 11:04 編集
『もともとの計算式』とは『=C6-B6+"23:00"』という理解で合っていますか？ ということは、ボタン押下前は数式表示「=C6-B6+"23:00"」・値表示「=C6-B6+"23:00"の結果」、ボタン押下後は数式表示「=C6-B6+"23:00"」・値表示「=C6-B6+"46:00"の結果」、にしたいという理解であってますか。さらにそうだとすると、表示されている数式と表示される値を矛盾する形にしたいという希望である、と理解してかまいませんか？
nikukyu
2017/12/11 11:32
返答ありがとうございます。質問が分かりづらく申し訳ありません...。Lhankor_Mhy様の理解で合っています。質問では意図していなかったことですが、ボタン押下後の数式表示、値表示ともに「=C6-B6+"46:00"」でも大丈夫です。質問の意図としては「=C6-B6＋（あるいは−）」という部分の数式をボタン押下後も残したかったので、"23:00"の部分だけでもスクリプトで計算で加減できるならそのようなものでも嬉しいです。よろしくお願い致しますm(_ _)m