Chat with us, powered by LiveChat

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);
}