How to send a email with a PDF file attachment based on a condition using Google App Script-CodePudding

I solved the following problem. Send PDF files to everyone using the Google Script service of Google Sheets. I tried sending one file to everyone on the list. It went well.
However, I've run into a problem trying to make a similar Script, but for sending multiple files to different people on a Google Sheet list.Each person has different files.

For example:
John has three PDF files: John_ 999901.pdf, John_ 999902.pdf, and John_999903.pdf.
David has two PDF files: David_ 999901.pdf and David_99990.
Jun has two PDF files: Jun_999901.pdf and Jun_999902.pdf.
And finally, Michel has only one PDF file: Michel_999901.pdf.
All PDF files are saved on GOOGLE DRIVE.
This is where I save the PDF file
This is my spreadsheet
Is there a way to send an email based on the file name that corresponds to the person of the same name in the list?
Below is the code that I tried to send the file

function onOpen() 
{
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('メール送信')
     
      .addItem('送信', 'sendFormToAll')
      .addToUi();
}
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
function sendFormToAll()
{
   var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  
   var last_row = sheet.getDataRange().getLastRow();
  
   for(var row=2; row <= last_row; row  )
   {
     sendEmailWithAttachment(row);
     sheet.getRange(row,8).setValue("send");
   }
}

function sendPDFForm()
{
  var row = SpreadsheetApp.getActiveSheet().getActiveCell().getRow();
  sendEmailWithAttachment(row);
}

function sendEmailWithAttachment(row)
{
  var filename= '?????.pdf';// I don't know what to do at this point
  
  var file = DriveApp.getFilesByName(filename);
  
  if (!file.hasNext()) 
  {
    console.error("Could not open file " filename);
    return;
  }
  
  var client = getClientInfo(row);
  
  var template = HtmlService
      .createTemplateFromFile('index');
  template.client = client;
  var message = template.evaluate().getContent();
  
  
  MailApp.sendEmail({
    to: client.email,
    subject: "Send File",
    htmlBody: message ,
    attachments: [file.next().getAs(MimeType.PDF)]
  });

}

function getClientInfo(row)
{
   var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
   
   var values = sheet.getRange(row,1,row,8).getValues();
   var rec = values[0];
  
  var client = 
      {
        name:rec[0],
        email: rec[1]
      };
  client.name = client.name;
  return client;
}

<!DOCTYPE html>
<html>
   <head>
      <base target="_top">
      <!-- CSS only -->
      <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
      <script src="https://code.jquery.com/jquery-3.6.0.js" integrity="sha256-H K7U5CnXl1h5ywQfKtSj8PCmoN9aaq30gDh27Xc0jk=" crossorigin="anonymous"></script>
      
   </head>

  
   <body>
     <div  >
     
     
      <p>Hi, <?= client.name ?>Sir </p>
      <p>I send you file . Please check it</p>
   
     
      <p>*********************************************************</p>
     
     </div>
   </body>
</html>

CodePudding user response：

In your script, how about the following modification for achieving your goal?

Modification points:

When getValue is used in a loop, the process cost becomes high.
It is requried to add the logic for searching the filename from the files in the folder.
When I saw your sample Spreadsheet, Situation is the column "C". But when I saw your script, it's sheet.getRange(row,8).setValue("send");. If Situation is the same with sheet.getRange(row,8).setValue("send");, it is requried to be sheet.getRange(row,3).setValue("send");. But I'm not sure about your actual situation. So in this modification, I used sheet.getRange(row,3).setValue("send"); for your sample Spreadsheet.
In your script, Sheet1 is used as the sheet name. But in your sample Spreadsheet, the sheet name is Request. In this modification, I used Request for your sample Spreadsheet.

When these points are reflected to your script, it becomes as follows.

Modified script:

Please set your folder ID to ### of DriveApp.getFolderById("###").searchFiles(`title contains '${name}' and trashed=false`);.

function sendFormToAll() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Request');
  var values = sheet.getRange("A2:C"   sheet.getLastRow()).getValues();
  var rangeList = values.reduce((ar, e, i) => {
    if (e[2] == "Send File") return ar;
    var [name, email] = e.map(f => f.trim());
    var files = DriveApp.getFolderById("###").searchFiles(`title contains '${name}' and trashed=false`);
    var attachments = [];
    var r = new RegExp(`^${name}[_\\d\\s] \\.pdf\$`, "i");
    while (files.hasNext()) {
      var file = files.next();
      if (r.test(file.getName())) attachments.push(file.getBlob());
    }
    if (attachments.length == 0) {
      console.error("Could not find files related to "   name);
      return ar;
    }
    var template = HtmlService.createTemplateFromFile('index');
    template.client = { name };
    var message = template.evaluate().getContent();
    MailApp.sendEmail({ to: email, subject: "Send File", htmlBody: message, attachments });
    ar.push(`C${i   2}`);
    return ar;
  }, []);
  if (rangeList.length == 0) return;
  sheet.getRangeList(rangeList).setValue("Send File");
}

Note:

This sample script is for your sample Spreadsheet. So if the structure of your actual Spreadsheet is different from your sample one, this script might not be able to be used. So, please be careful this.

Modification points:

Modified script:

Note:

References: