javascriptgoogle-apps-scriptpdfpdf-generationauto-generate

Define specific area to Generate PDF - Google Apps Script

In this case I have an annoying problem. I can't control a specific area to be a PDF format. Every time I execute the code, the result is like this:current preview

But I'm expecting the result is like this: let's say B2:G23

Here is the code:

function createblobpdf(sheetName, pdfName) {
  var sourceSpreadsheet = SpreadsheetApp.getActive();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var url = 'https://docs.google.com/spreadsheets/d/' + sourceSpreadsheet.getId() + '/export?exportFormat=pdf&format=pdf' + // export as pdf / csv / xls / xlsx
    + '&size=7' // paper size legal / letter / A4
    + '&portrait=true' // orientation, false for landscape
    + '&fitw=true' // fit to page width, false for actual size
    + '&sheetnames=false&printtitle=false' // hide optional headers and footers
    + '&pagenum=RIGHT&gridlines=false' // hide page numbers and gridlines
    + '&fzr=true' //  repeat row headers (frozen rows) on each page
    + '&horizontal_alignment=CENTER' //LEFT/CENTER/RIGHT
    + '&vertical_alignment=TOP' //TOP/MIDDLE/BOTTOM
    + '&gid=' + sourceSheet.getSheetId(); // the sheet's Id
    + '&ir=false'
    + '&ic=false'
    + '&r1=0'
    + '&c1=0'
    + '&r2=24'
    + '&c2=8'

Hopefully someone could solve my problem

Thank You!

I'm expecting the result is like this: let's say B2:G23

Solution

  • I believe your goal is as follows.

    Modification points:

    When these points are reflected in your script, how about the following modification?

    Modified script:

    function createblobpdf(sheetName, pdfName) {
  var sourceSpreadsheet = SpreadsheetApp.getActive();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var url = 'https://docs.google.com/spreadsheets/d/' + sourceSpreadsheet.getId() + '/export?exportFormat=pdf&format=pdf' + // export as pdf / csv / xls / xlsx
    + '&size=7' // paper size legal / letter / A4
    + '&portrait=true' // orientation, false for landscape
    + '&fitw=true' // fit to page width, false for actual size
    + '&sheetnames=false&printtitle=false' // hide optional headers and footers
    + '&pagenum=RIGHT&gridlines=false' // hide page numbers and gridlines
    + '&fzr=true' //  repeat row headers (frozen rows) on each page
    + '&horizontal_alignment=CENTER' //LEFT/CENTER/RIGHT
    + '&vertical_alignment=TOP' //TOP/MIDDLE/BOTTOM
    + '&gid=' + sourceSheet.getSheetId() // the sheet's Id
    + '&ir=false'
    + '&ic=false'
    + '&r1=1'
    + '&c1=1'
    + '&r2=23'
    + '&c2=7';

  var blob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob();
  DriveApp.createFile(blob.setName(pdfName));
}

    References: