0
234
ViewsGoogle sheets appscript to create calendar failing
I'm trying to create a calendar from the google sheets using appscript. I'm using trying below and getting error.
Here's what I'm trying to do. I want to create all the list of events in googlesheets to calendar, any further updates in sheet should update calendar only upon any recent entries/edit to the sheet and that particular event only.
Example: I've events from row A2: A15 in googlesheets, I want to create all at once. Post which if I add new row A16, it should create a calendar event for A16 details. Also, If I edit A10 row, then my existing calendar in A10 should update without spamming other calendar events.
Just for context: A1 = Title, B1 = Start date & time , C1 = End date & time, D1 = guest emails.
And I tried to declare the variable guest in for loop, it didn't work as well. Any suggestions or recommendations on how I can fulfill this.
function create_Events(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Calendar");
var last_row = sheet.getLastRow();
var data = sheet.getRange("A1:E" + last_row).getValues();
var cal = CalendarApp.getCalendarById("CALENDARID");
//Logger.log(data);
for(var i = 0;i< data.length;i++){
//index 0 =
var event = CalendarApp.getDefaultCalendar().createEvent(data[i][0],
new Date(data[i][1]),
new Date(data[i][2]),
{guests: data[i][3]});
Logger.log('Event ID: ' + event.getId());
}
}
2 answers
Answer question0
That works for me. I added a column with id (eventId) to be able to update in the future. The id will be also a good indicator to follow the process and prevent duplicates.
const myCalend = CalendarApp.getCalendarById("############@gmail.com");
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
function createCalendarEvent() {
sheet.getDataRange().getValues().forEach((entry, i) => {
if (entry[0] != '' && entry[4] == '') {
let id = myCalend.createEvent(entry[0], entry[1], entry[2], { guests: entry[3] }).getId();
sheet.getRange(+i + 1, 5).setValue(id)
}
});
}
0
Try something like this
function create_Events() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName("Calendar");
const vs = sh.getRange("A1:E" + sh.getLastRow()).getValues();
const cal = CalendarApp.getDefaultCalendar();
vs.forEach((r, i) => {
if (r[0] && r[1] && r[3]) {
cal.createEvent(r[0], new Date(r[1]), new Date(r[2]), { guests: r[3] });
} else {
Logger.log(i);
Logger.log(JSON.stringify(r));
}
});
}