4

I have created a script where in Google Sheet is converted to PDF and sent in an email (via Gmail API). for certain records, the PDF size gets increased up to 10 MB (with script) and actually the file size is 130 KB when downloaded manually.

Please find the below syntax which is used to convert sheet to PDF

var blob=DriveApp.getFileById(<<Google Sheet ID>>).getAs('application/pdf');

What could be the possible reason for this issue and How to resolve it?

  • Exportation of GAS may have different parameters than the sheet one. Look at this post it may help to reduce the size by parametering well : https://stackoverflow.com/questions/45209619/google-apps-script-getasapplication-pdf-layout – Julien Maret Jun 26 '20 at 14:01

1 Answers1

1

Your spreadsheet might have many empty cells which get converted into the pdf the way you do it.

I recommend you instead to convert in a more manual way, with the UrlfetchApp - this allows you to specify different options, among others the range you want to convert.

The following sample shows how to export only the data with contents (dataRange) for a spreadsheet that contains onlyone sheet:

function myFunction() {
  var token = ScriptApp.getOAuthToken();
  var ss = SpreadsheetApp.openById("<<Google Sheet ID>>");
  var sheet = ss.getActiveSheet();
  var name = ss.getName();
  var url = "https://docs.google.com/spreadsheets/d/" + "<<Google Sheet ID>>" + "/export?";  
  var options = 'exportFormat=pdf&format=pdf'        // export format
  + '&size=A4'                                       // paper size 
  + '&portrait=true'                                // orientation
  
  var range = sheet.getDataRange().getA1Notation();
  var response = UrlFetchApp.fetch(url + options + '&gid=' + sheet.getSheetId() + "&range=" + range, 
    {
      headers: {
        'Authorization': 'Bearer ' +  token
      },
        muteHttpExceptions:true
    });
  var blob = DriveApp.createFile(response.getBlob().setName(name));
}
ziganotschka
  • 25,866
  • 2
  • 16
  • 33