0

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

}
Aleksander Glapa
  • 3
  • 2
  • 1
    What do you mean by overgrid images? could you provide a sample sheet to work on with your expected output? – Ron M Jun 28 '21 at 15:41
  • Sure, here is a sample sheet with some stock image and a Download button with the assigned script: https://docs.google.com/spreadsheets/d/1UA5TacM7-X9XpZwtcxPYlVpQCil_ppU63sZERGy2rAQ/edit?usp=sharing The idea is for the image to be visible in the PDF export (currently, just text and cells formatting is there). – Aleksander Glapa Jun 29 '21 at 12:32

1 Answers1

0

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

}

What it does?

  1. Get the active sheet and define a pdfRange to be exported
  2. Generate an pdf export link with sheet range options '&range='+pdfRange
  3. Create a pdf file from the fetched blob
  4. Display the pdf file url

Output:

enter image description here

Ron M
  • 5,791
  • 1
  • 4
  • 16