Exporting unread mail details for further processing

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 "))
addresses.push([

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.

StackExchange Code Review Q#134282, answer score: 4