根据下拉菜单预览电子邮件模板,一旦满意即可发送电子邮件。

huangapple go评论73阅读模式
英文:

Preview Email Template based on drop down, Send email once your satisfuied

问题

在我的Google Sheets应用脚本中,我目前有三个template.html文件和一些脚本;我想创建一个预览电子邮件,并在用户满意后将其发送给他或她;然而,(openAI)为我构建的事件监听器不起作用;当我更改下拉菜单或单击发送按钮时,什么也不会发生,预览不会加载。当我向AI寻求帮助时,它不断修改我的代码;我的代码不再像原来那样;经过一周的尝试,我意识到我需要在这方面寻求帮助。以下是截至今天的我的最新代码。AI还坚持要使用Google Drive,但我拒绝了,因为我在应用脚本表中有HTML文件,并且过去曾使用以下代码获取它。

这段代码不再使用,但在我在GmailApp中使用它获取模板文件名时曾起作用。

var html = HtmlService.createTemplateFromFile('Proposal Template.html');  
var html = HtmlService.createTemplateFromFile('Marketing Template.html');  
var html = HtmlService.createTemplateFromFile('Trusted Partner Template.html'); 

请记住,尽管我不是Java或JS专家,但我对它们很熟悉。

我的代码

function showEmailPreview() {
  // 从活动工作表和活动行获取值
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRowIndex();
  var userEmail = sheet.getRange(row, getColIndexByName("Primary Email")).getValue(); 
  var userFullName = sheet.getRange(row, getColIndexByName("Contact Full Name")).getValue();  
  var userCompanyName = sheet.getRange(row, getColIndexByName("Company Name")).getValue();  
  var title = sheet.getRange(row, getColIndexByName("Title")).getValue();   
  var company_location = sheet.getRange(row, getColIndexByName("Company Location")).getValue();    
  var company_phone1 = sheet.getRange(row, getColIndexByName("Company Phone 1")).getValue();    
  var subjectLine = "Company Proposal - " + userCompanyName;
 
  // 创建选择元素
  const select = `
    <select id="template-select">
      <option value="Proposal Template.html">Proposal Template</option>
      <option value="Marketing Template.html">Marketing Template</option>
      <option value="Trusted Partner Template.html">Trusted Partner Template</option>
    </select>
  `;

  // 创建按钮元素
  const button = `<button id="send-button">Send Email</button>
<div id="preview"></div>`; //这可能是个问题?AI不知道在哪里放置这个或在给我一个合适的答案之前进行了削减。 

  // 创建一个HTML输出页面,显示电子邮件模板、选择元素和发送电子邮件的按钮
  var output = HtmlService.createHtmlOutput(`
    <script>
      var buttonElement;
  
  function getElementById(id) {
    return document.getElementById(id);
  }

  function init() {
    // 为选择元素添加更改事件监听器
    document.getElementById('template-select').addEventListener('change', function() {
      // 获取所选模板文件名
      var templateFile = this.value;
      
      // 读取所选文件的内容
      var template = readFile(templateFile);
      
      // 在模板中设置值
      var html = HtmlService.createTemplate(template);
      html.userFullName = userFullName;
      html.userCompanyName = userCompanyName;
      html.title = title;
      html.company_location = company_location;
      html.company_phone1 = company_phone1;
      
      // 以字符串形式获取填写的电子邮件模板
      var emailTemplate = html.evaluate().getContent();
      
      // 使用所选模板更新预览窗口
      document.getElementById('preview').innerHTML = emailTemplate;
    });
    
    // 为按钮元素添加点击事件监听器
    buttonElement = getElementById('send-button');
    buttonElement.addEventListener('click', function() {
      // 获取所选模板文件名
      var templateFile = document.getElementById('template-select').value;
      // 将所选模板文件名作为参数传递给sendEmail函数
      sendEmail(templateFile);
    });
  }

  window.onload = init; 
 
function sendEmail(templateFile) {
  // 读取所选文件的内容
  var template = readFile(templateFile);

  // 在模板中设置值
  var html = HtmlService.createTemplate(template);
  html.userFullName = userFullName;
  html.userCompanyName = userCompanyName;
  html.title = title;
  html.company_location = company_location;
  html.company_phone1 = company_phone1;
  
  // 以字符串形式获取填写的电子邮件模板
  var emailTemplate = html.evaluate().getContent();
  
  // 发送电子邮件
  GmailApp.sendEmail(userEmail, subjectLine, '', {htmlBody: emailTemplate});
}  
  
    </script>
    <script>
  init();
</script>

    ${select}
    ${button}
  
  `)
    .setWidth(950)
    .setHeight(750)
    .setSandboxMode(HtmlService.SandboxMode.IFRAME);

// 在模态对话框中显示输出页面

SpreadsheetApp.getUi().showModalDialog(output, 'Email Preview');
//output.evaluate();
//window.onload = init;


};
function readFile(templateFile) {
  // 获取所选文件的内容
  var file = DriveApp.getFilesByName(templateFile);
  var contents = file.next().getBlob().getDataAsString();
  return contents;
}//window.onload = init;

结果。

根据下拉菜单预览电子邮件模板,一旦满意即可发送电子邮件。

这是一个用于测试的链接。
https://docs.google.com/spreadsheets/d/1gXDbtjCYfZw8kOaOMorbJ54dXyl7

英文:

in my Google Sheets app script, I currently have three template.html files and a few scripts; I'd like to create a preview email and send it to the user once he or she is satisfied; however, the event listeners that (openAI) built for me do not work; when I change the Drop Down Menu or click send Button, nothing happens and the preview does not load. When I ask the AI for help, it keeps modifying my code; My code no longer looks like the original; after a week of trying, I've realized that I need assistance with this. Here's my most recent code as of today. The AI also insisted on using Google Drive, which I declined because I have the HTML files in the app scrip sheet itself and used to obtain it with this.

This code is not used anymore, But use to work when I used it in GmailApp to get the template File Name.

var html = HtmlService.createTemplateFromFile('Proposal Template.html');  
var html = HtmlService.createTemplateFromFile('Marketing Template.html');  
var html = HtmlService.createTemplateFromFile('Trusted Partner Template.html'); 

Keep in mind that while I am not an expert in Jave or JS, I am familiar with them.

My code

function showEmailPreview() {
  // Get values from the active sheet and active row
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRowIndex();
  var userEmail = sheet.getRange(row, getColIndexByName("Primary Email")).getValue(); 
  var userFullName = sheet.getRange(row, getColIndexByName("Contact Full Name")).getValue();  
  var userCompanyName = sheet.getRange(row, getColIndexByName("Company Name")).getValue();  
  var title = sheet.getRange(row, getColIndexByName("Title")).getValue();   
  var company_location = sheet.getRange(row, getColIndexByName("Company Location")).getValue();    
  var company_phone1 = sheet.getRange(row, getColIndexByName("Company Phone 1")).getValue();    
  var subjectLine = "Company Proposal - " + userCompanyName;
 
  // Create the select element
  const select = `
    <select id="template-select">
      <option value="Proposal Template.html">Proposal Template</option>
      <option value="Marketing Template.html">Marketing Template</option>
      <option value="Trusted Partner Template.html">Trusted Partner Template</option>
    </select>
  `;

  // Create the button element
  const button = `<button id="send-button">Send Email</button>
<div id="preview"></div>`; //This could be a issue? The ai did not know where to place this or cut down before giving me a proper answer. 

  // Create an HTML output page that displays the email template, the select element, and a button to send the email
  var output = HtmlService.createHtmlOutput(`
    <script>
      var buttonElement;
  
  function getElementById(id) {
    return document.getElementById(id);
  }

  function init() {
    // Add a change event listener to the select element
    document.getElementById('template-select').addEventListener('change', function() {
      // Get the selected template file name
      var templateFile = this.value;
      
      // Read the contents of the selected file
      var template = readFile(templateFile);
      
      // Set values in the template
      var html = HtmlService.createTemplate(template);
      html.userFullName = userFullName;
      html.userCompanyName = userCompanyName;
      html.title = title;
      html.company_location = company_location;
      html.company_phone1 = company_phone1;
      
      // Get the filled-in email template as a string
      var emailTemplate = html.evaluate().getContent();
      
      // Update the preview window with the selected template
      document.getElementById('preview').innerHTML = emailTemplate;
    });
    
    // Add a click event listener to the button element
    buttonElement = getElementById('send-button');
    buttonElement.addEventListener('click', function() {
      // Get the selected template file name
      var templateFile = document.getElementById('template-select').value;
      // Pass the selected template file name as an argument to the sendEmail function
      sendEmail(templateFile);
    });
  }

  window.onload = init; 
 
function sendEmail(templateFile) {
  // Read the contents of the selected file
  var template = readFile(templateFile);

  // Set values in the template
  var html = HtmlService.createTemplate(template);
  html.userFullName = userFullName;
  html.userCompanyName = userCompanyName;
  html.title = title;
  html.company_location = company_location;
  html.company_phone1 = company_phone1;
  
  // Get the filled-in email template as a string
  var emailTemplate = html.evaluate().getContent();
  
  // Send the email
  GmailApp.sendEmail(userEmail, subjectLine, '', {htmlBody: emailTemplate});
}  
 
    </script>
    <script>
  init();
</script>

    ${select}
    ${button}
  
  `)
    .setWidth(950)
    .setHeight(750)
    .setSandboxMode(HtmlService.SandboxMode.IFRAME);

// Display the output page in a modal dialog box

SpreadsheetApp.getUi().showModalDialog(output, 'Email Preview');
//output.evaluate();
//window.onload = init;


};
function readFile(templateFile) {
  // Get the contents of the selected file
  var file = DriveApp.getFilesByName(templateFile);
  var contents = file.next().getBlob().getDataAsString();
  return contents;
}//window.onload = init;

Results.

根据下拉菜单预览电子邮件模板,一旦满意即可发送电子邮件。

Here is a link for testing.
https://docs.google.com/spreadsheets/d/1gXDbtjCYfZw8kOaOMorbJ54dXyl7bh6MFM1LopTrNww/edit?usp=sharing

答案1

得分: 1

以下是您要翻译的内容:

"当你挣扎了一个星期后放弃,最后发布了一些内容,然后找到了解决方案,这总是这样。所以这是正确的做法。

首先让我们谈谈菜单。我创建了一个名为 template-select-menu.gs 的文件

<form>
  <label for="template">选择模板:</label><br>
  <select id="template" name="template">
    <option value="Proposal Template.html">提案模板</option>
    <option value="Markting Template">营销模板</option>
  </select>
  <br><br>
  <input type="button" value="预览" onclick="google.script.run.onTemplateSelected(document.forms[0].template.value)">
</form> 

然后我有我的 preview_email.gs

function showEmailPreview() {
  // 从活动工作表和活动行中获取值
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRowIndex();
  var rate = sheet.getLastRow();
  var userEmail = sheet.getRange(row, getColIndexByName("Primary Email")).getValue(); 
  var userFullName = sheet.getRange(row, getColIndexByName("Contact Full Name")).getValue();  
  var userCompanyName = sheet.getRange(row, getColIndexByName("Company Name")).getValue();   
  var subjectLine = "公司提案 - " + userCompanyName ;
  var aliases = GmailApp.getAliases()

  // 创建电子邮件模板选择菜单
  var proposalTemplate = HtmlService.createTemplateFromFile('Proposal Template.html');
  var marktingTemplate = HtmlService.createTemplateFromFile('Markting Template.html');

  var selectMenu = HtmlService.createTemplateFromFile('template-select-menu.html');
  var selectMenuHtml = selectMenu.evaluate().getContent();

  // 创建一个显示电子邮件模板选择菜单和发送电子邮件按钮的HTML输出页面
  var output = HtmlService.createHtmlOutput(selectMenuHtml)
      .setWidth(600)
      .setHeight(450);

  // 在模态对话框中显示输出页面
  SpreadsheetApp.getUi().showModalDialog(output, '电子邮件预览');
}

/**
 * 当用户从下拉菜单中选择模板时调用此函数。
 * 它使用所选模板创建电子邮件预览并在模态对话框中显示它。
 */
//var html = HtmlService.createTemplateFromFile(templateFileName);

function onTemplateSelected(templateFileName) {
  // 从活动工作表和活动行中获取值
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRowIndex();
  var rate = sheet.getLastRow();
  var userEmail = sheet.getRange(row, getColIndexByName("Primary Email")).getValue(); 
  var userFullName = sheet.getRange(row, getColIndexByName("Contact Full Name")).getValue();  
  var userCompanyName = sheet.getRange(row, getColIndexByName("Company Name")).getValue();   
  var subjectLine = "公司提案 - " + userCompanyName ;
  var aliases = GmailApp.getAliases();

  // 创建电子邮件模板并在模板中设置值
  if (templateFileName == 'Proposal Template.html') {
    var proposalTemplate = HtmlService.createTemplateFromFile('Proposal Template.html');
    proposalTemplate.userFullName = userFullName;
    proposalTemplate.userCompanyName = userCompanyName;
    var template = proposalTemplate.evaluate().getContent();
  } else if (templateFileName == 'Markting Template') {
    var marktingTemplate = HtmlService.createTemplateFromFile('Markting Template.html');
    marktingTemplate.userFullName = userFullName;
    marktingTemplate.userCompanyName = userCompanyName;
    var template = marktingTemplate.evaluate().getContent();
  } else {
    var template = selectMenuHtml;
  } 

  // 创建一个显示电子邮件模板和发送电子邮件按钮的HTML输出页面
  var selectMenu = HtmlService.createTemplateFromFile("template-select-menu.html");
  var selectMenuHtml = selectMenu.evaluate().getContent();

  var output = HtmlService.createHtmlOutput(template)
      .setWidth(600)
      .setHeight(450)
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setContent(selectMenuHtml + '<br><br>' + template);
  // 使用新的电子邮件模板更新模态对话框
  SpreadsheetApp.getUi().showModalDialog(output, '电子邮件预览');
}

现在创建两个文件:"Proposal Template.html" 和 "Markting Template.html"。当您切换并点击预览时,内容将会更改。
请注意,我仍然需要更新电子邮件按钮,但对我来说这是一个很好的起点。"

英文:

Its always like this when you struggle for a week and give up, And you finally post something and then you find yourself a solution. So here is the correct way of doing it.

Let's first talk about the menu. I created a file template-select-menu.gs

<form>
  <label for="template">Select a template:</label><br>
  <select id="template" name="template">
    <option value="Proposal Template.html">Proposal Template</option>
    <option value="Markting Template">Marketing Template</option>
  </select>
  <br><br>
  <input type="button" value="Preview" onclick="google.script.run.onTemplateSelected(document.forms[0].template.value)">
</form> 

then I have my preview_email.gs

function showEmailPreview() {
  // Get values from the active sheet and active row
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRowIndex();
  var rate = sheet.getLastRow();
  var userEmail = sheet.getRange(row, getColIndexByName("Primary Email")).getValue(); 
  var userFullName = sheet.getRange(row, getColIndexByName("Contact Full Name")).getValue();  
  var userCompanyName = sheet.getRange(row, getColIndexByName("Company Name")).getValue();   
  var subjectLine = "Company Proposal - " + userCompanyName ;
  var aliases = GmailApp.getAliases()

  // Create the email template selection menu
  var proposalTemplate = HtmlService.createTemplateFromFile('Proposal Template.html');
  var marktingTemplate = HtmlService.createTemplateFromFile('Markting Template.html');

  var selectMenu = HtmlService.createTemplateFromFile('template-select-menu.html');
  var selectMenuHtml = selectMenu.evaluate().getContent();

  // Create an HTML output page that displays the email template selection menu and a button to send the email
  var output = HtmlService.createHtmlOutput(selectMenuHtml)
      .setWidth(600)
      .setHeight(450);

  // Display the output page in a modal dialog box
  SpreadsheetApp.getUi().showModalDialog(output, 'Email Preview');
}

/**
 * This function is called when the user selects a template from the drop-down menu.
 * It creates an email preview using the selected template and displays it in the modal dialog box.
 */
//var html = HtmlService.createTemplateFromFile(templateFileName);


function onTemplateSelected(templateFileName) {
  // Get values from the active sheet and active row
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRowIndex();
  var rate = sheet.getLastRow();
  var userEmail = sheet.getRange(row, getColIndexByName("Primary Email")).getValue(); 
  var userFullName = sheet.getRange(row, getColIndexByName("Contact Full Name")).getValue();  
  var userCompanyName = sheet.getRange(row, getColIndexByName("Company Name")).getValue();   
  var subjectLine = "Company Proposal - " + userCompanyName ;
  var aliases = GmailApp.getAliases();

  // Create the email template and set values in the template 
  if (templateFileName == 'Proposal Template.html') {
    var proposalTemplate = HtmlService.createTemplateFromFile('Proposal Template.html');
    proposalTemplate.userFullName = userFullName;
    proposalTemplate.userCompanyName = userCompanyName;
    var template = proposalTemplate.evaluate().getContent();
  } else if (templateFileName == 'Markting Template') {
    var marktingTemplate = HtmlService.createTemplateFromFile('Markting Template.html');
    marktingTemplate.userFullName = userFullName;
    marktingTemplate.userCompanyName = userCompanyName;
    var template = marktingTemplate.evaluate().getContent();
  } else {

    var template = selectMenuHtml;
  } 

  // Create an HTML output page that displays the email template and a button to send the email
    var selectMenu = HtmlService.createTemplateFromFile("template-select-menu.html");
    var selectMenuHtml = selectMenu.evaluate().getContent();

  var output = HtmlService.createHtmlOutput(template)
      .setWidth(600)
      .setHeight(450)
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setContent(selectMenuHtml + '<br><br>' + template);
  // Update the modal dialog box with the new email template
  SpreadsheetApp.getUi().showModalDialog(output, 'Email Preview');
}


Now create the the 2 files "Proposal Template.html" and "Markting Template.html". When you switch and click preview the content will change.
Please note that I still need to update the email buttons. but this is a great start for me.

huangapple
  • 本文由 发表于 2023年1月9日 06:52:15
  • 转载请务必保留本文链接:https://go.coder-hub.com/75051865.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定