Google Sheet Range mail pdf-CodePudding

I am all new for the google app script, don't know whether it is possible or not. Need a script to send the defined range(A1:I5) as PDF on email (email address defined in K2,cc and bcc as defined in script).

SS link: https://docs.google.com/spreadsheets/d/1tmnDOMyupjeO8d65qHQsxb5KrrKeq7pMYIE8h2VmEJ4/edit#gid=0

Thanks in advance.

CodePudding user response：

I believe your goal is as follows.

You want to export the range of "A1:I5" in "Sheet1" as a PDF file, and retrieve the email address from cell "K2". And, you want to send them as an email.
You want to declare cc and bcc in the script.

When this information is used, the endpoint for exporting the range of "A1:I5" in "Sheet1" as a PDF data is like https://docs.google.com/spreadsheets/d/${ss.getId()}/export?format=pdf&range=A1:I5&gid=${sheet.getSheetId()}. When this endpoint is used, the sample script is as follows.

Sample script:

function myFunction() {
  const cc = ""; // Please set cc.
  const bcc = ""; // Please set bcc.

  // Retrieve Spreadsheet and Sheet objects.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet1");

  // Retrieve PDF blob.
  const url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/export?format=pdf&range=A1:I5&gid=${sheet.getSheetId()}`;
  const pdfBlob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer "   ScriptApp.getOAuthToken() } }).getBlob();

  // Send an email using email from cell "K2".
  const email = sheet.getRange("k2").getValue();
  MailApp.sendEmail({
    to: email,
    cc: cc || null,
    bcc: bcc || null,
    subject: "sample subject",
    body: "sample body",
    attachments: [pdfBlob],
  });

 // DriveApp.getFiles() // This comment line is used for automatically detecting a scope of "https://www.googleapis.com/auth/drive.readonly". In order to export PDF data, I thought that this scope might be required to be included.
}

When this script is run, the PDF data is retrieved from the cells "A1:I5" in "Sheet1", and an email is sent using the email address retrieved from "K2". The PDF is used as an attachment file.

Sample script:

References: