如何仅在从Google表格电子表格导出的pdf中包含特定范围 [英] How to include a specific range only in a pdf exported from a Google Sheets spreadsheet
问题描述
我有一张纸，希望将特定范围(A1:O44)作为pdf文档发送，并在其他单元格中省略其他信息.当前，发送这些工作表都正常工作，并且一切正常，但是，我似乎无法使脚本正确地将其限制在工作表中的某个范围内.
I have a sheet that I would like to send a specific range (A1:O44) as a pdf document and leave out further information in other cells. Currently, it is all functioning to send these sheets and all is working fine, however, I cannot seem to get the script correct to limit it to a certain range in the sheet.
工作表中有2个标签，我希望将这两个标签作为同一附件的一部分以电子邮件形式发送(当前已设置).通过单击顶部的自定义提交菜单中的提交"按钮来生成电子邮件:
There are 2 tabs in the sheet and I would like both of those to be sent in one email as part of the same attachment (as is currently set up). The email is generated by clicking the submit button from the custom submit menu on the top:
function onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu('SUBMIT')
.addItem('SUBMIT', 'menuItem1')
.addToUi();
}
function menuItem1() {
SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
sendSheetToPdfwithA1MailAdress();
}
function sendSheetToPdfwithA1MailAdress(){ // this is the function to call
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName("TEAM1"); // 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 practiceName = sh.getRange('E2').getValue()
var startDate = sh.getRange('I4:L5').getValue();
var emailBody = sh.getRange('I45').getValue();
var staffName = sh.getRange('c43').getValue()
var shName = Utilities.formatString('%s %s',practiceName, startDate)
var message = "<HTML><BODY>"
+ "<P>Hi,"
+ "<br /><br />"
+ " Please find attached the payroll info for " + practiceName + " for the period " + startDate + "."
+ "<br /><br />"
+ "" + emailBody + "."
+ "<br /><br />"
+ " If you have any question please let me know"
+ "<br /><br />"
+ "Thanks, " + staffName + "."
+ "<br /><br />"
+ "</HTML></BODY>";
sendSpreadsheetToPdf(0, shName, sh.getRange('A46').getValue(),"Payroll Summary", " " + message + " ");
}
function sendSpreadsheetToPdf(sheetNumber, pdfName, email,subject, htmlbody) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName("TEAM1");
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var spreadsheetId = spreadsheet.getId()
var sheetId = sheetNumber ? spreadsheet.getSheets()[sheetNumber].getSheetId() : null;
var url_base = spreadsheet.getUrl().replace(/edit$/,'');
var email2 = sh.getRange('A47').getValue();
var email3 = sh.getRange('A48').getValue();
var url_ext = 'export?exportFormat=pdf&format=pdf' //export as pdf
+ (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId))
// following parameters are optional...
+ '&size=A4' // paper size
+ '&portrait=false' // orientation, false for landscape
+ '&fitw=true' // fit to width, false for actual size
+ '&sheetnames=true&printtitle=false&pagenumbers=true' //hide optional headers and footers
+ '&gridlines=false' // hide gridlines
+ '&fzr=false'; // do not repeat row headers (frozen rows) on each page
var options = {
headers: {
'Authorization': 'Bearer ' + ScriptApp.getOAuthToken(),
}
}
var response = UrlFetchApp.fetch(url_base + url_ext, options);
var blob = response.getBlob().setName(pdfName + '.pdf');
if (email) {
var mailOptions = {
attachments:blob, htmlBody:htmlbody, email2:email2
}
MailApp.sendEmail(
email,
subject+" (" + pdfName +")",
"html content only",
mailOptions);
MailApp.sendEmail(
email2,
subject+" (" + pdfName +")",
"html content only",
mailOptions);
MailApp.sendEmail(
email3,
subject+" (" + pdfName +")",
"html content only",
mailOptions);
}
}
推荐答案
免责声明:我不认为Google正式支持使用查询参数来控制PDF导出，但是
Disclaimer: I don't believe the use of query parameters to control the PDF export is officially supported by Google, but this GitHub Gist from Spencer-Easton has the info you need to accomplish your goal.
问题出在url_ext中存储的查询参数中.
The issue is in the query parameters stored in url_ext.
您必须包含gid，您可以从工作表编辑器(/edit#gid=NUMBERS)的工作表URL中获取.
You must include the gid, which you can get from the sheet's URL from the sheet editor (/edit#gid=NUMBERS).
您需要添加到url_ext:
-
gid=NUMBERS(在URL中查找以获取数字) -
ir=false(未知含义) -
ic=false(未知含义) -
r1=0(起始行，零索引) -
c1=0(起始col，零索引) -
r2=43(结束行，注意索引的更改) -
c2=14(结束列，注意索引更改)
gid=NUMBERS(look in URL to get numbers)ir=false(unknown what it refers to)ic=false(unknown what it refers to)r1=0(starting row, zero index)c1=0(starting col, zero index)r2=43(end row, note change of index)c2=14(end col, note change of index)
我添加了这些内容，并且仅通过电子邮件发送了所选工作表中的选定部分.
I added those and was able to email just the selected portions of the selected sheet.
这篇关于如何仅在从Google表格电子表格导出的pdf中包含特定范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
