英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论