Gmail Search by Size with Google Apps Script

The following Google Apps Script will help you find all Gmail messages that have file attachments greater than 1 MB. Should be useful when you are running out of space in Gmail.

Also see: Sort Gmail Messages by Size


function Scanning_Gmail_Mailbox() {    
  
  if (!UserProperties.getProperty("start")) {
    UserProperties.setProperty("start", "0");    
  }
  
  var start   = parseInt(UserProperties.getProperty("start"));
  var sheet   = SpreadsheetApp.getActiveSheet();
  var row     = getFirstRow();
  var ss      = SpreadsheetApp.getActiveSpreadsheet()
  
  for (;;) {
  
  ss.toast("Now finding all the big emails in your Gmail mailbox. Please wait..", "Scan Started", -1);
  
  // Find all Gmail messages that have attachments
  var threads = GmailApp.search('has:attachment larger:1m', start, 100);
  
  if (threads.length == 0) {
    ss.toast("Processed " + start + " messages.", "Scanning Done", -1); 
    return;
  }
    
  for (var i=0; i<threads.length; i++) {
    
    var messages = threads[i].getMessages();
    UserProperties.setProperty("start", ++start);
    
    for (var m=0; m<messages.length; m++) {      
      
      var size = getMessageSize(messages[m].getAttachments());      

     // If the total size of attachments is > 1 MB, log the messages
     // You can change this value as per requirement.
      
      if (size >= 1) {      
        sheet.getRange(row,1).setValue(Utilities.formatDate(messages[m].getDate(),"GMT", "yyyy-MM-dd"));
        sheet.getRange(row,2).setValue(messages[m].getFrom());        
        sheet.getRange(row,3).setValue(messages[m].getSubject());
        sheet.getRange(row,4).setValue(size);        
        var id = "https://mail.google.com/mail/u/0/#all/" + messages[m].getId();
        sheet.getRange(row,5).setFormula('=hyperlink("' + id + '", "View")'); 
        row++;
      }
    }            
  }    
  }
}


// Compute the size of email attachments in MB

function getMessageSize(att) {
  var size = 0;
  for (var i=0; i<att.length; i++) {
    //size += att[i].getBytes().length;
    size += att[i].getSize(); // Better and faster than getBytes()
  }
  // Wait for a second to avoid hitting the system limit
  Utilities.sleep(1000);
  return Math.round(size*100/(1024*1024))/100;
}

// Clear the content of the sheet

function Clear_Canvas() {
  UserProperties.setProperty("start", "0");
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(2,1,sheet.getLastRow(), 5).clearContent();
  SpreadsheetApp.getActiveSpreadsheet().toast("Choose Scan Mailbox to continue..", "Initialized", -1);
}

// Find the first empty row to start logging

function getFirstRow() {
  var sheet  = SpreadsheetApp.getActiveSpreadsheet();
  var values = sheet.getRange('A:A').getValues();
  var c = 2;
  while ( values[c][0] != "" ) {
    c++;
  }
  return c;
}

// Add a Gmail Menu to the spreadsheet

function onOpen() {  
  var menu = [    
    {name: "Reset Canvas", functionName: "Clear_Canvas"},
    {name: "Scan Mailbox", functionName: "Scanning_Gmail_Mailbox"}
  ];
  
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Gmail", menu);
}