Export Specific sheet to PDF

Question

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.

score 0 · Answer 1 · answered Oct 22 '20 at 20:36

Add the contentOnly: true in line 38. See the code. Hope this solves the problem. This will clear all the formula's. (im not sure the setValues() accept this extra parameter. the copyTo does, so you can make a copy of the sheet to a contentOnly sheet and then use that for the pdf)

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, {contentsOnly:true});

  // 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);
}

This line is getting an error "Exception: The parameters (number[],(class)) don't match the method signature for SpreadsheetApp.Range.setValues. (line 40, file "PDF")" — Harsh, Oct 22 '20 at 20:45
Yeah that is what is was think, try the copyTo i mentioned in above the code. https://webapps.stackexchange.com/questions/118136/script-to-copy-values-only — RemcoE33, Oct 22 '20 at 20:49

score 0 · Answer 2 · answered Oct 22 '20 at 20:59

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);
}

Export Specific sheet to PDF

2 Answers2

Linked