将特定工作表导出为PDF [英] Export Specific sheet to PDF

问题描述

我知道之前在

虽然垃圾桶中的临时Google表格在各个单元格中都有值. vlookup 字段下方的相同屏幕截图以红色突出显示.

请帮助我解决问题.

解决方案

只需添加一行以刷新应用程序即可完成工作.更新的代码:

  function generatePdf(){//获取活动的电子表格.var sourceSpreadsheet = SpreadsheetApp.getActive();//获取活动工作表.varsheets = sourceSpreadsheet.getSheets();var sheetName = sourceSpreadsheet.getActiveSheet().getName();var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);//将输出文件名设置为SheetName.var pdfName = sheetName;//获取包含电子表格的文件夹以保存pdf.var parent = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents();如果(parents.hasNext()){var folder = Parents.next();}别的 {文件夹= DriveApp.getRootFolder();}//复制整个电子表格.var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf"，文件夹))//删除多余的工作表.varsheets = destSpreadsheet.getSheets();对于(i = 0; i< sheets.length; i ++){如果(sheets [i] .getSheetName()！= sheetName){destSpreadsheet.deleteSheet(sheets [i]);}}var destSheet = destSpreadsheet.getSheets()[0];//用文本重新填充单元格值(以避免损坏的引用).var sourceRange = sourceSheet.getRange(1,1，sourceSheet.getMaxRows()，sourceSheet.getMaxColumns());var sourcevalues = sourceRange.getValues();var destRange = destSheet.getRange(1，1，destSheet.getMaxRows()，destSheet.getMaxColumns());destRange.setValues(sourcevalues);SpreadsheetApp.flush();//保存为pdf.var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);var newFile = folder.createFile(theBlob);//删除临时表.DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);}  

I am aware that similar type of question has being asked earlier under the Using Google Apps Script to save a single sheet from a spreadsheet as pdf in a specific folder

The code that was used is working for me and is mentioned below:

function generatePdf() {
  // Get active spreadsheet.
  var sourceSpreadsheet = SpreadsheetApp.getActive();

  // Get active sheet.
  var sheets = sourceSpreadsheet.getSheets();
  var sheetName = sourceSpreadsheet.getActiveSheet().getName();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);

  // Set the output filename as SheetName.
  var pdfName = sheetName;

  // Get folder containing spreadsheet to save pdf in.
  var parents = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents();
  if (parents.hasNext()) {
    var folder = parents.next();
  } else {
    folder = DriveApp.getRootFolder();
  }

  // Copy whole spreadsheet.
  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))

  // Delete redundant sheets.
  var sheets = destSpreadsheet.getSheets();
  for (i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() != sheetName) {
      destSpreadsheet.deleteSheet(sheets[i]);
    }
  }

  var destSheet = destSpreadsheet.getSheets()[0];

  // Repace cell values with text (to avoid broken references).
  var sourceRange = sourceSheet.getRange(1, 1, sourceSheet.getMaxRows(), sourceSheet.getMaxColumns());
  var sourcevalues = sourceRange.getValues();
  var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
  destRange.setValues(sourcevalues);

  // Save to pdf.
  var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
  var newFile = folder.createFile(theBlob);

  // Delete the temporary sheet.
  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}

The problem that I encounter is that my source sheet has a few fields which are auto populated in the sheet using vlookup. These fields are exported in the temp file which is created by this script as I have checked the trash and the field values are present but the PDF generated by the code is missing all the vlookup data.

The sample image of PDF is as below in which the vlookup missing entries are marked in yellow:

While the temp google sheet which is in trash has the values in the respective cells. Screenshot of the same is below the vlookup fields are highlighted in red.

Kindly help me with the solution.

解决方案

Just needed to add a line to flush the app did the work. Updated code:

function generatePdf() {
  // Get active spreadsheet.
  var sourceSpreadsheet = SpreadsheetApp.getActive();
  
  // Get active sheet.
  var sheets = sourceSpreadsheet.getSheets();
  var sheetName = sourceSpreadsheet.getActiveSheet().getName();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  
  // Set the output filename as SheetName.
  var pdfName = sheetName;

  // Get folder containing spreadsheet to save pdf in.
  var parents = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents();
  if (parents.hasNext()) {
    var folder = parents.next();
  }
  else {
    folder = DriveApp.getRootFolder();
  }
  
  // Copy whole spreadsheet.
  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))

  // Delete redundant sheets.
  var sheets = destSpreadsheet.getSheets();
  for (i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() != sheetName){
      destSpreadsheet.deleteSheet(sheets[i]);
    }
  }
  
  var destSheet = destSpreadsheet.getSheets()[0];

  // Repace cell values with text (to avoid broken references).
  var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
  var sourcevalues = sourceRange.getValues();
  var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
  destRange.setValues(sourcevalues);
  SpreadsheetApp.flush();
  // Save to pdf.
  var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
  var newFile = folder.createFile(theBlob);

  // Delete the temporary sheet.
  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}

这篇关于将特定工作表导出为PDF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！