google-apps-script - Google Script：让变量以正确的格式出现在自动电子邮件中的问题

问题描述

我已经从谷歌表格设置了自动电子邮件，但是它引用的数字和日期并没有像表格中显示的那样正确格式化它们。有人可以帮我解决我需要更改的内容，以使百分比不是正常数字，到小数点后第二位。以及使用 mm/dd/yyyy 格式的日期。
var EMAIL_DRAFTED = "EMAIL DRAFTED";

function weekly_report() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //replace with source ID
  var sheet = ss.getSheetByName('Individual Report Emails');                         // replace with source Sheet tab name
  var startRow = 2;                                                                  // First row of data to process
  var numRows = sheet.getLastRow() - 1;                                              // Number of rows to process
  var lastColumn = sheet.getLastColumn();                                            // Last column
  var dataRange = sheet.getRange(startRow, 1, numRows, lastColumn)                   // Fetch the data range of the active sheet
  var data = dataRange.getValues();                                                  // Fetch values for each row in the range
  
  // Work through each row in the spreadsheet
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];  
    // Assign each row a variable
    var Isa = row[0];                              // Col A: ISA
    var IsaEmail = row[1];                         // Col B: Email
    var LastWeekTotalAudits = row[12];             // Col M: Last Week Total Audits
    var LastWeekQualifiers = row[13];              // Col N: Last Week Qualifiers
    var LastWeekObjection = row[14];               // Col O: Last Week Objection
    var LastWeekTransferred = row[15];             // Col P: Last Week Transferred
    var LastWeekDocumented = row[16];              // Col Q: Last Week Documented
    var LastWeekDiction = row[17];                 // Col R: Last Week Diction
    var LastWeekTone = row[18];                    // Col S: Last Week Tone
    var LastWeekKnowledge = row[19];               // Col T: Last Week Knowledge
    var LastWeekAverage = row[20];                 // Col U: Last Week Average
    var LastWeekConversions = row[21];             // Col V: Last Week Conversions
    var WeekBeforeLastTotalAudits = row[22];       // Col W: Week Before Last Total Audits
    var WeekBeforeLastQualifiers = row[23];        // Col X: Week Before Last Qualifiers
    var WeekBeforeLastObjection = row[24];         // Col Y: Week Before Last Objection
    var WeekBeforeLastTransferred = row[25];       // Col Z: Week Before Last Transferred
    var WeekBeforeLastDocumented = row[26];        // Col AA: Week Before Last Documented
    var WeekBeforeLastDiction = row[27];           // Col AB: Week Before Last Diction
    var WeekBeforeLastTone = row[28];              // Col AC: Week Before Last Tone
    var WeekBeforeLastKnowledge = row[29];         // Col AD: Week Before Last Knowledge
    var WeekBeforeLastAverage = row[30];           // Col AE: Week Before Last Average
    var WeekBeforeLastConversions = row[31];       // Col AF: Week Before Last Conversions
    var LastWeekSoW = row[54];                     // Col BC: Last Week SoW
    var LastWeekEoW = row[55];                     // Col BD: Last Week EoW
    var WkDifTotalAudits = row[64];                // Col BM: WkDif Total Audits
    var WkDifQualifiers = row[65];                 // Col BN: WkDif Qualifiers
    var WkDifObjection = row[66];                  // Col BO: WkDif Objection
    var WkDifTransferred = row[67];                // Col BP: WkDif Transferred
    var WkDifDocumented = row[68];                 // Col BQ: WkDif Documented
    var WkDifDiction = row[69];                    // Col BR: WkDif Diction
    var WkDifTone = row[70];                       // Col BS: WkDif Tone
    var WkDifKnowledge = row[71];                  // Col BT: WkDif Knowledge
    var WkDifAverage = row[72];                    // Col BU: WkDif Average
    var WkDifConversions = row[73];                // Col BV: WkDif Conversions
    var emailStatus = row[84];                     // Col CG: Email Status
    
    // Prevent from drafing duplicates and from drafting emails without a recipient
    if (emailStatus !== EMAIL_DRAFTED && IsaEmail) {  
    
      // Build the email message
      var emailBody =  '<h2><strong>WEEKLY AUDIT REVIEW  </strong></h2>' ;
          emailBody += '<h3><strong>' + LastWeekSoW + ' - ' + LastWeekEoW + '  </strong></h3>' ;
          emailBody += '<hr />';
          emailBody += '<h3><strong>Last Week:  </strong></h3>' ;
          emailBody += '<p><strong>Audits Performed:        </strong>' + LastWeekTotalAudits + '<br /><strong>Avg Qualifiers %:  </strong>' + LastWeekQualifiers + '<br /><strong>Avg Objection %:  </strong>' + LastWeekObjection + '<br /><strong>Avg Transferred %:  </strong>' + LastWeekTransferred + '<br /><strong>Avg Documented %:  </strong>' + LastWeekDocumented + '<br /><strong>Avg Diction %:  </strong>' + LastWeekDiction + '<br /><strong>Avg Tone %:  </strong>' + LastWeekTone + '<br /><strong>Avg Knowledge %:  </strong>' + LastWeekKnowledge + '<br /><strong>Last Week Average:  </strong>' + LastWeekAverage + '</p>';
          emailBody += '<hr />';
          emailBody += '<h3><strong>Week Prior:  </strong></h3>' ;
          emailBody += '<p><strong>Audits Performed:  </strong>' + WeekBeforeLastTotalAudits + '<br /><strong>Avg Qualifiers %:  </strong>' + WeekBeforeLastQualifiers + '<br /><strong>Avg Objection %:  </strong>' + WeekBeforeLastObjection + '<br /><strong>Avg Transferred %:  </strong>' + WeekBeforeLastTransferred + '<br /><strong>Avg Documented %:  </strong>' + WeekBeforeLastDocumented + '<br /><strong>Avg Diction %:  </strong>' + WeekBeforeLastDiction + '<br /><strong>Avg Tone %:  </strong>' + WeekBeforeLastTone + '<br /><strong>Avg Knowledge %:  </strong>' + WeekBeforeLastKnowledge + '<br /><strong>Prior Week Average:  </strong>' + WeekBeforeLastAverage + '</p>';
          emailBody += '<hr />';
          emailBody += '<h3><strong>Variance:  </strong></h3>' ;
          emailBody += '<p><strong>Audits Performed:  </strong>' + WkDifTotalAudits + '<br /><strong>Avg Qualifiers %:  </strong>' + WkDifQualifiers + '<br /><strong>Avg Objection %:  </strong>' + WkDifObjection + '<br /><strong>Avg Transferred %:  </strong>' + WkDifTransferred + '<br /><strong>Avg Documented %:  </strong>' + WkDifDocumented + '<br /><strong>Avg Diction %:  </strong>' + WkDifDiction + '<br /><strong>Avg Tone %:  </strong>' + WkDifTone + '<br /><strong>Avg Knowledge %:  </strong>' + WkDifKnowledge + '<br /><strong>Avg Variance:  </strong>' + WkDifAverage + '</p>';
          emailBody += '<hr />';
          emailBody += '<p><strong>In the event of poor performance, a manager will review with you.  </strong>' ;
      
      // Create the email draft
      MailApp.sendEmail(
        IsaEmail,                                         // Recipient
        'Your Weekly Call Audit Review for' + LastWeekSoW + ' - ' + LastWeekEoW,  // Subject
        '',                                               // Body (plain text)
        {
        htmlBody: emailBody                               // Options: Body (HTML)
        }
      );
      
      sheet.getRange(startRow + i, lastColumn).setValue(EMAIL_DRAFTED); // Update the last column with "EMAIL_DRAFTED"
      SpreadsheetApp.flush(); // Make sure the last cell is updated right away
    }
  }
}
但它在电子邮件中看起来像这样：当前不正确的电子邮件格式示例
标签： google-apps-script
google-apps-script - Google Script：让变量以正确的格式出现在自动电子邮件中的问题

问题描述

解决方案

推荐阅读
