Google sheet to auto- populate google calendar

Question

I am trying to get data from a particular google sheets to create events in google calendar. See the spreadsheet.

https://docs.google.com/spreadsheets/d/1eBEStiTKXI0YPXfQBYzqXjwwv4kZ21033TdtysxdhHI/edit?usp=sharing

Basically, when someone ticks the boxes, it creates an event for the person (row 2) at date (Col B) at 10:00:00 AEST. So for example, when some ticks the box in cell P14, it creates an event with:

name - Lily Ahadi - PC Date of event - 16-Mar-2020 Time of event: 10:00:00

Here is the code I partially worked on and then got someone else to help and we both didnt get anywhere. I am about to give up but thought I will give a last try with stackflow experts. The code is availabe in the script editor of the sheet.

 function onEdit(e) {
   try {
  var range =  e.range;
  Browser.msgBox(range);
  var nameSheet =  e.source.getSheetName();
  var rowID = range.rowStart;
  var colID = range.columnStart;
  var res = e;

  var oldValue = res.oldValue;
  var newValue = res.value;

  var data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(nameSheet);
  
  if (oldValue == "FALSE" && newValue == "TRUE" && nameSheet == "Master Client List"){

    var category = data.getRange(3, colID).getValue();
    var date = data.getRange(rowID, 2).getValue(); 
   date = getYesterdaysDate(date) 

    var timeDiff = 60;
    var startTime = "10:00:00";

    if (category == "PC"){
      var name = data.getRange(2, colID - 1).getValue();
     
     Browser.msgBox(name+'-'+category + date);
      
    }else{
      var name = data.getRange(2, colID - 2).getValue();

    }

    // var startDateTime = date+' '+startTime+':00';
    var startDateTime = testMoment1(date, startTime);


  
    var endDateTime = addMins(startDateTime, timeDiff);


    var event = CalendarApp.getDefaultCalendar().createEvent(name+'-'+category,
      new Date(startDateTime),
      new Date(endDateTime),
      {description: ''});
    Logger.log('Event ID: ' + event.getId());

  }
}
catch(err) {
    SpreadsheetApp.getUi().alert(err);
  }
}




function testMoment(date, time) {
  eval(UrlFetchApp.fetch('https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js').getContentText());
  var date = moment(date).format('YYYY/MM/DD');
  // var time = time;

  // Logger.log(moment(date).format('MM/DD/YYYY'));

  // tell moment how to parse the input string
  var momentObj = moment(date + time, 'YYYY-MM-DDLT');

  // conversion
  var dateTime = momentObj.format('YYYY-MM-DDTHH:mm:ss');

  return dateTime;
}

function testMoment1(date, time) {
  eval(UrlFetchApp.fetch('https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js').getContentText());
  // var date = moment(date).format('YYYY/MM/DD');
  // var time = time;

  // Logger.log(moment(date).format('MM/DD/YYYY'));

  var c = new Date();
  var n = c.getFullYear();

  // tell moment how to parse the input string
  var momentObj = moment(date + time, 'YYYY-MM-DDLT').set('year', n).add(0, 'days');

  // conversion
  var dateTime = momentObj.format('YYYY-MM-DDTHH:mm:ss');
      // dateTime = moment(dateTime, "YYYY-MM-DDTHH:mm:ss");


  return dateTime;
}

function addMins(dateTime, durationInMinutes){

  eval(UrlFetchApp.fetch('https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js').getContentText());
  var startTime = dateTime;
  // var durationInMinutes = '120';

  var endTime = moment(startTime, 'YYYY-MM-DDTHH:mm:ss').add(durationInMinutes, 'minutes').format('YYYY-MM-DDTHH:mm:ss');

  return endTime;
}

function getYesterdaysDate(date1) {
    var date = new Date(date1);
    date.setDate(date.getDate());

    var day = date.getDate();
    var month = (date.getMonth()+1);
    var year = date.getFullYear();

    month = month < 10 ? '0'+month : month;
    day = day < 10 ? '0'+day : day;
    // Logger.log(date.getFullYear() + '-' + (date.getMonth()+1) + '-' + date.getDate());
    return year + '/' + month + '/' + day;
}

function myFunction() {

  var data=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Master Client List');
  // var s=ss.getActiveSheet();
  var c=data.getLastColumn();

  for (var i = 3; i <= c; i++) {

    if (i%3 === 0){
      var name = data.getRange(2, i).getValue();
      var dob = data.getRange(1, i).getValue();

      var timeDiff = 60;
      var startTime = "10:00:00";
      Logger.log(name);
 Logger.log(name);
  Logger.log(startTime);
  
    //birthdayevent(name, dob, startTime, timeDiff)
      Logger.log(name);
      // break;
    }
  }
}   

Answer 1

Your code has two main issues:

1. You are querying for if (oldValue == "FALSE")

If you implement into your code the line Logger.log(oldValue); you will realize that an empty checkbox will return you the value "false" and not "FALSE". You need to modfy your if condition accordingly.

2. You are trying to use UrlFetchApp on simple onEdit trigger

As specified under restrictions for simple triggers:

They cannot access services that require authorization.

This problem can be easily solved by transforming your trigger into an installable one. For this:

• Rename your function onEdit() to something different
• Bind to the funciton an installable onEdit trigger as described here

After you implement those two modifications your code will run and create an event when you check a checkbox.

Now, I am not familiar enough to judge either the event parameters (data, event title) are retrieved as you desire. For troubleshooting I recommend you to log all variables - this will help you to spot any error quickly.