Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Attaching Files from Google Drive to Email sent in Google Sheets

I have a script in Google Sheets that allows me to send emails with attachments found in a selected folder in Google Drive. It mostly works fine, giving a failed message if there is no email address listed and sends emails with the correct attachment.

The one issue I have is when a file does not exist in the folder and the script stops running. I would rather have it skip and push a message to say file not found so I can investigate the missing files after the bulk is sent.

There are three scripts associated with it, firstly the main one sending the email:

{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName('Gas');
  var sheet2 = ss.getSheetByName('Email Gas');
  var subject = sheet2.getRange(2,1).getValue();
  var n = sheet1.getLastRow();

  folderID = displayPrompt("Enter folder ID:");


  for (var i = 2; i < n+1 ; i++ ) {

    var emailAddress = sheet1.getRange(i,3).getValue();
    var Date = sheet2.getRange(6,5).getValue();
    var message = sheet2.getRange(2,2).getValue();
    var documentName = '.xlsx';
    var name = sheet1.getRange (i,2).getValue();
    var finalfile = getFileFromFolder(folderID, name, documentName);
    message=message.replace("<Date>", Date);


    let errors = [];
   
      try{
        MailApp.sendEmail(emailAddress, subject, message, {attachments: [finalfile.getAs(MimeType.MICROSOFT_EXCEL)]});
        errors.push(["Sent"]);
      }catch(err){
        errors.push(["Failed"]);
      }
    

    sheet1.getRange(i,4).setValues(errors);
    
  }
}```


}

the second is a prompt to enter the folderID

function displayPrompt(question) {
  var ui = SpreadsheetApp.getUi();
  var result = ui.prompt(question);
  return result.getResponseText();
}

and lastly the script to get the attachment where I think the error is that is stopping the script when no file is found rather than skipping to the next line:

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var folder = DriveApp.getFolderById(folderID);
  var files = folder.getFilesByName(filename + docType);
  
  if (files.hasNext()){
    file = files.next();
  }else{
    error.push(["file not found"]);
  }
  

  return file;

}

Does anyone know how I can ammend this to give a message on the sheet to say file not found and skip to the next email?

Tags:

2

Answers


  1. 0 
      var ss = SpreadsheetApp.getActiveSpreadsheet()


  var folder = DriveApp.getFolderById(folderID);
  var files = folder.getFilesByName(filename + docType);
  
  if (files.hasNext()){
    file = files.next();
  }else{
    error.push(["file not found"]);//check this line
  }
  

  return file;

    Here you say error.push(["file not found"]); whereas you’ve defined the original array as let errors = [];. Perhaps the problem is that your code stops executing right there since error is something it doesn’t recognise.

    Login or Signup to reply.
  2. 0

    You can try this:

    Modify the current else condition in your current code so that if the exact filename is not found, the script will still proceed.

    Additionally, the execution log will display the name of the file that did not proceed.

    else {
    console.log("File not found: " + filename + docType);
    return null;  
  }

    Complete code :

    function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName('Gas');
  var sheet2 = ss.getSheetByName('Email Gas');
  var subject = sheet2.getRange(2, 1).getValue();
  var n = sheet1.getLastRow();

  var folderID = displayPrompt("Enter folder ID:");

  for (var i = 2; i <= n; i++) {
    var emailAddress = sheet1.getRange(i, 3).getValue();
    var Date = sheet2.getRange(2, 5).getValue();
    var message = sheet2.getRange(2, 2).getValue();
    var documentName = '.xlsx';
    var name = sheet1.getRange(i, 2).getValue();
    var finalfile = getFileFromFolder(folderID, name, documentName);
    message = message.replace("<Date>", Date);

    var errors = [];
    try {
      MailApp.sendEmail(emailAddress, subject, message, {attachments: [finalfile.getAs(MimeType.MICROSOFT_EXCEL)]});
      errors.push(["Sent"]);
    } catch (err) {
      errors.push(["Failed"]);
    }

    sheet1.getRange(i, 4).setValues(errors);
  }
}

function displayPrompt(question) {
  var ui = SpreadsheetApp.getUi();
  var result = ui.prompt(question);
  return result.getResponseText();
}

function getFileFromFolder(folderID, filename, docType) {
  var folder = DriveApp.getFolderById(folderID);
  var files = folder.getFilesByName(filename + docType);

  if (files.hasNext()) {
    return files.next();
  } else {
    console.log("File not found: " + filename + docType);
    return null;  
  }
}

    Sample Output:

    Output

    Output2

    Reference:

    IfElse

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search