I thing I'm really close here but for whatever reason this isn't generating a calendar event for me. This is based on the createEvent function from the following link: https://developers.google.com/apps-script/advanced/calendar. I'm using a test sheet at the moment with the following spreadsheet example
I intend for the onEdit function to notify the user that a calendar event has been created when the checkbox in column 9 is selected, then call the createEvent function based on parameters corresponding to values retrieved from the same row as the selected checkbox.
/**
* Creates an event in the user's default calendar.
* @see https://developers.google.com/calendar/api/v3/reference/events/insert
*/
function onEdit(e) {
const sh = e.range.getSheet();
if (e.range.columnStart == 9 && e.value == "TRUE") {
var desc = sh.getRange(e.range.rowStart, 1).getValue();
var dateVal1 = sh.getRange(e.range.rowStart, 2).getValue();
var dateVal2 = sh.getRange(e.range.rowStart, 3).getValue();
var timeVal1 = sh.getRange(e.range.rowStart, 4).getValue();
var timeVal2 = sh.getRange(e.range.rowStart, 5).getValue();
var emailAddress = sh.getRange(e.range.rowStart, 6).getValue();
var month = Number(dateVal1.getMonth() 1);
var day = dateVal1.getDate();
// console.log(month)
var monthname = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];
e.source.toast("Calendar Event Created for " monthname[month-1] " " day);
createEvent(desc, dateVal1, dateVal2, timeVal1, timeVal2, emailAddress);
}
}
function createEvent(summ, dateValue1, dateValue2, timeValue1, timeValue2, email) {
const calendarId = 'primary';
// event details for creating event.
var mo = dateValue1.getMonth() 1;
var dayofmo = dateValue1.getDate();
var year1 = dateValue1.getYear() 1900;
var mo2 = dateValue2.getMonth() 1;
var dayofmo2 = dateValue2.getDate();
var year2 = dateValue2.getYear() 1900;
const start = getSheetDate(mo, dayofmo, year1, timeValue1, timeValue1);
const end = getSheetDate(mo2, dayofmo2, year2, timeValue2, timeValue2);
let event = {
summary: summ,
location: 'The Office',
description: 'Meeting regarding ...',
start: {
dateTime: start.toISOString()
},
end: {
dateTime: end.toISOString()
},
attendees: [
{email: email.toString()}
],
};
try {
// call method to insert/create new event in provided calandar
event = Calendar.Events.insert(event, calendarId);
Logger.log('Event ID: ' event.id);
} catch (err) {
Logger.log('Failed with error %s', err.message);
}
}
function getSheetDate(day, month, year, hour, minutes) {
const date = new Date();
// console.log(date.getDate())
date.setDate(month, day, year);
var hourstring = String(hour);
var ampm = hourstring.split(" ")[1];
var hournum = Number(hourstring.split(":")[0]);
// console.log(hournum);
// console.log(ampm);
if (ampm == 'P' && hournum < 12) {
hournum = (hournum 12);
date.setHours(hournum);
}
date.setHours(hournum);
// console.log(hournum);
var minutesstring = String(minutes);
var minutesnum = String(minutesstring.split(":")[1]);
var minutesnum2 = Number(minutesnum.split(" ")[0]);
date.setMinutes(minutesnum2);
// console.log(minutesnum2);
date.setSeconds(0);
date.setMilliseconds(0);
return date;
}
Currently the onEdit function notifies the user when a checkbox is selected. For example, in the given spreadsheet example, when a checkbox is selected it will say 'Calendar Event Created for December 13.'
Previously the createEvent function had no parameters and I had established variables that directly pulled the description, date, time, etc. from specific cells. This created calendar events flawlessly but then I had to exclusively reference these set cells which I don't want. I attempted the above and tried to create parameters within the createEvent function that could draw from the variables defined in that onEdit function.
Here is an example of the creatEvent function that worked like a charm, I just need this to function when I edit that checkbox cell.
function createEvent() {
const calendarId = 'primary';
// event details for creating event.
var sheet = SpreadsheetApp.getActive();
var summ = sheet.getRange('A2').getValue();
var dateValue1 = sheet.getRange('B2').getValue();
var dateValue2 = sheet.getRange('C2').getValue();
var timeValue1 = sheet.getRange('D2').getValue();
var timeValue2 = sheet.getRange('E2').getValue();
var email = sheet.getRange('F2').getValue();
var mo = dateValue1.getMonth() 1;
var dayofmo = dateValue1.getDate();
var year1 = dateValue1.getYear() 1900;
var mo2 = dateValue2.getMonth() 1;
var dayofmo2 = dateValue2.getDate();
var year2 = dateValue2.getYear() 1900;
const start = getSheetDate(mo, dayofmo, year1, timeValue1, timeValue1);
const end = getSheetDate(mo2, dayofmo2, year2, timeValue2, timeValue2);
let event = {
summary: summ,
location: 'The Office',
description: 'Meeting regarding ...',
start: {
dateTime: start.toISOString()
},
end: {
dateTime: end.toISOString()
},
attendees: [
{email: email.toString()}
],
};
try {
// call method to insert/create new event in provided calandar
event = Calendar.Events.insert(event, calendarId);
Logger.log('Event ID: ' event.id);
} catch (err) {
Logger.log('Failed with error %s', err.message);
}
}
Could this maybe have something to do with how I'm getting the spreadsheet? (SpreadsheetApp.getActive() vs. e.range.getSheet())
SOLUTION:
Here is a functioning solution using an installable trigger thanks to onit, Cooper and doubleunary!
/**
* Creates an event in the user's default calendar.
* @see https://developers.google.com/calendar/api/v3/reference/events/insert
*/
function installableOnEdit(e) {
const sh = e.range.getSheet();
const [summ, dateValue1, dateValue2, timeValue1, timeValue2, email] = sh.getRange(e.range.rowStart, 1, 1, 6).getValues()[0];
if (e.range.columnStart == 9 && e.value == "TRUE") {
var month = Number(dateValue1.getMonth() 1);
var day = dateValue1.getDate();
// console.log(month)
var monthname = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];
e.source.toast("Calendar Event Created for " monthname[month-1] " " day);
const calendarId = 'primary';
// event details for creating event.
var mo = dateValue1.getMonth() 1;
var dayofmo = dateValue1.getDate();
var year1 = dateValue1.getFullYear();
var mo2 = dateValue2.getMonth() 1;
var dayofmo2 = dateValue2.getDate();
var year2 = dateValue2.getFullYear();
const start = getSheetDate(mo, dayofmo, year1, timeValue1, timeValue1);
const end = getSheetDate(mo2, dayofmo2, year2, timeValue2, timeValue2);
let event = {
summary: summ,
location: 'The Office',
description: 'Meeting regarding ...',
start: {
dateTime: start.toISOString()
},
end: {
dateTime: end.toISOString()
},
attendees: [
{email: email.toString()}
],
};
try {
// call method to insert/create new event in provided calandar
event = Calendar.Events.insert(event, calendarId);
} catch (err) {
Logger.log('Failed with error %s', err.message);
}
}
}
/**
* Helper function to get a new Date object relative to the current date.
* @param {number} daysOffset The number of days in the future for the new date.
* @param {number} hour The hour of the day for the new date, in the time zone
* of the script.
* @return {Date} The new date.
*/
function getSheetDate(day, month, year, hour, minutes) {
const date = new Date();
// console.log(date.getDate())
date.setDate(month, day, year);
var hourstring = String(hour);
var ampm = hourstring.split(" ")[1];
var hournum = Number(hourstring.split(":")[0]);
// console.log(hournum);
// console.log(ampm);
if (ampm == 'P' && hournum < 12) {
hournum = (hournum 12);
date.setHours(hournum);
}
date.setHours(hournum);
// console.log(hournum);
var minutesstring = String(minutes);
var minutesnum = String(minutesstring.split(":")[1]);
var minutesnum2 = Number(minutesnum.split(" ")[0]);
date.setMinutes(minutesnum2);
// console.log(minutesnum2);
date.setSeconds(0);
date.setMilliseconds(0);
return date;
}
CodePudding user response：
The onEdit(e) function is a simple trigger and thus cannot call functions that require authorization, such as Calendar.Events.insert().
To make it work, rename onEdit(e) to something like installableOnEdit(e) and run it through an installable trigger. You will be asked to authorize the function when you create the trigger. The function will run under the account that created the trigger, and all events will get created in that account's calendar.
var year1 = dateValue1.getYear() 1900;
You may want to use Date.getFullYear() instead of Date.getYear().