Where we set up our database and convert it to a manageable format

Sending out emails is done manually if you need to write only a few. For marketing and other purposes (e.g. password reminder) it is better to use an email marketing tool. However, sometimes you need to send out very similar, yet personalised emails and it just doesn’t make sense to automate it with an email marketing tool. It might also be the case that sending them out manually on a regular basis would eat up too much time.

I found myself in this situation: I wrote emails manually each week to about 20 different Samebug users which took 2-3 hours every week because I had to make small changes to every letter and manually look at data to decide which of my templates I would use. Even though I had little experience with programming I set out to automate this task.

What to expect

My objective was to have some kind of code which takes the contact info from Google Sheets and makes a draft in Gmail prefilled with the recipient, the subject, the template, the name of the user and a personalised link. I wanted to retain some kind of human element: review the email before sending it. In this article I’ll show you how to build this application even if you’ve never programmed before.

This will be a 3 part series:

1. Where we set up our database and convert it to a manageable format
2. Where we create and personalise our emails
3. Where we merge our scripts and add timestamps to our database

Note 1: Gmail limits the number of emails you can send to about a couple hundred a day, if you want to send more emails I would really suggest using an email marketing tool.

Note 2: Everything is case sensitive so be careful with this!

Note 3: Every bracket opened has to be closed or there will be an error.

Note 4: I’ll use indentation so that if you copy-paste the code, it will be more readable (i.e. you’ll be able to see the structure)

I added the full code to my Github account for reference at:

We will use:

• Gmail to send emails
• Google Sheets for database
• Any text editor to write the template
• Google Apps Script to write the app

Setting up our tools

We first have to create our database so add a blank spreadsheet to your drive and fill it with the information you will need to write a personalised email. For me it looks like this:

Please note that the data above is only for demonstration purposes. Certain cells are empty or NULL because either the user didn’t put anything in that field (i.e. there’s no data) or we will automatically fill that in later.

User ID: The unique number associated with the user.

Display name: The name the user chose to display.

Full name: The full name of the user, this is empty most of the time in my case as users don’t like to provide their full names.

Email address: Self-explanatory, sometimes the users don’t provide an email address (later we will put an X to the Letter sent column if the email is NULL).

Template: We will use one of 4 templates for every user based on their activity. The templates are based on the level of engagement. In your case, you should decide yourself which template to use for each user and also the number of templates you want to use.

Search: On Samebug, users search for error messages to find a solution to their programming errors and I wanted to have a personalised link which directs them back to their search.

Letter sent: The date when we created the email draft for that user. We will fill this in automatically with our app.

Next we should set up Google Apps Script (I will call it Gscript from now on). This tool works like magic when we want to add some extra functionality to Google applications or when we want to use them together. It’s best feature is that it has built-in commands to do tasks in Google applications. Fortunately, it is written in javascript, a programming language, so one only has to look at javascript tutorials and its documentation to be able to automate a lot of tasks.

Open Gscript by going to script.google.com

When you first open the site, some introductory info is shown, begin by clicking on “Start Scripting”. It should look like this:

Generate draft email

Next we learn how to create a general draft. Gscript has a very simple code to create drafts:

createDraft(recipient, subject, body)

The words in the brackets are called arguments. You should replace each argument with your own “text” or variable (element that is liable to change). When you run your code, Gscript will look at createDraft and put everything into its respective place (1st word into recipient line, 2nd into subject line and 3rd into the body of the email).

To see how this works simply write the following line between the { }:

GmailApp.createDraft(“[email protected]”, “Hello draft”, “I’ve created a draft”)

You have to put the arguments between quotation marks as you have written a text, without the quotation marks they would be viewed as variable and an error would be shown since we haven’t created such variables. The GmailApp in the beginning of the line tells Gscript to look for the function in the GmailApp.

Save your project, name it and run the code.

You will need to give authorisation for the app in order to run it so click on Review Permissions, choose your account to use the app with, click on “Advanced” in the bottom left corner and then on “Go to Draft (unsafe)” (Draft is my project name).

Finally, click on “Allow” and check your email drafts. Your app has written a draft:

Convert our data to CSV

For the next step we have to convert our data from the spreadsheet to a more manageable format: CSV. CSV stands for Comma Separated Values: a text file where each value is separated by commas and every line is written in a new row. Create a new script file and delete its content to get started.

You don’t need to understand this code to make your program work. I myself have copied the code written by Michael DeRazon and made only minor modifications to work for my case. If you are curious about what certain parts do, read Michael’s comments after each //. You can go ahead and copy-paste the following code:

function saveAsCSV() {

  var ss = SpreadsheetApp.openById(“xxxxxxxxxx“);

  var sheets = ss.getSheets();

  var sheet = sheets[0];

  // append “.csv” extension to the sheet name

  fileName = sheet.getName() + “.csv”;

  // convert all available sheet data to csv format

  var csvFile = convertRangeToCsvFile_(fileName, sheet);

  // create a file in the Docs List with the given name and the csv data

  DriveApp.createFile(fileName, csvFile);

}

function convertRangeToCsvFile_(csvFileName, sheet) {

  // get available data range in the spreadsheet

  var activeRange = sheet.getDataRange();

  var data = activeRange.getValues();

  var csvFile = undefined;

  // loop through the data in the range and build a string with the csv data

  if (data.length > 1) {

    var csv = “”;

    for (var row = 0; row < data.length; row++) {

      for (var col = 0; col < data[row].length; col++) {

        if (data[row][col].toString().indexOf(“,”) != -1) {

          data[row][col] = “\”” + data[row][col] + “\””;

        }

      }

      // join each row’s columns

      // add a carriage return to end of each row, except for the last one

      if (row < data.length-1) {

        csv += data[row].join(“,”) + “\r\n”;

      }

      else {

        csv += data[row];

      }

    }

    csvFile = csv;

  }

  return csvFile;

}

Make sure you replace “xxxxxxxxxx” with your own spreadsheet ID. You can get this by opening your spreadsheet and copying the 44 character code from the link as seen below:

Save and run your code!

If you check your drive folder, you can see that the app has created a new CSV file in your drive.

Conclusion and what’s next

In this part we’ve set up our database, seen how the createDraft function works and converted our data into CSV. In <a href=”Link to Part 2”>Part 2</a> we will go ahead and create a personalised draft for each user which is the most important section in the series.