Log Events From Google Calendar

  • Create a new sheet
  • Pre-populate a sheet with text
  • Add data validation (drop-down list)
  • Manual and automatic column/row size adjustments
  • Add number formatting to a specific range
  • Create colored and weighted cell borders
  • Format text and cells (color, font weight, alignment, etc.)
  • Insert formulas
  • And More!

Create a New Sheet with Formatted Text and Cells

function createNewSheet() {
  //Step 1 - Activate spreadsheet
  const spreadsheet = SpreadsheetApp.getActive();

  //Step 1 (Optional) - Create new sheet 
  const newSheet = spreadsheet.insertSheet("Calendar Event Log");

  //Step 2 - Declare variables for each cell
  const calendarID = newSheet.getRange('a1').setValue("Calendar ID:");
  const numOfDaysOut = newSheet.getRange('a2').setValue("Number of days out:");
  const customFormulaCell = newSheet.getRange('a3').setValue('=if(B2="Custom", "Enter custom days out:", "")');
  const eventNameCell = newSheet.getRange('d1').setValue("Event Name");
  const startTimeCell = newSheet.getRange('e1').setValue("Start Time");
  const endTimeCell = newSheet.getRange('f1').setValue("End Time");
  const colorCell = newSheet.getRange('g1').setValue("Color");
  const tagKeysCell = newSheet.getRange('h1').setValue("Tag Keys");
  const creatorsCell = newSheet.getRange('i1').setValue("Creators");
  const dateCreatedCell = newSheet.getRange('j1').setValue("Date Created");
  const descriptionCell = newSheet.getRange('k1').setValue("Description");
  const emailRemindersCell = newSheet.getRange('l1').setValue("Email Reminders (Minutes)");
  const eventSeriesCell = newSheet.getRange('m1').setValue("Event Series");
  const guestListCell = newSheet.getRange('n1').setValue("Guest List");
  const eventIDCell = newSheet.getRange('o1').setValue("Event ID");
  const lastUpdatedCell = newSheet.getRange('p1').setValue("Last Updated");
  const locationCell = newSheet.getRange('q1').setValue("Location");
  const popupRemindersCell = newSheet.getRange('r1').setValue("Popup Reminders (Minutes)");
  const smsRemindersCell = newSheet.getRange('s1').setValue("SMS Reminders (Minutes)");

  //Step 3 - Format cells
  //Data validation dropdown list
      .requireValueInList(['1 day', '7 days', '30 days', '60 days', '90 days', '180 days', '365 days', 'Custom'], true)

  //Date format
  spreadsheet.getRangeList(['E2:f', 'j1:j', 'p1:p']).activate();
  spreadsheet.getActiveRangeList().setNumberFormat('M/d/yyyy H:mm:ss');

  //Bold text
  spreadsheet.getRangeList(['A1:A3', 'D1:s1']).activate();

  //Center text alignment

  //Manual column width adjustment

  //Fit to data column width adjustment
  spreadsheet.getActiveSheet().autoResizeColumns(1, 1);

  //Set text font color


  //Creates colored cell border with solid, medium border weight
  spreadsheet.getActiveRangeList().setBorder(true, true, true, true, null, null, '#0000ff', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

  spreadsheet.getActiveRangeList().setBorder(true, true, true, true, null, null, '#ff0000', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

Create a Custom Menu

//Creates a custom menu with submenu items upon opening the spreadsheet

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')

    //First menu item ('Name of button', 'functionToRun')
    .addItem('Run', 'createNewSheet')

    //Adds a horizontal line to the menu (optional)

    //If you want to add sub-menu (optional):      
      .addItem('Second item', 'menuItem2'))

    //This is the menu button for the rest of the script. More on that later. (Second item optional)
    .addItem('Sync', 'logUserEvents')

//This function returns the variable daysOut which contains the number of days out to search the calendar
function getDaysOut() {

  //Activate spreadsheet
  const spreadsheet = SpreadsheetApp.getActive();
  const calendarSheet = spreadsheet.getSheetByName("Calendar Event Log");

  //User inputs
  const calendarID = calendarSheet.getRange("B1");
  const daysSelection = calendarSheet.getRange("B2");
  const customDays = calendarSheet.getRange("B3");

  //Declare daysOut variable
  let daysOut;

  //Assign daysOut to a value. Each of these correspond to the data validation dropdown list items
  if (daysSelection.getValue() == "1 day") {

    daysOut = 1;

  } else if (daysSelection.getValue() == "7 days") {

    daysOut = 7;

  } else if (daysSelection.getValue() == "30 days") {

    daysOut = 30;

  } else if (daysSelection.getValue() == "60 days") {

    daysOut = 60;

  } else if (daysSelection.getValue() == "90 days") {

    daysOut = 90;

  } else if (daysSelection.getValue() == "180 days") {

    daysOut = 180;

  } else if (daysSelection.getValue() == "365 days") {

    daysOut = 365;

  } else if (daysSelection.getValue() == "Custom") {

    daysOut = customDays.getValue();

  return daysOut;

//This function returns the date that is the number of days from today as determined by the getDaysOut() function above.
function daysFromNow() {

  //Assign value returned from getDaysOut() to a variable
  const daysOut = getDaysOut();

  //Create variable with the time at any given moment
  const nowTime = new Date();

  //Add the number of days out (in milliseconds) to nowTime
  const daysFromNow = new Date(nowTime.getTime() + (daysOut * 24 * 60 * 60 * 1000));

  //This will convert your date to a specific time zone, in this case America/Chicago (CST). Without doing this, the timezone defaults to the timezone of the spreadsheet.
  const daysFromNowCentralTZ = Utilities.formatDate(daysFromNow, 'America/Chicago', 'MMMM dd, yyyy HH:mm:ss Z');

  //If you want to use a specific timezone:
  //return daysFromNowCentralTZ

  //I chose to keep the default for now so that I don't have to convert each date. If I wait until after all of my date calculations to change timezones, I can just convert it once.
  return daysFromNow