How to send calendar based when checkbox is ticked

Question

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

Do NOT share [spreadsheets](//meta.stackoverflow.com/a/260455)/[images](//meta.stackoverflow.com/q/285551) as the only source of data. Make sure to add input and expected output as **plain text table** to the question. [Click here](//webapps.stackexchange.com/a/161855) to create a table easily, which are **easier to copy/paste as well**. Your question may be closed, if it isn't self contained. [Your email address can also be accessed by the public](//meta.stackoverflow.com/q/394304), if you share Google files. — TheMaster, Nov 02 '22 at 09:57
In your script, it seems that `e` is not declared. So, I'm worried that you might have miscopied your current script. How about this? — Tanaike, Nov 02 '22 at 12:19

score 2 · Accepted Answer · edited Nov 25 '22 at 06:08

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 number of days
    var nDay = new Date(day);
    CalendarApp.createAllDayEvent('This is a test event',nDay);
  }
}

Example:

References:

How to send calendar based when checkbox is ticked

1 Answers1