Wrap text using input from Google Sheet (Google Apps Script)

0
votes

I have a script that produces an automated email from cell content in a Google Sheet. Is it possible to restrict the width of a cell's output in the email, forcing the text to wrap? I have tried using textarea tags as follows:

+ <textarea rows="4" cols="20">

+ sheet.getRange(6,9,1,1).getValue()   

+ </textarea>

However, this simply outputs as "+ sheet.getRange(6,9,1,1).getValue() +" (i.e. it doesn't generate the cell content).

Is this possible?

Here's how I have built the script:

function EmailFormConfirmation() { 

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  Utilities.sleep(60000);

  var sheet = ss.getSheetByName("Form responses");

  var lock = LockService.getPublicLock();

  lock.waitLock(60000);

  lock.releaseLock();

  var email = sheet.getRange(2,9,1,1).getValue();  

  var message = "<HTML><BODY>"

    + "<P >Hi " 

    + sheet.getRange(4,9,1,1).getValue()

    + ","

    etc.

EDIT The below produces the cell content, but doesn't wrap the text. 

var htmlMsg = "<HTML><BODY>"

    + "<textarea rows='4' cols='10'>"

    + sheet.getRange(6,9,1,1).getValue()   

    + "</textarea>"

    + "</HTML></BODY>";


    MailApp.sendEmail(email, "LMI Request", "", {htmlBody: htmlMsg});
2
google-apps-script
Are you building this with HTMLService templating or string building? – Spencer Easton
Thanks for your reply Spencer. I have not used templating, so I assume it's string building? (I'm a novice). I've copied the script above. I would like to load a more complex 'styled' template that generates the cell content but I think this is beyond my capabilities. – Nick Allen
I'd need to look at more code, since this one is working for me. Also as good practice, you should getValue only once, and then use the Array, like this, getRange(2,9,3,1), ten for the message you'd use email[2][0] and for the real email email[0][0]. – Kriggs
Added example on how to do this with templates – Spencer Easton
in the last line of your edited message you have the tags reversed. The closing HTML tag should be outside of the body tag – ScampMichael

2 Answers

0
votes

To respond to the question re: textbox, the following did as you requested

  var htmlMsg = "<HTML><BODY>"
  + "<textarea rows='4' cols='20'>"
  + sheet.getRange(6,9,1,1).getValue()
  + "</textarea>"
  + "</BODY></HTML>";
  GmailApp.sendEmail("[email protected]", "subject","hi" , {htmlBody: htmlMsg});

note how the single and double quotes are used.

0
votes

Here is an example of how you can use templates to build your email responses. The documentation can be found here: https://developers.google.com/apps-script/guides/html/templates

In code.gs

function myFunction(){
  var sheet = SpreadsheetApp .....
  var value = sheet.getRange(6,9,1,1).getValue();
  var emailHtml = buildTemplate(value);
   GmailApp.sendEmail("[email protected]", "subject","Hello", {htmlBody: emailHtml});  
}

function buildTemplate(values){  
  var template = HtmlService.createTemplateFromFile('emailTemplate');
  template.tmpValues = values;
  return template.evaluate().getContent();
}

in emailTemplate.html

<html>
  <body>
       <textarea rows='4' cols='20'>
         <?=tmpValues?>
       </textarea>
  </body>
</html>