Send email from google sheets

This weekend, I was working on a personal project this requires me to send personalized certificates to all participants over email.

Composing mail, Adding attachments, Writing messages Adding email addresses, and sending to the recipient is tedious and time-consuming if I need to do this for multiple users.

Google sheets provide a nice feature to automate this process using “script-editor”. The first step is to collect user details using google forms, once we have those details we create a sheet for those details.

For creating certificates I used canva tool (an online tool) to create certificates, use the existing templates and the names in the certificates from the name in the form you created.

Google forms created the sheet and I uploaded the certificates to the google drive folder, these certificates will now be sent to each recipient from the sheet.

The next step is to configure google sheets to send mail

We will not use the sheet created by form, instead will create a new sheet.

Go to chrome browser and type sheets.new in the URL bar, this will create a new spreadsheet for adding our data.

Here we need to add the attachments field from google drive. The catch here is to add the file-id in the attachment column, which means, we need to collect the ‘file-id’ of all the files which we want to add as an attachment for every row.

The manual step is to right-click on the file and click on the get link to get the link of the file. The box marked in red is the file id.

Doing this for multiple files could be time-consuming, so we take google sheets script editor’s help again and create a new sheet to collect the data.

Go ahead and create a new sheet, open tools -> script editor

/* modified from @hubgit and http://stackoverflow.com/questions/30328636/google-apps-script-count-files-in-folder 
for this stackexchange question http://webapps.stackexchange.com/questions/86081/insert-image-from-google-drive-into-google-sheets by @twoodwar
*/
function listFilesInFolder(folderName) {

   var sheet = SpreadsheetApp.getActiveSheet();
   sheet.appendRow(["Name", "Date", "Size", "URL", "Download", "Description", "Image"]);


//change the folder ID below to reflect your folder's ID (look in the URL when you're in your folder)
    var folder = DriveApp.getFolderById("Add your folder id here");
    var contents = folder.getFiles();

    var cnt = 0;
    var file;

    while (contents.hasNext()) {
        var file = contents.next();
        cnt++;

           data = [
                file.getName(),
                file.getDateCreated(),
                file.getSize(),
                file.getUrl(),
                "https://docs.google.com/uc?export=download&confirm=no_antivirus&id=" + file.getId(),
                file.getId(),
                file.getDescription(),
                "=image(\"https://docs.google.com/uc?export=download&id=" + file.getId() +"\")",
            ];

            sheet.appendRow(data);



    };
};

Add the folder id in the above code, we get the folder id the same way we did for file id.

Run the script, it will get the file id and its name along with file size, file link, and thumbnail image of all files from the specified folder, copy the file-id and align it with the proper name in the firs sheet we created for sending mail.

Our sheet is now ready with all the details and will look more or less like below.

Go to tools -> script editor and add the code for sending mail.

The code below is going to pick the email id from the first column and rename the file attachment with the name of the user from the second column and sent the mail to the user.

after sending mail to each user, the script will update the last column with the value EMAIL_SENT, this is added to avoid sending duplicate mail to the same recepient

// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = 'EMAIL_SENT';

/**
 * Sends non-duplicate emails with data from the current spreadsheet.
 */
function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = 56; // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 6);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0]; // First column
    var emailSent = row[5]; // Sixth column
    // var certificateURL = row[4];
    var name = row[1];
    var id = row[4];
    var certificateBlob = DriveApp.getFileById(id).getBlob().setName(name + "_certificate");
    // var certificateBlob = UrlFetchApp.fetch(certificateURL).getBlob().setName(name + "_certificate");
    var comp_name = row[3] // Cometetion Name, Fifth Column
    var subject = 'Thank you for participating in ' + comp_name + ' Competition';
    if (emailSent !== EMAIL_SENT) { // Prevents sending duplicates
      MailApp.sendEmail({
        to: emailAddress, 
        subject: subject, 
        htmlBody: "Thank you for participating, Please find you e-Certificate! <img src='cid:certificate'> <br> <br>",
        inlineImages:
        {
          certificate: certificateBlob
        }
      });
      sheet.getRange(startRow + i, 6).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

This code is taken from google send mail tutorial

once the code is executed an EMAIL_SENT field will be added to the sheet.

Note: This can send only 100 emails using this method in 24 hours, so use cautiously.

%d bloggers like this: