Photo by Joshua Coleman via unsplash

Backup Email Octopus Lists with Google Script

By Jonathan Poland

I copy the emails from the spreadsheet into the BCC and send to my subscribers

Anybody who has ever had to send regular emails to more than a handful of people understands the need for tools like mailchimp, sendgrid, moosend, mailerlite, etc.  These tools make it really easy to send emails and manage email lists in a legally appropriate way.

I recently had a client looking to modernize their current workflow using google sheets.  They had a single spreadsheet with all of their mailing list subscribers.  When they wanted to email their list, they would copy out all of the emails from the email column and put those on the BCC and send their email.  Given that Gmail limits recipients, I'm not sure how well it really worked for their list of about 900 subscribers.  They say that worked ok, but was a tad tedious, error-prone, and not easy for users to opt-out of.  There was a lot of room for improvement!

Mailchimp

The client already had a relationship with mailchimp, as their web host had some integration of some sort.  I signed up for free account to test.  Sent only to emails I own as a test and was immediately banned.  I tried support, and got nowhere.  In fairness, I believe it was due to the fact I randomly used a Spongebob image in my test email.  However, the notification simply says I violated terms of service, and isn't specific about how.  I never was able to reach a real live person and get the account back.

Sendgrid

Next I tried sendgrid.  I'm a pretty happy customer of their parent company, twilio, and their free tier fit my list size for this purpose.  When you sign up they ask you a few questions about your business and do some sort of automated checking.  Whatever that check is, I failed.  My account was dead in the water there.  I tried contacting support and got nothing.

Others

I personally have used moosend and mailerlite in the past.  However, their free tiers weren't big enough for my purposes in this case.

Email Octopus

Email Octopus provides a basic product with a generous free plan of 2500 contacts.  The fact it is simplier was an added bonus for this particular client, as they didn't  need a ton of bells and whistles to add confusion.

I was able to create an account and get it activated with no issues.  I created a simple list with emails I control and was able to send a campaign with no issues.  Success!  

Given previous experience and having accounts terminated, I wanted to figure out how good the client's list of emails was.  The last thing I want is to get this all setup and transitioned over, and then have their account get banned after their first email because 10% of the recipients bounce.  To have some idea of the quality of the list, I ran it through the free analysis at never bounce.  

I did run into some user errors on my part during setup, and managed to use live chat with support and got things quickly cleared up.  The ability to talk to a live human is a HUGE perk.  

Having found a provider, importing my list, and confirming basic functionality, I was happy.  Now to move on to other concerns as the client migrates.

Backup Options

The client had 17 fields in their spreadsheet for each contact.  If they were going to use Email Octopus to replace that spreadsheet completely, I needed to carry them all over.  But if they put everything only on Email Octopus, there are some risks:

  • What if for some reason Email Octopus removes or suspends their account?
  • What if their account is hacked?
  • What if they accidently mess up their list while editing?
  • What if Email Octopus closes down or massively changes pricing?

For all these reasons, I wanted to backup the work done in Email Octopus to somewhere else.

Manual download

You can export your lists to CSV with the Email Octopus UI.  This works, but what always happens eventually is you forget and right after that is exactly when you'll need a backup.  Automatic is always the best solution.

Skyvia

Skyvia advertises the ability to do backups on a wide range of different products, including Email Octopus.  However, when I tried it, it did not export all the various extra columns in my list.  You only get the following, not the other 15 or so I have..

The options available in Skyvia

Zapier

Zapier has some integration with Email Ocotpus, but it's very limited and doesn't support lists.  It only supports adding things into Email Octopus from other places.

Google Scripts

I've used Google Scripts in several other projects and find it's a great place to host simple pieces of code without worrying about infrastructure.  For this project, I use the EO API to query the configured lists and export them to a new Google Sheets spreadsheet.  The spreadsheet will have one sheet per configured EO list and each list will have all the fields set in EO.  The code is available on Github.

Here's how to use it:

  1. Enable the EO API and create an API key.  Make a note of your key.
  2. On google scripts, click the New Project button.
  3. This creates a new Untitled Project in the code editor.  You can rename it if you want by clicking on the name at the top of the page, I call mine EmailOctopusBackup.
  4. Remove the existing code, and copy/paste the code into the editor.  Be sure to click the little disk icon at the top to Save.
  5. There is a button near the top right that says Use Classic Editor, click that.
  6. It's bring up a survey asking why, if you want tell them it's because you need project properties.
  7. Now in the old editor, go to File -> Project Properties.
  8. This opens a new modal window, click on Script Properties.
  9. Click the blue Add Row link.  Enter EO_API_TOKEN for the Property and your API key from step 1 as the value.
  10. Click the blue Save button, the modal window will close.
  11. You can now revert back to the new editor, click the blue Use New Editor button.

You need to run things once to get permissions setup:

  1. Near the top of the editor, select backupAllLists from the dropdown of functions.
  2. Click Run.
  3. It will bring up a window asking for permission to modify sheets, contact external services, and a few other things.  Agree.
  4. Assuming it all runs well, you should see a new spreadsheet in sheets.
  5. Just to be safe, repeat this procedure for the removeOldBackups function.

That's it for the code, now schedule it to run:

  1. Click on the little stop watch icon at the far left menu to access the Triggers settings.
  2. Click the blue Add Trigger button in the lower right.
  3. A modal that pops up.
  4. Select backupAllLists as the function to run.
  5. Select Head and Time-Driven.
  6. Select the approropriate time for it to run.  I chose Week Timer, Saturday, 4-5am.
  7. Keep in mind that every run will create a new spreadsheet file, so you don't want it to run too often!
  8. Click the blue Save button.

Ok, now schedule it to autoremove old backups:

  1. Click the blue Add Trigger button in the lower right.
  2. Select removeOldBackups as the function to run.
  3. Select Head and Time-Driven.
  4. Select the approropriate time for it to run.  I chose Week Timer, Wednesday, 5-6am.
  5. Click Save.
Final Trigger Configuration

Code Notes

The code is relatively self explanatory, but there are a few things to note

const EO_KEEP_DAYS = 365

Modify this setting near the top if you want to change how many days old backups are kept for.

If you don't want to mess with the old editor and script properties, you can just hard code your EO_API_TOKEN.  Change this line:

const EO_API_TOKEN = PropertiesService.getScriptProperties().getProperty('EO_API_TOKEN')

to 

const EO_API_TOKEN = <your hardcoded API key>

That's it!  Happy hacking!