Category Archives: Scripts

Protect Spreadsheet Ranges in Google Apps Script

In a recent post I described how Google have extended the capabilities of range protection in spreadsheets. They’ve also made it possible to protect ranges in Google Apps Script by means of the new Protection class.

Usage is pretty straightforward, and code samples probably serve better than written description. Here’s how to protect a simple range:

// Protect range C1:G10
var ss = SpreadsheetApp.getActive();
var range = ss.getRange('C1:G10');
var protection = range.protect().setDescription('This is now a protected range');

The object stored in the variable I’ve called protection now represents my protected range, and I can manipulate it directly in code.

First want to set up who are the editors for it, starting with myself:

var me = Session.getEffectiveUser(); protection.addEditor(me); ...read more

Worked Example – Weather Map in Google Sheets

This post is a little different to those that have gone before. Instead of presenting a hint or tip about some aspect of Google Drive, today let’s build a little spreadsheet application to pull together some of the techniques I’ve already discussed.

Drawing on some of the posts from recent weeks. I’m going to make a quick-and-dirty temperature map of Europe. Unfortunately, that means there’ll be links ducking and diving to other posts, but don’t worry, they all open in new tabs so you shouldn’t lose this page if you need to follow a link.

Firstly, I want to grab a list of European countries and their capitals. There’s a handy one at http://www.nationsonline.org/oneworld/capitals_europe.htm

We’ll grab the data from this table using IMPORTHTML, as described in this post. A quick look at the page’s source code tells me that I want the fourth table that appears on the page, so here’s my function:

=importhtml("http://www.nationsonline.org/oneworld/capitals_europe.htm","table",4)

Here’s the table successfully imported into my spreadsheet:

To get the temperature information, I’m going to use an online service provided by openweathermap.org. This post explains how to use UrlFetchApp() to access resources from elsewhere on the web.

The service returns weather information in JSON format. For example, the request

http://api.openweathermap.org/data/2.5/weather?q=London

returns the following:

{"coord":{"lon":-0.13,"lat":51.51},"sys":{"type":1,"id":5093,"message":0.0305,"country":"GB","sunrise":1407559044,"sunset":1407612869},"weather":[{"id":802,"main":"Clouds","description":"scattered clouds","icon":"03d"}],"base":"cmc stations","main":{"temp":289.41,"pressure":1007,"humidity":87,"temp_min":287.04,"temp_max":293.15},"wind":{"speed":5.7,"deg":240},"clouds":{"all":40},"dt":1407570620,"id":2643743,"name":"London","cod":200} ...read more

Fetch External Resources with Google Apps Script

You can use the class UrlFetchApp to let your script communicate with other hosts over the Internet, and fetch resources by fetching URLs. Your script can use the URL Fetch service to issue HTTP and HTTPS requests as if it were itself a browser. 

You can use this to grab information for your Google Docs, Sheets etcetera from an enormous range of online APIs providing data about weather, travel, entertainment, financial data and much, much more.

The general syntax of such a call is:

var response = UrlFetchApp.fetch(url [, options]);

The url parameter is, unsurprisingly, the URL of the service being called; the optional parameter options (which I won’t use in this example) can supply further information where it’s needed by the remote service, and is often used where authentication is required.

Let’s demonstrate the class by using the Telize (www.telize.com) GeoIP API. This is an online service that will return information about the calling IP address.

Here’s the script:

function getIpInfo() { var url = "http://www.telize.com/geoip"; var result = UrlFetchApp.fetch(url); var out = Utilities.jsonParse(result.getContentText()); var doc = SpreadsheetApp.getActiveSpreadsheet(); var cell = doc.getRange('a1'); var index = 0; for (var i in out) { var value = out[i]; cell.offset(index, 0).setValue(i); cell.offset(index, 1).setValue(value); index++; } } ...read more

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

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

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

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

Documenting an Apps Script Library

In a previous post I discussed how to make a code library in Google Apps Script, allowing you to re-use code in various scripts.

To allow other users to benefit from your library (and you too, in time to come, when you’ve forgotten the details) you can use JSDoc in your code to ensure automatic generation of documentation for your library, and the availability of auto-completion in the editor, for those using your code.

JSDoc is a poopular standard for documenting JavaScript source code (Google Apps Script is pretty much based on JavaScript). Code published with comments delimited by /** */ produces HTML documentation for the code.

/** * Repeat <tt>str</tt> several times. * @param {string} str The string to repeat. * @param {number} [times=1] How many times to repeat the string. * @returns {string} */ ...read more

Handle Errors in Apps Script with Try and Catch

In previous posts I discussed simple debugging techniques involving logging errors and using the Script Editor’s built in debugger. A more elegant way to deal with code errors is to include code in your script to intercept the production of the error and deal with it appropriately. This is usually referred to as exception handling

To help work out what went wrong in a script, you can surround part of the code with a try-catch block. Here’s an example:

try {
   // your script code here
 } catch(e) {
   // if the script code throws an error,
   // do something with the error here
 }

The following example is a snippet of code that will grab information about an error and send you an email notification containing the text of the error message:

function myFunction(){ try { // your script code here } catch (e) { MailApp.sendEmail("you@example.com", "Error report", e.message); } } ...read more