I'm a noob to scripting and i have a spreadsheet that contains dates in some cells in row 0. Now i like te automatically create a checkbox in row 5 if row 0 contains a date. I use the checkbox to create or delete a event in the spreadsheet to a calendar. Now a need to put the check boxes in manually when i put in a new date.The script i'm using you can see down here.
//**
// * Adds a custom menu to the active spreadsheet, containing a single menu item
// * for invoking the exportEvents() function.
// * The onOpen() function, when defined, is automatically invoked whenever the
// * spreadsheet is opened.
// * For more information on using the Spreadsheet API, see
// * https://developers.google.com/apps-script/service_spreadsheet
//*/
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Export Events",
functionName : "exportEvents"
}];
sheet.addMenu("Calendar Actions", entries);
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Delete Events",
functionName : "deleteAllDayEvents"
}];
sheet.addMenu("Delete events", entries);
};
/**
* Export events from spreadsheet to calendar
*/
function exportEvents() {
var space = " - "
var sheet = SpreadsheetApp.getActiveSheet();
//var sheetName = sheet.getName() als je een tweede sheet gebruikt
//if(sheetName != "Events"){
//return
var headerRows = 1; // Number of rows of header info (to skip)
var range = sheet.getDataRange();
var data = range.getValues();
var calId = "stichtingmotardstein@gmail.com";
var cal = CalendarApp.getCalendarById(calId);
for (i=0; i<data.length; i++) {
if (i < headerRows) continue; // Skip header row(s)
var row = data[i];
var date = new Date(row[0]); // First column
if(isNaN(date.valueOf()))
continue; // skip row, go to the next row
var numb = row[3];
var title = numb.substring(11,6) +space+ row[2];
var loc = row[1];
var id = row[4]; // Sixth column == eventId
// Check if event already exists, update it if it does
try {
var event = cal.getEventSeriesById(id);
}
catch (e) {
// do nothing - we just want to avoid the exception when event doesn't exist
}
if (!event) {
//cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
var newEvent = cal.createAllDayEvent(title, date, {location:loc}).getId();
row[4] = newEvent; // Update the data array with event ID 4
}
else {
event.setTitle(title);
event.setLocation(loc);
// event.setTime(tstart, tstop); // cannot setTime on eventSeries.
// ... but we CAN set recurrence!
var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
event.setRecurrence(recurrence, date);
}
debugger;
}
// Record all event IDs to spreadsheet
range.setValues(data);
}
function deleteAllDayEvents() {
var ss=SpreadsheetApp.getActive();
var sh=ss.getActiveSheet();
var rg=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn());
var vA=rg.getValues();
var cal=CalendarApp.getCalendarById("stichtingmotardstein@gmail.com");
vA.forEach(function(r,i){
var id=r[4];
if(id && r[5]) {
cal.getEventById(id).deleteEvent();
sh.getRange(i+2,5).setValue("");
sh.getRange(i+2,6).setValue("TRUE");//just to reset the checkbox
}
})
}
Aucun commentaire:
Enregistrer un commentaire