Hoping someone can help me out with handling 'errors' in my script when there is no message/thread found. I thought I had done that with the IF statement based on message.lengh but its not working and still errors when no email is found matching the inputted search criteria (e). It works perfectly every time a message IS found.

Many thanks. Moz

function ImportCDWFiles(e){
  
  var Threads = GmailApp.search(e,0,1); 
  var Message = Threads[0].getMessages()[0];
  var Attachment = Message.getAttachments()[0];
  if (Threads.length >0){

  Logger.log(Threads.length);
  Logger.log(Attachment.getName())
  var SourceFile = Folder.createFile(Attachment.copyBlob())
  var SourceID = SourceFile.getId();
  var XlsxBlob = Attachment[0]; // 
  var ConvertedSpreadsheetId = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS}, SourceFile).id;  //XlsxBlob
  Logger.log("Converted File ID= "  ConvertedSpreadsheetId)
  var Sheet = SpreadsheetApp.openById(ConvertedSpreadsheetId).getSheets()[0]; // There is the data in 1st tab.
  var Data = Sheet.getDataRange().getValues();
  //var Data = Sheet.getDataRange().offset(headerRowNumber, 0, sheet.getLastRow() - headerRowNumber).getValues();
    

  var sheet = SpreadsheetApp.openById('SheetIDHere').getSheetByName('SheetName');
  var range = sheet.getRange(LastRow 1, 1, Data.length, Data[0].length);

  range.setValues(Data);

  Drive.Files.remove(ConvertedSpreadsheetId); // Remove the converted file.
}

}

CodePudding user response：

Modification points:

• At var Message = Threads[0].getMessages()[0], when the length of Threads is 0, an error occurs.
• If the attachment files are not included, an error occurs.
• In your script, Folder and LastRow are not declared.

When these points are reflected in your script, how about the following modification?

Modified script:

function ImportCDWFiles(e) {
  var Folder = DriveApp.getFolderById("###"); // Please set your folder ID.

  var Threads = GmailApp.search(e, 0, 1);
  if (Threads.length == 0) return;
  var Message = Threads[0].getMessages()[0];
  var Attachment = Message.getAttachments();
  if (Attachment.length == 0) return;
  Attachment = Attachment[0];
  Logger.log(Attachment.getName())
  var SourceFile = Folder.createFile(Attachment.copyBlob());
  var ConvertedSpreadsheetId = Drive.Files.insert({ mimeType: MimeType.GOOGLE_SHEETS }, SourceFile).id;
  Logger.log("Converted File ID= "   ConvertedSpreadsheetId);
  var Sheet = SpreadsheetApp.openById(ConvertedSpreadsheetId).getSheets()[0];
  var LastRow = Sheet.getLastRow();
  var Data = Sheet.getDataRange().getValues();
  var sheet = SpreadsheetApp.openById('###').getSheetByName('###'); // Please set your values.
  var range = sheet.getRange(LastRow   1, 1, Data.length, Data[0].length);
  range.setValues(Data);
  Drive.Files.remove(ConvertedSpreadsheetId);
}

Note:

• Unfortunately, from your question, I cannot know the mimeType of the attachment file. So, I couldn't implement checking the mimeType of the attachment file. If you can know, when this check is implemented, the error related to the mimeType might be able to be avoided.