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 · Answer 1 · 2017-11-01T16:17:02.383

1

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.

edited Nov 01 '17 at 16:17

answered Nov 01 '17 at 15:30

Brian

4,274
2
27
55

Hi @brian thanks for your suggestion. I tried adding the above code but it says 'Range not found (line 15, file "Code")' - which is the line where I set the variables using the get range from today_row? Is there a way to set today_row to the row number where the iteration meets today's date? – Lisa M Nov 01 '17 at 15:43
The timestamp was messing with the comparison. You also can't call `.getRow()` on a string, so you need to store that as an integer value. The updated answer code gets the comparisons correct and sets `today_row` accordingly. – Brian Nov 01 '17 at 16:17
Many thanks. Unfortunately I get the same error "range not found". Although when I try the code without the variable today_h2s_f1 etc, today_row does return the correct row number. But when I run the code all together it says the today_row is undefined in the debugging details @Brian – Lisa M Nov 01 '17 at 16:34
@LisaM Here's a demo sheet I put together based on your question: https://docs.google.com/spreadsheets/d/1Z0IaxTR9K3kMM2YwV2DnjSJ7zJt8jtpt6TxKCTBvDNo/edit#gid=0 – Brian Nov 01 '17 at 16:44
Hi @brian . https://docs.google.com/spreadsheets/d/1HnKyTp31hWoRXyGeopUzGU9y1mvov6nK-sH1zTsodQM/edit?usp=sharing – Lisa M Nov 01 '17 at 16:47
This date comparison won't work because JavaScript objects are not equal to each other unless they are the same object. See [Compare two dates with JavaScript](https://stackoverflow.com/questions/492994/compare-two-dates-with-javascript) – Nov 10 '17 at 23:53

Google Script: getValue from row with today's date

1 Answers1

Linked