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

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

ただいまの
回答率

88.32%

GASで貸出・返却時刻の自動で出したい(エラーが出て困っています)

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,433

jinjinghaiyue

score 13

 前提・実現したいこと

プログラミング初心者です。

現在、機材の貸出の管理簿としてGoogle App Scriptを用いてスプレッドシートを使用しています。
貸出と返却の時刻を以前までは手入力していたのですが、面倒臭いのでGASで自動入力をしようと思っています。
スプレッドシートのAとBの行にそれぞれ貸出・返却のチェックボックスを作り、そこに貸出・返却の際にチェックを入れることでそれぞれGとHの行にその時刻が出るようにしています。

A:貸出用チェックボックス
B:返却用チェックボックス
C:端末番号
D:(空欄)
E:利用者番号
F:利用者番号からVlookupで導き出した氏名
G:貸出時刻
H:返却時刻

になっています。

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

貸出(A行のチェックボックスにチェックを入れる)をすると時刻が正しく入力されるのですが、返却の際にB行のチェックボックスにチェックを入れると貸出時刻も変わります。つまり貸出・返却時刻共に「返却時刻」に変更されてしまいます。

それぞれトリガーは「変更時」になっていますが、トリガーのエラー(stackdriverのログ)を見ると

エラー
範囲外のセル参照です at logerR(コード:16)


となっています。

 Google App Script

function logerB() {//logerBというイベント
  var ssB = SpreadsheetApp.getActiveSheet(); //現在のスプレッドシートを取得
  var RowB = ssB.getActiveCell().getRow(); //現在のスプレッドシートの現在の行を取得
  var BCell = ssB.getRange('A' + RowB).getValue(); //現在のスプレッドシートの現在の行のA列
  var BTime = ssB.getRange('G' + RowB);//現在のスプレッドシートの現在の行のG列

  if(RowB>1){ //Rowが1行目でないとき
    if(BCell==true) { //現在のスプレッドシートの現在の行のA列の値の変更があったとき
      BTime.setValue(new Date());//現在時刻を挿入
    }
  }
}

function logerR() {//logerRというイベント
  var ssR = SpreadsheetApp.getActiveSheet(); //現在のスプレッドシートを取得
  var RowR = ssR.getActiveCell().getRow(); //現在のスプレッドシートの現在の行を取得
  var RCell = ssR.getRange('B' + RowR).getValue(); //現在のスプレッドシートの現在の行のB列
  var RTime = ssR.getRange('H' + RowR);//現在のスプレッドシートの現在の行のH列

  if(RowR>1){ //Rowが1行目でないとき
    if(RCell==true) { //現在のスプレッドシートの現在の行のA列の値の変更があったとき
      RTime.setValue(new Date());//現在時刻を挿入
    }
  }
}

 試したこと

トリガーを変更してみたりしてみました。

初歩的な質問で申し訳ありませんが、どなたかご教授いただけると幸いです。

 補足情報(FW/ツールのバージョンなど)

管理簿は一ヶ月ごとにファイルを新しくしていて、全てのシートは日付ごとに分けられています。
例)11/1、11/2・・・とシートが別々
それら全てに適用させたいです。

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

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

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

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

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 2

checkベストアンサー

+1

イベントハンドラーはハードルが高いみたいなんで、↓こうしてください。

const RCell = ssB.getRange('B' + RowB).getValue(); //これif(BCell==true && !RCell)が変更点です。

adhocに考えれば、問題は、「RCell」がオンになった状態なのに、「BCell」がオンになったときの処理が走ってします、だと言えます。つまりRCellがオンである時にBCellがオンになったときの処理が動かなければいい。あるいは、RCellがオフのときだけBCellがオンになった処理が動けばいい、と考えられます。
従って↑の赤字の部分の前者は、BCell処理において、RCellを考慮にいれ、かつ、後者はifにおいて、RCellがfalseであるかを識別すればよい、と考えられます。

function logerB() {//logerBというイベント
  var ssB = SpreadsheetApp.getActiveSheet(); //現在のスプレッドシートを取得
  var RowB = ssB.getActiveCell().getRow(); //現在のスプレッドシートの現在の行を取得
  var BCell = ssB.getRange('A' + RowB).getValue(); //現在のスプレッドシートの現在の行のA列
  const RCell = ssB.getRange('B' + RowB).getValue(); //これ
  var BTime = ssB.getRange('G' + RowB);//現在のスプレッドシートの現在の行のG列

  if(RowB>1){ //Rowが1行目でないとき
    if(BCell==true && !RCell) { //現在のスプレッドシートの現在の行のA列の値の変更があったとき
      BTime.setValue(new Date());//現在時刻を挿入
    }
  }
}

function logerR() {//logerRというイベント
  var ssR = SpreadsheetApp.getActiveSheet(); //現在のスプレッドシートを取得
  var RowR = ssR.getActiveCell().getRow(); //現在のスプレッドシートの現在の行を取得
  var RCell = ssR.getRange('B' + RowR).getValue(); //現在のスプレッドシートの現在の行のB列
  var RTime = ssR.getRange('H' + RowR);//現在のスプレッドシートの現在の行のH列

  if(RowR>1){ //Rowが1行目でないとき
    if(RCell==true) { //現在のスプレッドシートの現在の行のA列の値の変更があったとき
      RTime.setValue(new Date());//現在時刻を挿入
    }
  }
}

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/11/08 16:13

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

    キャンセル

  • 2018/11/08 16:14

    こっちが動いたってことね

    キャンセル

  • 2018/11/08 16:19

    はい。そうです。ありがとうございます。

    キャンセル

+1

エラーと時間の更新が意図通りいかないのは別問題です。

 エラー

変更時トリガーはやめて、編集時にしてください。概略だけいうと、変更時は例えばコメントの追加などのイベントも拾うので、activecellの参照が範囲外になる問題があります。

 更新が意図どおりいかない

このコードにはエラーがありません。コード通りの処理がなされます。プログラムとはそういうものです。現在実装しておられるコードは、

  • 「変更時に」
  • 1行目でなく、かつA列がtrueだったら時刻を挿入するという処理
  • 1行目でなく、かつB列がtrueだったら時刻を挿入するという処理

の2つの処理が行われています。ですので、B列にチェックをつけたときの「変更」時に、A列がtrueであればG列に書き込みが起こって当然でしょう。
意図を実装するやり方としては、

  • 編集時に
  • "編集中のセルが"A列であれば、G列に書き込みをし、
  • "編集中のセルが"B列であれば、H列に書き込みをする、

という選択的な動作を1回だけ実行する、という書き方をする必要があります。

function onEdit(e) { //onEditという名前ならトリガ設定不要
  if(e.range.getSheet().getSheetName() !== "シート名") {return;} //シートが違ってたら中断
  const col = e.range.getColumn();
  const row = e.range.getRow();
  if(row === 1) {return;} //ヘッダ行だったら中断
  if(col !== 1 && col !== 2) {return;} //A列でもB列でもなかったら中断
  if(e.value === "FALSE") {return;} //チェックを外したときは対象外
  e.range.getSheet().getRange(row,col + 6,1,1).setValue(new Date()); //チェックをつけた場所の6列右に日付を入れる
}

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/11/08 16:12

    完璧に動きました。ありがとうございます!!

    キャンセル

  • 2018/11/08 16:14

    どゆこと………

    キャンセル

  • 2018/11/08 16:16

    上の回答2で動いたということです。すみません

    キャンセル

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

  • ただいまの回答率 88.32%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

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