title: Date picker for Calender to Sheets Script
body: <p>Below is a script I found and modified to track events by grabbing event information from our Calendar(s) and populating a spreadsheet (with mild reformatting)  based on a selected date range. </p>
  
  <p>Currently I have to manually change the dates within the script each week to get the events that I need.   I would like to have a date picker pop up when I open a new sheet  and have the selected dates input to the script,  but I haven't been able to figure it out yet.   Any help would be greatly appreciated. </p>
  
  <pre><code>function main(){
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.clearContents();
    var header = [["Calendar Address", "Calendar Name", "Event Title", "Event Location", "Event Start", "Event Description" ]]
    var range = sheet.getRange(1,1,1,6);
    range.setValues(header);
    sheet.setFrozenRows(1);
  // var mycals is an array of google calendar id's; for gsuite, this is the email address of the user which calendar you will be viewing.
    var mycals = ['9ss30f8ktuvbedmemka1ve8jss@group.calendar.google.com','8nmutqctujaetrnt5kfvi56kuo@group.calendar.google.com','ae1d2v0sqtktc183546u6cenps@group.calendar.google.com','ilr8eqk2l0ua9pr50t5307ivq8@group.calendar.google.com','5qubvv7mj5vjf7h1hhc23515h8@group.calendar.google.com', 'p2a9plkjd3vv9c87kb5kirapqk@group.calendar.google.com', '9ubfnjrb83tiqgi5l1nitaodp0@group.calendar.google.com', 'l6ouqfpktn05hstmokj8jhr1s4@group.calendar.google.com'  ]
  
  
  for (var j=0;j&lt;mycals.length;j++) {
    var cal = CalendarApp.getCalendarById(mycals[j]);
    // error handling in case you don't have access to any specified calendar
    if (!cal) { continue; }
    var events = cal.getEvents(new Date("12/30/2019 00:00:00 EST"), new Date("1/3/2020 23:59:59 EST"));
          for (var i=0;i&lt;events.length;i++) {
            var row = sheet.getLastRow() + 1
            var details=[[events[i].getOriginalCalendarId(),,events[i].getTitle(), events[i].getLocation(), events[i].getStartTime(), events[i].getDescription()]];
            var range=sheet.getRange(row,1,1,6);
            range.setValues(details);
            sheet.getRange(i+2, 5).setNumberFormat("mm/dd/yyyy h:mm Am/PM");
          }
  sheet.sort(1);
  }
  var lr= sheet.getLastRow();
  
    for(var k=0+1; k&lt;lr+1;k++){
  
      var workingCell = sheet.getRange(k,1).getValue();
  
        if(workingCell == '9ss30f8ktuvbedmemka1ve8jss@group.calendar.google.com'){
          sheet.getRange(k,2).setValue("ATG_Cert");
        }
         else if(workingCell == '8nmutqctujaetrnt5kfvi56kuo@group.calendar.google.com'){
          sheet.getRange(k,2).setValue("Functionality_Test");
           }
         else if(workingCell == 'ae1d2v0sqtktc183546u6cenps@group.calendar.google.com'){
          sheet.getRange(k,2).setValue("Tightness_Test");
           }
         else if(workingCell == 'ilr8eqk2l0ua9pr50t5307ivq8@group.calendar.google.com'){
          sheet.getRange(k,2).setValue("Pre_or_Post_Bury");
            }
         else if(workingCell == '5qubvv7mj5vjf7h1hhc23515h8@group.calendar.google.com'){
          sheet.getRange(k,2).setValue("LLD");
           }
         else if(workingCell == 'p2a9plkjd3vv9c87kb5kirapqk@group.calendar.google.com'){
          sheet.getRange(k,2).setValue("PMI");
           }
         else if(workingCell == '9ubfnjrb83tiqgi5l1nitaodp0@group.calendar.google.com'){
          sheet.getRange(k,2).setValue("Stage_II_DeComm_Test");
           }
         else if(workingCell == 'l6ouqfpktn05hstmokj8jhr1s4@group.calendar.google.com'){
          sheet.getRange(k,2).setValue("Other");
  
    }
  }
    sheet.getRange(1,1,lr,6).setFontSize(14)  
    sheet.hideColumns(1);
    sheet.autoResizeColumns(1, 6);
    sheet.getRange(1, 1, lr, 6).setHorizontalAlignment("left")
  
  
  
  }
  
  </code></pre>