Script download google spreadsheet to pdf - images missing

Aleksander Glapa

Is there any way to include overgrid images in the spreadsheet when downloading as PDF with the script provided by @ZektorH on the question Script to download a range of cells in google sheet as PDF to local computer and other automation scripts? ? I've been trying for a while now with no success.

Script in question:

function downloadRangeToPdf() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A1:E20");

  //Create temporary Spreadsheet
  var tempSpreadsheet = SpreadsheetApp.create("tempSheetInvoiceExport", range.getValues().length, range.getValues()[0].length);
  var tempSheet = tempSpreadsheet.getSheets()[0];
  var tempRange = tempSheet.getRange("A1:E20");

  tempRange.setValues(range.getDisplayValues());
  tempRange.setTextStyles(range.getTextStyles());
  tempRange.setBackgrounds(range.getBackgrounds());
  tempRange.setFontColors(range.getFontColors());
  tempRange.setFontFamilies(range.getFontFamilies());
  tempRange.setFontLines(range.getFontLines());
  tempRange.setFontStyles(range.getFontStyles());
  tempRange.setFontWeights(range.getFontWeights());
  tempRange.setHorizontalAlignments(range.getHorizontalAlignments());
  tempRange.setNumberFormats(range.getNumberFormats());
  tempRange.setTextDirections(range.getTextDirections());
  tempRange.setTextRotations(range.getTextRotations());
  tempRange.setVerticalAlignments(range.getVerticalAlignments());
  tempRange.setWrapStrategies(range.getWrapStrategies());

  SpreadsheetApp.flush(); //Force changes to be written before proceeding.

  //Generate Download As PDF Link

  var url = 'https://docs.google.com/spreadsheets/d/{ID}/export?'.replace('{ID}', tempSpreadsheet.getId());
  var exportOptions = 'exportFormat=pdf&format=pdf' + // export as pdf / csv / xls / xlsx
    '&size=letter' + // paper size legal / letter / A4
    '&portrait=true' + // 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
    '&top_margin=0.00' + //All four margins must be set!
    '&bottom_margin=0.00' + //All four margins must be set!
    '&left_margin=0.00' + //All four margins must be set!
    '&right_margin=0.00' + //All four margins must be set!
    '&gridlines=false' + //true/false
    '&gid=' + tempSheet.getSheetId(); // the sheet's Id
  var token = ScriptApp.getOAuthToken();

  var blob = UrlFetchApp.fetch(url + exportOptions, {
    headers: {
                               Authorization: 'Bearer '+token
    }
  }).getBlob().setName(tempSpreadsheet.getName()+".pdf");

  var pdfFile = DriveApp.createFile(blob);

  var downloadLink = HtmlService
    .createHtmlOutput('<p>Download your file <a href="' + pdfFile.getUrl() + '" target="_blank">here</a>.</p>')
    .setWidth(200)
    .setHeight(100);

  SpreadsheetApp.getUi().showModalDialog(downloadLink, "Download PDF");

  DriveApp.getFileById(tempSpreadsheet.getId()).setTrashed(true); //Place temporary sheet on trash

}

Ron M

The reason why the image was not included in your pdf file is because you did not copy the image available in your source sheet to your temporary sheet. You only copied the cell values in this line tempRange.setValues(range.getDisplayValues());

You can actually export a range to pdf just by adding this option &range=<SheetRange>

Sample Code:

function downloadRangeToPdf() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var pdfRange = "A1:H50"

  //Generate Download As PDF Link
  var url = 'https://docs.google.com/spreadsheets/d/{ID}/export?'.replace('{ID}', SpreadsheetApp.getActiveSpreadsheet().getId());
  var exportOptions = 'exportFormat=pdf&format=pdf' + // export as pdf / csv / xls / xlsx
    '&size=letter' + // paper size legal / letter / A4
    '&portrait=true' + // 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
    '&top_margin=0.00' + //All four margins must be set!
    '&bottom_margin=0.00' + //All four margins must be set!
    '&left_margin=0.00' + //All four margins must be set!
    '&right_margin=0.00' + //All four margins must be set!
    '&gridlines=false' + //true/false
    '&gid=' + sheet.getSheetId() + // the sheet's Id
    '&range='+pdfRange; // Sheet range

  var token = ScriptApp.getOAuthToken();

  var blob = UrlFetchApp.fetch(url + exportOptions, {
    headers: {
                               Authorization: 'Bearer '+token
    }
  }).getBlob().setName("tempSheetInvoiceExport.pdf");

  var pdfFile = DriveApp.createFile(blob);

  var downloadLink = HtmlService
    .createHtmlOutput('<p>Download your file <a href="' + pdfFile.getUrl() + '" target="_blank">here</a>.</p>')
    .setWidth(200)
    .setHeight(100);

  SpreadsheetApp.getUi().showModalDialog(downloadLink, "Download PDF");

}