jeudi 27 décembre 2018

Google Script to execute IF Statement in a loop for date value

I'm working on a script to automate a daily copy+paste (as value) function. I've worked through it piece-by-piece and my final issue is the looped if-statement that looks at the date relative to today's date.

You can see that in column B is my 'Date' column and in row 1 column AN I entered a today() function.

Essentially, everyday I copy and paste (as value) any rows (columns I - AM) that match 'today's' date, highlight them grey and then hide the rows. Once I can set up the if-statement for the loop to recognize the correct rows, I can set a daily trigger in the morning to run the function.

The code is below - any and all help is greatly appreciated!

function dailyUpdate() 
{
  var sht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Copy of Results');
  var dateValues = sht.getRange(2,2,1231).getValues();
  for (r=1; r<1232; r++)
    var todayValue = sht.getRange(1,39).getValue();
    var dateValues = sht.getRange(r,2).getValues();
    if(dateValues == todayValue)
    {
      var source = sht.getRange(r,9,1,31);
      source.copyTo(sht.getRange(r,9,1,31), {contentsOnly: true});
      sht.hideRow(source)
      source.setBackground("grey");
  }
}

Aucun commentaire:

Enregistrer un commentaire