Using Google Docs and Drive as a PDF template engine with Node.js on Google Cloud Functions
In my non-four-day-a-week-job time I’ve been working on a minimum viable product (MVP) that enables employers and employees in New Zealand to take advantage of a system our tax authority offers known as “payroll giving”. I’ll write more about the platform itself some time later, but right now I’ll talk about my solution to one of the problems I needed to solve: generating a PDF invoice and sending it as an email attachment.
In my day-job I work for a cloud accounting software company, and eventually it will make sense for the platform to use an accounting API to raise and send the invoices. But, while we are piloting with a few customers, paying for a subscription wouldn’t make sense. All I need is a tidy, branded email with a simple PDF invoice attached to it.
I already had a solution for sending email in place with a couple of domains verified on AWS Simple Email Service (SES). The server side for my platform runs in Google Cloud Functions though (I recently wrote about minimalist security for Cloud Functions with Google Accounts), and one thing I hadn’t tried before was calling the SES API from GCP.
I love taking advantage of off-the-shelf cloud software to build business solutions, and I always aim for “scale to zero”, where if no one is using a system, I’m not paying for it. On of my standard approaches is to use Google Sheets as a back end for master data. A spreadsheet has copy and paste, formulas, fill down/right, and a whole lot of ways to make bulk editing much easier. Google Sheets also provides a realtime, multi-user experience with version history. That’s a lot of bang for your buck.
Likewise, Google Docs is a full blown multi-user experience for editing print-ready documents and supports export as PDF. It seemed like that should “just work”.
Templating PDFs with the Google Docs API
A couple of decades back, I used to automate mail merges in Microsoft Word using Visual Basic for Applications. Mail merges use special fields embedded in docs as placeholders for the values. Google Docs do have named ranges in the same style as Sheets, but for some reason they are not accessible from the API. After a bit of experimentation, I decided the best path was a good old find-and-replace by convention. I used angle brackets with uppercase, as its unlikely I’m going to have these collide with anything that isn’t supposed to be a placeholder with an exact-match, case-sensitive find and replace.
The basic method for generating a doc from a template I settled on is:
- Make a copy of the template document in the same Google Drive folder
- Rename this copy to something meaningful
- Run a find and replace for each of the placeholder text elements in the copy
The Drive and Docs APIs follow a similar pattern to the Sheets API that I’m familiar with, referencing documents by their document ID. The ID is a long alphanumeric string that appears in the URL when the document is open. E.g. if the URL when the document is open is:
docs.google.com/document/d/1vCyasrfc-JSPZiGsiHILUaiUyflWGldk7zEilq123456/editThen the document ID is 1vCyasrfc-JSPZiGsiHILUaiUyflWGldk7zEilq123456
A function to copy the template to a new file with a given name using the Drive API looks like this:
const { google } = require('googleapis');
exports.copyDoc = async (fileId, nameForNewFile) => {
try {
const auth = await google.auth.getClient({ scopes: ['https://www.googleapis.com/auth/drive'] });
const drive = google.drive({ version: 'v3', auth });
const response = await drive.files.copy({
fileId,
auth
});
if (response.status === 200) {
const newFileId = response.data.id;
if (nameForNewFile) {
try {
await drive.files.update({
fileId: newFileId,
resource: {
name: nameForNewFile
}
});
} catch (err) {
console.log('Failed to rename the file', err);
}
}
return newFileId;
}
return response;
}
catch (err) {
console.error('Failed to copy doc', err);
}
}Because I’m calling this function from a Google Cloud Function, the authentication is implicit. The template is in a Drive folder that I have shared with edit permissions to the email address of the default service account for my GCP project. The copy is in the same folder, so the same permissions apply. The service account email address is usually the project ID at the GCP app domain. E.g. if the project ID is foobar then the address is foobar@appspot.gserviceaccount.com. At the time of writing, the service account details, including the email, can be sseen in the credentials tab in the GCP project settings.
The find and replace is performed as a batch update:
exports.findAndReplaceTextInDoc = async (documentId, find, replace) => {
let finds = Array.isArray(find) ? find : [find];
let replaces = Array.isArray(replace) ? replace : [replace];
try {
const auth = await google.auth.getClient({
scopes: [
'https://www.googleapis.com/auth/drive',
'https://www.googleapis.com/auth/documents',
'https://www.googleapis.com/auth/drive.file'
]
});
const docs = google.docs({ version: 'v1', auth });
let requests = [];
for (let i = 0; i < finds.length; i++) {
requests.push(
{
replaceAllText: {
containsText: {
text: finds[i],
matchCase: true,
},
replaceText: replaces[i],
},
}
);
}
const res = await docs.documents.batchUpdate({
documentId,
resource: {
requests
}
});
return res;
} catch (err) {
console.log(err);
}
}The batch update is handy, because all of the placeholders can be dealt with in a single Docs API call, e.g.:
const finds = ['<DATE>', '<NUMBER>', '<EMPLOYER>', '<EMPLOYER ADDRESS>', '<AMOUNT PAYABLE>'];
const replaces = ['2020-01-01', '1234', 'Employer Co Ltd', '1 Office Street', String(10,000)];
await findAndReplaceTextInDoc(newFileId, finds, replaces);Calling AWS SES from Google Cloud Functions
There is a “proper” way to use IAM to secure inter-cloud calls to avoid storing long-lived credentials. It involves setting up “domain-wide delegation”. I read a tutorial on it and realised I was straying outside MVP territory: secret management it is. I’d used GCP’s Secret Manager before when doing CI/CD for Cloud Functions based APIs, so I knew it was straightforward.
I created an AWS user with SES sending only permissions assigned:
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "VisualEditor0",
"Effect": "Allow",
"Action": [
"ses:SendEmail",
"ses:SendRawEmail"
],
"Resource": "*"
}
]
}I stored this user’s access key ID and access key secret in my GCP project using the Google Secret Manager. The gotcha here is that the GCP service account for the function needs to be given explicit permission to access each secret — i.e. two secrets means two executions of this kind of command:
gcloud secrets add-iam-policy-binding aws_secret_access_key \
--role roles/secretmanager.secretAccessor \
--member serviceAccount:my-project-id@appspot.gserviceaccount.comThe code to fetch the secret from a Cloud Function looks like this:
const { SecretManagerServiceClient } = require('@google-cloud/secret-manager');
const client = new SecretManagerServiceClient();
exports.fetchSecret = async (name) => {
try {
const [version] = await client.accessSecretVersion({
name,
});
const secret = version.payload.data.toString();
return secret;
} catch (err) {
console.error('Failed to fetch secret', name, err);
}
}Sending PDF attachments with SES
Now I had a way to produce a new Google Doc from a template and a way to call SES to send an email from a Cloud Function. The last step was to export that doc as a PDF and attach it to an email.
The lovely thing about the native PDF support in Docs is that generating a PDF is very easy:
exports.exportPdf = async (fileId) => {
const auth = await google.auth.getClient({
scopes: [
'https://www.googleapis.com/auth/drive',
]
});
const service = google.drive({ version: 'v3', auth });
try {
const result = await service.files.export(
{
fileId: fileId,
mimeType: 'application/pdf',
},
{ responseType: 'stream' }
);
return result;
} catch (err) {
console.log('Failed to export PDF', err);
}
}The return value of this function is a streamable, and thanks to the magic of the amazing Nodemailer library, that stream can be sent right into an email as content for a MIME attachment and transported on SES:
exports.sendMailWithAttachment = async (to, from, subject, body, htmlBody, filename, content) => {
const accessKeyId = await fetchSecret(ACCESS_KEY_ID);
const secretAccessKey = await fetchSecret(ACCESS_KEY_SECRET);
if (accessKeyId && secretAccessKey) {
try {
const client = new AWS.SES({
apiVersion: "2010-12-01",
accessKeyId,
secretAccessKey,
region: AWS_REGION,
});
let transporter = nodemailer.createTransport({
SES: client
});
let info = await transporter.sendMail({
from,
to,
subject,
text: body,
html: htmlBody,
attachments: [{
filename,
content
}]
});
return info.messageId;
} catch (err) {
console.error('Failed to send mail', err);
}
}
}Summary
I now have a solution where the existing functionality of Google Docs means anyone who can use Google Docs can update the invoice template. The Google Workspaces sharing features make administration easy, and there are no additional costs beyond the micro-charges of Cloud Functions!
