https://www.pbainbridge.co.uk/2021/02/bulk-create-google-calendar-events-with.html

Bulk create Google Calendar events with optional Google Meet.gs

/*
A number of Global Variables to be accessed throughout the scripts to
reduce effort in updating in future.
*/

// Event ID column number
var eventIDCol = 1;

// Status column number
var statusCol = 2;


/*
Create Menu item for resetting the tool.
Get Google Sheet ID and add to Config Sheet.
Create the 'Authorise your account' hyperlink on the Welcome Sheet.
*/

function onOpen() {

  // add a custom menu to the spreadsheet
  SpreadsheetApp.getUi()
    .createMenu('Reset')
    .addItem('Reset spreadsheet', 'reset') // label for menu item, name of function to run.
    .addToUi();

}


/*
Function to check the provided Calendar ID can be accessed.
Creates and deletes a quick test Event.
Calls main 'master' Function.
*/

function checkCalendarID() {

  // run Function to log message
  logEvent('Script started');

  toastPopup("Preparing to start ...", "Initialising");

  // get Welcome Sheet
  var welcomeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Welcome');

  // get Calendar ID and trim any white-spaces. Log in spreadsheet
  var calendarID = welcomeSheet.getRange(6, 2).getValue().trim();
  logEvent("Calendar ID is: " + calendarID);

  // check if Calendar ID cell is blank
  if (calendarID == "") {
    // Calendar ID is blank. Run Function to display popup
    displayPopup("Blank Calendar ID", "Please check the Welcome sheet.")
  }
  else {
    // try accessing the Calendar to confirm ID works
    try {
      // get the Calendar
      var calendar = CalendarApp.getCalendarById(calendarID);

      // create a test event to confirm have sufficient privileges
      var event = calendar.createEvent('Create event test',
        new Date('January 10, 2021 20:00:00 UTC'),
        new Date('January 10, 2021 21:00:00 UTC')
      );

      // get the ID of the new event so we can delete it after creation
      var eventID = event.getId();

      // delete the new event
      calendar.getEventById(eventID).deleteEvent();

      // log success with getting Calendar
      logEvent('Successfully got Calendar and created/deleted test event');


      // set Flag to true for rest of script to proceed
      var calendarFlag = true;
    }
    catch (e) {
      // problem accessing Calendar via given ID
      logEvent("Problem with given Calendar ID: " + e);

      // set Flag to false to stop rest of script
      var calendarFlag = false;

      // unable to access Calendar correctly. Run Function to display popup
      displayPopup("Unable to access the Calendar", "Please check the ID on the Welcome sheet and that " +
        "you have sufficient access to create events on this Calendar");
    }

    // run Function to proceed with rest of script
    if (calendarFlag) {
      // proceed with main Function
      master(calendarID);
    }
    else {
      // stop, do not continue any furter
    }

  }

}


/*
Master Function to run - will call other relevant Functions as required.
*/

function master(calendarID) {

  try {
    // create empty 'error' var for capturing any error messages
    var error = '';

    // run Function to get spreadsheet data
    var spreadsheetData = getSpreadsheetData();

    // get length of returned array to check there is data
    var spreadsheetDataLength = spreadsheetData.length;

    // check there is data in the spreadsheet before proceeding *********
    if (spreadsheetDataLength > 0) {

      // run Function to log message
      logEvent("There is data in the spreadsheet");


      // create counter variable for number of successful events created
      var eventCounter = 0;

      // loop through each row of data ***********************************
      for (var row = 0; row < spreadsheetDataLength; row++) {

        // check no existing Event ID or Status in sheet before proceeding
        var eventID = spreadsheetData[row][eventIDCol - 1];
        var status = spreadsheetData[row][statusCol - 1];
        if ((eventID == '') && (status == '')) {


          // run Function to check for missing data which will prevent event creation
          var checkDataReturn = checkData(spreadsheetData, row);
          if (checkDataReturn) {

            // run Function to create event and return its ID
            eventID = createEvent(calendarID, checkDataReturn, row);
            if (eventID) {
              // event successfully created

              // increment event creation counter by '1'
              eventCounter++;
            }
            else {
              // problem creating event so break out of loop
              // update error variable
              error = 'Script completed with error';
              break;
            }

          }
          else {
            // there is missing data so break out of loop
            // update error variable
            error = 'Script completed with error';
            break;
          }


        }
        else {
          // Event ID or Status for row is not empty. Ignore this row.
          logEvent("Event ID or Status for row " + (row + 3) + " is not empty");
        }


      }
      // loop through each row of data ***********************************


    }
    else {
      // no data in spreadsheet
      // update error variable
      error = 'Script completed with error';
    }
    // check there is data in the spreadsheet before proceeding *********


    // determine status of 'error' variable to output correct end status
    if (error != '') {
      // run Function to log message
      logEvent(error);
    }
    else {
      // no error occurred

      // check value of event creation counter to determine popup message
      if (eventCounter == 0) {
        // no new events were created
        var popupMessage = "<p>No new events were created.</p>";
      }
      else if (eventCounter > 0) {
        // new events were created
        var popupMessage = eventCounter + " new events were created.</p>";
      }
      // run Function to launch HTML popup to inform user of success
      var popupTitle = "Completed";
      htmlPopup(popupTitle, popupMessage);

      // log message
      logEvent('Script completed');
    }
  }
  catch (e) {
    // run Function to log message
    logEvent("''master' Function error: " + e);

    // run Function to launch HTML popup
    var popupTitle = "'master' Function error";
    var popupMessage = "<p>Problem with 'master' Function: </p>" + e;
    htmlPopup(popupTitle, popupMessage);
  }

}


/*
Function to gather all data from Google Sheet.
*/


function getSpreadsheetData() {

  try {
    // get spreadsheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();

    // get Events sheet
    var eventsSheet = ss.getSheetByName('Events');


    // run Function to get last row (minus 2 for Header rows)
    var eventsSheetLastRow = lastRow(eventsSheet) - 2;
    // run Function to log message
    logEvent("Function 'eventsSheetLastRow' returned: " + eventsSheetLastRow);

    // check value of number of rows
    if (eventsSheetLastRow == 0) {
      // problem, so display message to user and terminate Function

      // run Function to display popup box
      var popupTitle = "No rows of data";
      var popupMessage = "The Events sheet does not contain any data. Please correct.";
      displayPopup(popupTitle, popupMessage);

      // return nothing so Parent Function does not proceed
      return '';
    }
    else {
      // no problem so continue

      // get last column
      var eventsSheetLastCol = eventsSheet.getLastColumn();

      // get all event data
      var spreadsheetData = eventsSheet.getRange(3, 1, eventsSheetLastRow, eventsSheetLastCol).getValues();

      // return data to Parent Function
      return spreadsheetData;
    }
  }
  catch (e) {
    // run Function to log message
    logEvent("''getSpreadsheetData' Function error: " + e);

    // run Function to launch HTML popup
    var popupTitle = "'getSpreadsheetData' Function error";
    var popupMessage = "<p>Problem with 'getSpreadsheetData' Function: </p>" + e;
    htmlPopup(popupTitle, popupMessage);

    // return nothing so Parent Function does not proceed
    return '';
  }

}


/*
Function to find the last row of a given column (range).
Required as the tickboxes do not allow the usual 'getLastRow()' method to work.
This looks down a given column and stops at the last row of data.
*/


function lastRow(sheetName) {

  try {
    // get all data within column
    var columnToCheck = sheetName.getRange('C:C').getValues();

    // run Function to calculate last row of data
    var lastRowNumber = getLastRowSpecial(columnToCheck);

    // return row number to Parent Function
    return lastRowNumber;

    // Function to get the last row based on the data range of a single column.
    function getLastRowSpecial(columnToCheck) {

      // set initial variable values before proceeding
      var rowNum = 0;
      var blank = false;

      // loop through each row ******************************
      for (var row = 0; row < columnToCheck.length; row++) {

        // check if row item is blank and that 'blank' variable is not false (so true)
        if ((columnToCheck[row][0] == "") && (!blank)) {
          // condition is true to get row number and set 'blank' to true
          rowNum = row;
          blank = true;
        }
        else if (columnToCheck[row][0] != "") {
          // condition is not true as value in cell so set 'blank' to false
          blank = false;
        };

      }
      // loop through each row ******************************


      // return row number to Parent Function
      return rowNum;

    }
  }
  catch (e) {
    // run Function to log message
    logEvent("''lastRow' Function error: " + e);
  }


}


/*
Function to check through row of data looking for missing values which would
prevent Event creation.

Ignores optional values:
> Event Description
> Google Meet
> Guests
> Location
> Send Guest Emails
> Guests Invite Others
> Guests Can Modify
> Guests Can See Others
> Show Me As
*/


function checkData(spreadsheetData, row) {

  try {

    // get TimeZone from spreadsheet
    var timeZone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();

    // extract values from 2-D array
    var summary = spreadsheetData[row][2];
    var description = spreadsheetData[row][3];
    var startDate = spreadsheetData[row][4];
    var startHour = spreadsheetData[row][5];
    var startMin = spreadsheetData[row][6];
    var endDate = spreadsheetData[row][7];
    var endHour = spreadsheetData[row][8];
    var endMin = spreadsheetData[row][9];
    var meetTickbox = spreadsheetData[row][10];
    var guests = spreadsheetData[row][11];
    var location = spreadsheetData[row][12];
    var guestEmails = spreadsheetData[row][13];
    var guestsInviteOthers = spreadsheetData[row][14];
    var guestsCanModify = spreadsheetData[row][15];
    var guestsSeeOthers = spreadsheetData[row][16];
    var showMeAs = spreadsheetData[row][17];
    var visibility = spreadsheetData[row][18];

    // check for any blank values ********************************
    if (summary == '') {
      // run Function to launch HTML popup
      var popupTitle = "Row " + (row + 3);
      var popupMessage = "<p>Event not created: 'Event Title' is empty. Please fix and try again.</p>";
      htmlPopup(popupTitle, popupMessage);

      logEvent("''Event Title' is empty in row " + (row + 3));
      // return false to signify missing data
      return false;
    }
    else if (startDate == '') {
      // run Function to launch HTML popup
      var popupTitle = "Row " + (row + 3);
      var popupMessage = "<p>Event not created: 'Start Date' is empty. Please fix and try again.</p>";
      htmlPopup(popupTitle, popupMessage);

      logEvent("''Start Date' is empty in row " + (row + 3));
      // return false to signify missing data
      return false;
    }
    else if (startHour == '') {
      // run Function to launch HTML popup
      var popupTitle = "Row " + (row + 3);
      var popupMessage = "<p>Event not created: 'Start Hour' is empty. Please fix and try again.</p>";
      htmlPopup(popupTitle, popupMessage);

      logEvent("''Start Hour' is empty in row " + (row + 3));
      // return false to signify missing data
      return false;
    }
    else if (startMin == '') {
      // run Function to launch HTML popup
      var popupTitle = "Row " + (row + 3);
      var popupMessage = "<p>Event not created: 'Start Minutes' is empty. Please fix and try again.</p>";
      htmlPopup(popupTitle, popupMessage);

      logEvent("''Start Minutes' is empty in row " + (row + 3));
      // return false to signify missing data
      return false;
    }
    else if (endDate == '') {
      // run Function to launch HTML popup
      var popupTitle = "Row " + (row + 3);
      var popupMessage = "<p>Event not created: 'End Date' is empty. Please fix and try again.</p>";
      htmlPopup(popupTitle, popupMessage);

      logEvent("''End Date' is empty in row " + (row + 3));
      // return false to signify missing data
      return false;
    }
    else if (endHour == '') {
      // run Function to launch HTML popup
      var popupTitle = "Row " + (row + 3);
      var popupMessage = "<p>Event not created: 'End Hour' is empty. Please fix and try again.</p>";
      htmlPopup(popupTitle, popupMessage);

      logEvent("''End Hour' is empty in row " + (row + 3));
      // return false to signify missing data
      return false;
    }
    else if (endMin == '') {
      // run Function to launch HTML popup
      var popupTitle = "Row " + (row + 3);
      var popupMessage = "<p>Event not created: 'End Minutes' is empty. Please fix and try again.</p>";
      htmlPopup(popupTitle, popupMessage);

      logEvent("''End Minutes' is empty in row " + (row + 3));
      // return false to signify missing data
      return false;
    }
    else {
      // return a name:value pair array of spreadsheet data so easier to work with
      var cleanSpreadsheetData = {
        timeZone: timeZone, summary: summary, description: description, startDate: startDate, startHour: startHour, startMin: startMin, endDate: endDate,
        endHour: endHour, endMin: endMin, meetTickbox: meetTickbox, guests: guests, location: location, guestEmails: guestEmails, guestsInviteOthers: guestsInviteOthers, guestsCanModify: guestsCanModify, guestsSeeOthers: guestsSeeOthers,
        showMeAs: showMeAs, visibility: visibility
      };

      return cleanSpreadsheetData;
    }

  }
  catch (e) {
    // run Function to log message
    logEvent("'checkData' Function error: " + e);

    // run Function to launch HTML popup
    var popupTitle = "'checkData' Function error";
    var popupMessage = "<p>Problem with 'checkData' Function: </p>" + e;
    htmlPopup(popupTitle, popupMessage);

    // return false so Parent Function does not proceed
    return false;
  }

}


/*
Function to create the Google Calendar Event for the given row.
Will create a Google Meet or call to create a Zoom Meeting if selected.
*/


function createEvent(calendarID, checkDataReturn, row) {

  try {
    // run Function to log message
    logEvent("Creating Event for row: " + (row + 3));

    // create title for the event
    var summary = checkDataReturn['summary'];

    // create event description
    var description = checkDataReturn['description'];

    //create event location from spreadsheet data.
    var location = checkDataReturn['location'];

    // create start/end date/time for the event *********
    var timeZone = checkDataReturn['timeZone'];

    // extract relevant parts of start date/time to create 'ISO 8601 Extended format' compatible
    var startDate = checkDataReturn['startDate'];
    var formattedStartDate = Utilities.formatDate(startDate, timeZone, "yyyy-MM-dd");

    var startHour = checkDataReturn['startHour'].trim();
    var startMin = checkDataReturn['startMin'].trim();

    // recombine above parts to created suitably formatted date
    var fixedStartDate = formattedStartDate + 'T' + startHour + ':' + startMin + ':' + '00' + '.000';
    // run Function to log value


    // extract relevant parts of end date/time to create 'ISO 8601 Extended format' compatible
    var endDate = checkDataReturn['endDate'];
    var formattedEndDate = Utilities.formatDate(endDate, timeZone, "yyyy-MM-dd");

    var endHour = checkDataReturn['endHour'].trim();
    var endMin = checkDataReturn['endMin'].trim();

    // recombine above parts to created suitably formatted date
    var fixedEndDate = formattedEndDate + 'T' + endHour + ':' + endMin + ':' + '00' + '.000';
    // run Function to log value


    var start = {
      dateTime: fixedStartDate,
      timeZone: timeZone
    };
    var end = {
      dateTime: fixedEndDate,
      timeZone: timeZone
    };
    // create start/end date/time for the event *********


    // get data on attendees (guests) from spreadsheet
    var guests = checkDataReturn['guests'];
    if (guests) {
      // run Function to sort attendees (guests)
      var attendees = sortAttendees(calendarID, guests, row);

      // check if sorting attendees was successful before proceeding
      if (!attendees) {
        // 'attendees' is false, so problem, return 'false' flag to stop script proceeding
        return false;
      }
      else {
        // attendees were successfully sorted, no action required, script continues
      }

    }
    else {
      // there are no attendees (guests) in spreadsheet data
    }


    // get value for sending invite emails to guests - 'all' or 'none'
    var sendUpdates = checkDataReturn['guestEmails'];
    // translate value to terminology used by API rather than friendly terms used in sheet
    if (sendUpdates == 'yes') {
      sendUpdates = 'all';
    }
    else {
      sendUpdates = 'none';
    }

    // get value for guests inviting others
    var guestsCanInviteOthers = checkDataReturn['guestsInviteOthers'];
    // translate value to terminology used by API rather than friendly terms used in sheet
    if (guestsCanInviteOthers == 'no') {
      guestsCanInviteOthers = false;
    }
    else {
      guestsCanInviteOthers = true;
    }

    // get value for guests can modify event
    var guestsCanModify = checkDataReturn['guestsCanModify'];
    // translate value to terminology used by API rather than friendly terms used in sheet
    if (guestsCanModify == 'no') {
      guestsCanModify = false;
    }
    else {
      guestsCanModify = true;
    }

    // get value for guests seeing others
    var guestsCanSeeOtherGuests = checkDataReturn['guestsSeeOthers'];
    // translate value to terminology used by API rather than friendly terms used in sheet
    if (guestsCanSeeOtherGuests == 'no') {
      guestsCanSeeOtherGuests = false;
    }
    else {
      guestsCanSeeOtherGuests = true;
    }

    // get value for Show Me As
    var transparency = checkDataReturn['showMeAs'];
    // translate value to terminology used by API rather than friendly terms used in sheet
    if (transparency == "free") {
      transparency = "transparent";
    }
    else {
      // any other value should be set to this default
      transparency = "opaque";
    }

    // get value for event visibility
    var visibility = checkDataReturn['visibility'];
    // check if value blank and set to 'default' if so, as API requires a value
    if (visibility == '') {
      visibility = 'default';
    }
    else {
      // do nothing as value is not blank and hence will be accepted by API
    }


    // get Google Meet tickbox status and add conferencing if ticked
    var meetTickbox = checkDataReturn['meetTickbox'];
    if (meetTickbox) {

      // generate a random string to use for requestId
      var uniqueValue = Utilities.getUuid();
      var requestId = uniqueValue.slice(0, 5); // get first 5 characters

      // create conferencing information
      var conferenceData = {
        createRequest: {
          requestId: requestId,
          conferenceSolutionKey: {
            type: "hangoutsMeet"
          },
        }
      };
    }
    else {
      // Google Meet tickbox not selected so conferencing not required
    }


    // compile relevant options for the event
    var resource = {
      summary,
      description,
      start,
      end,
      conferenceData,
      attendees,
      location,
      guestsCanInviteOthers,
      guestsCanModify,
      guestsCanSeeOtherGuests,
      transparency,
      visibility
    };


    // create arguments to allow event modifications: conferencing, email guests
    var args = {
      conferenceDataVersion: 1,
      sendUpdates
    };
  }
  catch (e) {
    // problem sorting event details from spreadsheet, return false value
    logEvent("Problem sorting details from spreadsheet for row " + (row + 3) + ":" + e);

    // run Function to launch HTML popup
    var popupTitle = "Event not created: Row " + (row + 3);
    var popupMessage = "<p>Problem with 'createEvent' Function: </p>" + e;
    htmlPopup(popupTitle, popupMessage);

    // return 'false' flag for fail
    return false;
  }


  try {
    // make call to Calendar API to create event
    var request = Calendar.Events.insert(resource, calendarID, args);

    // capture event ID and run Function to toast
    var eventID = request.id;
    toastPopup("'" + summary + "'", 'Created Event Row ' + (row + 3));

    // capture event HTML link
    var eventHTML = request.htmlLink;

    // run Function and paste event ID into spreadsheet as clickable link
    var eventHTMLLink = '=HYPERLINK("' + eventHTML + '","' + eventID + '")';
    pasteIntoSheet((row + 3), eventIDCol, eventHTMLLink, '#000000');

    // run Function and update Status column in spreadsheet
    pasteIntoSheet((row + 3), statusCol, 'Completed', '#11c000');

    // return 'true' flag for success
    return true;
  }
  catch (e) {
    // problem creating event with Calendar API, return false value
    logEvent("Problem with 'Calendar.Events.insert' for row " + (row + 3) + ":" + e);

    // run Function to launch HTML popup
    var popupTitle = "Event not created: Row " + (row + 3);
    var popupMessage = "<p>Problem with 'createEvent' Function API call: </p>" + e;
    htmlPopup(popupTitle, popupMessage);

    // return 'false' flag for fail
    return false;
  }

}


/*
Function to sort through comma-separated list of guest email addresses.
Splits via a comma and space.
Then pushes each email address into an array in correct format.
Also adds the Calendar creating the Event as a Guest and sets
response-status to 'accepted' so they are attending.
*/


function sortAttendees(calendarID, guests, row) {

  try {
    // get array from comma-separated email addresses
    var emailAddresses = guests.split(', ');

    // create empty array to populate with email addresses
    var emailAddressArray = [];

    // get length of emailAddresses array for loop
    var emailAddressesLength = emailAddresses.length;

    // loop through each email address and push into array with correct format ********
    for (var email = 0; email < emailAddressesLength; email++) {
      // get email address as a variable so easier to use
      var emailAddress = emailAddresses[email].trim();

      // push each email address into array in correct format
      emailAddressArray.push({ email: emailAddress });
    }
    // loop through each email address and push into array with relevant syntax ********


    // also add Calendar creating the Event as a Guest
    emailAddressArray.push({ email: calendarID, responseStatus:  "accepted"});


    // run Function to log message
    logEvent("Successfully sorted Calendar Event Guests");

    // return array to Parent Function
    return emailAddressArray;
  }
  catch (e) {
    // problem sorting attendees

    logEvent('Problem with sortAttendees' + e);

    // run Function to launch HTML popup
    var popupTitle = "Event not created: Row " + (row + 3);
    var popupMessage = "<p>Problem with 'sortAttendees' Function: </p>" + e;
    htmlPopup(popupTitle, popupMessage);

    // return false flag to signify issue
    return false;
  }

}


/*
Display a modal dialog box with custom HtmlService content.
Does not suspend the script.
Message needs to include '<p></p>' tags.
*/

function htmlPopup(popupTitle, popupMessage) {

  var htmlOutput = HtmlService
    .createHtmlOutput(popupMessage)
    .setWidth(360)
    .setHeight(180);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, popupTitle);

}


/*
Output log information to Log Sheet.
*/

function logEvent(message) {
  
  // get the user running the script
  var theUser = Session.getActiveUser().getEmail();
  
  // get the relevant spreadsheet to output log details
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var logSheet = ss.getSheetByName('Log');
  
  // create and format a timestamp
  var dateTime = new Date();
  var timeZone = ss.getSpreadsheetTimeZone();
  var niceDateTime = Utilities.formatDate(dateTime, timeZone, "dd/MM/yy @ HH:mm:ss");
  
  // create array of data for pasting into log sheet
  var logData = [niceDateTime, theUser, message];
  
  // append details into next row of log sheet
  logSheet.appendRow(logData);
  
}


/*
Function that can be passed a row & column number, along with a value,
to paste something into the Events Sheet.
*/

function pasteIntoSheet(row, column, value, color) {

  // get spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get Events sheet
  var eventsSheet = ss.getSheetByName('Events');

  // get relevant cell
  var cell = eventsSheet.getRange(row, column);

  // paste in value
  cell.setValue(value);

  // set colour
  cell.setFontColor(color);

}


/*
Function to create a Toast Popup notification with customised message.
Created as standalone so can be called from anywhere else within Project.
*/

function toastPopup(msg, title) {
  
  // get Spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // create Toast Popup
  ss.toast(msg, title);
  
}


/*
Function for a universal popup box.
*/

function displayPopup(popupTitle, popupMessage) {
  
  // get Spreadsheet UI
  var ui = SpreadsheetApp.getUi();
  
  // create Alert popup box
  var result = ui.alert(
    popupTitle,
    popupMessage,
    ui.ButtonSet.OK);
  
}


/*
Function to reset spreadsheet to start again.
*/

function reset() {
  
  // log that reset Function has been selected
  logEvent("Initiated 'Reset' Function");
  

  // get Spreadsheet UI
  var ui = SpreadsheetApp.getUi();
  
  // create Alert popup box
  var result = ui.alert(
    'Reset spreadsheet',
    'Do you wish to remove all inputted information to start again?',
    ui.ButtonSet.OK_CANCEL
  );
  
  
  // process user response from Popup Box
  if (result == ui.Button.OK) {
    
    logEvent("Selected 'OK' to reset spreadsheet");
    
    // get current spreadsheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    
    // get Welcome sheet
    var welcomeSheet = ss.getSheetByName('Welcome');
    
    // clear Calendar ID box
    welcomeSheet.getRange(6, 2).clearContent();
    
    
    // get Events sheet
    var eventsSheet = ss.getSheetByName('Events');
    
    // run Function to get last row (minus 2 for Header rows)
    var eventsSheetLastRow = lastRow(eventsSheet) - 2;

    // get last column
    var eventsSheetLastCol = eventsSheet.getLastColumn();
    
    // clear all event details
    eventsSheet.getRange(3, 1, eventsSheetLastRow, eventsSheetLastCol).clearContent();
    
    
    // log completion of Function
    logEvent("Completed 'Reset' Function.");

    // display alert popup to user to confirm completion
    ui.alert('Successfully cleared spreadsheet data.');
     
  }
  else {
    // User clicked 'No' or 'X' in title bar so do nothing
    logEvent("Selected 'CANCEL' to reset spreadsheet");
  }
  
}