Add email contents from Gmail to a Spreadsheet with Apps Script

posted in: Code Sample | 0

So, if you are working with Gmail and do get tons of Emails – like bug reports, orders and other stuff all-in-one you definetly would like to have some advanced tools to further work with them, for example adding bug reports or orders to a spreadsheet. Google allows apps script usage to achieve whatever you need and that code sample below can really do a very basic parsing for you:

//Variables of your choice here

  var sheet = '';
  var query = '';    

//Proccess all emails that match query and add them into  2-dimensional array

  function getEmailsInbox(array2d, query){
  var emails = [];
  var thds = GmailApp.search(query);
  for(var i in thds){
    var msgs = thds[i].getMessages();
            for(var j in msgs){
    var dt = msgs[j].getDate();
    var mail = msgs[j].getReplyTo();
    var subj = msgs[j].getSubject();

      emails.push([dt, mail, subj]);

    }
    }
    return emails;
  }


//Function to add objects from 2 dimensional array to a spreadsheet as rows

    function appendInboxEmails(sheet, array2d){
      sheet.getRange(sheet.getLastRow() + 1, 1, array2d.length, array2d[0].length).setValues(array2d);
    }


//Put that function on trigger or run once

    function run() {
    var array2d = getEmailsSent(array2d, query);
    if(typeof array2d !== 'undefined' && array2d.length > 0) {
             appendInboxEmails(sheet, array2d);
             array2d = [];
    }
    }

Gmail to Spreadsheets Add-on

However, if you need some even more advanced solution you may take a look at this Google Add-on that does impressive stuff – creats charts for analytics, parses complex values from email contents and does that automatically.

 

Follow Sam Tyurenkov:
Hi there, I'm a web-designer, marketing and product manager, business developer. I have created this website and about 25 others. I'm also doing various tasks for IT projects besides websites - like mobile games.