Monthly Archives: July 2014

Custom Dialogs in Google Apps Script

In previous posts I’ve shown how to open dialogs such as alert() and prompt() to help you communicate messages to users and/or have them enter information. In addition to these pre-written dialogs, Apps Script lets you build custom modal dialogs to your own design.

A custom dialog can display a UI Service user interface inside a Google Docs document, a Sheets spreadsheet, or a Forms editor. Unlike alert() and prompt(), these custom dialogs do not pause the server-side script while the dialog is open; it carries on running.

Docs, Forms, and (the new version of) Sheets all use the following method to open the dialog:

Ui.showModalDialog()

First, we need to create the dialog in HTML. The following code goes into a new HTML file opened via the Script Editor:

<style> h2 { color:red; font: 20px bold verdana, helvetica, sans-serif; } p { font: 14px normal verdana, helvetica, sans-serif; } #btn { float:right; border-radius: 15px; } </style> <div> <h2>Warning</h2> <p>The action you are performing may be way too cool for some users.</p> <p>Try to dial back the awesomeness a little now and then.</p> <input id="btn" type="button" value="X" onclick="google.script.host.close()" /> </div> ...read more

Create GeoMap Charts in Google Sheets

I’ve previously posted about charts in Google Sheets, but this post concerns one type of chart in particular that might be useful in certain special situations – the GeoMapA GeoMap is a chart showing the map of a country, continent, or other geographic region, painted with differing colours representing the spreadsheet cell values associated with the map’s locations. ...read more

Talking to the Server from Apps Script’s HTML Service

In a previous post I talked about adding a sidebar to your document using Google Apps Script. Such a sidebar us written in HTML using Google Apps Script’s HTML Service. But how can you get this client-side HTML page to talk to your server-side routines behind your document or spreadsheet?

Google provides an asynchronous client-side JavaScript API called google.script.run to handle this for you. Using google.script.run you can call your Apps Script functions via JavaScript in your HTML page.

Suppose, for instance, that we have a spreadsheet with a function myFunc() defined in Apps Script. I can fire that function from my HTML page (i.e. my sidebar) by including the following in the HTML code:

<script>google.script.run.myFunc();</script>

The example I’m going to work through below is a little more complex, as we want to not only execute a server-side function, but also return a value from it to the HTML page. Here’s a simple little spreadsheet:

I want to include in a sidebar a button which, when clicked, will return the value of cell B2 and display it in the sidebar. This is a little more complex than the myFunc() example syntax shown above; in addition to executing a function, we want to return a value to the HTML page.

Instead of using a syntax like

google.script.run.myFunc()

I need to use

google.script.run.withSuccessHandler(onSuccess).myFunc()

which allows me to define a callback function onSuccess() to handle the returned value.

Let’s see some code. First, here’s the Apps Script running behind the spreadsheet. In addition to the code to implement the sidebar from a menu item (which you can read about in the previous post) I’ve now defined a further function returnCellValue(cell). This is the function I’m going to execute from the client-side code in the sidebar, and it simply grabs and returns a cell value from the spreadsheet.

function onOpen() { SpreadsheetApp.getUi() .createMenu('Sidebar').addItem('Open', 'openSidebar').addToUi(); } function openSidebar() { var html = HtmlService.createHtmlOutputFromFile('index'); SpreadsheetApp.getUi().showSidebar(html); } function returnCellValue(cell) { return SpreadsheetApp.getActiveSheet().getRange(cell).getValue(); } ...read more

Create Google Calendar Events in Google Search

You can now create Google Calendar events directly from Google’s Search page.

Type any of:

  • create event
  • add event
  • new event
  • add meeting
  • schedule appointment

and Google produces a box containing details for a generic ‘Meeting’ event that starts shortly. There’s a link to add the event to your calendar: ...read more

A Newer Way to make Dialogs in Apps Script

In a previous post I discussed the creation of user dialogs in Apps Script, using the Browser object. While this code still works, Google has gone away from this sort of interface, instead concentrating on the UI service. In thios article I show you how to use the newer syntax to generate user dialogs.

In place of Browser.msgBox, Google now recommends using the ui alert() method. Here’s an example:

var ui = SpreadsheetApp.getUi();
var userChoice = ui.alert(
   'Please confirm',
   'Are you sure you want to continue?',
   ui.ButtonSet.YES_NO);

An alert is a modal dialog box that opens inside a Google Docs, Sheets, or Forms document. It displays a user message along with an “OK” button. Alerts suspend the server-side script from being executed while the dialog is open, allowing it to resume after the the dialog is closed.

A dialog title, and alternative buttons to the default OK are optionally configurable too. The method is similar to the JavaScript window.alert() method available in client-side JavaScript code. To override the default OK button with alternative buttons, pass a value from the set of buttons arguments listed in the previous post. In some of these cases, we need to evaluate the user’s response by capturing the return value, for example in the following example function showChoice():

function showChoice() { var ui = SpreadsheetApp.getUi(); var userChoice = ui.alert( 'Please confirm', 'Are you sure you want to continue?', ui.ButtonSet.YES_NO); if (userChoice == ui.Button.YES) { ui.alert('Operation Confirmed.'); } else { ui.alert('Operation Halted.'); } } ...read more

Anonymous Animals

One of Google Drive’s key advantages is the ability to collaborate with others on your documents, as I described in this previous post In cases where you have set your document to Anyone with the link can view or Public on the web, and have then shared the link with people other than those expressly defined as being shared on the document, you may have noticed unusual animal-themed icons appearing at the top of your document (when someone who is a known sharer views a Google Doc their own profile image appears here). Each animal icon represents an anonymous viewer whom Drive has randomly assigned an animal avatar. ...read more

Export from Calendar to Sheet using Apps Script

Sometimes it can be really useful to grab events from your Google calendar into a spreadsheet, where you can process them further. Here’s some example code to let you do that.

I think the code is fairly self-explanatory – just change the email address in the opening lines to the one that corresponds to the calendar in question.

function getCal() { var mycal = "***********@gmail.com"; // change to suit var cal = CalendarApp.getCalendarById(mycal); //Clear the sheet: var sheet = SpreadsheetApp.getActiveSheet(); sheet.clearContents(); //Get the events (use your own dates and keywords, or pass them as arguments): var events = cal.getEvents(new Date("January 01, 2014 00:00:00 CST"), new Date("May 31, 2014 23:59:59 CST"), {search: 'Birthday'}); //Then loop through and write out (choose your own calendar fields, I've used just five): for (var i=0;i<events.length;i++) { var details=[[mycal, events[i].getTitle(), events[i].getDescription(), events[i].getLocation(), events[i].getStartTime(), events[i].getEndTime()]]; var range=sheet.getRange(i+1,1,1,6); range.setValues(details); } } ...read more