1

I'm making a very basic mail merge from Google Sheets using Google Apps Script. I have text that needs to be presented as html, but when it sends the message body has '[object Object]' rather than the HTML content.

function test(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var howmany = ss.getRangeByName("howmany").getValue();
var emails = ss.getRangeByName("email").getValues();
var subjects = ss.getRangeByName("subject").getValues();
var text = ss.getRangeByName("text").getValues();

for (var i = 0; i < howmany; i++) {
MailApp.sendEmail(emails[i],subjects[i],{htmlBody: text[i]});
}
Chris Bisset
  • 13
  • 1
  • 1
  • 3

3 Answers3

3

get values is a two-dimensional array even if its only one column wide.

Try:

MailApp.sendEmail(emails[i][0],subjects[i][0],"",{htmlBody: text[i][0]});
ScampMichael
  • 3,688
  • 2
  • 16
  • 23
1

Checking the API docs, it looks like you need to use a different overload of sendEmail.

MailApp.sendEmail(emails[i], subjects[i], text[i], { htmlBody: text[i] });

The fourth argument is a JS object that sets advanced options. This is what the docs say about htmlBody:

if set, devices capable of rendering HTML will use it instead of the required body argument; you can add an optional inlineImages field in HTML body if you have inlined images for your email

So I would recommend you also provide a plain text version of the third argument, to support people who are using email that can't display HTML.

Tmdean
  • 9,108
  • 43
  • 51
  • I made the correction to the code so that it reads'MailApp.sendEmail(emails[i],subjects[i],text[1],{ htmlBody: text[i] });' but the email now shows up with [Ljava.lang.Object;@12c8f962 - any ideas most appreciated! – Chris Bisset May 08 '15 at 01:01
0

text[i] is currently a javascript object, the function sendEmail expects a string representing the body of the email. This printing of [object Object] occurs when a javascript object is printed in Google App Script.

You need to specify the non-html body, and/or the html body as, see specification, a String.

sent to the function sendEmail(recipient, subject, body, options). Also within the options hash the value assigned to the htmlBody needs to be a String.

I recommend you use Logger.log(text[i]); to check that what is in text[i] is what you expect.

David
  • 3,166
  • 2
  • 30
  • 51
  • I have used logger and it has shown [15-05-08 10:56:56:851 AEST] [test] which is what I'm aiming for. I made the correction to the code so that it reads'MailApp.sendEmail(emails[i],subjects[i],text[1],{ htmlBody: text[i] });' but the email now shows up with [Ljava.lang.Object;@12c8f962 - any ideas most appreciated! – Chris Bisset May 08 '15 at 00:59
  • use `Logger.log(typeof text[1]); Logger.log(typeof text[i]);` - we need to find out what the type of text[1] and text[i] to move forward here. [Ljava.lang.Object;@12c8f962 is returned as this class object represents a reference type that is not an array type so the binary name of the class is returned, as specified by the Java Language Specification see here: http://stackoverflow.com/questions/3442090/java-what-is-this-ljava-lang-object – David May 08 '15 at 01:13
  • Returns: [15-05-08 11:16:45:957 AEST] object [15-05-08 11:16:45:958 AEST] object – Chris Bisset May 08 '15 at 01:17
  • Hi Chris - sorry for the slow reply. so now we know we have two objects. lets find out what keys they have: `Logger.log(text[1].keys()); Logger.log(text[i].keys());` we are looking to find the String in these objects. Please also try `Logger.log(String(text[1])); Logger.log(String(text[i]));` where we will try to convert these objects to strings directly – David May 08 '15 at 01:53