How to add signature to email in google apps script generated email with proper formatting

2
votes

I have an email sender linked to a google sheets file as follows:

function sendEmail(form) {
const sSheet = SpreadsheetApp.getActiveSpreadsheet(); 
const file = DriveApp.getFileById(sSheet.getId());
const documentUrl = file.getUrl();

var toEmail = form.toAddress;  
var ccEmail = form.ccAddress;  
var subject = form.subject;
var message = form.message;


const folderId = "";
const ssId = sSheet.getId();
const returnFlag= 'blob';

if (form.process == "sendHotelRegistration"){
var fileName = "Hotel Registration Form";
var sheetId = sSheet.getSheetByName("Hotel Registration form").getSheetId();
var sheet= sSheet.getSheetByName("Hotel Registration form");
sheet.showSheet();
} else if (form.process == "updateRegistry"){
var fileName = "Hotel Contract Document";
var sheetId = "All";
}

var pdfFile = createPDF(folderId, ssId, fileName, sheetId, returnFlag);

MailApp.sendEmail({
    to:toEmail,
    cc:ccEmail,
    subject:subject,
    body:message,
    attachments:[pdfFile]
    });
}

I have 2 issues:

1- I want to add a signature including a logo to the email. So I need the company name in bold and the logo. I cannot work out how to insert an image or to have formatted text. I have read through a lot of posts and the following google documentation: https://developers.google.com/apps-script/reference/mail/mail-app

And I have tried to implement this in my code but I cannot get it to work.

Secondly, I have a variable set in my email body which retrieves a value from my google sheet document as follows:

html.contractorName = data.contractorName.toLowerCase();

However, I don't actually want lower case, I want title / proper case. I cannot though work out how to implement this, particularly bearing in mind that the contractor name may be two, three, four names long.

Thanks

5
javascriptgoogle-apps-scriptgoogle-sheets

5 Answers

3
votes

I have a similar sheetsEmail document. The logo problem took me a long time to solve, I recently found a semi-convenient solution from the following source.
The relevant code can take your personal signature as a string, and with htmlBody it can include images, all the desired formatting and what not. 

var signature = Gmail.Users.Settings.SendAs.list("me").sendAs.filter(function(account){if(account.isDefault){return true}})[0].signature;

From your comment on another reply, you are correct that it causes some problems with your regular body. My solution was to include html tags around it as well, and concatenate the two strings. So, for example, my original body is stored in the var bod: 

var body1 = "<p>"+bod+"</p>"+signature;

You could fool around with different tags to get the desired format. To solve your spaces and new lines issue, you could search your original body string and replace line break \n with the html br> tag, but I can't help you on the specifics of that!

1
votes

You can attach your logo and name to the message and send email in HTML format.

Something like this

message += "<BR><BR>" + name + "<BR><BR> <inline logo image>";

You can use htmlBody parameter(to add name logo to email body) and inlineImages options (to attach logo). Check this for full reference.

https://developers.google.com/apps-script/reference/mail/mail-app#sendemailmessage

1
votes

Please read MailApp.sendEmail() documentation here :

 // This code fetches the Google and YouTube logos, inlines them in an email
 // and sends the email
 function inlineImage() {
   var googleLogoUrl = "http://www.google.com/intl/en_com/images/srpr/logo3w.png";
   var youtubeLogoUrl =
         "https://developers.google.com/youtube/images/YouTube_logo_standard_white.png";
   var googleLogoBlob = UrlFetchApp
                          .fetch(googleLogoUrl)
                          .getBlob()
                          .setName("googleLogoBlob");
   var youtubeLogoBlob = UrlFetchApp
                           .fetch(youtubeLogoUrl)
                           .getBlob()
                           .setName("youtubeLogoBlob");
   MailApp.sendEmail({
     to: "[email protected]",
     subject: "Logos",
     htmlBody: "inline Google Logo<img src='cid:googleLogo'> images! <br>" +
               "inline YouTube Logo <img src='cid:youtubeLogo'>",
     inlineImages:
       {
         googleLogo: googleLogoBlob,
         youtubeLogo: youtubeLogoBlob
       }
   });
 }

Get the Blob of your logo and assign it to inlineImages property. Then you can include it in your HTML body. Use HTML to format the body according to your needs and assign it to htmlBody property. About the contractor name, try with this prototype function:

html.contractorName = data.contractorName.toProperCase();

String.prototype.toProperCase = function () {
      return this.replace(/\w\S*/g, function(txt){return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();});
};

It will capitalize each word in the string so john dOe becomes John Doe.

1
votes

Sorry to be a dummy, but when I add this line of code 

 var signature =
 Gmail.Users.Settings.SendAs.list("me").sendAs.filter(function(account){if(account.isDefault){return
 true}})[0].signature;

to my GAS, I get the error message

ReferenceError: "Gmail" is not defined. (line 127, file "Code")

This is the first time I've tried to create a GAS, and I know no JS, so I'm sure this will be a basic error. Thanks for helping out.

0
votes

You're seeing the Reference error because you don't have the Gmail API enabled. You can enable the API in the developer console. You will then have to enable it for the individual script you're working on. Clicking Resources > Advanced Google services then flip the on-switch next to Gmail API