0

I'm completely new to Google Apps Script and I am trying to add a drop down list which has an IF statement in Google Sheets.

I have a column with the title 'Publication Date' in column A and the "Status" in column D. I would like each cell in column D to change from "Scheduled on Hootsuite" and "Scheduled on Wordpress" to "Published" once today's date has passed the publication date.

I created an IF statement formula but formulas cannot be used in standard Google Sheets data validation so I am turning to Google Apps Script but have no idea how to do it.

I've set up the drop down list in Google Apps Script:

function myFunction() {
  var cell = SpreadsheetApp.getActive().getRange('D2:D999');
var range = SpreadsheetApp.getActive().getRange('J2:J6');
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
cell.setDataValidation(rule);

}

The var range (J2:J6) is listed as follows; To be written, First draft written, Scheduled on Hootsuite, Scheduled on WordPress, Published.

Do you know how to add IF statements to this script?

sparkywales
  • 1
  • 2

1 Answers1

0

You can do it with a combination of a loop and an conditional statement

Sample:

function checkOnTimer(){
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var values = SpreadsheetApp.getActive().getRange('A2:D' + sheet.getLastRow()).getValues(); 
  for (var i = 0; i < values.length; i++){
    if(values[i][0].getTime() >= new Date().getTime() && (values[i][3] == "Scheduled on Hootsuite" || values[i][3] == "Scheduled on WordPress")){
      sheet.getRange((i+2), 4).setValue("Published");
    }
  }      
}

If you want to automate the task, create e.g. a daily time-driven trigger that will run the function automatically.

References:

ziganotschka
  • 25,866
  • 2
  • 16
  • 33
  • Thanks, but I'm getting the following error when I try to use that code; TypeError: values[i][0].getTime is not a function (line 5, file "Code") – sparkywales May 14 '20 at 15:15
  • This means that your cell value is either not a valid date or an empty cell. You can catch it with an if statement or adjust your data range accordingly. – ziganotschka May 14 '20 at 18:09