Monthly Archives: June 2014

Handling Microsoft Office Docs in Google Drive

Often people send you files in Microsoft Office formats and you need to be able to open, edit and return them. If they don’t work in Google Drive it can be a frustrating process.

Announced at I/O 2014, though, that’s all changed, because both the web (for Chrome users) and mobile apps for Docs, Sheets, and Slides allow you to open and edit MS Office files without first converting them, so you can now edit and return the files in their original MS Office format. more

Some Simple Debugging for Google Apps Script

Even though I’ve been programming for more years than I care to admit, I rarely write code correctly at the first attempt. Debugging, therefore, is an important part of of my development workflow.

In this post I’ll cover some basic techniques that can help you track down and stomp on errors in your Apps Scripts.

When using the Script Editor, you’ll occasionally see an error message appear in a red bar at the top of the screen. The line of code containing the error is usually highlighted too. Two common types of errors, namely syntax errors and runtime errors, tend to be displayed via this method.

In this instance, the message is helpfully telling me that I have an Unterminated string literal on line 2. That’s JavaScript-speak for a missing closing comma – and it’s right, I have. That’s a syntax error.

Runtime errors can be harder to track down, because they don’t contravene the grammar rules; they only show up when trying to run the code. Take a look at this code snippet:

function displayDistance() {
   var output = "The current distance is: " + distance + "miles";

In this version of the code, I’ve corrected the missing comma, but forgotten to pass the parameter distance as an argument in my function definition. Nothing’s wrong until I try to run the code:

Now the message correctly points out that distance is not defined.

You can generate these messages yourself, if you want to, using the throw() command in your code. Of course, using throw() can generate such a message at any time during code execution, not just under error conditions. Here’s an example:

function displayDistance(distance) { var output = "The current distance is: " + distance + "miles"; throw("The value of parameter distance was displayed as " + distance); Browser.msgBox(output); } more

Edit the same Google Doc in Multiple Tabs

Ever find yourself scrolling up and down through a lengthy document to make edits and changes? You can make life easier to some extent using bookmarks, as outlined in this previous post. Another neat trick, however, is to open several instances of your document all at once. You can then scroll each open copy to a different part of the document, and work on them in separate tabs (or browser windows). more

Two-Column Layout in Google Docs

Sadly, two-column layouts are not (yet?) supported in Google Docs. If you really, really want this type of layout, there is a workaround, but it may not be quite as easy as you’d like.

In this previous post, I discussed aligning tables side-by-side via a container table, and that’s what we’re going to do again here. This time, though, take the table borders all the way out to the margins of the page: more

Saving Data Between Script Runs via Properties Service

Any variable values you set while running an Apps Script will be stored only for that one execution of the script. The next time you run the script, those values will be gone. So how do you save some variables to re-use them later?

There are a few options, like using range of data within your spreadsheet, or perhaps saving data to a file in Google Drive. Either of these approaches let you save your data in a place from where you can later retrieve it.

A cleaner and perhaps more secure way, though, is to use Google’s Properties Service global object.

The Properties service lets you store data in key-value pairs relevant either to only one particular script,  or to one particular user (there is actually a third way, to one particular document, but only where the script is running as an add-on). It is typically used to store setup data, user preferences or similar.

In this example I’ll use the UserProperties property store to save some data on a per-user basis. First, let’s get the current user properties:

var userProperties = PropertiesService.getUserProperties();

To retrieve an individual property (in this case, a property I’ve called CURRENT_TIMESTAMP in which I’ve previously stored the date and time the script was last opened by me) I can use the getProperty() method. Here I’ll display it in a MsgBox dialg, as described in this previous post:

Browser.msgBox('Last run: ' + userProperties.getProperty('CURRENT_TIMESTAMP'), 

Now I want to save a new value to this variable, overwriting the value previously stored. This means getting the current timestamp and setting the value in userProperties:

var rightnow = new Date(); userProperties.setProperty('CURRENT_TIMESTAMP', rightnow); more