google-apps-script - 使用脚本将 PDF 导出到驱动器

Question

我正在尝试将 PDF 导出到我的驱动器。该脚本当前执行此操作。但是，我只希望导出特定范围的单元格，并且我希望保存文件的名称从工作表中的单元格中提取信息。请帮忙！我有以下脚本：

function getpdf2(){
SpreadsheetApp.flush();
   
      var theurl = 'https://docs.google.com/spreadsheets/d/'
      + '1C-ONDP4pZeUrlMYekvg0i8oNceASL1s4_o8oBpQuzHk'  
      + '/export?exportFormat=pdf&format=pdf'
      + '&size=LETTER'
      + '&portrait=false'
      + '&fitw=false'       
      + '&top_margin=0.50'              
      + '&bottom_margin=0.50'          
      + '&left_margin=0.50'             
      + '&right_margin=0.50'           
      + '&sheetnames=false&printtitle=false'
      + '&pagenum=false'
      + '&gridlines=false'
      + '&fzr=FALSE'      
      + '&gid='
      + '1101884643';       

      var token = ScriptApp.getOAuthToken();
      
      var docurl = UrlFetchApp.fetch(theurl, { headers: { 'Authorization': 'Bearer ' +  token } });
      var fileid = DriveApp.createFile(docurl.getBlob()).setName('CAITLYN!B7.pdf').getId();
      
      var pdf = docurl.getBlob().setName('CAITLYN!B7.pdf');
      var pdf = docurl.getBlob().getAs('application/pdf').setName('testss.pdf'); 
      var filetodel = DriveApp.getFileById(fileid);

      if (DriveApp.getFoldersByName("PDF Quotes").hasNext()){
      var folder = DriveApp.getFoldersByName("PDF Quotes").next();
      filetodel.makeCopy(folder);
       }
      DriveApp.removeFile(filetodel);
 }

标签： google-apps-script

Answer 1

由于没有导出特定范围的本地方法，我们需要访问电子表格中的数据。

为了简化导出过程，我们需要做的是将数据范围复制到临时工作表中，然后将其粘贴到第一组单元格中。

之后，我们需要将复制的单元格之外的单元格隐藏起来。

最后，我们将该临时工作表导出为驱动器中的 pdf。

这是整个工作代码。

function exportRangeToPdf() {
  // Get the sheet id, sheet name, range of cells you want to export 
  // and the folder ID in your drive where you want to store the pdf
  var sheetId = '1ZzkBAR1EgismFCd-hxOUyC_uZKYucu7Av-lDHhrEMx4';
  var sheetName = 'Sheet1';
  var sheetSrcRange = 'B5:C6';
  var driveId = '0B55C21aJsSBlfk9FTjRqOG8tb3hjR1N4MTU1YjVPNU4weGVhSldfU3F4OXladVVNMF9Ccms';

  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);
  var range = sheet.getRange(sheetSrcRange);
  var sheetDstRange = 'A1:' + sheet.getRange(range.getHeight(), range.getWidth()).getA1Notation();
  var rangeValues = range.getValues();
  var folder = DriveApp.getFolderById(driveId);

  // Feel free to modify if needed
  // From sheet name (e.g. Sheet1.pdf)
  var pdfName = sheetName + '.pdf';
  // From specific cell, can be inside or outside your selected range to export (e.g. exportedPDF.pdf)
  // var pdfName = sheet.getRange('C7').getValue() + '.pdf';
  // From the combination of sheet name and range you chose (e.g. Sheet1_B5:C6.pdf)
  // var pdfName = sheetName + '_' + sheetSrcRange + '.pdf';
  
  // Create a blank spreadsheet to be able to export just the range
  var destSpreadsheet = SpreadsheetApp.create('PDF');
  
  // If our range is 2 rows and 2 columns we want it to be copied from A1 to B2
  // Then we can hide the rest of columns and rows and export 
  var sheet2 = destSpreadsheet.getSheetByName('temp');
  if(!sheet2){
    destSpreadsheet.insertSheet('temp').getRange(sheetDstRange).setValues(rangeValues);
    var sheet2 = destSpreadsheet.getSheetByName('temp');
  }
  
  // Hide all the rows and columns that do not have content 
  sheet2.hideRows(sheet2.getLastRow() + 1, sheet2.getMaxRows() - sheet2.getLastRow());
  sheet2.hideColumns(sheet2.getLastColumn() + 1, sheet2.getMaxColumns() - sheet2.getLastColumn());
  // Delete the first sheet that is automatically created when you create a new spreadsheet
  destSpreadsheet.deleteSheet(destSpreadsheet.getSheetByName(sheetName));
  
  // Export our new spreadsheet to PDF
  var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
  folder.createFile(theBlob);
  
  // Delete the spreadsheet we created to export this range. 
  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}

样本数据：

输出：