仅生成一张电子表格的PDF [英] Generate PDF of only one sheet of my spreadsheet

问题描述

我需要一个脚本，只用一张电子表格创建PDF。
我目前有一个脚本可以生成一个PDF文件，但是可以处理整个文件。
我无法将这些值复制到另一个文件，因为我需要导出的图纸是从另一张图表中提取数据的图形。
你能帮我吗？谢谢。

  function myFunction（）{
 var archivo = SpreadsheetApp.getActive（）; 
 var hoja = archivo.getSheetByName（Graficos 2）; 
 var id = archivo.getId（）; 
 var archivoId = DriveApp.getFileById（id）; 
 var archivoBlob = archivoId.getBlob（）; 
 var carpetaId = archivoId.getParents（）; 
 var contenidoPDF = archivo.getAs（MimeType.PDF）; 
 carpetaId.next（）。createFile（contenidoPDF）; 
} 
  

解决方案

或更少此处。

简而言之，您可以修改Url queryString中的导出参数以获取特定工作表。然后将blob转换为pdf。

以下是相同的代码，基本代码从 here

 函数emailSpreadsheetAsPDF（）{$​​ b 
 $ b //将电子表格的PDF发送到这个电子邮件地址
 var email =您的电子邮件ID; 
 
 //获取当前有效的电子表格网址（链接）
 //或者使用SpreadsheetApp.openByUrl（<<<<<<<<><<< 
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sheet = ss.getSheetByName（<您的工作表名称>）
 
 //电子邮件主题
 var subject =从工作表产生的PDF+工作表。的getName（）; 
 
 //电子邮件正文可以是HTML，也可以是您的徽标图片 - 请参阅ctrlq.org/html-mail 
 var body =安装< a href ='http：// www .labnol.org / email-sheet'>电子邮件电子表格附加组件< / a>进行一键转换。; 
 
 //基本网址
 var url =https://docs.google.com/spreadsheets/d/SS_ID/export?\".replace(\"SS_ID，ss.getId（ ））; 
 
 / *指定PDF导出参数
从：https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579 
 * / 
 
 var url_ext ='exportFormat = pdf& format = pdf'// export as pdf / csv / xls / xlsx 
 +'& size = letter'// paper size legal / letter / A4 
 +'& portrait = false'// orientation，for landscape 
 +'& fitw = true& source = labnol'//适合页面宽度，实际为false大小
 +'& sheetnames = false& printtitle = false'//隐藏可选页眉和页脚
 +'& pagenumbers = false& gridlines = false'//隐藏页码和网格线
 +'& fzr = false'//不要在每页上重复行标题（冻结行）
 +'& gid ='; //工作表的Id 
 
 var token = ScriptApp.getOAuthToken（）; 
 
 
 //创建一个空数组来存放您获取的blob 
 var blobs; 
 
 
 //将您的特定工作表转换为blob 
 var response = UrlFetchApp.fetch（url + url_ext + sheet.getSheetId（），{
 headers：{
'授权'：'承载'+令牌
} 
}）; 
 
 //将响应转换为blob并存储在我们的数组中
 blobs = response.getBlob（）。setName（sheet.getName（）+'.pdf'）; 
 
 
 //定义范围
 Logger.log（使用的存储空间：+ DriveApp.getStorageUsed（））; 
 
 //如果允许发送电子邮件，发送PDF附件的电子邮件
 if（MailApp.getRemainingDailyQuota（）> 0）
 GmailApp.sendEmail（电子邮件，主题， body，{
 htmlBody：body，
 attachments：[blobs] 
}）; 
} 
  

编辑：要合并特定工作表并将其转换进入和pdf。

首先，你将不得不获得你想合并到pdf中的所有工作表的工作表对象

  var sheet2 = ss.getSheetByName（< Your sheet name>）
 var sheet3 = ss.getSheetByName（< Your sheet name>）
  

然后将每个工作表的sheetId传递给由％分隔的URL查询EE％B8％80 ，像这样

  url + = url_ext + sheet.getSheetId（）+ ％EE％B8％80+ sheet2.getSheetId（）+％EE％B8％80+ sheet3.getSheetId（）
 var response = UrlFetchApp.fetch（url，{
 headers：{ 
'授权'：'承载'+令牌
} 
}）; 
  

I need a script that creates a PDF with only one sheet of my spreadsheet. I currently have a script that generates a PDF but does it with the entire file. I can not copy the values to another file, since the sheet I need to export is graphics with data extracted from another sheet. Could you help me? Thank you.

function myFunction() {
  var archivo = SpreadsheetApp.getActive();
  var hoja = archivo.getSheetByName("Graficos 2");
  var id = archivo.getId();
  var archivoId = DriveApp.getFileById(id);
  var archivoBlob = archivoId.getBlob();
  var carpetaId = archivoId.getParents();
  var contenidoPDF = archivo.getAs(MimeType.PDF);
  carpetaId.next().createFile(contenidoPDF);
}

解决方案

This question has been addressed more or less here.

In short, you can modify export parameters in Url queryString to get a specific sheet. And then convert the blob to a pdf.

Here is the code for the same, base code was obtained from here

function emailSpreadsheetAsPDF() {

  // Send the PDF of the spreadsheet to this email address
  var email = "Your Email Id"; 

  // Get the currently active spreadsheet URL (link)
  // Or use SpreadsheetApp.openByUrl("<<SPREADSHEET URL>>");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("<Your Sheet name>")

  // Subject of email message
  var subject = "PDF generated from sheet " + sheet.getName(); 

  // Email Body can  be HTML too with your logo image - see ctrlq.org/html-mail
  var body = "Install the <a href='http://www.labnol.org/email-sheet'>Email Spreadsheet add-on</a> for one-click conversion.";

  // Base URL
  var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ss.getId());

  /* Specify PDF export parameters
  From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
  */

  var url_ext = 'exportFormat=pdf&format=pdf'        // export as pdf / csv / xls / xlsx
  + '&size=letter'                       // paper size legal / letter / A4
  + '&portrait=false'                    // orientation, false for landscape
  + '&fitw=true&source=labnol'           // fit to page width, false for actual size
  + '&sheetnames=false&printtitle=false' // hide optional headers and footers
  + '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
  + '&fzr=false'                         // do not repeat row headers (frozen rows) on each page
  + '&gid=';                             // the sheet's Id

  var token = ScriptApp.getOAuthToken();


  //make an empty array to hold your fetched blobs  
  var blobs;


    // Convert your specific sheet to blob
    var response = UrlFetchApp.fetch(url + url_ext + sheet.getSheetId(), {
      headers: {
        'Authorization': 'Bearer ' +  token
      }
    });

    //convert the response to a blob and store in our array
    blobs = response.getBlob().setName(sheet.getName() + '.pdf');


  // Define the scope
  Logger.log("Storage Space used: " + DriveApp.getStorageUsed());

  // If allowed to send emails, send the email with the PDF attachment
  if (MailApp.getRemainingDailyQuota() > 0) 
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments:[blobs]     
    });  
}

Edit: To combine specific sheets and convert it into and pdf.

Firstly you will have to obtain the sheet object of all the sheets you want to merge into the pdf

var sheet2 = ss.getSheetByName("<Your Sheet name>")
var sheet3 = ss.getSheetByName("<Your Sheet name>")

Then pass the sheetId of each of the sheet to the URL query delimited by %EE%B8%80, Like so

   url += url_ext + sheet.getSheetId()+ "%EE%B8%80"+sheet2.getSheetId()+ "%EE%B8%80"+sheet3.getSheetId()
   var response = UrlFetchApp.fetch(url, {
      headers: {
        'Authorization': 'Bearer ' +  token
      }
    });

这篇关于仅生成一张电子表格的PDF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！