edited original i'm trying figure out how retrieve , report out details of calendar events repeated each guest in table format information can turned simple printed-out register of attendees each event.
i'm getting error range height - code create log of each event each guest (so can see compiling information correctly) doesn't output individual rows. error "incorrect range height, 1 should 7".
my code below. commented clear on each bit should do:
function onopen() { var sheet = spreadsheetapp.getactivespreadsheet(); var entries = [{ name : "get calendar info", functionname : "getcal" }]; sheet.addmenu("calendar actions", entries); } function getcal() { // export google calendar events google spreadsheet, 1 row each guest // // code retrieves events , guests between 2 dates specified calendar. // logs results in current spreadsheet starting @ cell a2 listing events, // dates/times, etc , calculates event duration (via creating formulas in spreadsheet). // reference websites: // https://developers.google.com/apps-script/reference/calendar/calendar // https://developers.google.com/apps-script/reference/calendar/calendar-event var mycal = "myemailaddress"; //this email address of whichever google account use var cal = calendarapp.getcalendarbyid(mycal); var guestemail = ""; var gueststatus = ""; var guestname = ""; //var startdate = browser.inputbox("start date, in format mm / dd / yyyy"); //var enddate = browser.inputbox("end date, in format mm / dd / yyyy"); //var startdate = "september 25, 2015 00:00:00 cst"; //var enddate = "september 26, 2015 23:59:59 cst"; //var events = cal.getevents(new date("september 25, 2015 00:00:00 cst"), new date("october 01, 2015 23:59:59 cst"), {search: '-project123'}); var events = cal.getevents(new date("september 25, 2015 00:00:00 cst"), new date("october 22, 2015 23:59:59 cst")); //var events = cal.getevents(new date(startdate), new date(enddate)); var sheet = spreadsheetapp.getactivesheet(); // uncomment next line if want clear spreadsheet content before running - note people have added columns on data though lost sheet.clearcontents(); // create header record on current spreadsheet in cells a1 onwards - match number of entries in "header=" last parameter // of getrange entry below var header = [["calendar address", "event title", "event description", "event location", "event start", "event end", "calculated duration", "visibility", "date created", "last updated", "mystatus", "created by", "all day event", "recurring event", "id","email","status","name"]] var range = sheet.getrange(1,1,1,18); range.setvalues(header); // loop through calendar events found , write them out starting on calulated row 2 (i+2) (var i=0;i<events.length;i++) { var row=i+2; var myformula_placeholder = ''; // matching "header=" entry above, detailed row entry "details=", , must match number of entries of getrange entry below logger.log("event "+i+": "+events[i].getid()); var guestlist=events[i].getguestlist(); //get email , status of each guest each event for(var d=0; guestlist!=null && d<guestlist.length; d++) { guestemail = guestlist[d].getemail(); gueststatus = guestlist[d].getgueststatus(); guestname = guestlist[d].getname(); logger.log("guest "+d+": "+guestlist[d].getemail()+", status: "+guestlist[d].getgueststatus()); var details=[[mycal,events[i].gettitle(), events[i].getdescription(), events[i].getlocation(), events[i].getstarttime(), events[i].getendtime(), myformula_placeholder, ('' + events[i].getvisibility()), events[i].getdatecreated(), events[i].getlastupdated(), events[i].getmystatus(), events[i].getcreators(), events[i].isalldayevent(), events[i].isrecurringevent(), events[i].getid(), guestlist[d].getemail(), guestlist[d].getgueststatus(), guestlist[d].getname()]]; logger.log(details); var range = sheet.getrange(row,1,guestlist.length,18); range.setvalues(details); // writes formula out calculate number of hours, specific row, in column 7 match position of field myformula_placeholder above. var cell = sheet.getrange(row,7); cell.setformula('=(hour(f' +row+ ')+(minute(f' +row+ ')/60))-(hour(e' +row+ ')+(minute(e' +row+ ')/60))'); cell.setnumberformat('.00'); } } }
with of wonderful academic @ work have completed code , operates required. final code posted below others.
function onopen() { var sheet = spreadsheetapp.getactivespreadsheet(); var entries = [{ name : "get calendar info", functionname : "getcal" }]; sheet.addmenu("calendar actions", entries); } // export google calendar events google spreadsheet, 1 row each guest // // code retrieves events between 2 dates specified calendar including guests included in event. // logs results in current spreadsheet starting @ cell a2 listing events, // dates/times, etc , calculates event duration (via creating formulas in spreadsheet) , formats values. // // reference websites: // https://developers.google.com/apps-script/reference/calendar/calendar // https://developers.google.com/apps-script/reference/calendar/calendar-event function getcal() { // export google calendar events google spreadsheet, 1 row each guest // // code retrieves events between 2 dates specified calendar. // logs results in current spreadsheet starting @ cell a2 listing events, // dates/times, etc , calculates event duration (via creating formulas in spreadsheet) , formats values. // // re-write spreadsheet header in row 1 every run, found faster delete entire sheet content, // change parameters, , re-run exports versus trying save header row manually...so sure if change // code, keep header in agreement readability! // // 1. please modify value mycal calendar email address or 1 visible on calendars section of google calendar // 2. please modify values events date/time range want , search parameters find or omit calendar entires // note: events can filtered out/deleted once exported calendar // // reference websites: // https://developers.google.com/apps-script/reference/calendar/calendar // https://developers.google.com/apps-script/reference/calendar/calendar-event var mycal = "youremailorcalendaraddress"; var cal = calendarapp.getcalendarbyid(mycal); //var startdate = browser.inputbox("start date, in format mm / dd / yyyy"); //var enddate = browser.inputbox("end date, in format mm / dd / yyyy"); //var startdate = "september 25, 2015 00:00:00 cst"; //var enddate = "september 26, 2015 23:59:59 cst"; // optional variations on getevents // var events = cal.getevents(new date("january 3, 2014 00:00:00 cst"), new date("january 14, 2014 23:59:59 cst")); // var events = cal.getevents(new date("january 3, 2014 00:00:00 cst"), new date("january 14, 2014 23:59:59 cst"), {search: 'word1'}); // // explanation of how search section works (as not quite things google) part of getevents function: // {search: 'word1'} search events word1 // {search: '-word1'} search events without word1 // {search: 'word1 word2'} search events word2 // {search: 'word1-word2'} search events ???? // {search: 'word1 -word2'} search events without word2 // {search: 'word1+word2'} search events word1 , word2 // {search: 'word1+-word2'} search events word1 , without word2 // //var events = cal.getevents(new date("september 25, 2015 00:00:00 cst"), new date("october 01, 2015 23:59:59 cst"), {search: '-project123'}); //var events = cal.getevents(new date("september 25, 2015 00:00:00 cst"), new date("october 02, 2015 23:59:59 cst")); //var events = cal.getevents(new date(startdate), new date(enddate)); var events = cal.getevents(new date("september 29, 2015 00:00:00 cst"), new date("september 29, 2015 23:59:59 cst")); var sheet = spreadsheetapp.getactivesheet(); // clear spreadsheet content before running sheet.clearcontents(); // create header record on current spreadsheet in cells a1:n1 - match number of entries in "header=" last parameter // of getrange entry below var header = [["calendar address", "event title", "event description", "event location", "event start", "event end", "calculated duration", "visibility", "date created", "last updated", "mystatus", "created by", "all day event", "recurring event", "id","email","status","name"]] var range = sheet.getrange(1,1,1,18); range.setvalues(header); // loop through calendar events found , write them out starting on row 2 (row = 2) allow header on row 1 var row = 2; (var i=0;i<events.length;i++) { var myformula_placeholder = ''; // matching "header=" entry above, detailed row entry "details=", , must match number of entries of getrange entry below var guestlist=events[i].getguestlist(); //get email , status of each guest each event for(var d=0; guestlist!=null && d<guestlist.length; d++) { var details=[[mycal,events[i].gettitle(), events[i].getdescription(), events[i].getlocation(), events[i].getstarttime(), events[i].getendtime(), myformula_placeholder, ('' + events[i].getvisibility()), events[i].getdatecreated(), events[i].getlastupdated(), events[i].getmystatus(), events[i].getcreators(), events[i].isalldayevent(), events[i].isrecurringevent(), events[i].getid(), guestlist[d].getemail(), guestlist[d].getgueststatus(), guestlist[d].getname()]]; logger.log(details); var range2 = sheet.getrange(row+d,1,1,18); range2.setvalues(details); var cell=sheet.getrange(row+d,7); // go column 7 (the placeholder) of output data cell.setformula('=(hour(f' +row+ ')+(minute(f' +row+ ')/60))-(hour(e' +row+ ')+(minute(e' +row+ ')/60))'); // calculate number of hours of session cell.setnumberformat('.00'); } row=row+d; // increment row start next output after previous output } } 
Comments
Post a Comment