Exporting unread mail details for further processing

Question

As the result of some expert procrastination I have over 4000 unread mail in my gmail box. While that does not hold a candle to what some people likely have I wanted to try and do something about it. Namely unsubscribe where I can, create filters and labels or delete.

I wanted to gather data on all my unread mail so that I could process it in spreadsheet manually. The following code was running as a trigger every 5 minutes. What it does is get 100 unread threads from my mailbox and take all the matching messages and outputs the from and subject into spreadsheet. Another sheet tracks the progress in 100 mail chunks so that I don't hit an execution limit in Apps Script. Sheet records the record start which was manually set to 0 first. When the script runs out of mail to process it deletes its trigger and marks the start index to -1.

function groupUnreadMail() {
  // This function will group all unread mail to help decide how to filter/remove/deleted unread mail faster.
  var spreadsheetName = "Unread Mail";
  var numberOfMailPerPass = 100;
  var addresses = [];

  // Open up the spreadsheet that contains the progress details and collected data thus far.
  var sheetID = getDriveIDfromName(spreadsheetName);

  // Verify that only one sheet was located. 
  if(sheetID.length = 1){
    // Get the pertinent details from the sheet to start searching for mail.
    var spreadsheet = SpreadsheetApp.openById(sheetID[0]);

    // Get the starting iteration from the first cell in the first sheet. 
    var mainSheet = spreadsheet.getSheetByName("main")
    var startSearchIndex = mainSheet.getRange("A1").getValue()

    // find all messages that are unread
    var unreadThreads = GmailApp.search('is:unread',startSearchIndex,numberOfMailPerPass);
    Logger.log("Total number of threads found: " + unreadThreads.length);  

    if(unreadThreads.length > 0){
      // Loop each thead in the thread array
      for(var threadIndex = 0; threadIndex < unreadThreads.length; threadIndex++){
        // Return matching messages from this thread
        var unreadMessages = unreadThreads[threadIndex].getMessages();
        Logger.log("Total number of messages found in thread #" + threadIndex + ": " + unreadMessages.length);

        for(var messageIndex = 0; messageIndex < unreadMessages.length; messageIndex++){
          // Return details from the matching messages
          var fromAddress = unreadMessages[messageIndex].getFrom()
          Logger.log(fromAddress.lastIndexOf(">"))
          addresses.push([
            fromAddress,
            fromAddress.slice(fromAddress.indexOf("<") + 1,fromAddress.lastIndexOf(">")),
            unreadMessages[messageIndex].getSubject()
          ]);
        }
      }

      // Add the addresses into the data sheet on file. Find the next free row and place details after that. 
      var outputSheet = spreadsheet.getSheetByName("data");
      var lastRow = outputSheet.getLastRow();
      outputSheet.getRange(lastRow + 1, 1,addresses.length, addresses[0].length).setValues(addresses);

      // Update the iteration value in the sheet for the next run. 
      mainSheet.getRange("A1").setValue(startSearchIndex + numberOfMailPerPass);
    } else {
      // If no threads were returned then there is nothing to do.
      mainSheet.getRange("A1").setValue(-1);

      // Delete the trigger that is running this function
      deleteTriggersAssociatedtoFunction(groupUnreadMail);
    }

  } else if (sheetID.length > 1){
    Logger.log("More than one document found with the given search criteria. Narrow the search and/or rename the file.")
  } else {
    Logger.log("Could not find logging sheet using: '" + + "'")
  }

}

function getDriveIDfromName(fileName){
  // This function will find the Google Drive ID of all the files matching the filename passed.
  var driveIDs = [];

  // Attempt the get files matching the fileName passed
  Logger.log("Looking for: " + fileName);
  var FileIterator = DriveApp.getFilesByName(fileName);

  // Check to see if any files are returned
  if(FileIterator){
    // Cycle all of the files matching the criteria
    while (FileIterator.hasNext())
    {
      // Get the current FileIterator
      var file = FileIterator.next();

      Logger.log("Located File ID: '" + file.getId() + "'")
      // Save the file id for it to be returned.
      driveIDs.push(file.getId())
    }
  } else {
    Logger.log("No files found matching: '" + fileName + "'")
  }

  return driveIDs;
}

function deleteTriggersAssociatedtoFunction(functionName){
  // This function will delete any trigger matchins triggerName in the current project scope
  var triggers = ScriptApp.getProjectTriggers();
  for(var triggerIndex = 0; triggerIndex < triggers.length; triggerIndex++){
    // Check if the trigger matches 
    if(triggers[triggerIndex].getHandlerFunction() == functionName){
      // Delete this trigger
      ScriptApp.deleteTrigger(triggers[triggerIndex])
    }
  }
} 

You could run this yourself if you had a Google account and created a spreadsheet called "Unread Mail" with two sheets. "Main" would have 0 in the first cell and then "Data" with a header of From, Address, Subject.

Answer 1

Let's start with getDriveIDfromName function. Only one issue here: the conditional statement if(FileIterator) doesn't do what you expect. Indeed, DriveApp.getFilesByName always returns an iterator, which is an object, and therefore a truthy value even if no files with given name were found. To check for nonemptiness in order to print out "no files" statement, you could use hasNext method:

 if (FileIterator.hasNext()) {
   while (FileIterator.hasNext()) {
     // loop
   }
   else {
     Logger.log('No files found');
   }

However, I would drop the if-statement from this function completely. The function returning an empty array already says clearly enough that no files were found.

Next, groupUnreadMail function. There's a typo in if(sheetID.length = 1) which needs == comparison. Further, the line

var spreadsheet = SpreadsheetApp.openById(sheetID[0]);

will throw an uncaught exception if the file you found in getDriveIDfromName happens to be, say, an image rather than a spreadsheet. One way to deal with this is to use a try-catch block; alternatively, one can make getDriveIDfromName more specialized, so that it only retrieves spreadsheets:

if (file.getMimeType() == MimeType.GOOGLE_SHEETS) {
  driveIDs.push(file.getId());
} 

Another sanity check is called for after var mainSheet = spreadsheet.getSheetByName("main"). If there is no sheet with such name then mainSheet is null and the subsequent line throws an exception. The function should return if mainSheet is falsy.

The line

var startSearchIndex = mainSheet.getRange("A1").getValue()

takes for granted that someone will fill A1 with an integer prior to the first script run. I wouldn't assume this: with

var startSearchIndex = mainSheet.getRange("A1").getValue() || 0;

the call A1 can be initially empty, and search index will begin at 0.

As a general remark, consistent spacing after commas and some consistency in use of semicolons after statements would be nice.