spreadsheetexport-to-pdf

Google Spreadsheet export to PDF script hides formula outcome (values)

I am working on a project that uses a script to output a PDF on the touch of a button (assigned to said script).

The problem I am having, is that there are values in the sheet it's exporting to PDF, which are calculated with formulas, that are not showing on the exported PDF.

For extra clarity, say cell A1 has =SUM(1+1) which shows the outcome 2, it will not show it on the PDF, it rather shows an empty cell.

I am using this code:

function onOpen() {
  var submenu = [{name:"Save PDF", functionName:"generatePdf"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu('Export', submenu);
}

function generatePdf() {
  var sourceSpreadsheet = SpreadsheetApp.getActive();

  var sheets = sourceSpreadsheet.getSheets();
  var sheetName = sourceSpreadsheet.getActiveSheet().getName();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var titleName = sourceSpreadsheet.getRange("G1").getValue();
  var pdfName = titleName;

  var parents = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents();
  if (parents.hasNext()) {
    var folder = parents.next();
  }
  else {
    folder = DriveApp.getRootFolder();
  }

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

  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];

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

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

  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true) ;
}

Does someone have a suggestion to fix my problem?

Solution

  • About For extra clarity, say cell A1 has =SUM(1+1) which shows the outcome 2, it will not show it on the PDF, it rather shows an empty cell., when I tested this using your showing script, 2 is showing in the exported PDF file. Unfortunately, I cannot replicate your situation. So, this is just my guess. Please modify your script as follows and test it again.

    From:

    destRange.setValues(sourcevalues);

var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);

    To:

    destRange.setValues(sourcevalues);
SpreadsheetApp.flush(); // Added
var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);