将 Google 脚本复制到剪贴板并邮寄到 [问题] [英] Google Script copy to clipboard and mailto [Questions]
问题描述
我有 Excel 电子表格，用于编写时间表并将其发送给工作人员.这涉及隐藏列并保存为 PDF 的 vba.
为了让我使用 Google-Sheets 打印到 PDF，在 Gmail 中打开单个电子邮件似乎效率较低.我发现您可以复制 (ctrl+c) 一个范围(例如:A1:E10")，然后直接粘贴到 Gmail (ctrl+v) 中，效果一样好.
我想做的是按下一个按钮来运行一个脚本:
- 激活特定范围(我已经这样做了)
- 将其复制到剪贴板(我无法弄清楚这一点
- &激活一个 mailto URL(我没有弄清楚，但我使用的是 =hyperlink(url,name)).
或
- 使用格式和范围值直接从工作表发送电子邮件
或
- 运行打印对话框或另存为 PDF 的脚本特定的 Google-Drive 文件夹.
请参阅.
函数 sendEmail() {var ss = SpreadsheetApp.getActiveSpreadsheet();var sheet = ss.getActiveSheet();var 收件人 = sheet.getRange("I4").getValue();//收件人"电子邮件地址var 主题 = Utilities.formatDate(sheet.getRange("E2").getValue(),ss.getSpreadsheetTimeZone(),MMM d EEE")；var schedRange = sheet.getRange("B5:G26");//输入名称 &首先将日期输入电子邮件.//我们只想要边界内的时间表，所以//这些是分开处理的.var body = '<div style="text-align:center;display: inline-block;font-family: arial,sans,sans-serif">'body += '<H1>'+ sheet.getRange("E1").getValue() +'</H1>';正文 += '<H2>'+ 实用程序.formatDate(sheet.getRange("E2").getValue(),ss.getSpreadsheetTimeZone(),EEEEE, MMMMM d, yyyy")+ '</H2>';正文 += getHtmlTable(schedRange);正文 += '</div>';调试器；收件人 = Session.getActiveUser().getEmail();//用于调试，只发送给自己GmailApp.sendEmail(收件人，主题，需要 HTML"，{htmlBody:body})}getHtmlTable()
sendEmail() 函数依赖于 getHtmlTable()，这是将电子表格范围呈现为 HTML 表格的通用实用程序的开始.最新版本见 github.
注意事项:
- 目前它产生了太多的样式信息，但结果是电子表格的一个相当忠实的副本.
- 一般表格样式，包括边框，在
tableFormat变量中设置.由于无法确定电子表格上有哪些边框，因此无法传输它们. - 可以从电子表格中读取数字格式，但不能直接在 Javascript 中进行调整，因此数字不会按照电子表格中的显示方式呈现.
- 日期也是如此.为了支持这个特定的问题，日期将被识别和格式化，如问题中所示.当心.
代码:
/*** 返回一个包含 HTML 表格表示的字符串* 给定范围，保留样式设置.*/函数getHtmlTable(范围){var ss = range.getSheet().getParent();var sheet = range.getSheet();startRow = range.getRow();startCol = range.getColumn();lastRow = range.getLastRow();lastCol = range.getLastColumn();//读取表格内容var data = range.getValues();//从范围中获取 css 样式属性var fontColors = range.getFontColors();var backgrounds = range.getBackgrounds();var fontFamilies = range.getFontFamilies();var fontSizes = range.getFontSizes();var fontLines = range.getFontLines();var fontWeights = range.getFontWeights();var HorizontalAlignments = range.getHorizontalAlignments();var verticalAlignments = range.getVerticalAlignments();//以像素为单位获取列宽var colWidths = [];for (var col=startCol; col<=lastCol; col++) {colWidths.push(sheet.getColumnWidth(col));}//以像素为单位获取行高var rowHeights = [];for (var row=startRow; row<=lastRow; row++) {rowHeights.push(sheet.getRowHeight(row));}//未来考虑...var numberFormats = range.getNumberFormats();//构建 HTML 表格，为每个单元格添加内联样式var tableFormat = 'style="border:1px solid black;border-collapse:collapse;text-align:center"border = 1 cellpadding = 5';var html = ['电子邮件示例
PS:我认为彩色网格是 Firefox 的奇怪之处.在 Chrome 中看起来不错，并且 HTML 确实指定了黑色.
I have Excel spreadsheets for writing schedules and sending them to crews. This involves vba which hides columns and saves as a PDF.
In order for me to use Google-Sheets printing to PDF, and opening individual emails in Gmail seems less efficient. I discovered you can copy (ctrl+c) a range (ex: "A1:E10"), and paste straight into Gmail (ctrl+v) and it looks just as good.
What I would like to do is press a button to run a script that:
- Activates a specific range ( I already did this )
- Copies it to clipboard ( I can not figure this one out
- & activates a mailto URL
( I didn't figure that out, but I'm using =hyperlink(url,name) ).
or
- Directly emails from sheet with the formating and range-values
or
- A script to either run the print dialogue, or save as a PDF to a
specific Google-Drive folder.
See here (my public version of this 'sheet')
I am new to Google Scripts, but familiar with VBA (and object oriented programming in general with exception to scripting languages XD)
Any help or sources, or alternative solutions to accomplish the same thing would be very helpful.
解决方案 Since Google Sheets is not an application running on your computer, its script capabilities are very different from VBA in Excel. No access to your PC's clipboard, for one. No triggering of a print dialog, for another. You can do those things in a browser while using Sheets, but not from a script.
The most straight-forward approach given the capabilities of Google Apps Script, though, will be:
- Change your script button to call a function that will...
- Build email with the embedded schedule, and
- Send the message.
There is no need to hide or unhide columns this way, as the embedded schedule can be built of only the interesting columns.
sendEmail()
You've asked to preserve formatting and range-values, so here's an approach that will do that. The sendMail() function operates on the active spreadsheet, and reads the schedule from a fixed range on that sheet, builds an email, and sends it to the email address found on that sheet.
For the most up-to-date code, refer to this library in Github.
function sendEmail() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var recipient = sheet.getRange("I4").getValue(); // "TO" email address
var subject = Utilities.formatDate(
sheet.getRange("E2").getValue(),
ss.getSpreadsheetTimeZone(),
"MMM d EEE");
var schedRange = sheet.getRange("B5:G26");
// Put Name & Date into email first.
// We only want the schedule within borders, so
// these are handled separately.
var body = '<div style="text-align:center;display: inline-block;font-family: arial,sans,sans-serif">'
body += '<H1>'+ sheet.getRange("E1").getValue() +'</H1>';
body += '<H2>'
+ Utilities.formatDate(
sheet.getRange("E2").getValue(),
ss.getSpreadsheetTimeZone(),
"EEEEE, MMMMM d, yyyy")
+ '</H2>';
body += getHtmlTable(schedRange);
body += '</div>';
debugger;
recipient = Session.getActiveUser().getEmail(); // For debugging, send only to self
GmailApp.sendEmail(recipient, subject, "Requires HTML", {htmlBody:body})
}
getHtmlTable()
The sendEmail() function relies on getHtmlTable(), which is the beginning of a general utility to render a spreadsheet range as an HTML table. See github for the latest version.
Caveats:
- It produces WAY too much style info presently, but the result is a reasonably faithful copy of the spreadsheet.
- The general table style, including borders, is set in the
tableFormat variable. Since there is no way to determine what borders are in place on a spreadsheet, it isn't possible to transfer them.
- Numeric formatting can be read from a spreadsheet, but is not directly adaptable in Javascript, so numbers aren't rendered as they appear in the spreadsheet.
- Dates, likewise. In support of this specific question, dates will be identified and formatted as shown in the question. Beware.
Code:
/**
* Return a string containing an HTML table representation
* of the given range, preserving style settings.
*/
function getHtmlTable(range){
var ss = range.getSheet().getParent();
var sheet = range.getSheet();
startRow = range.getRow();
startCol = range.getColumn();
lastRow = range.getLastRow();
lastCol = range.getLastColumn();
// Read table contents
var data = range.getValues();
// Get css style attributes from range
var fontColors = range.getFontColors();
var backgrounds = range.getBackgrounds();
var fontFamilies = range.getFontFamilies();
var fontSizes = range.getFontSizes();
var fontLines = range.getFontLines();
var fontWeights = range.getFontWeights();
var horizontalAlignments = range.getHorizontalAlignments();
var verticalAlignments = range.getVerticalAlignments();
// Get column widths in pixels
var colWidths = [];
for (var col=startCol; col<=lastCol; col++) {
colWidths.push(sheet.getColumnWidth(col));
}
// Get Row heights in pixels
var rowHeights = [];
for (var row=startRow; row<=lastRow; row++) {
rowHeights.push(sheet.getRowHeight(row));
}
// Future consideration...
var numberFormats = range.getNumberFormats();
// Build HTML Table, with inline styling for each cell
var tableFormat = 'style="border:1px solid black;border-collapse:collapse;text-align:center" border = 1 cellpadding = 5';
var html = ['<table '+tableFormat+'>'];
// Column widths appear outside of table rows
for (col=0;col<colWidths.length;col++) {
html.push('<col width="'+colWidths[col]+'">')
}
// Populate rows
for (row=0;row<data.length;row++) {
html.push('<tr height="'+rowHeights[row]+'">');
for (col=0;col<data[row].length;col++) {
// Get formatted data
var cellText = data[row][col];
if (cellText instanceof Date) {
cellText = Utilities.formatDate(
cellText,
ss.getSpreadsheetTimeZone(),
'MMM/d EEE');
}
var style = 'style="'
+ 'color: ' + fontColors[row][col]+'; '
+ 'font-family: ' + fontFamilies[row][col]+'; '
+ 'font-size: ' + fontSizes[row][col]+'; '
+ 'font-weight: ' + fontWeights[row][col]+'; '
+ 'background-color: ' + backgrounds[row][col]+'; '
+ 'text-align: ' + horizontalAlignments[row][col]+'; '
+ 'vertical-align: ' + verticalAlignments[row][col]+'; '
+'"';
html.push('<td ' + style + '>'
+cellText
+'</td>');
}
html.push('</tr>');
}
html.push('</table>');
return html.join('');
}
Email Example
PS: The colored grid is a firefox oddity, I think. Looks fine in Chrome, and the HTML does specify black.
这篇关于将 Google 脚本复制到剪贴板并邮寄到 [问题]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
登录
关闭
扫码关注1秒登录
发送“验证码”获取
|
15天全站免登陆