István Erdő

István Erdő

fellow of Bridge Budapest, Samebug, Budapest, 2017

Automating letters to users with Google Apps Script (Part 3)

Where we merge our scripts and add timestamps to our database

In <a href=”Link to Part 1”>Part 1</a> and <a href=”Link to Part 2”>Part 2</a> we’ve managed to set up an app which takes data from Google sheets, converts it into CSV, creates a matrix from CSV and creates a draft message in Gmail with personalised messages and templates. This part will be about the finishing touches: We will start by merging the “convert to CSV” and “create personalised draft” scripts together so we don’t have to manually copy-paste the CSV file ID every single time we want to run the app. Then we will finish by writing some code which looks up the user ID in the database and puts the current date into the specified column in the same row, indicating that we’ve sent a letter to them on that date. Let’s get to it!

The full code is available at my Github account:

https://github.com/istvanerdo/google-script-autodraft/blob/master/code

Merge Scripts

Open the 2 script files written previously and create a new one. In the new file paste the code which converts the sheet into a CSV file and directly under it the code which creates the personalised drafts.

Until this point we made our app create a new file every time we had it convert into CSV. We can now get rid of this step as we don’t need to store the file contents to do something with it.

Take the part which creates the CSV file:

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

  DriveApp.createFile(fileName, csvFile);

and replace it with:

  // create draft from csv

  makeDraft(csvFile)

The latter code executes our makeDraft function on the csvFile variable which has been defined already.

We already have the data stored in the csvFile variable so from the makeDraft function delete the part which gets the data:

  // Gets file content

  var data = DriveApp.getFileById(“…”).getBlob().getDataAsString();

I also renamed the main function because it now creates our final draft so instead of calling it saveAsCSV, we can call it finalDraft.

Now let’s test if it works as intended. Make sure you select which function to execute (finalDraft) in the drop down menu next to the run and debug icons.

Great, it works! We’re done with merging the script. If you check your drive you’ll see that it didn’t create any new files this time. This is exactly what we wanted to achieve.

Add time stamp

There’s only one more step until we’re finished: we want to track who we sent and email to. Therefore, we will write some code which will add the current date to the users.

This section of the code is based on the Stack Overflow question asked by user1783229 and answered by Serge insas.

Under the closing } of the makeDraft function we create a new function called addCustomValue(). This function will have 3 arguments: sheetNamestr, searchKeystr and writeValstr which are the name of the sheet where we’ll make the app perform the task, the string we’ll search for in a given column and the string we will write in the row we found the search string in, respectively.

function addCustomValue(sheetNamestr, searchKeystr, writeValstr) {

}

Write the rest of the code between { }. With the function we first need to get the data which we’ve already done at the beginning of our code. Fortunately, we’ve stored it in the variable ss. Let’s make sure we work on the right sheet:

  // Get all data

  var sheet = ss.getSheetByName(sheetNamestr)

  var ssdata = sheet.getDataRange().getValues()

For this to work we need to add ss to all the function arguments we want to use it in. If we skip this step then our app won’t recognise that the ss we use in the addCustomValue function is the same as the ss we defined in the finalDraft function. Go back to where we defined addCustomValue and write ss to the end of the argument list. Do this makeDraft, too. Go back to where we called makeDraft and add ss to it as well.

Now the variable ssdata represents all the data present in our spreadsheet. We can use the for loop to go through each row of the spreadsheet and set the values we specify. The structure of the for loop looks like this: for (statement 1; statement 2; statement 3) { code block to be executed }. Statement 1 is executed before each loop (code block), statement 2 is the condition for running the loop and statement 3 is executed after each loop. With this in mind we set up our for loop (n will denote the row number where 0 is the first row):

  // Loop through the data

  for (n=0; n<ssdata.length; ++n) {

  }

This means that for each loop, we check if the current loop number is smaller than the length of our data (since we only want to go through the rows which contain information, not thousands of rows of empty cells), if the condition holds, it executes the code inside { }, if not, it stops.

Now for the code block inside { } we add an if condition to check for correspondence:

    // Rewrite value

    if (ssdata[n][1-1].toString()==searchKeystr){ 

      ssdata[n][7-1] = writeValstr

    }

This sets the 7th column in the current row equal to whatever writeValstr we specify when calling the function only if our condition in ( ) holds. The condition is that the app should look at the value of the 1st column [1-1] of the current row [n], convert it into a string and check whether the searchKeystr we specified equals this value.

We have to be careful because this doesn’t set the value to writeValstr, only stores it. To make sure our changes are saved,  we need to use setValue on the whole sheet. We take our sheet variable; get the range with getRange() which will take on the arguments starting row, starting column, number of rows and number of columns (for more info on this, check out the documentation); and set the values of ssdata. Put it outside of the for loop as we only need to do this once in the addCustomValue function.

  sheet.getRange(1,1,ssdata.length,ssdata[0].length).setValues(ssdata)

The part where we wrote ssdata.length gets the number of rows and ssdata[0].length gets the number of columns in the first row. We’ll need to call this function for every user so go back to where we called the createDraft function and under it add the following code:

    // Add date stamp to registered_users sheet

    addCustomValue(‘Sheet1’, userId, Utilities.formatDate(new Date(), “GMT+1”, “yyyy/MM/dd”), ss)

We want to add value to the sheet named Sheet1 (or whatever you named your sheet), we want to search for the userId in the first column and we want to put the current date in the 6th column.

To make our app more complete, let’s add a few more lines of code. The part where we told the app to return if there is no email can be made more complete with our new function, so replace it with the following:

    // Put X if no email

    if(email === “NULL”){ 

      addCustomValue(‘Sheet1’, userId, “X”, ss)

      return

    }

This will put an X in the Letter Sent column and skips it instead of only skipping it. Under this write one more case when we want to skip the row if we’ve already sent a letter to that person (when the letterSent column is not empty, !=””):

    //Return if already written email to the user

    if(letterSent != “”) return

We haven’t specified letterSent yet, so let’s go back to our column variables list and add:

    var letterSent = cell[6]

The last thing left to do is to run it and pray that it works. You can find the full code below:

function finalDraft() {

  var ss = SpreadsheetApp.openById(“1zqAfLfaoR7DMdRu1HGi4isWLoip2oZXwrX308IK0Jgw”);

  Logger.log(ss.getName());

  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 draft from csv

  makeDraft(csvFile, ss)

}

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;

}

function makeDraft(data, ss) {

  // 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.trim().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 letterSent = cell[6]

    var hasName = name && name !== “”

    // Use first name as displayname

    if(hasName) {

      var fullName = name.split(” “)

      displayName = fullName ? fullName[0] : displayName

    }

    // Put X if no email

    if(email === “NULL”){ 

      addCustomValue(‘Sheet1’, userId, “X”, ss)

      return

    }

    //Return if already written email to the user

    if(letterSent != “”) 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)

    // Add date stamp to registered_users sheet

    addCustomValue(‘Sheet1’, userId, Utilities.formatDate(new Date(), “GMT+1”, “yyyy/MM/dd”), ss)

  })

}

function addCustomValue(sheetNamestr, searchKeystr, writeValstr, ss) {

  // Get all data

  var sheet = ss.getSheetByName(sheetNamestr)

  var ssdata = sheet.getDataRange().getValues()

  // Loop through the data

  for (n=0; n<ssdata.length; ++n) {

    // Rewrite value

    if (ssdata[n][1-1].toString()==searchKeystr){ 

      ssdata[n][7-1] = writeValstr

    }

  }

  sheet.getRange(1,1,ssdata.length,ssdata[0].length).setValues(ssdata)

}

After running finalDraft:

We have our drafts.

And we have our Letter sent column filled in. If you run the code again, you can see that it won’t create more drafts as you’ve already have the Letter sent column filled in for each row.

We’re done with our app. Thank you for sticking with me for this guide, I hope I managed to teach you something new. If you have any questions, reach out to me via email at [email protected] or via Facebook at www.facebook.com/samebug.io. Feel free to follow our blog at blog.samebug.io.