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

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

ただいまの
回答率

91.36%

  • JavaScript

    11226questions

    JavaScriptは、プログラミング言語のひとつです。ネットスケープコミュニケーションズで開発されました。 開発当初はLiveScriptと呼ばれていましたが、業務提携していたサン・マイクロシステムズが開発したJavaが脚光を浴びていたことから、JavaScriptと改名されました。 動きのあるWebページを作ることを目的に開発されたもので、主要なWebブラウザのほとんどに搭載されています。

  • VBA

    1124questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • Excel

    968questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

  • Google Apps Script

    363questions

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

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

解決済

回答 2

投稿 2017/12/05 18:26 ・編集 2017/12/10 23:36

  • 評価
  • クリップ 0
  • VIEW 104

nikukyu

score 8

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ページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • 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

    キャンセル

回答 2

+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); //ミリ秒⇒日変換

}

(追記2)

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

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

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

参考になれば幸いです。

投稿 2017/12/06 15:11

編集 2017/12/11 16:32

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

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

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

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/12/10 23:22

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

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

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

    キャンセル

  • 2017/12/11 11:28

    ミリ秒ですよ?

    キャンセル

  • 2017/12/11 21:12

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

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

    キャンセル

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);
}

投稿 2017/12/11 18:13

編集 2017/12/12 09:36

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

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

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

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/12/11 21:19

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

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

    キャンセル

  • 2017/12/11 21:28

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

    キャンセル

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

ただいまの回答率

91.36%

関連した質問

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

  • JavaScript

    11226questions

    JavaScriptは、プログラミング言語のひとつです。ネットスケープコミュニケーションズで開発されました。 開発当初はLiveScriptと呼ばれていましたが、業務提携していたサン・マイクロシステムズが開発したJavaが脚光を浴びていたことから、JavaScriptと改名されました。 動きのあるWebページを作ることを目的に開発されたもので、主要なWebブラウザのほとんどに搭載されています。

  • VBA

    1124questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • Excel

    968questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

  • Google Apps Script

    363questions

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