Emailing With Google Apps Scripts

function listFilesInSubfolder() {
  // Set the parent folder ID
  var parentFolderId = 'Add Folder ID';

  // Get today's date in the format of YYYY-MM-DD
  var today = Utilities.formatDate(new Date(), 'GMT', 'yyyy-MM-dd');
  Logger.log(today);

  // Set the subfolder name as today's date
  var subfolderName = today;

  // Get the parent folder
  var parentFolder = DriveApp.getFolderById(parentFolderId);

  // Get the subfolder by name
  var subfolder = parentFolder.getFoldersByName(subfolderName).next();

  // Get the Log sheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var logSheet = spreadsheet.getSheetByName('Log');

  // Clear existing data in the Log sheet
  logSheet.clear();

  // Append headers to the Log sheet
  logSheet.appendRow(['File Name', 'Direct Download Link']);

  // Get all files in the subfolder
  var files = subfolder.getFiles();

  // Append file names and direct download links to the Log sheet
  while (files.hasNext()) {
    var file = files.next();
    var fileName = file.getName();
    var fileId = file.getId();
    
    // Construct the direct download link
    var directDownloadLink = 'https://drive.google.com/uc?id=' + fileId;

    logSheet.appendRow([fileName, directDownloadLink]);
  }
}

function sendEmails() {
  // Record the start time
  var startTime = new Date().getTime();
  
  // Open the spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Get the Log sheet
  var logSheet = ss.getSheetByName('Log');
  
  // Get the data range starting from the second row
  var dataRange = logSheet.getRange(2, 1, logSheet.getLastRow() - 1, 3);
  
  // Get values in the range
  var data = dataRange.getValues();
  
  // Iterate through each row
  for (var i = 0; i < data.length; i++) {
    var fileName = data[i][0];
    var fileURL = data[i][1];
    var status = data[i][2];
   
    // Check if the status is not "Sent"
    if (status !== 'Sent') {
      // Create email message
      var subject = 'Your Email Subject';
      var body = 'This is the body of the email.';

      // Extract the file ID from the file URL
      var fileId = fileURL.match(/[-\w]{25,}/);
      
      // Check if a valid file ID is found
      if (fileId) {
        // Get the file by ID
        var file = DriveApp.getFileById(fileId[0]);
        
        // Attach the file
        var attachmentBlob = file.getBlob();
        
        // Send the email with attachment
        GmailApp.sendEmail(
          'email@email.com', // Replace with your recipient's email address
          subject,
          body,
          {
            attachments: [attachmentBlob]
          }
        );

        // Update the status in the spreadsheet to 'Sent'
        logSheet.getRange(i + 2, 3).setValue('Sent');
      } else {
        // Log a warning if no valid file ID is found
        Logger.log('Warning: Invalid file ID for file ' + fileName);
      }
    }
  }
  
  // Record the end time
  var endTime = new Date().getTime();
  
  // Calculate and log the execution time
  var executionTime = (endTime - startTime) / 1000; // Convert to seconds
  Logger.log('Total elapsed time: ' + executionTime + ' seconds');
}