javascript - 导出 PDF 时 Google 应用脚本引发 #REF 错误
问题描述
我正在运行一个运行良好的应用程序脚本,并从电子表格中的有机工作表中导出 PDF。
问题是电子表格复制并#REF error
在所有cells
.
有没有办法解决?我确实看到了这个SO question,但我无法解决它。下面的代码:
我会假设需要使用范围。复制到函数?但我想不通。
// Simple function to send Weekly Status Sheets to contacts listed on the "Contacts" sheet in the MPD.
// Load a menu item called "Project Admin" with a submenu item called "Send Status"
// Running this, sends the currently open sheet, as a PDF attachment
function onOpen() {
var submenu = [{name:"Send Status", functionName:"exportSomeSheets"}];
SpreadsheetApp.getActiveSpreadsheet().addMenu('Project Admin', submenu);
}
function exportSomeSheets() {
// Set the Active Spreadsheet so we don't forget
var originalSpreadsheet = SpreadsheetApp.getActive();
// Set the message to attach to the email.
var message = "Daily Sales Snapshot"; // Could make it a pop-up perhaps, but out of wine today
// Get Project Name from Cell A1
var projectname = originalSpreadsheet.getRange("A1:A1").getValues();
// Get Reporting Period from Cell B3
var period = originalSpreadsheet.getRange("B3:B3").getValues();
// Construct the Subject Line
var subject = projectname + " - Daily Status Sheet - " + period;
// Get contact details from "Contacts" sheet and construct To: Header
// Would be nice to include "Name" as well, to make contacts look prettier, one day.
var contacts = originalSpreadsheet.getSheetByName("Contacts");
var numRows = contacts.getLastRow();
var emailTo = contacts.getRange(2, 2, numRows, 1).getValues();
// Google scripts can't export just one Sheet from a Spreadsheet
// So we have this disgusting hack
// Create a new Spreadsheet and copy the current sheet into it.
var newSpreadsheet = SpreadsheetApp.create("Spreadsheet to export");
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var projectname = SpreadsheetApp.getActiveSpreadsheet();
sheet = originalSpreadsheet.getActiveSheet();
sheet.copyTo(newSpreadsheet);
//create temp sheet that lets us copy the values
sheet = originalSpreadsheet.getActiveSheet();
var temp = originalSpreadsheet.duplicateActiveSheet();
//copy the values
range = temp.getDataRange();
range.copyTo(range, {contentsOnly: true});
//copy the values to a new spreadsheet
temp.copyTo(newSpreadsheet);
//delete our temp sheet.
ss.deleteSheet(temp);
// Find and delete the default "Sheet 1", after the copy to avoid triggering an apocalypse
newSpreadsheet.getSheetByName('Sheet1').activate();
newSpreadsheet.deleteActiveSheet();
// Make zee PDF, currently called "Weekly status.pdf"
// When I'm smart, filename will include a date and project name
var pdf = DriveApp.getFileById(newSpreadsheet.getId()).getAs('application/pdf').getBytes();
var attach = {fileName:'Daily Status.pdf',content:pdf, mimeType:'application/pdf'};
// Send the freshly constructed email
MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
// Delete the wasted sheet we created, so our Drive stays tidy.
DriveApp.getFileById(newSpreadsheet.getId()).setTrashed(true);
}
解决方案
如果任何不幸的灵魂落在此页面上,这对我有用。
// Simple function to send Weekly Status Sheets to contacts listed on the "Contacts" sheet in the MPD.
// Load a menu item called "Project Admin" with a submenu item called "Send Status"
// Running this, sends the currently open sheet, as a PDF attachment
function onOpen() {
var submenu = [{name:"Send Status", functionName:"exportSomeSheets"}];
SpreadsheetApp.getActiveSpreadsheet().addMenu('Project Admin', submenu);
}
function exportSomeSheets() {
// Set the Active Spreadsheet so we don't forget
var originalSpreadsheet = SpreadsheetApp.getActive();
// Set the message to attach to the email.
var message = "Daily Sales Snapshot"; // Could make it a pop-up perhaps, but out of wine today
// Get Project Name from Cell A1
var projectname = originalSpreadsheet.getRange("H1:H1").getValues();
// Get Reporting Period from Cell B3
var period = originalSpreadsheet.getRange("B3:B3").getValues();
// Construct the Subject Line
var subject = projectname + " - Daily Status Sheet - " + period;
// Get contact details from "Contacts" sheet and construct To: Header
// Would be nice to include "Name" as well, to make contacts look prettier, one day.
var contacts = originalSpreadsheet.getSheetByName("Contacts");
var numRows = contacts.getLastRow();
var emailTo = contacts.getRange(2, 2, numRows, 1).getValues();
// Google scripts can't export just one Sheet from a Spreadsheet
// So we have this disgusting hack
var newSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheets = newSpreadsheet.getSheets;
for (var i = 5; i < sheets.length; i++) {
if (sheets[i].getSheetName() != sheetName) {
sheet[i].hideSheet();
}
}
// Make zee PDF, currently called "Weekly status.pdf"
// When I'm smart, filename will include a date and project name
var pdf = DriveApp.getFileById(newSpreadsheet.getId()).getAs('application/pdf').getBytes();
var attach = {fileName:'Daily Status.pdf',content:pdf, mimeType:'application/pdf'};
// Send the freshly constructed email
MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
}
推荐阅读
- javascript - Jquery ajax FormData 始终为空
- python - Python 3:列表和字典。如何创建一个字典来告诉我每个项目来自哪个列表?
- android - Android 10 (api 29) 如何启用 HDR 和夜间模式
- javascript - 我怎样才能弹出一个警告,询问“你确定要取消吗?”
- kubernetes - 为什么clusterip(iptables)的性能这么差?是不是我的配置有问题
- c# - .net core 3.1 无法删除 cookie
- google-app-engine - 从 Angular 访问受 Google IAP 保护的 API
- string - DBFlow 丢失了 String 类型的数据
- amazon-web-services - 有没有办法使用 AWS 流量镜像功能记录生产服务器流量并在舞台环境中重放相同的流量
- javascript - 使用引导程序 4 选项卡时触发单击不起作用