Until now I’ve mostly avoided the developer ecosystem around Google Apps like Docs and Sheets. I didn’t really have a need for those APIs or any good ideas about what to do with them. I have played with Chrome Extensions a little bit in the past, but nothing very practical.

It turns out the Google Apps Scripts platform is insanely powerful. You can build add-ons for Google Docs, Sheets, and Forms including things like custom menus and buttons, custom code triggered on an event basis, or even custom Sheet formula functions.

I hope to provide some insight into Google Apps Scripts based on what I learned while building a small add-on. I’ll go over the steps I took to build the add-on, which is a simple Google Apps Script that will find and replace image URLs in your Google Doc page with the image itself.

First, I’ll talk about the larger concepts needed to tackle it, then I’ll go through the add-on itself, using those concepts along the way.

Google Apps Scripts

  “11 Google apps, 1 platform in the cloud”

Google Apps

What is it?

Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services and build web applications.

Google Apps Scripts has a massive API but we’ll focus specifically on Docs.

An example of a Google Apps Script looks something like this:

// Open a document by ID.
var doc = DocumentApp.openById('DOCUMENT_ID_GOES_HERE');

// Create and open a document.
var doc = DocumentApp.create('Document Name');

// Use the current active document (more on that later).
var doc = DocumentApp.getActiveDocument();

It’s JavaScript, but it exposes a ton of rich objects and interfaces that are essentially the building blocks of a Google App, it’s pretty cool. The DocumentApp refers to the Google Docs class that allows you to extend Google Docs behavior and features.

Elements of a Document

Below is a Google Doc page, but I’ve marked it up with some of it’s common pieces. Each piece is an element that you can either create or manipulate using Google Apps Scripts, which remember is just plain old JavaScript.

Google Apps Elements

A full list of items including their hierarchy can be found on the Extending Google Docs page. Each one of these elements has an extensive reference section that documents all of the available methods, their return types, and a brief description. For example, here is the reference section for Paragraph.

I’ve really got to hand it to the Google Apps team for their documentation, it’s really well structured, so much so that the URLs are quite navigable if you have an idea what you’re looking for. I love me some guessable URLs.

How to Run Apps Scripts

So you know a Google Doc is made up of several elements in a hierarchy. Those elements have their own methods that can be used to manipulate (or for some, to create) that element.

The next thing you probably want to know is how to use those methods. To do so, you can go to Tools > Script Editor... in the Google Docs toolbar. From there you’ll be presented with a code editor that will allow you to type in some code and run it. The code will be bound to the document you’re working in. This is important because it will allow us to reference the document without an ID, using the third variation from earlier:

// Use the current active document
var doc = DocumentApp.getActiveDocument();

Adding Text to the Page

With the script editor open, let’s do some simple editing of the document.

function myFunction() {
  // Get a reference to the current document
  var doc = DocumentApp.getActiveDocument();

  // Get the active document's Body element
  var body = doc.getBody();

  // Add a paragraph to the end of the body
  body.appendParagraph("Hello world!")
}

Run the script by clicking the “Run” button or type ⌘ + R (you may have to give permission) then switch back to the Google Doc and you should see the text has been added!

Google Apps Scripts: Logs

Using the Logger

It’s really helpful to know how to print to and read from the log. To do so, you can use the Logger object. Check out the example below:

myFunction() {
  Logger.log("Hello world!");
}

Run the script using the “Run” button in the toolbar or type ⌘ + R. Then, to view the logs go to View > Logs or type ⌘ + Enter.

Google Apps Scripts: Logs

This will become really useful when we’re writing the script because we can ensure method calls are returning the things we expect. Plus we can use the log output to figure out which type of element we’re dealing with, which we can then use to find the correct documentation.

Good old puts debugging!

Events and Triggers

It’s all well and good to be able to run code manually with the run button, but to make this actually useful, we need to know how to respond to actions that occur, i.e. someone editing the document. We need some way to activate our code when we’ve pasted an image URL.

Enter Triggers.

Triggers let Apps Script run a function automatically when a certain event, like opening a document, occurs.

Google Apps supports simple triggers like onChange and onOpen, so my first thought was “oh perfect I can just use an onChange trigger!” Unfortunately that trigger is only available for Sheets. It runs when a column or row is added, removed or updated.

Since that’s not an option I kept digging and found that Google Apps also supports time driven triggers, which can execute a user-defined function at a given interval. The quickest interval allowed is once per minute, but that’ll do.

Creating a Trigger

To setup a time-based trigger it’s advised you do so in the onOpen function.

function myFunction() {
  var doc = DocumentApp.getActiveDocument();
  var body = doc.getBody();
  body.appendParagraph("Hello world!")
}

function onOpen() {
  ScriptApp.newTrigger("myFunction")
    .timeBased()
    .everyMinutes(1)
    .create();
}

Save the code and run the onOpen function. You’ll probably have to select the function from the dropdown menu to the right of the run button since there are now two functions defined in your file.

After running the function, switch back to your document. Wait and watch for a couple minutes while “Hello world” is added to the document over and over again.

Cleaning up Triggers

If you run the onOpen function multiple times, you’ll find that the trigger has been created again. You can see this by clicking on the “current project triggers” button, directly left of the “Run” button.

google apps scripts: triggers

If you don’t delete triggers that were there before, your project will be filled with duplicates which will cause your code to stop running and Google will let kindly let you know.

google apps scripts: trigger error

You can run this function to clean up your triggers.

// Deletes all triggers in the current project.
// https://developers.google.com/apps-script/reference/script/script-app#deletetriggertrigger
function deleteAllTriggers() {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
}

More on Editing

Having a handle on the document and having the timer trigger in place, we essentially have an event loop we can use to continually modify the document. To build the add-on that automatically detects and replaces image URLs we’ll need a couple more pieces.

Finding Text with Regex

The Body class has a method called findText that we’ll need to find a URL and replace it with an image.

findText(searchPattern)

Searches the contents of the element for the specified text pattern using regular expressions. The provided regular expression pattern is independently matched against each text block contained in the current element.

https://developers.google.com/apps-script/reference/document/body#findText(String)

function logHelloWorldText() {
  var doc = DocumentApp.getActiveDocument();
  var body = doc.getBody();

  // We'll just assume you still have the
  // "Hello world" in the document from earlier.
  // body.appendParagraph("Hello world!")

  selection = body.findText("^Hello world!$")

  if(selection) {
    var element = selection.getElement();
    Logger.log("Element: " + element)
    Logger.log("Type: " + element.getType())
    Logger.log("Text: " + element.getText())
  }
}

function onOpen() {
  ScriptApp.newTrigger("logHelloWorldText")
    .timeBased()
    .everyMinutes(1)
    .create();
}
[17-02-02 15:21:49:497 PST] Element: Text
[17-02-02 15:21:49:498 PST] Type: TEXT
[17-02-02 15:21:49:498 PST] Text: Hello world!

From the logs we can see we successfully found our Hello world! text in the document. From there we can use methods on that Text element like getText() to get or setBold(true) to make the text bold (go ahead, try it!).

Inserting an Image from a URL

Google docs has this functionality built in, so we’re basically replicating the “Insert > Image > From URL…” functionality here. the UrlFetchApp provides a wrapper around a method called fetch that lets us get content at the specified URL.

function insertUrlImage() {
  var doc = DocumentApp.getActiveDocument();
  var body = doc.getBody();
  var url = "https://www.google.com/images/branding/googlelogo/1x/googlelogo_color_272x92dp.png";
  var fetch_img = UrlFetchApp.fetch(url);
  var imageblob = fetch_img.getBlob();
  body.appendImage(imageblob);
}

Run that function and you should see the image appear shortly.

Going through line by line, we’re familiar with doc and body already. url is of course the URL that points to the image we want to insert.

The fetch_img variable is using the UrlFetchApp class and the fetch method just mentioned. This returns an HTTPResponse so fetch_img will contain an instance of that Class. The HTTPResponse reference docs list all of the methods available.

One method is the getBlob method that will give us the content from the URL as a Blob. Finally, the appendImage method on the body instance accepts a BlobSource which conveniently our imageblob variable (the Blob) is one such source.

Here it is in action:

google apps scripts: insert image


Putting it All Together

To recap, we’ve got several pieces working independently. We know how to find and change text in a document, and we can insert images from a URL. In addition, we have a timed event trigger set up so our code can execute once per minute.

Now that we have an idea of each piece we’ll need, let’s make them all work together.

Finding Image URLs

First, let’s get the timed event trigger going and try to find a URL.

function insertImages() {
  var doc = DocumentApp.getActiveDocument();
  var body = doc.getBody();

  var selection = body.findText("https?://")
  var element = selection.getElement();
  var url = element.getText();

  Logger.log(url);
}

function onOpen() {
  // Delete previous triggers
  var allTriggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < allTriggers.length; i++) {
    ScriptApp.deleteTrigger(allTriggers[i]);
  }

  // Run once immediately and then once per minute.
  insertImages();
  ScriptApp.newTrigger("insertImages")
  .timeBased()
  .everyMinutes(1)
  .create();
}

google apps scripts: logurl

That’s great, but it only finds the first URL on the page. To get all URLs on the page we can use plain old JavaScript. The insertImages method becomes:

function insertImages() {
  var doc = DocumentApp.getActiveDocument();
  var body = doc.getBody();

  var selection;
  var lastSelection = null;

  while(selection = body.findText("https?://", lastSelection)) {
    lastSelection = selection;
    var element = selection.getElement();
    if(element) {
      var url = element.getText();
      Logger.log(url);
    }
  }
}

We use a while loop to find all occurrences of text that looks like a URL. We track the lastSelection and pass it on each successive call so that Google Docs doesn’t have to search from the top of the document every time.

[17-02-02 17:52:15:503 PST] https://www.google.com/images/branding/googlelogo/1x/googlelogo_color_272x92dp.png
[17-02-02 17:52:15:505 PST] https://upload.wikimedia.org/wikipedia/commons/0/05/Twitter-logo-black.png
[17-02-02 17:52:15:508 PST] https://upload.wikimedia.org/wikipedia/en/a/ae/Google_Docs,_Sheets,_and_Slides_Icon.png

Perfect. Getting closer. Now let’s just drop in the images.

Transforming URLs Into Images

We can insert the images using the same URL fetching technique from earlier, but this time we’ll be more careful by checking the content type of the blob before we attempt to use it as an image. The insertImages method becomes:

function insertImages() {
  var doc = DocumentApp.getActiveDocument();
  var body = doc.getBody();

  var selection;
  var lastSelection = null;

  while(selection = body.findText("https?://", lastSelection)) {
    lastSelection = selection;
    var element = selection.getElement();
    if(element) {
      var url = element.getText();

      // Retrieve the image from the web.
      var fetchimg = UrlFetchApp.fetch(url);
      var imageblob = fetchimg.getBlob();

      if(imageblob && imageblob.getContentType().indexOf("image") != -1) {
        element.getParent().appendInlineImage(imageblob);

        // Remove the URL text element
        element.removeFromParent();
      }
    }
  }
}

After the image is appended, we remove the Text element containing the URL from it’s parent (the Body in this case). Finally, we’ve got the basic add-on behavior we were looking for:

google apps scripts: transform images

Resizing the Appended Images

A keen eye will notice the resizeProportionally function call after the image is appended. If you provide a URL to a very large resolution image, the image will hang off the document.

Instead, we can resize the image after it’s appended, so it starts off at a better default. Below is a function we can use to resize an image so the dimensions stay proportional.

// Resize the image proportionally down to a max width/height
function resizeProportionally(image, maxWidth, maxHeight) {
  var maxWidth = maxWidth || 600;   // default to 600 max width
  var maxHeight = maxHeight || 600; // default to 600 max height

  var width = image.getWidth();
  var height = image.getHeight();

  var widthRatio = maxWidth / width;
  var heightRatio = maxHeight / height;
  var ratio = Math.min(widthRatio, heightRatio);

  var newWidth  = width  * ratio;
  var newHeight = height * ratio;

  if(newWidth < width || newHeight < height) {
    image.setWidth(newWidth);
    image.setHeight(newHeight);
  }
}

With that in place, insertImages becomes:

function insertImages() {
  var doc = DocumentApp.getActiveDocument();
  var body = doc.getBody();

  var selection;
  var lastSelection = null;

  while(selection = body.findText("https?://", lastSelection)) {
    lastSelection = selection;
    var element = selection.getElement();
    if(element) {
      var url = element.getText();

      // Retrieve the image from the web.
      var fetchimg = UrlFetchApp.fetch(url);
      var imageblob = fetchimg.getBlob();

      if(imageblob && imageblob.getContentType().indexOf("image") != -1) {
        var image = element.getParent().appendInlineImage(imageblob);
        resizeProportionally(image);

        // Remove the URL text element
        element.removeFromParent();
      }
    }
  }
}

Supporting Droplr Short URLs

I use Droplr for screen captures when I need to quickly share with someone. Once you’ve captured a screenshot, Droplr automatically copies its short URL to your clipboard so you can paste it in an email, a Google Doc, or wherever.

It makes for a nice workflow when you can simply snag a screenshot and paste its short URL right into a Google Doc and have the image insert automatically.

To support this, a small change is required so the Blob data fetched is the correct type. Droplr will provide the raw downloadable image if you append a + to the end of the short URL.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
function insertImages() {
  var doc = DocumentApp.getActiveDocument();
  var body = doc.getBody();

  var selection;
  var lastSelection = null;

  while(selection = body.findText("https?://", lastSelection)) {
    lastSelection = selection;
    var element = selection.getElement();
    if(element) {
      var url = element.getText();

      // Droplr provides raw images if the URL is appended with +
      if(url.indexOf("d.pr/i/") != -1 && !url.match(/\+$/)) {
        url = url + "+";
      }

      // Retrieve the image from the web.
      var fetchimg = UrlFetchApp.fetch(url);
      var imageblob = fetchimg.getBlob();

      if(imageblob && imageblob.getContentType().indexOf("image") != -1) {
        var image = element.getParent().appendInlineImage(imageblob);
        resizeProportionally(image);

        // Remove the URL text element
        element.removeFromParent();
      }
    }
  }
}

Lines 15-17 show the relevant changes.

Limiting Image Fetches

If you have a really large Google Doc or happen to paste a large number of URLs into the Document over the course of a minute, it may be useful to limit the number of fetches that occur each time. During development, I also ran into the page slowing and becoming unresponsive because of an accidental infinite while loop.

To prevent this from happening, a simple precondition on the while loop can be helpful:

var i = 0;
var maxruns = 10;
while(i++ < maxruns && (selection = body.findText("https?://", lastSelection))) {
  ...
}

Wrapping Up

If you’re still with me, that’s amazing. For such a small add-on, we went through a lot to get there. Hopefully walking it through has given you a quick dive into Google Apps Sheets enough so that you feel comfortable delving into the documentation and trying out your own add-ons.

If you still want to keep going and improve what we have, you might try adding a button to the UI that will replace the URLs when clicked. That way you don’t have to wait for the 60 second interval to trigger again. 😄

The Whole App Script

Here’s the entire thing, all put together:

function insertImages() {
  var doc = DocumentApp.getActiveDocument();
  var body = doc.getBody();

  var selection;
  var lastSelection = null;

  var i = 0;
  var maxruns = 10;
  while(i++ < maxruns && (selection = body.findText("https?://", lastSelection))) {
    lastSelection = selection;
    var element = selection.getElement();
    if(element) {
      var url = element.getText();

      // Droplr provides raw images if the URL is appended with +
      if(url.indexOf("d.pr/i/") != -1 && !url.match(/\+$/)) {
        url = url + "+";
      }

      // Retrieve the image from the web.
      var fetchimg = UrlFetchApp.fetch(url);
      var imageblob = fetchimg.getBlob();

      if(imageblob && imageblob.getContentType().indexOf("image") != -1) {
        // Append the image and resize it
        var image = element.getParent().appendInlineImage(imageblob);
        resizeProportionally(image);

        // Remove the URL text element
        element.removeFromParent();
      }
    }
  }
}

// Resize the image proportionally down to a max width/height
function resizeProportionally(image, maxWidth, maxHeight) {
  var maxWidth = maxWidth || 600;   // default to 600 max width
  var maxHeight = maxHeight || 600; // default to 600 max height

  var width = image.getWidth();
  var height = image.getHeight();

  var widthRatio = maxWidth / width;
  var heightRatio = maxHeight / height;
  var ratio = Math.min(widthRatio, heightRatio);

  var newWidth  = width  * ratio;
  var newHeight = height * ratio;

  if(newWidth < width || newHeight < height) {
    image.setWidth(newWidth);
    image.setHeight(newHeight);
  }
}

function onOpen() {
  // Delete previous triggers
  var allTriggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < allTriggers.length; i++) {
    ScriptApp.deleteTrigger(allTriggers[i]);
  }

  // Run once immediately and then once per minute.
  insertImages();
  ScriptApp.newTrigger("insertImages")
  .timeBased()
  .everyMinutes(1)
  .create();
}

Publishing the Add-on

In order to reuse the App Script we’ve created, rather than just sticking the script in the script editor every time, we can publish it as an add-on in the Chrome Web Store.

At the time of publishing this blog post, I was still waiting for approval.

google apps scripts: chrome webstore

Hopefully it’ll be approved soon.

Make something with Google Apps Scipts? Share it with me on Twitter! I’d love to see it.

Cheers!