How to send calendar based when checkbox is ticked-CodePudding

right now , im having trouble to figuring out a script where when user ticks the checkbox, it will send an email invite 3 weeks in advance based on the payment date

would need your help.

Here is my code which is rather incomplete.


function sendreminder(){
var sheet = SpreadsheetApp.getActiveSheet();
var sheetName = sheet.getName();
var range = e.range;
var approvalEdit = range.getValue().toString(); // Use string to avoid accidentally accepting truthy values.
var column = range.getColumn();
var emailsend = "EMAIL_SENT";
var approvalColumnNo = 12;
var invoice = sheet.getRange(e.range.getRow(),12).getValue();
var calend = CalendarApp.createAllDayEvent();
if( sheetName === "Sheet1" && column === approvalColumnNo && approvalEdit === "true" ){
calend.createAllDayEvent('TEST', new Date('November 20, 2022')
SpreadsheetApp.flush();

Many thanks!

it will send an email invite 3 weeks in advance based on the payment date

CodePudding user response：

You can try the following script:

function calendarEvent(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var sheetName = sheet.getName();
  var val = e.value;
  var rCol = e.range.getColumn();
  var rRow = e.range.getRow();
  if(rCol==12 && val=="TRUE" && sheetName=="Sheet1")
  {
    var val = sheet.getRange(rRow, 10).getValue();
    var day = new Date(val).getTime()   86400000*21; // 21 for the ammount of days
    var nDay = new Date(day);
    CalendarApp.createAllDayEvent('This is a test event',nDay);
  }
}

Example:

References: