App Script to download specific cells in Google Sheet to PDF

Edit: 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");
}

Is there a way to select cells and download them as PDF using macro/app script? I am totally new to app script as I have been using excel and decided to change to google sheets for its connectivity. On excel, I recorded a Macro of me selecting the cells then print to PDF, and it works. But when I tried to record the same macro in google sheets, it selects the cells, then ends without downloading.

My sheet contains multiple invoices, but they are all in the same format. Example, 1st invoice is in Rows 1-50 (First 50 rows), the 2nd invoice will be in Rows 51-100 (Second 50 rows). I have attached screenshots of how it looks like. I have attached one from a phone screenshot as the desktop version is not scrollable/too short.

.

What I want the App Script to do is:-

1. Select Corresponding Page (Button in H1 - Cells A1 TO G50/Button in H51 - Cells A51 TO G100)
2. Download as PDF
   • Export: Selected Cells
   • Paper Size: A4 (8.27" X 11.69")
   • Page Orientation: Portrait
   • Scale: Fit to Height
   • Margins: Custom Numbers - Top: 0.197" - Right: 0.197" - Left: 0.197" - Bottom: 0.197"
   • Do not show gridlines
   • Do not show notes
   • Page Order: Order, then Down
   • Alignment: Horizontal - Centre Vertical - Top
   • Do not show any headers/footers
3. Download on my Computer (Not in Google Drive)
4. Do not need to rename (I can rename it myself later once its downloaded)

Im hoping some kind soul can help come out with an app script that I can just copy and paste into my file. For my excel, it downloads all the pages in the workbook when I press the button. I'm hoping in the google sheet, I can download the corresponding page beside the inserted button. I'm thinking to assign an individual script to each different buttons, so please advise me on what I should change in the script for this to work.

I have found some app scrips solutions and tried it but they do not work on my file. Once again, I am totally new to app scrips, thus I do not understand it at all.

Here is the link that I tried:

• How to Print sheet/range using .gs script in Google Sheets?
• creating a PDF from a specific range of cells in google sheets and saving it to google drive

I have no idea why these linked solutions dont work. When I try to run them, nothing happens, pop up says 'running script' then 1 second later it says 'script end' but nothing changed. There are no new download nor changes. I am not sure if I am running it wrongly? Or am I supposed to edit some things myself? Please help me, I am very lost.

You can achieve your desired output by using URLfetch

Here are the steps you need to do:

1. Open your Google Sheets
2. Click the Extensions then Apps script
3. Replace the existing code with the code below
4. Run the script then accept the authorization

Complete code:

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('INVOICES');
  const range = sheet.getRange("A1:G50");
  
  const url = 'https://docs.google.com/spreadsheets/d/' + sheet.getParent().getId() + 
    '/export?format=pdf&' +
    'size=letter&' + 
    'portrait=true&' + 
    'fitw=true&' +
    '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("sheet-export.pdf");
  
  const htmlOutput = HtmlService.createHtmlOutput(`
    <a href="data:application/pdf;base64,${Utilities.base64Encode(blob.getBytes())}" download="sheet-export.pdf" id="downloadLink"></a>
    <script>document.getElementById('downloadLink').click(); google.script.host.close();</script>
  `);
  
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, "Download PDF");
}

Here's how this code works:

5. Get the complete URL including the parameters you have set, and the range to make a PDF
6. Fetch the URL
7. Converts it to a blob
8. Creates an HTML to download a link that triggers automatically.

Sample output:

If you want to change the range of what to convert into a PDF, you may adjust the range of this code:

  const range = sheet.getRange("A1:E28");

Reference: Urlfetch