Apps script email send with attachment based on a google drive url

[Jonathan Kessous]

I have a google sheet document with a list of people, their emails, and a google drive url (column 40) that should be the attachment to the email. I have been trying to play with the attchment but i cant get it to work. Below you can find my function. hope you guys can help. thanks

function SendEmail(){
var Activesheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastrow = Activesheet.getLastRow();
var EmailTemplate=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Settings").getRange(9, 1).getValue();
   var DailyEmailQuota = MailApp.getRemainingDailyQuota();
for (var i = 2; i <= lastrow; i++) {
var Emaillist = Activesheet.getRange(i, 10).getValue();
var EmailSubject = 'Re: ' + Activesheet.getRange(i, 2).getValue();
var EmailPersonName = Activesheet.getRange(i, 7).getValue();
var PropertyAddress = Activesheet.getRange(i, 2).getValue();
var EmailBody = EmailTemplate.replace("{Name}", EmailPersonName).replace("{Property Address}", PropertyAddress);
var attachment = Activesheet.getRange(i, 40).getValue(); Logger.log(attachment);

if (Emaillist === "") { return; }
      else { MailApp.sendEmail({ name: 'Jonathan', to: Emaillist, subject: EmailSubject, htmlBody: EmailBody, attachments: [attachment], }); } } 

[Remco Edelenbos]

A couple of thinks: 

1. You want to get all the data at once and then loop over it. This will be a lot faster
2. Point one will give you a 2d array and we loop over that and get the values. Arrays are 0 indexed to column 40 = 39 (if you start at 1)
3. You will need to get the blob via the DriveApp

I think this would work:

function SendEmail() {

  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  const emailTemplate = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Settings").getRange(9, 1).getValue();

  const table = activeSheet.getRange(2, 1, activeSheet.getLastRow() - 1, 40).getValues()

table.forEach(row => {

  if (row[0] != "") {

    const emailList = row[9]

    const propertyAddress = row[1]

    const emailSubject = 'Re: ' + propertyAddress

    const emailPersonName = row[6]

    const emailBody = emailTemplate.replace("{Name}", emailPersonName).replace("{Property Address}", propertyAddress);

    const fileIdFromUrl = /https:\/\/\w*?\.google\.com\/\w*?\/d\/(.*?)\//g.exec(row[39])

    const attachment = DriveApp.getFileById(fileIdFromUrl).getBlob()

MailApp.sendEmail({ name: 'Jonathan', to: emailList, subject: emailSubject, htmlBody: emailBody, attachments: [attachment], })

console.log(`Email: ${emailList} | Subject: ${emailSubject}`)

    }

})

console.log(`Quota: ${MailApp.getRemainingDailyQuota()}`)

}

[Remco Edelenbos]

A couple of thins:

1. You want to get all the data via one call into memory instead of connecting to the call with every loop. This will speeds thinks up!

2. With point 1 you will get a 2d array and they are 0 indexed so column 40 would be array[39] (if you start at column 1 ofcourse)

3. You will need to GET the file with the DriveApp. Or you want to "Drive insert" the file?

I think this would work:

````js

function SendEmail() {

const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

const emailTemplate = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Settings").getRange(9, 1).getValue();

const table = activeSheet.getRange(2, 1, activeSheet.getLastRow() - 1, 40).getValues()

table.forEach(row => {

if (row[0] != "") {

const emailList = row[9]

const propertyAddress = row[1]

const emailSubject = 'Re: ' + propertyAddress

const emailPersonName = row[6]

const emailBody = emailTemplate.replace("{Name}", emailPersonName).replace("{Property Address}", propertyAddress);

const fileIdFromUrl = /https:\/\/\w*?\.google\.com\/\w*?\/d\/(.*?)\//g.exec(row[39])

const attachment = DriveApp.getFileById(fileIdFromUrl).getBlob()

MailApp.sendEmail({ name: 'Jonathan', to: emailList, subject: emailSubject, htmlBody: emailBody, attachments: [attachment], })

console.log(`Email: ${emailList} | Subject: ${emailSubject}`)

}

})

console.log(`Quota: ${MailApp.getRemainingDailyQuota()}`)

}

`````

On Friday, 8 July 2022 at 15:38:04 UTC+2 yoni...@gmail.com wrote:

[Jonathan Kessous]

Thanks Remko.

I am getting an error on the line of the const attachment.

Exception: Invalid argument: id at [unknown function](Code:22:29) at SendEmail(Code:14:7)

[Martin Hawksey]

In case it helps I shared a modified version of the Mail Merge solution published in the Google Apps Script documentation that handles user attachments with Drive file IDs

You can see/copy the modified version here https://docs.google.com/spreadsheets/d/1n_M_obCnHxXzWgVnUvTNwyunQiMsO6b1ZiMX4fYVIYc/copy

[Shared in this reddit thread https://www.reddit.com/r/GMail/comments/rb2pxj/has_anybody_here_tried_to_improve_martin_hawkseys/hnwflz5/ ] 

[Jonathan Kessous]

thanks
but i am not a coder and have little knowledge in it. I have most of the code done for what i need i am just missing the attachment part in which in my sheets column it is in a url form and i need the script to send the attachment.

thanks