Generate and Email PDF From a Range of Cells in Google Sheets

Question

This is my first time posting a question and I'm a complete novice so I apologize upfront if I don't include all the information you may need.

I have a Google Sheet that contains several tabs of data that is hand-keyed into a table. For eg: Data Entry Table in Sheet

Within each sheet, there are numerous tables where the data from each "company" is entered that falls within a specific region.

What I'm trying to do is adjust a script that will go to the sheet specified, grab a range of cells for a specific table, convert it to a PDF and email it using the email address listed in another tab of the same sheet. I have searched online and found the following script:

function emailSpreadsheetAsPDF() {
  DocumentApp.getActiveDocument();
  DriveApp.getFiles();

  // This is the link to my spreadsheet with the Form responses and the Invoice Template sheets
  // Add the link to your spreadsheet here 
  // or you can just replace the text in the link between "d/" and "/edit"
  // In my case is the text: 17I8-QDce0Nug7amrZeYTB3IYbGCGxvUj-XMt8uUUyvI
  const ss = SpreadsheetApp.openByUrl("SHEETURLGOESHERE/edit");

  // We are going to get the email address from the cell "B7" from the "Invoice" sheet
  // Change the reference of the cell or the name of the sheet if it is different
  const value = ss.getSheetByName("emails").getRange("A2").getValue();
  const email = value.toString();

  // Subject of the email message
  const subject = ss.getSheetByName("emails").getRange("B2").getValue();

  // Email Text. You can add HTML code here - see ctrlq.org/html-mail
  const body = "Sent via Generate Invoice from Google Form and print/email it";

  // Again, the URL to your spreadsheet but now with "/export" at the end
  // Change it to the link of your spreadsheet, but leave the "/export"
  const url = 'SHEETURLGOESHERE/export?';

  const exportOptions =
    'exportFormat=pdf&format=pdf' + // export as pdf
    '&size=letter' + // paper size letter / You can use A4 or legal
    '&landscape=true' + // orientation portal, use false for landscape
    '&fitw=true' + // fit to page width false, to get the 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=879200050' // the sheet's Id. Change it to your sheet ID.
    '&if=false' +
    '&ic=false' +
    '&r1=51' +
    '&c1=0' +
    '&r2=102' +
    '&c2=20';
  // You can find the sheet ID in the link bar. 
  // Select the sheet that you want to print and check the link,
  // the gid number of the sheet is on the end of your link.
  
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
  
  // Generate the PDF file
  var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();
  
  // Send the PDF file as an attachement 
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments: [{
            fileName: ss.getSheetByName("emails").getRange("B2").getValue() + ".pdf",
            content: response.getBytes(),
            mimeType: "application/pdf"
        }]
    });

  // Save the PDF to Drive. The name of the PDF is going to be the name of the Company (cell B5)
  const nameFile = ss.getSheetByName("01_Allegany_R3").getRange("A52").getValue().toString() +".pdf"
  DriveApp.createFile(response.setName(nameFile));
}

question from:https://stackoverflow.com/questions/66052945/generate-and-email-pdf-from-a-range-of-cells-in-google-sheets

Answer 1

You need to put the gid parameter at the end of the export options:

  const exportOptions =
    'exportFormat=pdf&format=pdf' + // export as pdf
    '&size=letter' + // paper size letter / You can use A4 or legal
    '&landscape=true' + // orientation portal, use false for landscape
    '&fitw=true' + // fit to page width false, to get the 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
    '&if=false' +
    '&ic=false' +
    '&r1=51' +
    '&c1=0' +
    '&r2=102' +
    '&c2=20'+
    '&gid=879200050'; // the sheet's Id. Change it to your sheet ID.