Change Drop Down List cell in Google Sheets based on date using Google Apps Script

0
votes

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?

1
google-apps-scriptgoogle-sheetsdrop-down-menu

1 Answers

0
votes

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: