soledad penadés

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! :-P

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!