Automate personalising and sharing documents with Apps Script

As a manager I spend a fair amount of time in… Google Docs!

While I don’t mind writing things which are not code, I do dislike tedious tasks… specially tasks that can be automated. One of these is creating separate documents per person using a template. You have to open the template, make a copy, change things, share it with the report… TEDIOUS! 😤🙅🏻‍♀️

Fortunately, there’s a solution to automate this: Apps Script!

It exposes the same apps you’re used to interact with, but programmatically. And so I translated the sequence from above into code. I can now do in 22 seconds what used to take me 15 minutes and a considerable amount of frustration building. And because I’m VERY NICE I’m sharing the code with you, so you can also save time and do better things with your brain. You’re very welcome 💓

Apps Script is basically JavaScript, but it runs in The Cloud. To access the dashboard you need to be logged in with a Google account and then go to https://script.google.com/. Then create a new project and paste the following in the editor:

var docs = [
  // The id is the string you see in the document url:
  // https://docs.google.com/document/d/{the very long string here}/edit
  { id: 'documentID1', title: 'PERSON:Your name 1:1 - YYYY' },
  { id: 'documentID2', title: 'PERSON YYYY goals' }
];

var people = [
  { name: 'Person number one', email: 'personnumberone@example.com' },
  // More people if you need
  // I recommend you test with your own address before sending to everyone!
];

function main() {
  people.forEach(function(person) {
    docs.forEach(function(item) {
      var newTitle = personalise(item.title, person);
      var fileClone = clone(item.id);
      var clonedId = fileClone.getId();
      var docClone = DocumentApp.openById(clonedId);
     
      fileClone.setName(newTitle);

      // Replace template tags
      personaliseDoc(docClone, person);

      // Share with person's email, read+write
      fileClone.addEditor(person.email);
    });
   
    // Let them know why you sent them a bunch of docs out of the blue
    // (specially if you're sending many!)
    GmailApp.sendEmail(person.email, "New year, blank slate!",
      "Hello!\n\nI have created and shared new documents for 2018.\n\nThanks,\nSole");
  });
}

function getReplacements(data) {
  var replacements = {
    'PERSON': data.name,
    'YYYY': 2018
  };
  return replacements;
}

function personalise(str, data) {
  var out = str;
  var replacements = getReplacements(data);
 
  Object.keys(replacements).forEach(function(term) {
    out = out.replace(term, replacements[term]);
  });
  return out;
}

function personaliseDoc(doc, data) {
  var replacements = getReplacements(data);
  var body = doc.getBody();

  Object.keys(replacements).forEach(function(term) {
    body.replaceText(term, replacements[term]);
  });
}

function clone(docId) {
  var doc = DriveApp.getFileById(docId).makeCopy();
  return doc;
}

To run this, you need to select the main function from the drop down, and press the ‘play’ button to run. You’ll need to authorise the script to read and write your docs, files and email. Scary! 😛

Each template document has a few fields that will be replaced with data when running the script. For example the title in the document is “PERSON:My name 1:1 YYYY”; when run the PERSON and YYYY placeholders will become Person number one and 2018 respectively. If you want to have more fields, you need to add them to the getReplacements function. This replacement is done using a regular expression, so admittedly you could do really fancy things here (or shoot yourself in the feet, heh!).

And the personalise* functions aren’t the most DRY you’ve ever seen, but I figured that they were simple enough and I really didn’t need to refactor them to use iterators and what not. Sometimes simple enough is enough…

Regarding Apps Script, I found the distinction between the document and the file a bit tricky at the beginning. The DriveApp only cares about files and folders, and not whether they’re documents or spreadsheets or whatever. So that’s what’s used in clone(). But then to open the document you need the id of the file clone, and use that with the DocumentApp. But this is my only minor gripe, and I think once you understand, it becomes a non-issue.

The documentation is quite decent, although a few more code samples would be nice. For example, it doesn’t tell you how to send multiline messages; I figured I could use \n because I’m an old dev, but I wonder whether someone who’s not used to ‘low level C’ would have come up with that idea naturally.

I also can’t stop being amazed that they really use a floppy disk as the icon for saving. I wonder if young people know what that is; I haven’t even owned a computer with a floppy disk in more than 10 years!

I haven’t figured out how not to use their online editor, but this script was simple enough that I didn’t need to. If I did more work with App Script, I would try to figure out things such as how to version control, etc.

All in all this is quite enjoyable to use, and once you start looking at the API you wonder what else could you automate… ah, the possibilities!

Splitting PDFs into single pages using Automator

Every quarter at Mozilla managers get an automatically generated PDF report that we need to split into single pages.

The first time, I did that with Preview.app… dragging each page out. Slow, but reliable! It was only two pages, so it’s doable.

The second time, I automated it, using THE AUTOMATOR!

This app was introduced in 2005 in Mac OS X and I’ve never actually used it for good. Well, I’m glad I finally found some good use for it, as these reports are getting longer and I would rather spend my time on something more interesting than dragging thumbnails around 🙂

So, instructions if you don’t like watching videos:

  1. Open Automator
  2. Create a new workflow
  3. Select Library… Ask For Finder Items… double click to add it to the workflow
  4. Select PDFs… Split PDF
  5. And RUN IT, using the bottom on the top right!

It will ask you for the PDF you want to split, then it will split it in pages and place it on the Desktop.

You could get fancier, and you probably still need to rename the individual files, but I haven’t researched that bit yet.

Happy (and fast) splitting!

Google Calendar shortcuts and tricks

Since I started managing people, I spend a lot of time looking at my calendar!

Here’s some shortcuts I use a lot:

  • view:
    • m: month view
    • w: week view
    • d: day view
  • navigation:
    • n: next (if you’re in month view, it jumps to next month, if you’re in week view, it jumps to next week, etc)
    • p: previous
    • t: jump to today

I have the calendar pinned on the first tab, so ⌘+1 always brings me to it (I think it’s CTRL+1 in Windows and Linux).

The first thing I do in the morning is to look at what’s in for today–this way I make sure I don’t forget to attend important meetings because I get deep down into something else and then I forget. So I will press “d” and “t” to get the calendar to ‘day view’ plus ‘today’.

I use the other shortcuts to quickly flip between dates when I’m booking new events or to find what is upcoming.

There are also shortcuts to create and edit events but I found them not very useful as you still have to modify the time and date of event anyway-I find it easier to use the mouse to schedule this.

Organising files in Google drive

We use Google Drive at work and for a particular project we ended up in a situation where there were multiple documents related to it, but not an easy way to have “links” to all of them on the same place.

One solution would be to create yet another document and link to the documents. Another one is to bookmark documents in your own browser—but the issue is that if someone wants to collect all the links together in their computer, then they have to bookmark the stuff individually as well.

But there’s an easier way: you can make a folder in Google Drive and add any file to it (whether it’s owned by you or not). Then you can share the folder, and voilà! everyone has access to the collection of documents.

To make a folder in your drive, first go to your google drive. Click NEW… folder. Give it a name, for example: Magnificent folder.

Then go to the document you want to add to the folder, click on the File… menu, select Add to my drive. A little pop up will show up, click on the Organize link… Choose Move to folder, and choose the Magnificent folder you created before.

If you refresh the folder in your drive, the document should be present there.

To share the folder, click on the down-facing arrow on the right hand side of the name of the folder. This opens a drop down menu and you can select “Share…” to open the usual Google docs interface to share stuff with people.

Done!

I’d also suggest that changing the folder view from grid to list, showing the document titles, might be useful in many cases, as the document titles tend to get truncated way too easily.