Introduction

Do you have a list of a few hundred email recipients in a spreadsheet? If you want to send a newsletter, for almost no money and no effort, read on.

If you’ve got squillions of users in your recipient list, spreadsheets are useless. Skip this blog, check out our awesome partners and spend the five minutes you’ve saved feeding your cat, throwing a ball for your dog, or going for a very short walk.

A newsletter brings forth an idea

A while back, I shared a tool for “sending scheduled mailings simply with SparkPost” (here). You need to be comfortable with the command-line, confident with CSV files, and ready to run a Python script. I hear you thinking:

“Command-line – no thanks! I have my list right here in a spreadsheet. Why can’t I just send it from there?”  

I love Python, but it’s a bit .. well .. clunky. Can we have something with a shiny, happy clicky user interface? Well .. our partners do that. My good friend Tom Mairs is working on a proper thing with Front End / Back End code, SQL Databases and all, but that’s his story. In the meantime, we have a Very Important and Interesting Newsletter to send, so let’s find the quickest route to success.

I’ll tell you what I want, what I really, really want…

Like all good projects, this started with a chat with my “customer” who needs this. We briefly looked at the commercial marketing tools. These would be nice if we scale up later, but the email was already written and loaded in a SparkPost template. We were going to:

  • Upload the recipients into a SparkPost Recipient List object;
  • Trigger the send of the stored template and the stored recipient-list via Postman.

All he wanted was a way to get the recipients from a spreadsheet into SparkPost. Together we looked at what the expected JSON-format substitution data looks like. Would you like us to format your spreadsheet substitution data into JSON?

Nope!

The data was already in nice columns, one item per column, and it had to stay like that. We lose some flexibility to create fancy nested JSON objects per-recipient and stuff.

“Nah, we’ll never need that!”

We could create something to munge your spreadsheet data into the expected JSON format? Then you can easily upload using the “CSV upload” feature built into the SparkPost UI.

“Nope – the list is going to have last-minute additions, and we’re in different time-zones. I need something I can use myself.”

We’d eliminated everything else and come down to what we would actually build. Just like a fine Victorian-era fictional sleuth!

When you have eliminated the impossible, whatever remains, however improbable, must be the truth.

~ Sherlock Holmes

Spreadsheets. Love ‘em or hate ‘em, they’re everywhere. The two obvious choices are Excel (with VBA), and Google Sheets. We are a G Suite shop as it is excellent for online collaboration. Turns out that Google Apps Script is nice, with JavaScript-like notation and support for building little UI extensions, such as new menus and dialog boxes. A few experiments later, I felt confident I could make something we both might like.

The requirements

  • Take the Google Sheet we already had, with data items one per column, and upload it to a SparkPost recipient list. The data will look like this:

  • The columns email.address and email.name will always be present, as these are fundamental to how email gets sent by SparkPost. We color these orange to discourage people from messing with them.
  • Other column headings (which will become the substitution_data) should be changeable and extensible by the user. We color those green.
  • The list ID, name, and description must be present, but the values should be user-changeable so they can do trial runs, make more lists, and so on, like this:

  • Nice-to-have: see the current recipient lists stored on SparkPost, with names & sizes, right there in Google Sheets. Of course you can see this on SparkPost too, but this gives confirmation that you’re working on the right list in the correct account!
  • There’s no need (today) for list downloads back from SparkPost to Google Sheets; we envisaged a one-way process. This would be a possible later addition.
  • There’s no need to trigger the send from the spreadsheet; we’ll do that via Postman. This would be another possible addition.

A requirement I put in: we should follow good practice for API key management. No-one should be able to mess with anyone else’s SparkPost account, unless they have specifically granted an API key to allow it. Put another way, I want to be testing the same Google sheet with my key, on my sandbox account. Other users will be working with their keys, on their account. We should not see each others’ keys, even if we are sharing and working on the same spreadsheet!

Is this dark magic? Nope – the Apps Script Cache Service supports private caches per user, independent of the spreadsheet contents. We use that for the API keys. This has the minor annoyance of expiring any time within a set period (say 1 hour). We make a virtue of necessity by encouraging users to keep their API keys in a key-safe such as 1Password.  They will have to re-enter their key at the start of each session.

The SparkPost documentation says “The Recipient List API operates on lists as a whole and does not currently support management of individual recipients”. This little project helps you a bit, by giving an easy, editable grid view of recipients, but the upload is always of the “whole list”.

OK, but what does it look like?

Sadly, we’re not going to have dancing unicorns and rainbows on the UI of this project. But we might have some just-about-OK menus and dialogs:

The sheet gains a new “Recipient List” menu, with items:

  • Set API Key
  • View Recipient Lists
  • Upload Recipient List

In the body of the sheet, you leave the pale orange bits alone. The green cells denote the substitution_data field names: these you can extend rightwards with new fields, or change field names, as you wish. The white cells are where you add your data.

Note also the drop-down selection of SparkPost US or EU service (cell B4) – the tool works with either region.

In action

The “Set API Key” dialog pops up like this:

Copy/paste your key. It disappears inside the private cache, and you’ll never see it again! Seriously, keep a copy in a key-safe.

“View Recipient Lists” displays your lists, with id, name, and total recipients. At the top, it shows the total number of lists, and a redacted form of your key, so you can tell the tool is fetching from the account you expected.

“Upload Recipient Lists” displays the ID, name, and the results coming back from the SparkPost API, i.e. total accepted & rejected recipients.

Press “Ok” to clear the dialog and return to your sheet.

A list is a thing

As we saw earlier, SparkPost treats existing lists as entire objects; unfortunately, you can’t just add more recipients to an existing list. SparkPost has a useful safety feature, protecting you from overwriting a list that already exists.

For simplicity, if you get upload errors, it shows the whole JSON error message text received. I keep track of updates by adding a version number to the end of my list-ID.

Great, that’s just what I need, how do I get it?

Grab the script source and the blank spreadsheet file from this Github repo.

Import the .XLSX file into your Google Drive, then File / Save As type “Google Sheets”. You can now remove the .XLSX version from your drive.

Go to Tools / Script Editor. A code editing window opens.

Open the file Code.gs in the repository, and view in Raw mode. Copy and paste the code into the Script Editor, overwriting the myFunction() stub content that’s already there. It should look like this.

Give the project a name (top left), and save it.

Go back to your Sheet tab, and press refresh. After a few seconds, the new “Recipient List” menu will magically appear:

The first time you run the tool, you’ll be asked to authorize. 

Review the code (or have your IT department review the code) to ensure you’re happy to grant these permissions.

Now set up your substitution_data and data rows as you wish. Set your API key and upload.

Rough guide to performance

Remember, this is not a tool for handling large lists! Here’s some informal tests done with various list sizes, with and without substitution_data, to give you a rough guide to performance.

List size Upload time with
email.address and email.name only
Upload time with
email.address, email.name, plus 100 bytes of substitution_data
1000 5s 5s
10,000 17s 17s
50,000 1m11s 2m33s
100,000 2m34s Error “Request Entity too large”

I ran into the SparkPost API upload list size limit on my final test. Google Sheets also has an upper limit of 500,000 cells, placing a practical limit on list size anyway. Lists up to 50,000 should be fine unless you have a lot more substitution_data than we did.

Test in progress…

Completion screen

A peek at the code internals

There are around 250 lines of code and these should be pretty self explanatory. Here’s a summary of what each function does.

Function Purpose
onOpen Establishes the user interface menu option
getConfig, setConfig, setConfigWithExpiry, setApiKey, validKey, redactedKey API key management
getSparkPostURL handles your US / EU service selection
viewRecipientLists, htmlTableFromJSON, html, shadedTableStyle, htmlTable The “View” feature
uploadRecipientList, getData The “upload” feature

Summary

This project manages and uploads tiny recipient lists (a few thousand recipients) easily, directly from Google Sheets. We got our newsletter sent on time, and the crowd went wild!

I hope you enjoyed this project as much as I did, and that you find it useful. Google Apps Script is powerful, I hope to find more interesting problems to solve with it.

What do you think – are we crazy for trying this? Let us know! You can reach us at @sparkpost on Twitter.

~ Steve