Google Script: getValue from row with today's date

Question

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.

Here is an example of the data

      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

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.

Brian Brian · Accepted Answer · 2017-11-01T15:30:35

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

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 Script: getValue from row with today's date

1 Answers