Tutorial: Create and Distribute Automated Mailing List with Google Sheets & Google Apps Script

Executing the Scripts From the UI

To be able to execute the scripts from the library we can either create clickable buttons and place them on the sheet, or create a menu. Because our sheet that imports the library has no sheet named “Maillist” and “Settings”, the Script wont do anything or won’t function properly. Also, the getRange() function and Setting cells used in our library are hard-coded. Basically, it is a good idea to deliver a reference sheet that follows the quirks of the code. To be able to have a working example, you can build/add this into the Library Sheet.

Button

• In the sheet menu click Insert -> Drawing.
• Draw something and press Save and Close.
• Right click the drawing.
• Left click the 3 dots on the upper right side of the drawing so a menu pops up.
• Click Assign Script.
• Fill in a local function name, e.g.: CreateMailingList (this is the reason we need facade functions)
• Left clicking the button will now execute the function.

Menu

function onOpen() {

  const ui = SpreadsheetApp.getUi();
  
  ui.createMenu('Mailing List Menu')
    .addItem('Send Email to Mail List', 'CreateMailingList')
    .addSeparator()
    .addToUi();
} 

function onOpen() {

  const ui = SpreadsheetApp.getUi();
  
  ui.createMenu('Mailing List Menu')
    .addItem('Send Email to Mail List', 'CreateMailingList')
    .addSeparator()
    .addToUi();
} 

Save the script. Go back to the parent sheet, and refresh the page to be able to see the menu appear on top.