google-sheetsgoogle-apps-scriptpdfpdf-generationgoogle-sheets-api

How do I center my pdf download using app script?


I've used a script from a kind and helpful commentor and it worked if my format in the sheets are the same. However, when i type in the details and the row size changed, the page is shifted to the left. Is there a way to align it to the center every time?

Here is the script i used:

    function SavePage1() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Delivery Orders');
  const range = sheet.getRange("A1:D50");
  
  const url = 'https://docs.google.com/spreadsheets/d/' + sheet.getParent().getId() + 
    '/export?format=pdf&' +
    'size=a4&' + 
    'portrait=true&' + 
    'fitw=true&' +
    'scale=4&' +
    'gridlines=false&' + 
    'printtitle=false&' + 
    'sheetnames=false&' + 
    'pagenum=false&' + 
    'fzr=false&' + 
    'gid=' + sheet.getSheetId() + 
    '&range=' + range.getA1Notation() + 
    '&top_margin=0.197&' + 
    'right_margin=0.197&' + 
    'left_margin=0.197&' + 
    'bottom_margin=0.197&' + 
    'horizontal_alignment=center&' + 
    'vertical_alignment=top';
  
  const response = UrlFetchApp.fetch(url, {headers: {"Authorization": 'Bearer ' + ScriptApp.getOAuthToken()}});
  const blob = response.getBlob().setName("D0.pdf");
  
  const htmlOutput = HtmlService.createHtmlOutput(`
    <a href="data:application/pdf;base64,${Utilities.base64Encode(blob.getBytes())}" download="D0.pdf" id="downloadLink"></a>
    <script>document.getElementById('downloadLink').click(); google.script.host.close();</script>
  `);
  
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, "Download PDF");
}

Correct Format

Shifted to the left

I tried to manually change the margins in the script but this is not feasible for all pages as the sizes might change throughout. I want the script to automatically align the pdf to the center.


Solution

  • When I saw your script, I noticed the following modification points.

    So, please modify your script as follows and test it again.

    From:

    'horizontal_alignment=center&' + 
    'vertical_alignment=top';
    

    To:

    'horizontal_alignment=CENTER&' +
    'vertical_alignment=TOP';