0

I have a sheets file and am trying to export one tab to pdf and email it to a recipient. First it was working, but would pdf the whole workbook vs the specified sheet. Now the attached pdf file is corrupt.

Ideally this would export just he range A1:O31 on "Shop Invoice Template' sheet, but I'm going on hour of testing and nothing seems to be working.

`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 
  const ss = SpreadsheetApp.openByUrl("SHEETFULLURL");

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

  // Subject of the email message
  const subject = "ServiceUp Payment Notification";

  // 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 = 'https://docs.google.com/spreadsheets/d/SHEETID/export?';

  const exportOptions =
    'exportFormat=pdf&format=pdf' + // export as pdf
    '&size=letter' + // paper size letter / You can use A4 or legal
    '&landscape=false' + // 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=SHEETID'; // the sheet's Id. Change it to your sheet ID.
  // 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 at 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 attachment 
  GmailApp.sendEmail(email, subject, body, {
    htmlBody: body,
    attachments: [{
      fileName: ss.getSheetByName("Shop Invoice Template").getRange("P2").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("Shop Invoice Template").getRange("P2").getValue().toString() + ".pdf";
  DriveApp.createFile(response.setName(nameFile));
}
`
  • I thought that this thread might lead to an answer to your question. https://stackoverflow.com/q/46088042 – Tanaike Jul 13 '23 at 00:51
  • Thanks, that fixed it. Net net, had the gid in the export function and should have. Also the link gave some formatting instructions. – aklobby Jul 14 '23 at 04:00
  • Thank you for replying. I'm glad your issue was resolved. From your reply, I flagged it as a duplicate question. – Tanaike Jul 14 '23 at 04:58

0 Answers0