我正在尝试在谷歌表格中以 pdf 的形式在电子邮件中发送范围.我的脚本卡在 getid() 上

Question

我正在尝试在Google脚本中创建一个宏，该宏向我发送了我创建的工作表的电子邮件。当我尝试运行它时，脚本卡在函数 GetSheetID(( 上。(第2个函数的第4行(

"类型错误： 无法读取未定义的属性'getSheetId'(第 51 行，文件"宏"(">

我也对其他电子邮件技术持开放态度。我的主要目标是拍摄范围并在电子邮件中以图片或PDF形式发送。

function sendSheetToPdfwithA1MailAdress(){ // this is the function to call
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheets()[4]; // it will send sheet 0 which is the first sheet in the spreadsheet.
// if you change the number, change it also in the parameters below
var shName = 4 //sh.getName()

var shNum = 4
var shRng = 'A1:R35'
var pdfName = 'Automated Snapshot'
var email = '[e-mail]'
var subject = 'Daily Snapshot'
var htmlbody = ''
mailPdf(shNum,shRng,pdfName,email,subject,htmlbody);
}
function mailPdf(shNum,shRng,pdfName,email,subject,htmlbody) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = ss.getId();
var shId = shNum ? ss.getSheets()[shNum].getSheetId() : null;  
var url_base = ss.getUrl().replace(/edit$/,'');
var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
+ (shId ? ('&gid=' + shId) : ('&id=' + ssId))
+ (shRng ? ('&range=E1:L25') : null)
+ '&format=pdf'                   //export format
+ '&size=letter'                      //A3/A4/A5/B4/B5/letter/tabloid/legal/statement/executive/folio
//+ '&portrait=false'               //true= Portrait / false= Landscape
//+ '&scale=1'                      //1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
//+ '&top_margin=0.00'              //All four margins must be set!
//+ '&bottom_margin=0.00'           //All four margins must be set!
//+ '&left_margin=0.00'             //All four margins must be set!
//+ '&right_margin=0.00'            //All four margins must be set!
+ '&gridlines=false'              //true/false
//+ '&printnotes=false'             //true/false
//+ '&pageorder=2'                  //1= Down, then over / 2= Over, then down
//+ '&horizontal_alignment=CENTER'  //LEFT/CENTER/RIGHT
+ '&vertical_alignment=TOP'       //TOP/MIDDLE/BOTTOM
//+ '&printtitle=false'             //true/false
//+ '&sheetnames=false'             //true/false
//+ '&fzr=false'                    //true/false frozen rows
//+ '&fzc=false'                    //true/false frozen cols
//+ '&attachment=false'             //true/false
var options = {
headers: {
'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
'muteHttpExceptions': true
}
}
var response = UrlFetchApp.fetch(url_base + url_ext, options);
var blob = response.getBlob().setName(pdfName + '.pdf');
if (email) {
var mailOptions = {
attachments:blob, htmlBody:htmlbody
}

MailApp.sendEmail(
// email + "," + Session.getActiveUser().getEmail() // use this to email self and others
email,                                              // use this to only email users requested
subject+' (' + pdfName +')', 
'html content only', 
mailOptions);
}
}

Answer 1

关于

"类型错误： 无法读取未定义的属性'getSheetId'(第 51 行，文件"宏"(">

您很可能使用的是基数为一索引而不是从零开始的索引，并且电子表格的工作表少于 5 张。

此修复取决于要分配给以下代码行shId工作表 ID：

var shId = shNum ? ss.getSheets()[shNum].getSheetId() : null; 

参考

• https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array