How to create custom Menu, Function, Email Template in Google Apps Script

Google Apps Script Tutorial Part 5 – Hello Everyone, Welcome to the fifth series of the Google Apps Tutorials. In this part I am going to show couple of things; first How to Create Custom Menu for Google Spreadsheet, second Learn to call methods from custom menu items, and third is sending mails to all email ids with custom template. This Tutorial is long one and will need you to go through earlier parts to complete this section. Below is the list of the relevant Google Apps Script Tutorial:

  1. Google Script to Write Data in Spreadsheet
  2. Google Script to Send Email
  3. Google Script to Create GUI Application

Step 1: Create Custom Menu in Google Script

First part to learn is how to create and add custom menu in Google Spreadsheet using Script. Doing so is very easy and needs only couple of line code.

.createMenu(“Menu Name”); is the function which is used to create a Top level Menu like “File” etc. To create real menu items use .addItem(“Item Name”); See the code below for more understanding. 

Create Custom Menu in Google Script

In the above Code I have called function ‘sendemail‘ from menu item “Send Email“. Now I hope it is clear about the concept of Calling Functions from Custom Menu Items in Google Script. Now next part is send mails to all the email id’s in the spreadsheet along with details such as Name, Mobile Number etc.

First see the screenshot of the spreadsheet I have created for the tutorial of sending batch emails in Google Script. As you can see, there are three fields i.e.; email id, Name and Mobile. Now my aim is to send emails to all id’s in a proper format on click on above created menu item.

Screenshot_5

In the previous tutorial about sending email from Google Script, I have discussed about MailApp.sendEmail() function. Same method will be used in this part also but message text is customized to include all the field values.

emailtext=emailtext+”Name:”+values[i][1]+”\nMobile:”+values[i][2]+”\n\n”;

As you can see in the code above i have concatenated the email message with field values such as for Name -> values[i][1] and so on.

Complete Code

Send Email from Google Spreadsheet

Try the above tutorial and comment below if you face any problem in the implementation

Leave a Reply