I created a sheets where my team (with zero knowledge on Google Apps Script and HTML) fills with Customer Names and Customer Email Addresses and click on a button to execute my script.

I made a script where I retrieve those names and emails and send them a HTML-formatted email, all through App Script.

However, the email body keeps changing and 1) I don't have the time to keep change it on the HMTL page inside Apps scrip; and 2) My team don't know how to change it.

I imagine there's an easier way for them to input the email body they want, and then my function retrieves it and use it in the email. One extra problem: on my script, I call variables to use on the html (like Customer Name).

Any thoughts?

(sorry for the long question)

CodePudding user response：

The simpliest way is to allow a range or many ranges in a sheet and use it, for instance

function sendEmail() {
  MailApp.sendEmail({to: 'the emails',
  subject: 'the subject', 
  htmlBody: myText()})
};
function myText(){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('mySheet')
  var plage = sh.getRange('A1:H33'); 
  return (tableHTML(plage))
}
function tableHTML(plage){
  var data=plage.getDisplayValues()
  var taille=plage.getFontSizes()
  var fond=plage.getBackgrounds()
  var couleur=plage.getFontColors()
  var police=plage.getFontFamilies()
  var htmltable = '<table>';
  for (row = 0; row<data.length; row  ){
    htmltable  = '<tr>';
    for (col = 0 ;col<data[row].length; col  ){
      if (data[row][col] === "" || 0) {htmltable  = '<td>'   '&nbsp;'   '</td>';} 
      else
        htmltable  = '<td style="font-family:'   police[row][col]   '; background-color:'   fond[row][col]   '; color:'   couleur[row][col]   '; font-size:'   taille[row][col]   'px;">'   data[row][col]   '</td>';
      }
      htmltable  = '</tr>';
    }
  htmltable  = '</table>';
  return htmltable
}

This solution preserve also the colors you use within your sheet.