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', 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;

}

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

}