Month: January 2021

A couple of years ago, I shared a workflow that we still use to auto generate documents that we use each week. A few months ago, I shared another workflow that showed how I automated printing our weekly Planning Center Online paperwork.

I decided recently that I was tired of still having to manually print these weekly “talking points” documents, while having my Planning Center paperwork fully automated. So, I took a few minutes and wrote a new Google Apps Script to help with this.

Here is what the script does:

• Searches a specific Google Drive folder for all subfolders with files that match today’s date (the script will run on a weekly trigger)
• If the file is a match, it opens the file as a PDF and stores the binary contents in a variable
• An upload request is made to the Dropbox API with that binary data and a file name
• Dropbox saves the file into the “Automated Printing” folder
• Dropbox then syncs the file to the local computer (Mac)
• The local Mac is configured with a Folder Action that automatically prints any files placed in this folder
• After the Automator Folder Action prints the file, it removes the file

Here’s how you set it up:

First, you want to create a new Dropbox “App”. Go to dropbox.com/developers and click “Create apps”.

Then, you need to fill out these fields:

1. “Choose an API”: Scoped Access. It’s your only choice.
2. “Choose the type of access you need”: I chose “Full Dropbox” because I already had a specific folder set up in the root of my Dropbox. If you’re setting up the Automator Folder action for the first time, you could probably keep the scope within “App folder”.
3. “Name Your App”: Give it a meaningful name. It does have to be unique across all of Dropbox, for some reason, so if you get an error here, just add something unique to you.
4. “Choose the Dropbox account that will own your app”: If you have personal/business accounts linked, you’ll need to choose the account that owns the app. I’m using a business account for this, so I chose that one.

On the next page, choose the “Permissions” tab.

Then give your app “files.content.write” access.

Now back on the Settings tab, generate a new Token and set the Expiration to “No expiration”.

This will generate a Token key which you will use within the Google Apps Script in the next steps.

Now in Google Drive, click “New”, go down to “More”, and choose “Google Apps Script”. Google Apps Script is essentially Javascript, so it’s super easy to use.

Give your project a helpful name.

In the code section, paste in my script below:

/*
EDIT THESE VARIABLES FOR YOUR SETUP
*/
var accessToken = "token"; //Dropbox App Access Token
var rootFolder = "folderID"; // Google Drive Root Folder where these files live
var dropboxPath = "/Automated Printing/"; //Dropbox Folder Path to place file in
var numberOfCopies = 2; //the number of copies you want per file

//Nothing to edit below

function myFunction() {
  var dtDate = new Date();
  const monthNames = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];
  var strDate = monthNames[dtDate.getMonth()] + " " + dtDate.getDate() + ", " + dtDate.getFullYear();
  var mainFolder = DriveApp.getFolderById(rootFolder);
  var subFolders = mainFolder.getFolders();
  while(subFolders.hasNext()) {
    var subFolder = subFolders.next();
    var files = subFolder.getFiles();
    while(files.hasNext()) {
      var file = files.next();
      var fileName = file.getName();
      if ((fileName.indexOf(strDate) > -1) && (fileName.indexOf(".pdf") == -1)) {
        //this is a file we want to print
        Logger.log("Generating PDF: " + file.getName());
        for (let i = 0; i < numberOfCopies; i++) {
          sendToDropbox(file.getName() + ".pdf", file.getAs('application/pdf'));
          Utilities.sleep(15000); // wait 15 seconds before doing the next file, so that Dropbox has time to sync the file, the Automator can print the file, remove it, and close out
        }
      }
    }
  }
}

function sendToDropbox(fileName, fileBlob) {
  var parameters = {
    "path": dropboxPath + fileName,
    "mode": "add",
    "autorename": true,
    "mute": false,
    "strict_conflict": false
  };

  var headers = {
    'Authorization': 'Bearer ' + accessToken,
    'Content-Type': 'application/octet-stream',
    'Dropbox-API-Arg': JSON.stringify(parameters)
  };

  var options = {
    "method": "POST",
    "headers": headers,
    "payload": fileBlob
  };

  var apiUrl = "https://content.dropboxapi.com/2/files/upload";
  var response = JSON.parse(UrlFetchApp.fetch(apiUrl, options).getContentText());
}

Now modify the top section to include your Dropbox access token (the one you generated earlier), the Google Drive folder ID (the folder ID is in the URL of the page when you open that folder in Google Drive), the Dropbox path to save to, and the number of copies you need for each matching document. In our case, I need 2 copies of each document.

I learned in testing that if Dropbox syncs the files too fast while my Automator folder action is still running, the new files that were added don’t get included in the folder action, and the folder action doesn’t re-run those new files. So, what this script does is it uploads a new PDF for every copy needed, but it waits 15 seconds in-between. This gives Google time to upload to Dropbox, Dropbox time to sync to my local Mac with the Automator action, and Automator time to run its script and print the file and delete it. It’s not very efficient, but the files are not that large.

Now that your script is in place, you need to assign a trigger to it. Click “Triggers” on the left-hand side of the screen:

Add a new trigger. I used the following settings to have it run weekly on Sundays between 6 and 7am. Be sure to target the “myFunction” function as that’s the main one we are using.

You’ll need to create the folder action in Automator. Follow my previous post on how to do this, as the steps are the same. I didn’t have to change that at all!

Here’s a tutorial video if you learn better that way:

I hope this helps you think of ways to automate what you’re doing in Google Drive so you can spend more time on ministry and less on manual tasks!