CODEWITHSUNDEEP
emailgoogle-apps-script
user6537067
user6537067

Reputation: 397

get gmail email contents and transfer to google spreadsheet and arrange accordingly

Hi I'm just wondering if i could extract all email contents of a certain emails on a label and put them into a google spreadsheet.Its very tiring to open every email and copy and paste every data to a spreadsheet to arrange accordingly. ive tried to create a macro in vb to arrange the emails but still i would need to open each email just to get the data and put them on my excel file.

here is the sample email content i want to arrange based on their labels and each email will be displayed per row and column:

CO type:IV ONLY

TYPE AUTO:Manual DATE:11/21/2016 1:49:11 PM CAR Type: MITSUBISHI
Company address: PEAK Price: 8,000.00

CA: Michael,John SPO: SINGLE ADD: UNIT 3 San Franciscvo, Canada LM : Oyster
SOI: Factory Worker POS: 1st mate ADD: UNIT 3 San Diego, California

And what i want is to arrange them based on the headers ive created:which is before beggining of every mail until the colon(:) mark. I've already done it by searching the begining of word until the colon and by using cut and paste on excel but still takes alot of time due to manual opening and pasting of emails on my file. is there any work around for this?

Upvotes: 3

Views: 27541

Answers (4)

Moayad Hani Abu Rmilah
Moayad Hani Abu Rmilah

Reputation: 830

I coded what you want on Google App Script and I have made a YouTube video that explains this in details. You may watch it from the link below:

https://www.youtube.com/watch?v=gdgCVqtcIw4

However if you would like to extract specific texts from the body of the email, you would need to use regular expressions. Part 2 of my video series explains how to achieve this. You may watch it here:

https://youtu.be/nI1OH3pAz6s?t=9

You may also read the full step-by-step explanation from my blog: https://codewondo.com/2021/07/29/gmail-emails-to-spreadsheet/

You can also get the full code from GitHub from the following link:

Upvotes: 3

Sam Tyurenkov
Sam Tyurenkov

Reputation: 440

A bit late response, but hopefully my answer might help others. My add-on was just released, it does exactly the mentioned function:

  • iterate through yesterdays emails
  • parse data from them
  • add new rows to spreadsheet daily with the parsed data

Here is the add-on link: https://chrome.google.com/webstore/detail/email-parser-analytics/ilaecpapiiijoeijcffhnlhfjimammek

This add-on is advanced version of that kind of scripting:

//Variables of your choice here

  var sheet = '';
  var query = '';
  var array2d = getEmailsInbox(array2d, 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();
    var body = msgs[j].getPlainBody();

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

    }
    }
    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() {
    if(typeof array2d !== 'undefined' && array2d.length > 0) {
             appendInboxEmails(sheet, array2d);
             array2d = [];
    }
    }

Upvotes: 0

Amit Agarwal
Amit Agarwal

Reputation: 11268

You can use the Save Gmail add-on for saving the email metadata to a Google Sheet. It also saves the messages to Drive. (disclaimer: I am the author of the add-on)

It can be done with Google Script attached to the Google Sheet, something like this snippet.

function getGmailData() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var threads = GmailApp.search("is:unread in:inbox", 0, 20);
  for (var t=0; t<threads.length; t++) {
    sheet.appendRow([threads[t].getDate(), threads[t].getId(), threads[t].getFirstMessageSubject()])
  }
}

Upvotes: 3

Vytautas
Vytautas

Reputation: 2286

yes, you can do it. In fact, I have a Google script that does just that. My script has a web UI, where you set a before and after date fields and a field for the file name. Once you start the script it gets all emails (not threads, Gmail Service works with threads, if you want to search for individual messages, you need to use Gmail API) and then you can output anything about it to a Google Spreadsheet.

Instead of using VB I would recommend to use GAS. You can use GmailApp.getUserLabelByName("MyLabel") to get the label you want to extract the messages from and then use .getThreads() to grab all the threads (the conversations) from the label.

Read on what you can do with threads here and what you can do with messages here. Personally I used Gmail API and used

queriedMessages =
      Gmail.Users.Messages.list(userInfo.mail, {
        'q': queryString,
        'pageToken': execProperties.nextPageId
      });

and then I can extract the info I want from individual messages. The query string is basically what you would put into Gmail to search for the email, so you can use something like label:"This-label"

Upvotes: 0

Related Questions