How to generate PDF from a Google Sheet and send copies to specific email accounts when a specific c-CodePudding

I am trying to make an invoice for a project. The way it works is a person would fill in the required details and as soon as the details are filled. They would edit a specific cell (could be a drop-down like "invoice filled" and "invoice incomplete"). As soon as they click on filled, it would send the sheet as a PDF file to different emails.

I'm having a hard time going around this through apps scripts.

CodePudding user response：

Here is a sample

function createAndSendPDF() {
  const docID = '___________'; // ID of spreadsheet
  const feuilleID = '0'; // sheetID
  const email = '[email protected]';
  // const dossier = DriveApp.getFolderById('____________'); // ID of Folder if you want to save a copy of the pdf file
  const d = Utilities.formatDate(new Date(), "GMT 1", "yyyyMMdd")
  const fichier = 'myFileName'   "_"   d   ".pdf"
  const objet = 'Test pdf';
  const corps = "Please find ...";

  const url = 'https://docs.google.com/spreadsheets/d/'   docID   '/export?';
  const exportOptions =
    'exportFormat=pdf&format=pdf'   
    '&size=A4'   
    '&portrait=true'  
    '&fitw=true'  
    '&sheetnames=false&printtitle=false'  
    '&pagenumbers=false&gridlines=false'  
    '&fzr=false'  
    '&gid='   feuilleID;
  var params = {method:"GET",headers:{"authorization":"Bearer "  ScriptApp.getOAuthToken()}};
  var reponse = UrlFetchApp.fetch(url   exportOptions, params).getBlob();

    GmailApp.sendEmail(email, objet, corps, {
      htmlBody: corps,
      attachments: [{
            fileName: fichier,
            content: reponse.getBytes(),
            mimeType: "application/pdf"
        }]
    });

  // dossier.createFile(reponse.setName(fichier));
}

CodePudding user response：

You simply need to add a trigger of type onEdit(e) and control when the checkbox value is set to true.

Then you basically need to send an email with the attachment:

const ss = SpreadsheetApp.getActiveSpreadsheet()
const sheet = ss.getSheetByName('Proforma Sheet')
const PDF_MIME = "application/pdf"
const listEmail = ["[email protected]", "[email protected]"]

const sendPDF = (e) => {
  const { range: { rowStart, columnStart } } = e
  const {value} = e
  if (rowStart === 11 && columnStart === 12 && value === "TRUE" ) {
    const copy = DriveApp.getFileById(ss.getId()).getAs(PDF_MIME).copyBlob()
    listEmail.forEach(email=>{
      MailApp.sendEmail(email, "Your Spreadsheet", "See attched", {
        attachments: [copy]
      })
    })
  }
}

And create an installable edit trigger for the sendPDF function.