Create a Custom Menu

Watch the Video!

Copy the Code!

//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();

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