Send File by File ID in Google Apps Script

Question

I am using Google Sheets to store the list of file names in Google Drive, their respective File ID. I am using another sheet in the same workbook to record email addresses and file names that I want to send those email addresses.

I have a Sheet named Data with the following columns

1. File Name - Files stored on Google Drive
2. File ID - File ID of the file derived from the share link

I have another Sheet named Request with the following columns

1. File Name
2. Email Address

I want to fetch the File mentioned as the File Name mentioned in the Request sheet by looking up the corresponding File ID from the Data sheet for that file name. Once the file is fetched, I want to send that file as an attachment to the email address mentioned in the Request sheet. I want to do this for each row in the Request sheet.

I have created an HTML file named DocTemplate to constitute the body of the email.

I have written the below code in the script editor of Google Sheets. On running it, a mail is triggered but it does not contain the attachment nor does it include the content of the HTML file. The body of the mail just reads [object Object]

What am I doing wrong?

function sendfile(){
  
  var request = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Request");
  var data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  
  var Avals = request.getRange("A1:A").getValues();
  var requestlastrow = Avals.filter(String).length;
  var Avalsu = data.getRange("A1:A").getValues();
  var datalastrow = Avalsu.filter(String).length;
  
  
  for (var i=2;i<= requestlastrow;i++) {

    var control = request.getRange(i,6).getValue();
    
    if(control != 1){
      
      
      var FilenameRequest = request.getRange(i,1).getValue();

      
      for (var j=2;j<= datalastrow;j++) {
      
        var FilenameData = upload.getRange(j,1).getValue();
        
        if(FilenameRequest == FilenameData) {
        
          /// to get the File ID
          var FileID = data.getRange(j,2).getValue();
          var file = DriveApp.getFileById(FileID);
          
  
          
          /// to form the email
          var values = request.getRange(i,1,1,2).getValues();
          var rec = values[0];
          var client = 
              {
                email: rec[1],
                name: rec[0]
              };
          
          client.subject = 'Request Filename:' + client.name;
      
          var template = HtmlService
          .createTemplateFromFile("DocTemplate.html");
          template.client = client;
          var message = template.evaluate().getContent();
  
          MailApp.sendEmail(client.email,client.subject,{
            htmlBody: message,
            attachments: [file.getAs(MimeType.PDF)]
          });
          
          request.getRange(i,6).setValue("1");
        }
      }
    }
  }
}

Answer 1

Since this is not a reproducible example I can only assume that the rest of the code works except for this :

MailApp.sendEmail(client.email,client.subject,{
            htmlBody: message,
            attachments: [file.getAs(MimeType.PDF)]
          });

It should also contain the message argument :

MailApp.sendEmail(client.email,client.subject,message,{
            htmlBody: message,
            attachments: [file.getAs(MimeType.PDF)]
          });