Google脚本:带有今天日期的行中的getValue [英] Google Script: getValue from row with today's date

问题描述

我正在使用Google Sheets脚本，正在尝试识别超出某个阈值的值.在这种情况下，有3个变量f1，f2和pf.当具有最高值(三个中的一个)的单元格超过500时，它将把该值写入另一个单元格.最终的结果是创建一个onEdit触发器，该触发器将能够自动检查每天输入的值，并在超出限制时发送电子邮件通知.但是为了简单起见，我暂时错过了这一点.我正在努力从包含今天日期的行中将脚本获取到getValues().

I am working with google sheets scripting and am trying to identify values which exceed a certain threshold. In this case there are 3 variables f1,f2 and pf. When the cell with the highest value (out of the three) exceeds 500, it will write the value to another cell. The end game is to create an onEdit trigger that will be able to automatically check the values as they are entered daily and send email notifications if the limit is breached. But for simplicity I have missed this bit out for now. I am struggling to get the script to getValues() from the row that contains today's date.

以下是数据示例

      A          B       C        D
    ____________________________________
1   |                  H2S values
2   | Date       f1      f2      pf
3   |30/10/17   971.4   1037.6   809
4   |31/10/17   795.6   795.1    576
5   |01/11/17    429    444.3   351.8

它采用日期范围的第一行，而不是使用今天的日期作为行.其余代码在限制和取最大值方面起作用，但是我不知道如何将行与日期匹配. "Today_row"应返回与日期匹配的行号，即17年1月11日"Today_row"应等于5，但它返回"daterange"中的第一行，因此返回第3行.

Instead of taking the row with today's date, it takes the top row of the date range. The rest of the code works in terms of the limits and taking the highest value, but I can't figure out how to match the row with the date. 'Today_row' should return the row number which matches the date i.e on the 01/11/17 'Today_row' should equal 5, but instead it returns the first row in the 'daterange' and so it returns row 3.

这是我正在处理的代码:

Here is the code I am working on:

 function readCell() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();

  // set today's date
  var date = sheet.getRange("F1").setValue(new Date());
  // look up date range
  var daterange = sheet.getRange("A3:A");
  // find the row position of today's date
   if (date = daterange) {
   var today_row = daterange.getRow();
   var today_set = sheet.getRange("F2").setValue(today_row); }

  // set today's variables and show value in cell
    var today_h2s_f1 = sheet.getRange("B"+today_row).getValue();
    var today_f1_set = sheet.getRange("F3").setValue(today_h2s_f1);
    var today_h2s_f2 = sheet.getRange("C"+today_row).getValue();
    var today_f2_set = sheet.getRange("F4").setValue(today_h2s_f2);
    var today_h2s_pf = sheet.getRange("D"+today_row).getValue();
    var today_pf_set = sheet.getRange("F5").setValue(today_h2s_pf);

  // Set value of cell if the h2s level exceeds 500ppm, highest value out of f1,f2 and pf is chosen
    if (today_h2s_f1 > 500 && today_h2s_f1 > today_h2s_f2 && today_h2s_f1>today_h2s_pf) {
      var highest_h2s = sheet.getRange("F6").setValue(today_h2s_f1)}
    else if (today_h2s_f2 > 500 && today_h2s_f2 > today_h2s_f1 && today_h2s_f2 >today_h2s_pf){
      var highest_h2s = sheet.getRange("F6").setValue(today_h2s_f2)}
    else if (today_h2s_pf > 500 && today_h2s_pf > today_h2s_f1 && today_h2s_pf >today_h2s_f2){
      var highest_h2s = sheet.getRange("F6").setValue(today_h2s_pf)}
  }

任何帮助将不胜感激-谢谢.

Any help will be much appreciated - thank you.

推荐答案

获取daterange将为您提供一个Range对象，您需要对其进行迭代才能匹配特定的单元格.首先，我们需要获取日期范围，然后在比较之前使时间戳记信息无效.进行以下更改:

Getting daterange gives you a Range Object which you need to iterate in order to match a specific cell. First, we need to get a range of dates and then nullify timestamp information before comparing. Make the following changes:

// set and store a date object for today
var date = sheet.getRange("F1").setValue(new Date()).getValue();

// Get the range of dates to test
var daterange = sheet.getRange("A3:A").getValues()

// iterate the values in the range object
for(var i=0; i<daterange.length; i++) {

  // Compare only month/day/year in the date objects
  if(new Date(daterange[i]).setHours(0,0,0,0) == date.setHours(0,0,0,0)) {

    // if there's a match, set the row
    // i is 0 indexed, so add 3 to get the correct row
    var today_row = (i+3);

    // rest of your code
  }
}

我还没有测试if块中设置的每个变量，但是此位返回对日期和正确行的正确评估.

I haven't tested each of your variables set in the if block, but this bit returns a correct evaluation of the date as well as the correct row.

这篇关于Google脚本:带有今天日期的行中的getValue的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！