0

I am trying to make things easier for my team so I want to have them print out a given area from my Google Sheets document into a pdf with predefined margins/printing settings. The printed area is not on the active spreadsheet.

I just moved over from Excel to Spreadsheets. I know some VBA but do not really know that much about GAS. I did quite a lot of research but all the samples I found are based on UiApp which not supported any longer.

I found some threads (where I think messages are missing?): https://plus.google.com/u/1/115432608459317672860/posts/DQTbozyTsKk?cfem=1&pageId=none

Google Apps Script print sheet with margins

The last one I found was this one (PDF margins - Google Script) where I added the settings for margins (does it work that way though? I could not try it out yet because I do not know how to download the pdf. I tried do research but couldn't find anything..


  var report = SpreadsheetApp.getActive();            
  var pdfName = "Angebot";
  var sheetName = "Angebot";
  var sourceSheet = report.getSheetByName(sheetName);

  SpreadsheetApp.getActiveSpreadsheet().toast('Creating the PDF');

  // export url
  var url = 'https://docs.google.com/spreadsheets/d/'+report.getId()+'/export?exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
  + '&size=A4'                           // 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
  + '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
  + '&fzr=false'                         // do not repeat row headers (frozen rows) on each page
  + '&gid='+sourceSheet.getSheetId();    // the sheet's Id
  + '&top_margin=0'
  + '&left_margin=0' 
  + '&right_margin=0' 
  + '&bottom_margin=0' 


  var token = ScriptApp.getOAuthToken();

  // request export url
  var response = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' +  token
    }
  });

  var theBlob = response.getBlob().setName(pdfName+'.pdf');

  //var attach = {fileName:'Monthly Report.pdf',content:pdf, mimeType:'application/pdf'};

  var name = report.getRange("H1:H1").getValues();   // Get Name
  var emailTo = report.getRange("H2:H2").getValues();   // Get email
  var period = report.getRange("H3:H3").getValues();   // Get Reporting Period
  var subject = " - TEST Monthly Report - " + period;   // Construct the Subject Line
  var message = "Hi " + name + ", here is your latest report for " + period; // email body text

  // download pdf



}
Vincenzo
  • 182
  • 1
  • 13
  • 1
    If you want to save the PDF file using your script, please put the script of `DriveApp.createFile(theBlob)` after the line of `var theBlob = response.getBlob().setName(pdfName+'.pdf');`. By this, the PDF file is created to the root folder. But I'm not sure whether this is the result you want. – Tanaike Sep 12 '19 at 23:25
  • @Tanaike that seems to make it work, thanks you very much, creating the PDF file to the root folder now works very well! Best would be a direct download of the pdf file, how would I do that? (printing should do it as if I press print, it downloads the file) I just found out, that I need to add the margin settings before the gid settings otherwise it does not work. In case anyone will search about that. – Vincenzo Sep 13 '19 at 01:10
  • 1
    Thank you for replying. When you want to download the file to local PC, are there threads useful for your situation? https://stackoverflow.com/q/41093159 https://stackoverflow.com/q/40382041 – Tanaike Sep 13 '19 at 03:39
  • There is currently no way to access print settings from Sheets API, but you can access them from Docs, so perhaps that would help you get it set in the way you want. – AMolina Sep 13 '19 at 07:09
  • 1
    @Tanaike thanks for your answer, I thought maybe it would be possible to instead of saving it to Google Drive, providing a direct download without saving it to google drive before that. Idk. It seems like that the posts you link all nee dto have an exact ID and to already have the file in the drive app. Thank you very very much for trying though! I would give you a correct answer mark but unfortunately cannot as it is a comment :D – Vincenzo Sep 13 '19 at 15:31
  • @AMolina: That is really unfortunate. I would need Excel but thank you very much! – Vincenzo Sep 13 '19 at 15:31

0 Answers0