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

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

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

问题

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

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

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

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

我的代码

  1. function showEmailPreview() {
  2. // 从活动工作表和活动行获取值
  3. var sheet = SpreadsheetApp.getActiveSheet();
  4. var row = sheet.getActiveRange().getRowIndex();
  5. var userEmail = sheet.getRange(row, getColIndexByName("Primary Email")).getValue();
  6. var userFullName = sheet.getRange(row, getColIndexByName("Contact Full Name")).getValue();
  7. var userCompanyName = sheet.getRange(row, getColIndexByName("Company Name")).getValue();
  8. var title = sheet.getRange(row, getColIndexByName("Title")).getValue();
  9. var company_location = sheet.getRange(row, getColIndexByName("Company Location")).getValue();
  10. var company_phone1 = sheet.getRange(row, getColIndexByName("Company Phone 1")).getValue();
  11. var subjectLine = "Company Proposal - " + userCompanyName;
  12. // 创建选择元素
  13. const select = `
  14. <select id="template-select">
  15. <option value="Proposal Template.html">Proposal Template</option>
  16. <option value="Marketing Template.html">Marketing Template</option>
  17. <option value="Trusted Partner Template.html">Trusted Partner Template</option>
  18. </select>
  19. `;
  20. // 创建按钮元素
  21. const button = `<button id="send-button">Send Email</button>
  22. <div id="preview"></div>`; //这可能是个问题?AI不知道在哪里放置这个或在给我一个合适的答案之前进行了削减。
  23. // 创建一个HTML输出页面,显示电子邮件模板、选择元素和发送电子邮件的按钮
  24. var output = HtmlService.createHtmlOutput(`
  25. <script>
  26. var buttonElement;
  27. function getElementById(id) {
  28. return document.getElementById(id);
  29. }
  30. function init() {
  31. // 为选择元素添加更改事件监听器
  32. document.getElementById('template-select').addEventListener('change', function() {
  33. // 获取所选模板文件名
  34. var templateFile = this.value;
  35. // 读取所选文件的内容
  36. var template = readFile(templateFile);
  37. // 在模板中设置值
  38. var html = HtmlService.createTemplate(template);
  39. html.userFullName = userFullName;
  40. html.userCompanyName = userCompanyName;
  41. html.title = title;
  42. html.company_location = company_location;
  43. html.company_phone1 = company_phone1;
  44. // 以字符串形式获取填写的电子邮件模板
  45. var emailTemplate = html.evaluate().getContent();
  46. // 使用所选模板更新预览窗口
  47. document.getElementById('preview').innerHTML = emailTemplate;
  48. });
  49. // 为按钮元素添加点击事件监听器
  50. buttonElement = getElementById('send-button');
  51. buttonElement.addEventListener('click', function() {
  52. // 获取所选模板文件名
  53. var templateFile = document.getElementById('template-select').value;
  54. // 将所选模板文件名作为参数传递给sendEmail函数
  55. sendEmail(templateFile);
  56. });
  57. }
  58. window.onload = init;
  59. function sendEmail(templateFile) {
  60. // 读取所选文件的内容
  61. var template = readFile(templateFile);
  62. // 在模板中设置值
  63. var html = HtmlService.createTemplate(template);
  64. html.userFullName = userFullName;
  65. html.userCompanyName = userCompanyName;
  66. html.title = title;
  67. html.company_location = company_location;
  68. html.company_phone1 = company_phone1;
  69. // 以字符串形式获取填写的电子邮件模板
  70. var emailTemplate = html.evaluate().getContent();
  71. // 发送电子邮件
  72. GmailApp.sendEmail(userEmail, subjectLine, '', {htmlBody: emailTemplate});
  73. }
  74. </script>
  75. <script>
  76. init();
  77. </script>
  78. ${select}
  79. ${button}
  80. `)
  81. .setWidth(950)
  82. .setHeight(750)
  83. .setSandboxMode(HtmlService.SandboxMode.IFRAME);
  84. // 在模态对话框中显示输出页面
  85. SpreadsheetApp.getUi().showModalDialog(output, 'Email Preview');
  86. //output.evaluate();
  87. //window.onload = init;
  88. };
  89. function readFile(templateFile) {
  90. // 获取所选文件的内容
  91. var file = DriveApp.getFilesByName(templateFile);
  92. var contents = file.next().getBlob().getDataAsString();
  93. return contents;
  94. }//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.

  1. var html = HtmlService.createTemplateFromFile('Proposal Template.html');
  2. var html = HtmlService.createTemplateFromFile('Marketing Template.html');
  3. 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

  1. function showEmailPreview() {
  2. // Get values from the active sheet and active row
  3. var sheet = SpreadsheetApp.getActiveSheet();
  4. var row = sheet.getActiveRange().getRowIndex();
  5. var userEmail = sheet.getRange(row, getColIndexByName("Primary Email")).getValue();
  6. var userFullName = sheet.getRange(row, getColIndexByName("Contact Full Name")).getValue();
  7. var userCompanyName = sheet.getRange(row, getColIndexByName("Company Name")).getValue();
  8. var title = sheet.getRange(row, getColIndexByName("Title")).getValue();
  9. var company_location = sheet.getRange(row, getColIndexByName("Company Location")).getValue();
  10. var company_phone1 = sheet.getRange(row, getColIndexByName("Company Phone 1")).getValue();
  11. var subjectLine = "Company Proposal - " + userCompanyName;
  12. // Create the select element
  13. const select = `
  14. <select id="template-select">
  15. <option value="Proposal Template.html">Proposal Template</option>
  16. <option value="Marketing Template.html">Marketing Template</option>
  17. <option value="Trusted Partner Template.html">Trusted Partner Template</option>
  18. </select>
  19. `;
  20. // Create the button element
  21. const button = `<button id="send-button">Send Email</button>
  22. <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.
  23. // Create an HTML output page that displays the email template, the select element, and a button to send the email
  24. var output = HtmlService.createHtmlOutput(`
  25. <script>
  26. var buttonElement;
  27. function getElementById(id) {
  28. return document.getElementById(id);
  29. }
  30. function init() {
  31. // Add a change event listener to the select element
  32. document.getElementById('template-select').addEventListener('change', function() {
  33. // Get the selected template file name
  34. var templateFile = this.value;
  35. // Read the contents of the selected file
  36. var template = readFile(templateFile);
  37. // Set values in the template
  38. var html = HtmlService.createTemplate(template);
  39. html.userFullName = userFullName;
  40. html.userCompanyName = userCompanyName;
  41. html.title = title;
  42. html.company_location = company_location;
  43. html.company_phone1 = company_phone1;
  44. // Get the filled-in email template as a string
  45. var emailTemplate = html.evaluate().getContent();
  46. // Update the preview window with the selected template
  47. document.getElementById('preview').innerHTML = emailTemplate;
  48. });
  49. // Add a click event listener to the button element
  50. buttonElement = getElementById('send-button');
  51. buttonElement.addEventListener('click', function() {
  52. // Get the selected template file name
  53. var templateFile = document.getElementById('template-select').value;
  54. // Pass the selected template file name as an argument to the sendEmail function
  55. sendEmail(templateFile);
  56. });
  57. }
  58. window.onload = init;
  59. function sendEmail(templateFile) {
  60. // Read the contents of the selected file
  61. var template = readFile(templateFile);
  62. // Set values in the template
  63. var html = HtmlService.createTemplate(template);
  64. html.userFullName = userFullName;
  65. html.userCompanyName = userCompanyName;
  66. html.title = title;
  67. html.company_location = company_location;
  68. html.company_phone1 = company_phone1;
  69. // Get the filled-in email template as a string
  70. var emailTemplate = html.evaluate().getContent();
  71. // Send the email
  72. GmailApp.sendEmail(userEmail, subjectLine, '', {htmlBody: emailTemplate});
  73. }
  74. </script>
  75. <script>
  76. init();
  77. </script>
  78. ${select}
  79. ${button}
  80. `)
  81. .setWidth(950)
  82. .setHeight(750)
  83. .setSandboxMode(HtmlService.SandboxMode.IFRAME);
  84. // Display the output page in a modal dialog box
  85. SpreadsheetApp.getUi().showModalDialog(output, 'Email Preview');
  86. //output.evaluate();
  87. //window.onload = init;
  88. };
  89. function readFile(templateFile) {
  90. // Get the contents of the selected file
  91. var file = DriveApp.getFilesByName(templateFile);
  92. var contents = file.next().getBlob().getDataAsString();
  93. return contents;
  94. }//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 的文件

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

然后我有我的 preview_email.gs

  1. function showEmailPreview() {
  2. // 从活动工作表和活动行中获取值
  3. var sheet = SpreadsheetApp.getActiveSheet();
  4. var row = sheet.getActiveRange().getRowIndex();
  5. var rate = sheet.getLastRow();
  6. var userEmail = sheet.getRange(row, getColIndexByName("Primary Email")).getValue();
  7. var userFullName = sheet.getRange(row, getColIndexByName("Contact Full Name")).getValue();
  8. var userCompanyName = sheet.getRange(row, getColIndexByName("Company Name")).getValue();
  9. var subjectLine = "公司提案 - " + userCompanyName ;
  10. var aliases = GmailApp.getAliases()
  11. // 创建电子邮件模板选择菜单
  12. var proposalTemplate = HtmlService.createTemplateFromFile('Proposal Template.html');
  13. var marktingTemplate = HtmlService.createTemplateFromFile('Markting Template.html');
  14. var selectMenu = HtmlService.createTemplateFromFile('template-select-menu.html');
  15. var selectMenuHtml = selectMenu.evaluate().getContent();
  16. // 创建一个显示电子邮件模板选择菜单和发送电子邮件按钮的HTML输出页面
  17. var output = HtmlService.createHtmlOutput(selectMenuHtml)
  18. .setWidth(600)
  19. .setHeight(450);
  20. // 在模态对话框中显示输出页面
  21. SpreadsheetApp.getUi().showModalDialog(output, '电子邮件预览');
  22. }
  23. /**
  24. * 当用户从下拉菜单中选择模板时调用此函数。
  25. * 它使用所选模板创建电子邮件预览并在模态对话框中显示它。
  26. */
  27. //var html = HtmlService.createTemplateFromFile(templateFileName);
  28. function onTemplateSelected(templateFileName) {
  29. // 从活动工作表和活动行中获取值
  30. var sheet = SpreadsheetApp.getActiveSheet();
  31. var row = sheet.getActiveRange().getRowIndex();
  32. var rate = sheet.getLastRow();
  33. var userEmail = sheet.getRange(row, getColIndexByName("Primary Email")).getValue();
  34. var userFullName = sheet.getRange(row, getColIndexByName("Contact Full Name")).getValue();
  35. var userCompanyName = sheet.getRange(row, getColIndexByName("Company Name")).getValue();
  36. var subjectLine = "公司提案 - " + userCompanyName ;
  37. var aliases = GmailApp.getAliases();
  38. // 创建电子邮件模板并在模板中设置值
  39. if (templateFileName == 'Proposal Template.html') {
  40. var proposalTemplate = HtmlService.createTemplateFromFile('Proposal Template.html');
  41. proposalTemplate.userFullName = userFullName;
  42. proposalTemplate.userCompanyName = userCompanyName;
  43. var template = proposalTemplate.evaluate().getContent();
  44. } else if (templateFileName == 'Markting Template') {
  45. var marktingTemplate = HtmlService.createTemplateFromFile('Markting Template.html');
  46. marktingTemplate.userFullName = userFullName;
  47. marktingTemplate.userCompanyName = userCompanyName;
  48. var template = marktingTemplate.evaluate().getContent();
  49. } else {
  50. var template = selectMenuHtml;
  51. }
  52. // 创建一个显示电子邮件模板和发送电子邮件按钮的HTML输出页面
  53. var selectMenu = HtmlService.createTemplateFromFile("template-select-menu.html");
  54. var selectMenuHtml = selectMenu.evaluate().getContent();
  55. var output = HtmlService.createHtmlOutput(template)
  56. .setWidth(600)
  57. .setHeight(450)
  58. .setSandboxMode(HtmlService.SandboxMode.IFRAME)
  59. .setContent(selectMenuHtml + '<br><br>' + template);
  60. // 使用新的电子邮件模板更新模态对话框
  61. SpreadsheetApp.getUi().showModalDialog(output, '电子邮件预览');
  62. }

现在创建两个文件:"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

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

then I have my preview_email.gs

  1. function showEmailPreview() {
  2. // Get values from the active sheet and active row
  3. var sheet = SpreadsheetApp.getActiveSheet();
  4. var row = sheet.getActiveRange().getRowIndex();
  5. var rate = sheet.getLastRow();
  6. var userEmail = sheet.getRange(row, getColIndexByName("Primary Email")).getValue();
  7. var userFullName = sheet.getRange(row, getColIndexByName("Contact Full Name")).getValue();
  8. var userCompanyName = sheet.getRange(row, getColIndexByName("Company Name")).getValue();
  9. var subjectLine = "Company Proposal - " + userCompanyName ;
  10. var aliases = GmailApp.getAliases()
  11. // Create the email template selection menu
  12. var proposalTemplate = HtmlService.createTemplateFromFile('Proposal Template.html');
  13. var marktingTemplate = HtmlService.createTemplateFromFile('Markting Template.html');
  14. var selectMenu = HtmlService.createTemplateFromFile('template-select-menu.html');
  15. var selectMenuHtml = selectMenu.evaluate().getContent();
  16. // Create an HTML output page that displays the email template selection menu and a button to send the email
  17. var output = HtmlService.createHtmlOutput(selectMenuHtml)
  18. .setWidth(600)
  19. .setHeight(450);
  20. // Display the output page in a modal dialog box
  21. SpreadsheetApp.getUi().showModalDialog(output, 'Email Preview');
  22. }
  23. /**
  24. * This function is called when the user selects a template from the drop-down menu.
  25. * It creates an email preview using the selected template and displays it in the modal dialog box.
  26. */
  27. //var html = HtmlService.createTemplateFromFile(templateFileName);
  28. function onTemplateSelected(templateFileName) {
  29. // Get values from the active sheet and active row
  30. var sheet = SpreadsheetApp.getActiveSheet();
  31. var row = sheet.getActiveRange().getRowIndex();
  32. var rate = sheet.getLastRow();
  33. var userEmail = sheet.getRange(row, getColIndexByName("Primary Email")).getValue();
  34. var userFullName = sheet.getRange(row, getColIndexByName("Contact Full Name")).getValue();
  35. var userCompanyName = sheet.getRange(row, getColIndexByName("Company Name")).getValue();
  36. var subjectLine = "Company Proposal - " + userCompanyName ;
  37. var aliases = GmailApp.getAliases();
  38. // Create the email template and set values in the template
  39. if (templateFileName == 'Proposal Template.html') {
  40. var proposalTemplate = HtmlService.createTemplateFromFile('Proposal Template.html');
  41. proposalTemplate.userFullName = userFullName;
  42. proposalTemplate.userCompanyName = userCompanyName;
  43. var template = proposalTemplate.evaluate().getContent();
  44. } else if (templateFileName == 'Markting Template') {
  45. var marktingTemplate = HtmlService.createTemplateFromFile('Markting Template.html');
  46. marktingTemplate.userFullName = userFullName;
  47. marktingTemplate.userCompanyName = userCompanyName;
  48. var template = marktingTemplate.evaluate().getContent();
  49. } else {
  50. var template = selectMenuHtml;
  51. }
  52. // Create an HTML output page that displays the email template and a button to send the email
  53. var selectMenu = HtmlService.createTemplateFromFile("template-select-menu.html");
  54. var selectMenuHtml = selectMenu.evaluate().getContent();
  55. var output = HtmlService.createHtmlOutput(template)
  56. .setWidth(600)
  57. .setHeight(450)
  58. .setSandboxMode(HtmlService.SandboxMode.IFRAME)
  59. .setContent(selectMenuHtml + '<br><br>' + template);
  60. // Update the modal dialog box with the new email template
  61. SpreadsheetApp.getUi().showModalDialog(output, 'Email Preview');
  62. }

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:

确定