JW-GAS

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

Code Snippet

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
  spreadsheet.getRange('B2').setDataValidation(
    SpreadsheetApp
      .newDataValidation()
      .setAllowInvalid(true)
      .requireValueInList(['1 day', '7 days', '30 days', '60 days', '90 days', '180 days', '365 days', 'Custom'], true)
      .build()
  );

  //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();
  spreadsheet.getActiveRangeList().setFontWeight('bold');

  //Center text alignment
  spreadsheet.getRange('D1:s1').activate();
  spreadsheet.getActiveRangeList().setHorizontalAlignment('center');

  //Manual column width adjustment
  spreadsheet.getRange('D:s').activate();
  spreadsheet.getActiveSheet().setColumnWidths(4,16,180);

  //Fit to data column width adjustment
  spreadsheet.getRange('A:A').activate();
  spreadsheet.getActiveSheet().autoResizeColumns(1, 1);

  //Set text font color
  spreadsheet.getRange('A1').activate();
  spreadsheet.getActiveRangeList().setFontColor('#0000ff');

  spreadsheet.getRange('A3').activate();
  spreadsheet.getActiveRangeList().setFontColor('#ff0000');

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

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

Create a Custom Menu

Code Snippet

//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)
    .addSeparator()

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

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

//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
}