Google脚本从Spreedsheet中的单元格中获取小时+分钟 [英] Google Script to get hour + minute from cellrange in Spreedsheet
问题描述
我正在尝试创建一个Google脚本，从一定范围的单元格中获取小时和分钟，并将其添加为总分钟数。
但是我在网上找到的例子都没有匹配任何东西来完成任务，并且我在检查API时没有找到任何帮助。
所以可以说我有一些这样的单元格：
| | A | B | C | D |
| 1 | 08:01:00 | 07:57:00 | 06:33:00 | 08:08:00 |
如何获得分钟数？
我正在考虑将范围作为函数的参数，在这种情况下x（A1：D1）
编辑：
澄清：我想发送一系列单元坐标给函数。从每个细胞中，我想要得到一个在几分钟内持续时间的总和。所以在两个单元格'08：00：00'和'07：30：00'之间，我应该得到930个。 解析方案
应该使用JavaScript日期方法相当简单，我尝试使用返回毫秒的 getTime（）并将结果值除以60000得到分钟，但测试不起作用我使用了一种不同的方法。
function test（）{
var sh = SpreadsheetApp.getActiveSheet（）;
var range = sh.getRange（'A1：D1'）; //用于测试的值，更改为您的用例
var minutes = totalMinutes（range）;
Logger.log（分钟）;
}
函数totalMinutes（范围）{
var data = range.getValues（）;
var total = 0;
for（var n in data [0]）{
var min = data [0] [n] .getMinutes（）+ 60 * data [0] [n] .getHours（）;
total + = min
}
总回报;
$ / code> 使用您的示例
它返回
上次编辑（和后续问题）： b
$ b
你没有提到你使用这个作为一个自定义函数...现在，我在你的工作表上测试它，我实际上可以看到这个问题！ （但不知道它来自哪里......）解决方案（我应该说解决方法）很简单，只需将1加到分钟值，结果是正确的，但问题就会变得不同：
当源范围中的一个单元格被修改时，此自定义函数不会更新结果!!!
例如：
单元格A1 = 20，单元格A2 = 20，自定义函数结果是40
现在当我将 A1 更改为10时，结果总是40 ，无论我做什么...
这真的很奇怪...
<对于信息，这里是代码作为自定义函数：
function myMinutes（cells）{
var range = SpreadsheetApp.getActiveSheet（）。getRange（cells）;
var data = range.getValues（）;
var total = 0;
for（var cell in data [0]）{
var hourtomin = data [0] [cell] .getMinutes（）+ 1;
var min = 60 *（data [0] [cell] .getHours（））;
total + = hourtomin + min;
}
总回报;
}
如果有人能解释？我没有。
我从不使用自定义函数，因为我不喜欢它们，这种行为不会让我改变主意......; - ）
I am trying to create a Google script to get hour and minute from a range of cells and add them up as total of minutes. But none of the examples I have found online matches anything to get the thing done, and I haven't found any help in checking the API.
So lets say that I have some cells like this:
| | A | B | C | D |
| 1 | 08:01:00 | 07:57:00 | 06:33:00 | 08:08:00 |
How do I get the minutes? I'm thinking of giving the range as a parameter to the function, in this case x(A1:D1)
EDIT: For clarify: I want to send a range of cell-coordinates to the function. From each cell I want to get a total sum of duration in minutes. So with two cells '08:00:00' and '07:30:00' I should get 930.
This should be fairly simple using JavaScript date methods, I tried working with getTime() that returns milliseconds and divide the resulting value by 60000 to get minutes but the test wasn't working so I used a different approach.
code below (it works if you don't have values with seconds, only hours and minutes)
function test(){
var sh = SpreadsheetApp.getActiveSheet();
var range = sh.getRange('A1:D1');// value used for test, change to your use case
var minutes = totalMinutes(range);
Logger.log(minutes);
}
function totalMinutes(range){
var data = range.getValues();
var total = 0;
for(var n in data[0]){
var min = data[0][n].getMinutes()+60*data[0][n].getHours();
total+=min
}
return total;
}
Using your example
it returns
last edit (and follow up question):
You didn't mention you were using this as a custom function... now that I tested it on your sheet I can actually see the issue ! (but have no idea where it comes from...) the "solution" (I should say "the workaround") is simple, just add 1 to minute value and results are right but the issue becomes different :
This custom function does not update results when one of the cells in the source range is modified !!!
for example :
cell A1 = 20, cell A2 = 20, custom function result is 40
now when I change A1 to 10, the result is always 40, no matter what I do...
That's really weird...
For info, here is the code as a custom function :
function myMinutes(cells){
var range = SpreadsheetApp.getActiveSheet().getRange(cells);
var data = range.getValues();
var total = 0;
for(var cell in data[0]){
var hourtomin = data[0][cell].getMinutes()+1;
var min = 60*(data[0][cell].getHours());
total += hourtomin + min;
}
return total;
}
If anyone can explain ??? I don't.
I never use custom functions because I don't like them and this behavior won't make me change my mind... ;-)
这篇关于Google脚本从Spreedsheet中的单元格中获取小时+分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
