In this article by the author, Ramalingam Ganapathy, of the book, Learning Google Apps Script, we will see how to create new projects in sheets and send an email with inline image and attachments. You will also learn to create clickable buttons, a custom menu, and a sidebar.

(For more resources related to this topic, see here.)

Creating new projects in sheets

Open any newly created google spreadsheet (sheets). You will see a number of menu items at the top of the window. Point your mouse to it and click on Tools. Then, click on Script editor as shown in the following screenshot:

A new browser tab or window with a new project selection dialog will open. Click on Blank Project or close the dialog. Now, you have created a new untitled project with one script file (Code.gs), which has one default empty function (myFunction).

To rename the project, click on project title (at the top left-hand side of the window), and then a rename dialog will open. Enter your favored project name, and then click on the OK button.

Creating clickable buttons

Open the script editor in a newly created or any existing Google sheet. Select the cell B3 or any other cell. Click on Insert and Drawing as shown in the following screenshot:

A drawing editor window will open. Click on the Textbox icon and click anywhere on the canvas area. Type Click Me. Resize the object so as to only enclose the text as shown in the screenshot here:

Click on Save & Close to exit from the drawing editor. Now, the Click Me image will be inserted at the top of the active cell (B3) as shown in the following screenshot:

You can drag this image anywhere around the spreadsheet.

In Google sheets, images are not anchored to a particular cell, it can be dragged or moved around.

If you right-click on the image, then a drop-down arrow at the top right corner of the image will be visible.

Click on the Assign script menu item. A script assignment window will open as shown here:

Type "greeting" or any other name as you like but remember its name (so as to create a function with the same name for the next steps). Click on the OK button. Now, open the script editor in the same spreadsheet. When you the open script editor, the project selector dialog will open. You'll close or select blank project. A default function called myFunction will be there in the editor. Delete everything in the editor and insert the following code.

function greeting() {
  Browser.msgBox("Greeting", "Hello World!", Browser.Buttons.OK);
}

Click on the save icon and enter a project name if asked. You have completed coding your greeting function.

Activate the spreadsheet tab/window, and click on your button called Click Me. Then, an authorization window will open; click on Continue. In the successive Request for Permission window, click on Allow button. As soon as you click on Allow and the permission gets dialog disposed, your actual greeting message box will open as shown here:

Click on OK to dispose the message box. Whenever you click on your button, this message box will open.

Creating a custom menu

Can you execute the function greeting without the help of the button? Yes, in the script editor, there is a Run menu. If you click on Run and greeting, then the greeting function will be executed and the message box will open.

Creating a button for every function may not be feasible. Although, you cannot alter or add items to the application's standard menu (except the Add-on menu), such as File, Edit and View, and others, you can add the custom menu and its items.

For this task, create a new Google docs document or open any existing document. Open the script editor and type these two functions:

function createMenu() {
  DocumentApp.getUi()
    .createMenu("PACKT")
    .addItem("Greeting", "greeting")
    .addToUi();
}

function greeting() {
  var ui = DocumentApp.getUi();
  ui.alert("Greeting", "Hello World!", ui.ButtonSet.OK);
}

In the first function, you use the DocumentApp class, invoke the getUi method, and consecutively invoke the createMenu, addItem, and addToUi methods by method chaining. The second function is familiar to you that you have created in the previous task but this time with the DocumentApp class and associated methods.

Now, run the function called createMenu and flip to the document window/tab. You can notice a new menu item called PACKT added next to the Help menu. You can see the custom menu PACKT with an item Greeting as shown next. The item label called Greeting is associated with the function called greeting:

The menu item called Greeting works the same way as your button created in previous task. The drawback with this method of inserting custom menu is used to show up the custom menu. You need to run createMenu every time within the script editor. Imagine how your user can use this greeting function if he/she doesn't know about the GAS and script editor? Think that your user might not be a programmer as you. To enable your users to execute the selected GAS functions, then you should create a custom menu and make it visible as soon as the application is opened. To do so, rename the function called createMenu to onOpen, that's it.

Creating a sidebar

Sidebar is a static dialog box and it will be included in the right-hand side of the document editor window. To create a sidebar, type the following code in your editor:

function onOpen() {
  var htmlOutput = HtmlService
    .createHtmlOutput('<button onclick="alert('Hello
       World!');">Click Me</button>')
    .setTitle('My Sidebar');

  DocumentApp.getUi()
    .showSidebar(htmlOutput);
}

In the previous code, you use HtmlService and invoke its method called createHtmlOutput and consecutively invoke the setTitle method. To test this code, run the onOpen function or the reload document. The sidebar will be opened in the right-hand side of the document window as shown in the following screenshot. The sidebar layout size is a fixed one that means you cannot change, alter, or resize it:

The button in the sidebar is an HTML element, not a GAS element, and if clicked, it opens the browser interface's alert box.

Sending an email with inline image and attachments

To embed images such as logo in your email message, you may use HTML codes instead of some plain text. Upload your image to Google Drive and get and use the file ID in the code:

function sendEmail(){
  var file = SpreadsheetApp.getActiveSpreadsheet()
              .getAs(MimeType.PDF);

  var image = DriveApp.getFileById("[[image file's id in Drive ]]").getBlob();

  var to = "[[receiving email id]]";

  var message = '<img src="cid:logo" /> Embedding inline image example.</p>';

  MailApp.sendEmail(
    to,
    "Email with inline image and attachment",
    "",
    {
      htmlBody:message,
      inlineImages:{logo:image},
      attachments:[file]
    }
  );
}

Summary

In this article, you learned how to customize and automate Google applications with a few examples. Many more useful and interesting applications have been described in the actual book. 

Resources for Article:

Further resources on this subject:

• How to Expand Your Knowledge [article]
• Google Apps: Surfing the Web [article]
• Developing Apps with the Google Speech Apis [article]