勤務時間のオリジナル関数を作りたい。

Question

実現したいこと

googleスプレッドシートで勤務時間や超過勤務時間、深夜勤務時間を個別に各月毎に算出したい。
ただし、勤務時間の計算は別会社で働いていたとしても同じ日であるならば、1日の勤務時間は合計され、1日の勤務時間が8時間以上の場合は休憩時間としてマイナス60分、6時間以上8時間未満なら45分マイナスされるような計算をしたいです。

前提

次のようなシートがあります。
1つ目は合計シートとして、

A列、B列、C列に関しては手打ちです。
D列、E列、F列がオリジナル関数で数値をオートで求められるようにしたいと考えています。

その他に下記のような各月のシートがあります。

A列、B列、D列～J列までは直接値を入力し、C列に関してはvlookupで合計シートから表示されるようにしています。またK列に関しましては、画像のように行毎にH列-E列+G列-F列となっています。

例えば、現状の4月の状態であれば
台帳番号897の太郎さんの場合は
4/1は1施設で9時間以上ということで 8時間
4/2は2施設で9時間以上ということで8時間
4/3は有給で9時間ということで8時間
合計4月の勤務時間は24時間ということで、合計シートのD3の答えは24になります。

当初D4の数式は、
=IF(sumifs('4月'!K:K,'4月'!B:B,A2,'4月'!A:A,"2023/4/1")*24>=9,sumifs('4月'!K:K,'4月'!B:B,A2,'4月'!A:A,"2023/4/1")*24-1,IF(sumifs('4月'!K:K,'4月'!B:B,A2,'4月'!A:A,"2023/4/1")*24>=6,sumifs('4月'!K:K,'4月'!B:B,A2,'4月'!A:A,"2023/4/1")*24-0.75,sumifs('4月'!K:K,'4月'!B:B,A2,'4月'!A:A,"2023/4/1")*24))
とし、これを月末まで作ろうとすると年間分を入れ込むと重くなると思いオリジナル関数を作成しようという運びになりました。

該当のソースコード

function CALCULATE_TOTAL(numberValue, monthValue, sheetName, dateColumn, numberColumn, valueColumn) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  
  var dataRange = sheet.getRange(dateColumn + ":" + valueColumn);
  var dataValues = dataRange.getValues();
  
  var total = 0;
  
  for (var i = 0; i < dataValues.length; i++) {
    var row = dataValues[i];
    var date = row[0];
    var number = row[numberColumn.charCodeAt(0) - dateColumn.charCodeAt(0)];
    var value = row[valueColumn.charCodeAt(0) - dateColumn.charCodeAt(0)];
    
    if (number === numberValue && date.getMonth() + 1 === monthValue) {
      if (value * 24 >= 9) {
        total += value * 24 - 1;
      } else if (value * 24 >= 6) {
        total += value * 24 - 0.75;
      } else {
        total += value * 24;
      }
    }
  }
  
  return total;
}

で、セルには
=CALCULATE_TOTAL(897, 4, "Sheet1", "A", "B", "K")
と入力しました。
結果は-159060412800000.00と答えがおかしくなりました。
その後もchatGPTとやりとりしていると

function CALCULATE_NUMBER_MONTHLY_TOTAL(numberValue, monthValue, sheetName, dateColumn, numberColumn, valueColumn) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  
  var dataRange = sheet.getRange(dateColumn + ":" + valueColumn);
  var dataValues = dataRange.getValues();
  
  var total = 0;
  
  for (var i = 0; i < dataValues.length; i++) {
    var row = dataValues[i];
    var date = row[0];
    var number = row[numberColumn.charCodeAt(0) - dateColumn.charCodeAt(0)];
    var value = row[valueColumn.charCodeAt(0) - dateColumn.charCodeAt(0)];
    
    if (number === numberValue && date.getMonth() + 1 === monthValue) {
      if (value >= 0) {
        if (value * 24 >= 9) {
          total += value * 24 - 1;
        } else if (value * 24 >= 6) {
          total += value * 24 - 0.75;
        } else {
          total += value * 24;
        }
      }
    }
  }
  
  return total;
}

など試してみました。

試したこと

ChatGPTとのやり取りの一部
以下の手順に従って関数を作成します：

スクリプトエディタを開きます。スプレッドシート上で「ツール」→「スクリプトエディタ」を選択します。

スクリプトエディタが開かれたら、以下の関数を入力します：

function calculateTotal(hoursRange, ledgerRange, monthRange) {
  var hours = hoursRange.getValues();
  var ledgers = ledgerRange.getValues();
  var months = monthRange.getValues();
  var total = 0;
  
  for (var i = 1; i < hours.length; i++) {
    var month = new Date(months[i][0]).getMonth() + 1;
    
    if (month === new Date().getMonth() + 1) {
      var ledger = ledgers[i][0];
      var hour = hours[i][0];
      
      if (ledger === "" || hour === "") {
        continue;
      }
      
      var timeValue = hour * 24;
      
      if (timeValue >= 9) {
        total += timeValue - 1;
      } else if (timeValue >= 6) {
        total += timeValue - 0.75;
      } else {
        total += timeValue;
      }
    }
  }
  
  return total;
}

上記の関数を保存し、スクリプトエディタを閉じます。

シート「合計」に戻り、合計の計算を行いたいセルに以下の式を入力します（例: C2セル）：

less
Copy code
=calculateTotal('4月'!K2:K, '4月'!B2:B, '4月'!A2:A)
この式は、'4月'シートの列Kを時間の範囲、列Bを台帳番号の範囲、列Aを日付の範囲として指定しています。適宜、これらの範囲を修正してください。

入力した式に基づいて、特定の月の合計が自動的に計算されます。この式を他のセルにコピーして、他の月に対しても適用することができます。
以上の手順に従って、各月に応じて修正されるオリジナル関数を作成しました。この関数は現在の月を基準に合計を計算します。

User
TypeError: hoursRange.getValues is not a function（行 2）が発生しました。
ChatGPT
申し訳ありません、エラーが発生しました。おそらくhoursRangeが範囲ではなく、単一のセルを参照しているためです。

以下の修正を行い、再度試してみてください：

スクリプトエディタを開きます。

calculateTotal関数の以下の行を修正します：

Copy code
var hours = hoursRange.getValues();

修正後の行は以下のようになります：

Copy code
var hours = hoursRange.getValue();

修正を保存し、スクリプトエディタを閉じます。
これでエラーが修正され、関数が正常に動作するはずです。再度試してみてください。お手数ですが、もし再びエラーが発生した場合はお知らせください。

User
修正したようには見えませんが…

ChatGPT
申し訳ありません、修正が不十分でした。もう一度修正を行います。

以下の修正を行ってください：

スクリプトエディタを開きます。

calculateTotal関数の以下の行を修正します：

Copy code
var hours = hoursRange.getValue();

修正後の行は以下のようになります：

Copy code
var hours = hoursRange.getValues();

修正を保存し、スクリプトエディタを閉じます。
お手数ですが、もう一度修正後のスクリプトを試してみてください。もし再度エラーが発生した場合は、再度お知らせください。

User
var hours = hoursRange.getValues();とvar hours = hoursRange.getValue();なにがちがうのですか？
ChatGPT
hoursRange.getValues()とhoursRange.getValue()は、スプレッドシート上のデータの取得方法に違いがあります。

hoursRange.getValues()は、指定した範囲内の複数のセルの値を二次元の配列として返します。つまり、範囲内のすべてのセルの値を取得します。たとえば、K2:Kのような範囲を指定すると、K2セルからK列の最後のセルまでの値を取得します。

一方、hoursRange.getValue()は、指定した範囲の単一のセルの値を取得します。つまり、範囲内の最初のセルの値を取得します。たとえば、K2のような単一のセルを指定すると、そのセルの値を取得します。

修正すべきはhoursRange.getValues()です。お手数ですが、もう一度修正後のスクリプトを試してみてください。もし再度エラーが発生した場合は、再度お知らせください。

User
TypeError: hoursRange.getValue is not a function（行 2）が発生しました。
ChatGPT
申し訳ありません、再度エラーが発生しました。おそらくhoursRangeが範囲ではなく、単一のセルを参照しているためです。

以下の修正を行ってください：

スクリプトエディタを開きます。

calculateTotal関数の以下の行を修正します：

Copy code
var hours = hoursRange.getValues();

修正後の行は以下のようになります：

Copy code
var hours = [[hoursRange.getValue()]];

修正を保存し、スクリプトエディタを閉じます。
お手数ですが、もう一度修正後のスクリプトを試してみてください。もし再度エラーが発生した場合は、再度お知らせください。

User
TypeError: hoursRange.getValue is not a function（行 2）が発生しました。
ChatGPT
申し訳ありません、再度エラーが発生しました。おそらくhoursRangeが範囲ではなく、単一のセルを参照しているためです。

以下の修正を行ってください：

スクリプトエディタを開きます。

calculateTotal関数の以下の行を修正します：

Copy code
var hours = hoursRange.getValues();

修正後の行は以下のようになります：

Copy code
var hours = [[hoursRange.getDisplayValue()]];

修正を保存し、スクリプトエディタを閉じます。
お手数ですが、もう一度修正後のスクリプトを試してみてください。もし再度エラーが発生した場合は、再度お知らせください。

User
上記で試しています
ChatGPT
申し訳ありません、再度エラーが発生しました。もう一度修正を行います。

以下の修正を行ってください：

スクリプトエディタを開きます。

calculateTotal関数の以下の行を修正します：

var hours = hoursRange.getValues();

修正後の行は以下のようになります：

var hours = [[hoursRange.getValue()]];

修正を保存し、スクリプトエディタを閉じます。
お手数ですが、もう一度修正後のスクリプトを試してみてください。もし再度エラーが発生した場合は、再度お知らせください。

User
function calculateTotal(hoursRange, ledgerRange, monthRange) {
var hours = [[hoursRange.getValue()]];

var ledgers = ledgerRange.getValues();
var months = monthRange.getValues();
var total = 0;

for (var i = 1; i < hours.length; i++) {
var month = new Date(months[i][0]).getMonth() + 1;

if (month === new Date().getMonth() + 1) {
  var ledger = ledgers[i][0];
  var hour = hours[i][0];
  
  if (ledger === "" || hour === "") {
    continue;
  }
  
  var timeValue = hour * 24;
  
  if (timeValue >= 9) {
    total += timeValue - 1;
  } else if (timeValue >= 6) {
    total += timeValue - 0.75;
  } else {
    total += timeValue;
  }
}

}

return total;
}
の状態でエラーが出ています
ChatGPT
申し訳ありません、エラーが発生しているようです。修正が必要です。

以下の修正を行ってください：

スクリプトエディタを開きます。

calculateTotal関数の以下の行を修正します：

var hours = [[hoursRange.getValue()]];

修正後の行は以下のようになります：

var hours = hoursRange.getValues();

修正を保存し、スクリプトエディタを閉じます。
お手数ですが、修正後のスクリプトを試してみてください。もし再度エラーが発生した場合は、再度お知らせください。

User
最初に戻っています
ChatGPT
申し訳ありません、混乱を招いてしまいました。おっしゃる通り、最初の修正で正しい修正内容を提供しました。

以下の修正を行ってください：

スクリプトエディタを開きます。

calculateTotal関数の以下の行を修正します：

var hours = [[hoursRange.getDisplayValue()]];

修正後の行は以下のようになります：

var hours = hoursRange.getValues();

修正を保存し、スクリプトエディタを閉じます。
お手数ですが、もう一度修正後のスクリプトを試してみてください。もし再度エラーが発生した場合は、再度お知らせください。

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

このような状況でありAIとは意思疎通が難しかったため、投稿させていただきました。
上記の勤務時間のオリジナル関数の他、深夜勤務（22時以降の勤務時間）や超過勤務時間（休憩を除いた8時間以上の時間）の出し方をご教授いただければ幸いです。
お手数をお掛け致しますが、よろしくお願いいたします。

Comments

[退会済みユーザー]

追記しました

Answer 1

function CALCULATE_TOTAL(numberValue, monthValue, sheetName, dateColumn, numberColumn, valueColumn) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var dataRange = sheet.getRange(dateColumn + ":" + valueColumn);
  var dataValues = dataRange.getValues();
  var subtotal = {};

  for (var i = 0; i < dataValues.length; i++) {
    var row = dataValues[i];
    var date = row[0];
    if (date === '' || 
      Object.prototype.toString.call(date) !== '[object Date]') 
        continue;
    var dateindex = Utilities.formatDate(date, "Asia/Tokyo", "yyMMdd");
    if (subtotal[dateindex] == null) subtotal[dateindex] = 0;
    var number = row[numberColumn.charCodeAt(0) - dateColumn.charCodeAt(0)];
    var value = row[valueColumn.charCodeAt(0) - dateColumn.charCodeAt(0)];
    value = value.getHours() + value.getMinutes() / 60;
    if (number === numberValue && date.getMonth() + 1 === monthValue) {
      subtotal[dateindex] += value;
    }
  }

  var total = 0;

  for (var dateindex of Object.keys(subtotal)) {
    var rest = 0;
    var perday = subtotal[dateindex];
    if (perday >= 9) {
      rest = 1;
    } else if (perday >= 6) {
      rest = 0.75;
    }
    total += (perday - rest);
  }
  return total;
}

AIは一切つかってません。

Comments

[sloth824]

ご回答ありがとうございます！

=CALCULATE_TOTAL(A3,4,"4月",'4月'!A:A,'4月'!B:B,'4月'!K:K)という入力式で会っていますでしょうか。
その場合、Exception: Range not found（行 3）が発生しました。と出ました。

ちなみにChatgptに
function CALCULATE_TOTAL(numberValue, monthValue, sheetName, dateColumn, numberColumn, valueColumn) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
var dataRange = sheet.getRange(dateColumn + ":" + valueColumn);
var dataValues = dataRange.getValues();
var subtotal = {};

for (var i = 0; i < dataValues.length; i++) {
var row = dataValues[i];
var date = row[0];
if (date === '' ||
Object.prototype.toString.call(date) !== '[object Date]')
continue;
var dateindex = Utilities.formatDate(date, "Asia/Tokyo", "yyMMdd");
if (subtotal[dateindex] == null) subtotal[dateindex] = 0;
var number = row[numberColumn.charCodeAt(0) - dateColumn.charCodeAt(0)];
var value = row[valueColumn.charCodeAt(0) - dateColumn.charCodeAt(0)];
value = value.getHours() + value.getMinutes() / 60;
if (number === numberValue && date.getMonth() + 1 === monthValue) {
subtotal[dateindex] += value;
}
}

var total = 0;

for (var dateindex of Object.keys(subtotal)) {
var rest = 0;
var perday = subtotal[dateindex];
if (perday >= 9) {
rest = 1;
} else if (perday >= 6) {
rest = 0.75;
}
total += (perday - rest);
}
return total;
}
は、具体的には数式として同入力したらよいでしょうか。
numberValueはsheet合計のA2、 monthValueは４月なので4, sheetNameは4月を参照したく、 dateColumnはsheet４月のA列、 numberColumnはsheet４月 のB列,valueColumnはsheet４月のK列です。

と質問してみたら、

数式として同じように入力する場合、以下のようになります。

=CALCULATE_TOTAL(A2, 4, "'4月'", "'4月'!A:A", "'4月'!B:B", "'4月'!K:K)

この数式は、A2セルの値をnumberValueとして、4をmonthValueとして、'4月'をsheetNameとして、'4月'シートのA列をdateColumn、B列をnumberColumn、K列をvalueColumnとして関数に渡します。

ただし、数式を直接入力する場合は、Google スプレッドシートの数式として認識されるため、スクリプトとして実行されるわけではありません。もし数式をスクリプトとして実行したい場合は、スクリプトエディタにコードを貼り付け、関数を呼び出す必要があります。

と言われ、実際に入力してみるとTypeError: Cannot read properties of null (reading 'getRange')（行 3）が発生しました。
と言われました。

本当に色々理解できておらず、申し訳ありません。

[退会済みユーザー]

> =CALCULATE_TOTAL(A3,4,"4月",'4月'!A:A,'4月'!B:B,'4月'!K:K)という入力式で会っていますでしょうか。
いいえ。
私の書いたコードを使いたいならば
=CALCULATE_TOTAL(A3, 4, "4月", "A", "B", "K")
という式を書けば動くと思います。

chatGPTの回答は間違ってます。

[sloth824]

ありがとうございました！
お蔭様で答えが出ました。
cm67809733様のお蔭で、やはりAIより人の方が素晴らしいことも改めて学ぶことが出来ました。
ありがとうございます！