Wsrtjtyk

I have been trying to create a working script to extract events from different people calendar. Access is available to read the events.

I have the IDs in a different sheet("Cals2Extract") in the range A2: B4 Column Header A(Cal_ID) and B(Active).

While I'm able to extract events from the first name in the column A2, I'm unable to iterate through the second and third name in column A (A3, A4....).

Extracted events are written in a different sheet (EventCal).

Sample Sheet

Any help is appreciated.

function Get(){



var ss=SpreadsheetApp.openById("ID of the sheet")

var sheet=ss.getSheetByName("Cals2Extract");

var sheetArr=sheet.getDataRange().getValues();

var range = sheet.getRange(1, 1, 1, sheet.getMaxColumns());

var values = range.getValues();

var ColIndexmap = {};

var mycal="";

var cal="";



Logger.log(sheet);



Logger.log (sheetArr);



for (var row in values) {

  for (var col in values[row]) {

  ColIndexmap[values[row][col]]=parseInt(col);

    }}



for(var i=1;i<sheetArr.length;i++){

mycal ="";

cal ="";



Logger.log(sheetArr.length);



Logger.log(i);



mycal=sheetArr[i][ColIndexmap["Cal_ID"]];



Logger.log(mycal);



cal = CalendarApp.getCalendarById(mycal);



Logger.log(cal);



if(sheetArr[i][ColIndexmap["Active"]] != ""){





var events = cal.getEvents(new Date("November 1, 2018 00:00:00 CST"), new 

Date("November 14, 2018 23:59:59 CST"), {search: ''});





var sheet1 = ss.getSheetByName("EventCal");



sheet1.clearContents();  



Logger.log(sheet1);

// Create a header record on the current spreadsheet in cells A1:N1 - Match the number of entries in the "header=" to the last parameter
// of the 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"]]

var range = sheet1.getRange(1,1,1,14);

range.setValues(header);

// Loop through all calendar events found and write them out starting on calulated ROW 2 (i+2)

for (var i=0;i<events.length;i++) {

var row=i+2;

var myformula_placeholder = '';



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()]];

var range=sheet1.getRange(row,1,1,14);

range.setValues(details);



var cell=sheet1.getRange(row,7);

cell.setFormula('=(HOUR(F' +row+ ')+(MINUTE(F' +row+ ')/60))-(HOUR(E' +row+ 

')+(MINUTE(E' +row+ ')/60))');

cell.setNumberFormat('.00');



     }

   }

  } 

SpreadsheetApp.flush();

}