-1

I already had a look to the community but I'm struggling in building a script should do the following:

  • select a specific range of cells of a Google Sheet sheet
  • save the selected area as a PDF to be downloaded on the PC (no need to save to GDrive or to add a specif name to the PDF)

I tried recording a Macro but dosn't work. Can you give some hints on how to move on? Thanks.

JacopoS
  • 1
  • 1
  • Welcome to [so]. Please show what you tried and add brief description (including at least one link to a similar question) of your search efforts as is suggested in [ask]. – Rubén Mar 05 '21 at 18:12
  • Struggling is often the first step in learning. Often the first step in getting an answer here is writing a script on your own. – Cooper Mar 05 '21 at 18:47
  • We need more detail. Why dont your share your sheet with dummy data? – Sebastian Mar 05 '21 at 21:08

2 Answers2

1

These reference links may help you to have some hints on how you will achieve your target:

This first link shows you how to download a range of cell as PDF:

Script to download a range of cells in google sheet as PDF to local computer and other automation scripts?

Script that exports a range to PDF without borders in reference to the first link and answered by ZektorH:

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

}

Then this second link shows you how to download a spreadsheet as PDF to local computer. You can refer best to Tanaike's answer here:

How to download single sheet as PDF (not export to Google Drive)

EDITED (Answer to your question below):

You need to use the sheetID of the second sheet in order to save the PDF for the second sheet.

You should change

FROM:

var sheetId = SpreadsheetApp.getActiveSheet().getSheetId();

TO:

var sheetId = ss.getSheetId();

Because you get the reference of the second spreadsheet by the following logic:

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];

So, you just need to use the following code to get the sheet id of the second spreadsheet:

var sheetId = ss.getSheetId();

For your original logic, which is var sheetId = SpreadsheetApp.getActiveSheet().getSheetId(); it will only get the sheetId of the first sheet.

Monique G.
  • 239
  • 1
  • 6
0

thanks a lot for your hints. I basically reached what I need even if I need a small adaptation. My spreadsheet has 2 sheets, I would like to add a script button to sheet 1 to download as PDF sheet 2, I slightly modified the script as reported below but I'm still getting my sheet 1 as PDF.

function downloadSheetAsPDF() {
  var filename = "Filename.pdf"; // Please set the filename here.

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];
  var sheetId = SpreadsheetApp.getActiveSheet().getSheetId();

  // Creat PDF file as a temporary file and create URL for downloading.
  var url = "https://docs.google.com/a/mydomain.org/spreadsheets/d/" + SpreadsheetApp.getActiveSpreadsheet().getId() + "/export?exportFormat=pdf&gid=" + sheetId + "&access_token=" + ScriptApp.getOAuthToken();
  var blob = UrlFetchApp.fetch(url).getBlob().setName(filename);
  var file = DriveApp.createFile(blob);
  var dlUrl = "https://drive.google.com/uc?export=download&id=" + file.getId();

  // Open a dialog and run Javascript for downloading the file.
  var str = '<script>window.location.href="' + dlUrl + '"</script>';
  var html = HtmlService.createHtmlOutput(str);
  SpreadsheetApp.getUi().showModalDialog(html, "Download PDF");
  file.setTrashed(true);

  // This is used for closing the dialog.
  Utilities.sleep(3000);
  var closeHtml = HtmlService.createHtmlOutput("<script>google.script.host.close()</script>");
  SpreadsheetApp.getUi().showModalDialog(closeHtml, "Download PDF");
}

Any suggestion? Thanks.

JacopoS
  • 1
  • 1