Extraction of multiple calendars to Google sheet

Question

I'm having a hard time making a loop to extract more than one calendar to a google sheet. The calendars' IDs are in cell P1, the start date is in P7, and the end date is in P8.

The code works fine for one calendar, but when I try to add the j loop on more calendars, my extraction is empty. Can someone figure out what I'm doing wrong?

Thanks for your help.

Here is the code:


var sheet = SpreadsheetApp.getActiveSheet();

  // Set filters
var startDate = sheet.getRange('p7').getValue();
var endDate = sheet.getRange('p8').getValue();
var mycal = sheet.getRange('p1').getValue().toString(); 
var cal = CalendarApp.getCalendarById(mycal);

 

// 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 = sheet.getRange(1,1,1,14);
range.setValues(header);

var row=2

for (var j = 0; j< mycal.lengh; j++){
    //here we do the things we do once per calander
 var cal = CalendarApp.getCalendarById(mycal[j]);   
var events = cal.getEvents(startDate, endDate);

  
// 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=sheet.getRange(row,1,1,14);
        range.setValues(details);

      
      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');
      row++; 
           }
  }
}

@Cooper is "Can someone figure out what I'm doing wrong?" better ? — Julien, Jun 03 '22 at 18:35
mycal is undefined and lengh is not an attribute of an array. — Cooper, Jun 03 '22 at 20:57

Lorena Gomez · Accepted Answer · 2022-06-07T17:20:51.993

As @Cooper mentioned, lengh is not an attribute. I made that correction and other changes to the script. Also, I created another sheet where I have a list of calendar ID's, the startDate and endDate. See the script below, I added some comments to explain the changes I made. Make sure to change ranges based on where you have your data.

function calendarFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2"); //Sheet name where you will have the list of calendar ID's, startDate and endDate

  sheet.clear() // If you'll be runnning the script several times, the data will be duplicated so I added this line to clear the sheet before adding the data
  // other option would be to create a script to check if data already exists before adding it to the sheet

  // Set filters
  var startDate = sheet2.getRange('B1').getValue(); //Range for startDate
  var endDate = sheet2.getRange('B2').getValue(); //Range for endDate
  var users = sheet2.getRange('A1:A').getValues(); //Range where you have the calendar ID's

  // 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 = sheet.getRange(1,1,1,14);
  range.setValues(header);
  
  for (var j = 0; j< users.length; j++){
    //here we do the things we do once per calander
    if (users[j] == ""){
      break;
    }
    
    else{
      var cal = CalendarApp.getCalendarById(users[j]);
      var events = cal.getEvents(startDate, endDate);

      // Loop through all calendar events found and write them out starting on the next empty row
      for (var i=0;i<events.length;i++) {
        var myformula_placeholder = '';
        var details=[
          [users[j].toString(),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().toString(), events[i].isAllDayEvent(), events[i].isRecurringEvent()]
        ];

        var lastRow = sheet.getLastRow()+1;
        var range=sheet.getRange(lastRow,1,1,14);
        range.setValues(details);
          
        var cell=sheet.getRange(lastRow,7);
        cell.setFormula('=(HOUR(F' +lastRow+ ')+(MINUTE(F' +lastRow+ ')/60))-(HOUR(E' +lastRow+ ')+(MINUTE(E' +lastRow+ ')/60))');
        cell.setNumberFormat('.00');
      }
    }
  }
}

Thanks, @Lorena for this update, thanks also Cooper for your feedback, I'll try to understand the mistake I did. I was able to implement the code. It is working as expected. Is there a way to make the list if calendar ID dynamic? I'll add more calendars to the list in the future. Any time I have a new employee coming. Best, j. — Julien, Jun 05 '22 at 09:40
I see another problem with the script, It looks like if I run it twice, the next time it runs the lines are written on rows 926 and up. — Julien, Jun 05 '22 at 13:26
@Julien I have updated my answer, you can keep adding more calendar ID's in column A and it will work. Regarding lines written at the end of the sheet it was a problem with the `ARRAYFORMULA`, I have fixed that too. — Lorena Gomez, Jun 07 '22 at 17:17
Thanks, @Lorena this works exactly how I wanted! I have a time restriction problem when running it, probably because I extract a year and half of 10 different calendars. Do you know it there is a way to avoid this restriction? — Julien, Jun 10 '22 at 14:06
@Julien glad to know the script works! Regarding the time restriction, Apps Script have some [limitations](https://developers.google.com/apps-script/guides/services/quotas#current_limitations). For the 6 minute execution limit, there are some workarounds that could help, you can refer to these other answers [1](https://stackoverflow.com/questions/61017175/google-apps-script-exceeded-maximum-execution-time-issue/61057513#61057513), [2](https://stackoverflow.com/questions/41971806/how-can-i-increase-the-6-minute-execution-limit-in-google-apps-script) and try to apply it to this script. — Lorena Gomez, Jun 10 '22 at 22:53
I'm following the link number 2 and was able to add the Gasretry library ID 1EugqzsWbWssuYne0wNZvNgeXqFfD8dPRMZM3PnpaV3SsiW5qUANNxhXL but then I have a hard time to understand this part "Change the line var thingies = to whatever you want to process. It should ideally be an array." and run it for the CalendarFunction() – — Julien, Jun 11 '22 at 19:33
I have got this answer for the time limit as well. I thought you will like to see this https://stackoverflow.com/a/72629650/17709151 have a great day — Julien, Jun 15 '22 at 12:05

score 0 · Answer 2 · answered Jun 17 '22 at 18:05

Thanks to the help of this amazing community, I was able to have a script that run so much faster.

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Extraction 8 - Calendrier");
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Id Calendriers - Dates Debut et Fin"); //Sheet name where you will have the list of calendar ID's, startDate and endDate

  sheet.clear() // If you'll be runnning the script several times, the data will be duplicated so I added this line to clear the sheet before adding the data
  // other option would be to create a script to check if data already exists before adding it to the sheet

  // Set filters
  var startDate = sheet2.getRange('k15').getValue(); //Range for startDate
  var endDate = sheet2.getRange('k16').getValue(); //Range for endDate
  var users = sheet2.getRange('b3:B').getValues(); //Range where you have the calendar ID's

  // 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
  const data = []
  const formulas = [];
  const headers = [["Titre", "Description", "Location", "Début", "Fin", "Heures effectives","Extraction 2","Extraction 3","Heures Planifiées", "Vacances", "Maladie","Congé légal", "Absence"]]

  
  for (var j = 0; j< users.length; j++){
    //here we do the things we do once per calander
    if (users[j] == ""){
      break;
    }
    
    else{
      var cal = CalendarApp.getCalendarById(users[j]);
      var events = cal.getEvents(startDate, endDate);
      var lastRow = sheet.getLastRow()+8;

      // Loop through all calendar events found and write them out starting on the next empty row
      for (var i = 0; i < events.length; i++) {
        var details=
        [
          events[i].getTitle(), 
          events[i].getDescription(), 
          events[i].getLocation(), 
          events[i].getStartTime(), 
          events[i].getEndTime()
        ];

        data.push(details);
        const rowFormulas = 
        [
          '=(HOUR(RIGHT(b' +lastRow+';5))+(MINUTE(RIGHT(b' +lastRow+ ';5))/60))-(HOUR(LEFT(b' +lastRow+ ';5))+(MINUTE(LEFT(b' +lastRow+ ';5))/60))',
          '=IFERROR(TEXT(INDEX(SPLIT(A'+lastRow+';" ");2);"hh:mm");"")',
          '=IFERROR(TEXT(INDEX(SPLIT(A'+lastRow+';" ");3);"hh:mm");"")',
          '=IF(OR(G'+lastRow+'="Maladie";G'+lastRow+'="Congé";G'+lastRow+'="Absence";G'+lastRow+'="00:00";G'+lastRow+'="Vacances");0;(HOUR(H'+lastRow+')+(MINUTE(H'+lastRow+')/60))-(HOUR(G'+lastRow+')+(MINUTE(G'+lastRow+')/60)))',
          '=IF(IFNA(VLOOKUP(D'+lastRow+'; feries;1;FALSE);1)<>1;0;IF(AND(G'+lastRow+'="00:00";H'+lastRow+'="Vacances");0.5;IF(G'+lastRow+'="Vacances";1;0)))',
          '=IF(G'+lastRow+'="Maladie";1;0)',
          '=IF(G'+lastRow+'="Congé";1;0)',
          '=IF(G'+lastRow+'="Absence";1;0)'
        ]
        
        formulas.push(rowFormulas)
        lastRow=lastRow+1
      }
    }
  }

  sheet.getRange(7,1,headers.length, headers[0].length).setValues(headers)
  sheet.getRange(8,1,data.length,data[0].length).setValues(data);
  sheet.getRange(8,data[0].length + 1,formulas.length, formulas[0].length).setFormulas(formulas);
  sheet.getRange(8,6,sheet.getLastRow()).setNumberFormat('.00');

}```

Extraction of multiple calendars to Google sheet

2 Answers2