Tutorial Mengirim Sertifikat Via Email Secara Otomatis Menggunakan Spreadsheet + AppScript

Banyak yang bertanya bagaimana cara mengirim sertifikat yang telah dibuat via Certifast ke seluruh peserta ke email masing-masing peserta. Pada kali ini saya ingin membagikan cara mengirim sertifikat secara otomatis ke email masing-masing peserta, dan cara ini 100% GRATIS.

Notes: Jika kamu menggunakan akun Gmail versi gratis, kamu hanya bisa mengirim 100 email perhari. Dan jika kamu menggunakan akun Google Workspace, kamu bisa mengirim hingga 2000 email perhari.

1. Buat Spreadsheet

Hal pertama yang perlu kamu lakukan yaitu mengunjungi https://docs.google.com/spreadsheets lalu membuat blank spreadsheet. Isi A1, B1, C1 sebagai Email, File Name, Status secara berurutan seperti gambar di bawah. Lalu isi tiap baris-nya dengan email dan nama dari tiap peserta.

Untuk memudahkan, kamu bisa langsung menduplikasi spreadsheet yang telah kami buat disini.

2. Upload Seluruh Sertifikat ke Google Drive

Buat folder khusus untuk mengupload seluruh sertifikat (cth: Certifast Email Demo Folder). Lalu upload seluruh sertifikat yang telah dibuat di Certifast ke folder tersebut.

Notes: Pastikan nama file pada sertifikat di Google Drive sama persis dengan nama-nama yang ditulis di Spreadsheet pada kolom B.

3. Buat AppScript

Langkah selanjutnya kembali ke Spreadsheet lalu hover ke menu Extension -> AppScript untuk membuat AppScript project.

Setelah mengclick menu AppScript, kamu akan diarahkan ke halaman untuk mengedit AppScript. Pada editor AppScript hal yang perlu kamu lakukan yaitu mengcopy-paste code yang ada di bawah ini:

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(`Succesfully sent email to ${name} <${recipient}>. Remaining daily quote: ${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')
    }
  }
}

Setelah memasukkan code di atas ke AppScript, ada beberapa variable yang perlu kamu edit, antara lain FOLDER_ID , SENDER_NAME , dan EMAIL_BODY .

Mendapatkan Folder ID di Google Drive

Untuk mendapatkan folder ID, kamu bisa membuka folder google drive kamu dan mendapatkannya di url google drive-nya.

Selanjutnya gunakan folder ID yang telah kamu dapatkan di url google drive ke dalam code, seperti di bawah ini:

const FOLDER_ID = 'COPY_AND_PASTE_YOUR_FOLDER_ID_HERE'

/// ... other code

Update Nama Pengirim dan Email Body

Kamu perlu mengupdate nama pengirim dan email body sesuai kebutuhan kamu. Untuk mengupdate-nya kamu bisa melakukannya dengan mengubah variable SENDER_NAME dan 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

Langkah terakhir yang perlu kamu lakukan yaitu menjalakan AppScript, untuk menjalankannya kamu hanya perlu mengclick tombol Run yang ada di AppScript.

Biasanya ketika pertama kali menjalankan AppScript kamu akan diminta untuk mengotorisasi script yang ingin dijalankan. Untuk mengotorisasi AppScript kamu bisa mengikuti langkah yang ada di artikel ini: https://spreadsheet.dev/authorizing-an-apps-script .