Tutorial on Automatically Sending Certificates via Email Using Spreadsheet + AppScript

Many people ask how to send certificates created via Certifast to all participants through each participant's email. This time I want to share a way to automatically send certificates to each participant's email, and this method is 100% FREE.

Notes: If you use a free version of a Gmail account, you can only send 100 emails per day. And if you use a Google Workspace account, you can send up to 2000 emails per day.

1. Create a Spreadsheet

The first thing you need to do is visit https://docs.google.com/spreadsheets and create a blank spreadsheet. Fill A1, B1, C1 with Email, File Name, Status respectively as shown in the image below. Then fill each row with the email and name of each participant.

To make it easier, you can directly duplicate the spreadsheet we have created here.

2. Upload All Certificates to Google Drive

Create a special folder to upload all the certificates (e.g., Certifast Email Demo Folder). Then upload all the certificates created in Certifast to that folder.

Notes: Make sure the file names on the certificates in Google Drive are exactly the same as the names written in the Spreadsheet in column B.

3. Create AppScript

The next step is to return to the Spreadsheet, then hover over the Extension menu -> AppScript to create an AppScript project.

After clicking the AppScript menu, you will be directed to the page to edit AppScript. In the AppScript editor, what you need to do is copy-paste the code below:

const FOLDER_ID = 'ABCDEFGHIJKLMN' // Update this with your gdrive folder id
const SENDER_NAME = 'Certifast Team' // Update this with your name
const EMAIL_BODY = `Dear {{name}},<br/><br/> we're happy to congratulate you on completing Digital Marketing Course, Please find your certificate attached below.<br/><br/> Best Regards,<br/>Certifast Team` // Update this with your own email body

function sendEmail() {
  const sheet = SpreadsheetApp.getActiveSheet()
  const range = sheet.getDataRange()
  const values = range.getValues()

  const folder = DriveApp.getFolderById(FOLDER_ID)
  const files = folder.getFiles()

  const fileNameToBlobMap = {}

  function iterateFiles(files) {
    if (!files.hasNext()) {
      return
    }

    const file = files.next()
    fileNameToBlobMap[file.getName()] = file.getBlob()
    iterateFiles(files)
  }

  iterateFiles(files)

  const fileNames = Object.keys(fileNameToBlobMap)

  for (let i = 0; i < values.length; i++) {
    const row = values[i]

    if (i === 0) {
      continue
    }
      
    const recipient = row[0]
    const name = row[1]
    const status = row[2]
   
    const subject = `Digital Marketing Course Certificate for ${name}`
    const emailBody = EMAIL_BODY.replace(/{{name}}/g, name)

    if (status === 'Sent') {
      continue
    }

    const dailyQuota = MailApp.getRemainingDailyQuota()
    if (dailyQuota === 0) {
      Logger.log('Your email sending quota is running out, send again after 24 hours')
      Logger.log('You can only send 100 emails/day if you use free account. And 2000 emails/day if you are using Google workspace.')
      break
    }

    const key = fileNames.find(v => [`${name}.pdf`, `${name}.jpg`, `${name}.jpeg`, `${name}.png`, name].includes(v))

    if (key && fileNameToBlobMap[key]) {
      MailApp.sendEmail({
        subject,
        name: SENDER_NAME,
        to: recipient,
        htmlBody: emailBody,
        attachments: [fileNameToBlobMap[key]]
      })

      Logger.log(`Successfully sent email to ${name} <${recipient}>. Remaining daily quota: ${dailyQuota - 1}`)
      range.getCell(i + 1, 3).setValue('Sent')
      range.getCell(i + 1, 1).setBackground('#fff').setFontColor('#000')
      range.getCell(i + 1, 2).setBackground('#fff').setFontColor('#000')
      range.getCell(i + 1, 3).setBackground('#fff').setFontColor('#000')
    } else {
      range.getCell(i + 1, 1).setBackground('#EA4335').setFontColor('#fff')
      range.getCell(i + 1, 2).setBackground('#EA4335').setFontColor('#fff')
      range.getCell(i + 1, 3).setBackground('#EA4335').setFontColor('#fff')
      range.getCell(i + 1, 3).setValue('File not found')
    }
  }
}

After entering the above code into AppScript, there are several variables you need to edit, including FOLDER_ID, SENDER_NAME, and EMAIL_BODY.

Getting Folder ID in Google Drive

To get the folder ID, you can open your Google Drive folder and get it in the Google Drive URL.

Next, use the folder ID you have obtained in the Google Drive URL in the code, as shown below:

const FOLDER_ID = 'COPY_AND_PASTE_YOUR_FOLDER_ID_HERE'

/// ... other code

Update Sender Name and Email Body

You need to update the sender name and email body as needed. To update them, you can do this by changing the variables SENDER_NAME and EMAIL_BODY.

/// ... other code 

const SENDER_NAME = 'EDIT_WITH_YOUR_NAME_HERE' 
const EMAIL_BODY = Dear {{name}},<br/><br/> we're happy to congratulate you on completing [Your Course Or Webinar Name], Please find your certificate attached below.<br/><br/> Best Regards,<br/>Certifast Team 

/// ... other code

4. Run AppScript

The last step you need to do is run the AppScript. To run it, you just need to click the Run button in AppScript.

Usually, when you run AppScript for the first time, you will be asked to authorize the script you want to run. To authorize AppScript, you can follow the steps in this article: https://spreadsheet.dev/authorizing-an-apps-script .