Where we create and personalise our emails
In <a href=”Link to Part 1”>Part 1</a> we looked at how to set up our database, convert it to a manageable format and how the createDraft function works. This time we will use the same createDraft function but instead of giving it parameters ourselves, we will automate getting the information from our previously created CSV file.
The full code is available at my Github account:
Create Draft
We have to create a personalised draft for our users. You have to understand this part of the app so I will go through each line and explain what it does. Create a new script file in the project to get started.
Let’s create a function which takes in 1 argument (data) to perform our tasks, we call it makeDraft:
function makeDraft(data) {
}
Between { } we start writing our code (lines starting with // are comments written by me so whenever me or someone else looks at the code, they will know what certain parts do):
We first create a variable (called “data”) which gets the data from our CSV file. Javascript allows you to perform multiple operations in quick succession by putting a dot and a new operation for each one. Here we call DriveApp, get a file from it using .getFileById(“xxxxxxxxxx“), get its content with .getBlob() (blob is basically just raw data), and finally convert our blob to a string (which is a series of characters) as seen below:
//Get file content
var data = DriveApp.getFileById(“xxxxxxxxxx“).getBlob().getDataAsString()
We want to take the data from our Sheet1.csv so we right click on Sheet1.csv in our drive, then on “Get shareable link” and it automatically copies the link to our clipboard. Then replace “xxxxxxxxxx” with our link and delete the everything from the link other than the ID. Don’t forget the quotation marks, they have to stay there.
The best thing about having such a variable is that anytime we write data, our app will do everything after the equation sign.
Tip: To see how your variables look like, use Logger.log(varname). In this case: Logger.log(data). After running the app like this, go to View → Logs to see it.
This is how our data will look like:
There’s a problem with it: even though we see that there are multiple lines, our app has no way of knowing it so we have to let it know somehow. Imagine it this way: right now there is one single line in our data made up of values separated by commas and enters. We want to have a matrix so we first tell our app that whenever it sees an enter, take it as a new row. This way we will end up with n number of rows. We create another variable called allRows and use the command .split() to do this. The code for enter is \n so this will be our argument.
//Split into lines
var allRows = data.split(“\n”)
It will look the same as before but the app will know that enters mean new rows:
Now we define our templates. I’ll use a single subject for each of my templates: create a variable with your subject.
var subject = “Please provide feedback”
Feel free to use any text editor to write the messages. Enters are again not recognised as characters so I advise you to write it with enters first and then replace each enter with \n. Leave unique words in places you want to customise later, see the example for suggestions. Go back to your script and define a list (it’s a list of items in order), then paste your messages separating them with commas like this:
var messages = [
“1Hi $name,\n\nLorem ipsum dolor sit amet LINK, consectetur adipiscing elit. Fusce sed.\n\nThank you,\nIstvan\n– \nIstvan Erdo\nCustomer Relations @samebug\nhttps://samebug.io”,
“2Hi $name,\n\nLorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce sed.\n\nThank you,\nIstvan\n– \nIstvan Erdo\nCustomer Relations @samebug\nhttps://samebug.io”,
“3Hi $name,\n\nLorem ipsum dolor sit amet LINK, consectetur adipiscing elit LINK. Fusce sed.\n\nThank you,\nIstvan\n– \nIstvan Erdo\nCustomer Relations @samebug\nhttps://samebug.io”,
“4Hi $name,\n\nLorem ipsum dolor sit amet LINK, consectetur adipiscing elit. Fusce sed.\n\nThank you,\nIstvan\n– \nIstvan Erdo\nCustomer Relations @samebug\nhttps://samebug.io”
]
It’s important to write them in order: the template denoted number 1 should be the first, 2 should be second, and so on. This will have significance later in this section.
Each of the emails will have the first name of the person, some of them will have no LINK, some have 1 and the rest have 2. I added a number to the beginning of each message to be able to see the which templates the app used which will help later when testing the drafts. Feel free to delete them after you made sure the right template is used in each case. Our log will show the results:
After this we should go through each row (called looping in programming terms) and do something with each row. Go ahead and define a new variable i which will take on the value of 0. We will take allRows and perform repeatedly the same thing on them using .map(). Map will take a function() as an argument. This function will perform the tasks we need it to and we give it 2 arguments, line and key:
// Loop through all rows
allRows.map(function(line, key) {
})
Write the rest of the code in this section between { }.
The first line of our data contains only the titles so we get rid of them by writing:
if(key === 0) return
Since computers start counting at 0, the first row will be the 0th item.
Now let’s define some more variables which we will use later. We’ll use .trim() to get rid of the enters from the end of each line. This is some precaution for later. We’ll also use .split() on our lines again to finish building the matrix. Define a variable called cell which will be equal to our line argument split at the commas without enter at the end.
var cell = line.trim().split(“,”)
We now create a new variable for each of our columns:
var userId = cell[0]
var displayName = cell[1]
var name = cell[2]
var email = cell[3]
var templateNumber = cell[4]
var search = cell[5]
Make sure you change these variables to apply to your case and be careful with the numbering: the first column is the 0th, the second is the 1st and so on.
We need one more variable because, as you can see in the table, we have both display and full names and we want to use the first name from the full name if there is one and if the full name is empty, we should use the display name. Remember that we called Full name as name and Display name as displayName.
var hasName = name && name !== “”
We now have a variable called hasName which is true if it has a name variable and it’s not empty.
Then write a logical expression which will first check if hasName is true, in which case it will split the name (at the space) into 2 words: first and last and it will replace displayName with the first name. The last expression in the code below works like this: is displayName equal to fullName? If yes, use fullName[0] (we split the name into 2 words, the first of which is at the 0th place). If no, use displayName.
// Use first name as displayname
if(hasName) {
var fullName = name.split(” “)
displayName = fullName ? fullName[0] : displayName
}
Now our displayName variable will be the first name of the user if they filled in their Full name and Display name is they haven’t.
Fun fact: A lot of our users at Samebug are Chinese and their full names often look like 林长安. In such cases there’s no space between the first and last name. In China, people often use the full name when addressing someone, even if they want to be friendly. It turns out that not separating Chinese names made our app meet cultural standards as well 🙂
We next specify some additional conditions to account for special cases.
If the person has no email, do nothing:
//Return if no email
if(email === “NULL”) return
Sometimes there is an extra empty line at the end of the file which can lead to an error, so if there is no userId, do nothing:
//Return if no user ID
if(userId === “”) return
We’re nearly done with our draft maker!
Remember how we defined the templates of the emails at somewhere in the beginning of this chapter? Now we’re going to define which template to use and replace the words: $name and LINK. We create a variable called template which will be equal to a specific item from the messages list. We already have the number of the template stored in templateNumber variable but we need to make sure it’s a number, so we multiply by 1 and, because counting in the list starts from 0 and our numbers are from 1, take away 1. Then us .replace(), the first argument of which is what you want to replace and the second is what the first should be replaced with. Use quotation marks for a single item to be replaced and /yourword/g to replace all words:
//Set and personalise messages
var template = messages[templateNumber * 1 – 1]
.replace(“$name”, displayName)
.replace(/LINK/g, search)
Finally, we have the variables: email, subject and template, so we can create our draft:
GmailApp.createDraft(email, subject, template)
Make sure you have no mistakes in your code, here is the complete script for this section:
function makeDraft(data) {
// Get file content
var data = DriveApp.getFileById(“xxxxxxxxxx“).getBlob().getDataAsString()
// Split into lines
var allRows = data.split(“\n”)
var subject = “Please provide feedback”
var messages = [
“1Hi $name,\n\nLorem ipsum dolor sit amet LINK, consectetur adipiscing elit. Fusce sed.\n\nThank you,\nIstvan\n– \nIstvan Erdo\nCustomer Relations @samebug\nhttps://samebug.io”,
“2Hi $name,\n\nLorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce sed.\n\nThank you,\nIstvan\n– \nIstvan Erdo\nCustomer Relations @samebug\nhttps://samebug.io”,
“3Hi $name,\n\nLorem ipsum dolor sit amet LINK, consectetur adipiscing elit LINK. Fusce sed.\n\nThank you,\nIstvan\n– \nIstvan Erdo\nCustomer Relations @samebug\nhttps://samebug.io”,
“4Hi $name,\n\nLorem ipsum dolor sit amet LINK, consectetur adipiscing elit. Fusce sed.\n\nThank you,\nIstvan\n– \nIstvan Erdo\nCustomer Relations @samebug\nhttps://samebug.io”
]
// Loop through all rows
allRows.map(function(line, key) {
if(key === 0) return
var cell = line.split(“,”)
var userId = cell[0]
var displayName = cell[1]
var name = cell[2]
var email = cell[3]
var templateNumber = cell[4]
var search = cell[5]
var hasName = name && name !== “”
// Use first name as displayname
if(hasName) {
var fullName = name.split(” “)
displayName = fullName ? fullName[0] : displayName
}
//Return if no email
if(email === “NULL”) return
//Return if no user ID
if(userId === “”) return
//Set and personalise messages
var template = messages[templateNumber * 1 – 1]
.replace(“$name”, displayName)
.replace(/LINK/g, search)
GmailApp.createDraft(email, subject, template)
})
}
If there are no more mistakes, let’s run it and look at the result.
And result is the following: 4 drafts, each with different template, person with NULL email not included, all $name and LINK replaced with data from the user. Don’t worry about the links not being formatted as links (underlined, blue), after the emails are sent, Gmail will automatically format them:
Conclusion and what’s next
We’ve come a long way this time: we’ve created a fully automatic personalised draft maker which takes data from our CSV file. Next up is the final part: <a href=”Link to Part 3”>Part 3</a>, where we will make some final touches to our app like merging a script together so we don’t end up with so many CSV files and creating a function which will put a timestamp into our database to the Letter Sent Column.
