CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgmail
One Two
One Two

Reputation: 1

Trouble extracting data from Gmail to Sheets

I need to extract specific data from Gmail into a spreadsheet.

So far I've been able to more or less tweak it to my needs, but one big problem is that it only extracts the data from the first email on any thread. The emails in question are booking confirmations for tours, so Gmail will automatically group them together under one thread (if they are for the same day). I disabled Gmail conversation view feature, and even though on my inbox each email is separate, when I run the code, it still adds only the data from the first email of each thread to my sheet. Any ideas?

This is the code:

function gather() {
    let messages = getGmail();

    let curSheet = SpreadsheetApp.getActive();

    messages.forEach(message => {curSheet.appendRow(parseEmail(message))});
}

function getGmail() {
    const query = "from:[email protected] NOT label:done";

    let threads = GmailApp.search(query);

    let label = GmailApp.getUserLabelByName("done");
    if (!label) {label = GmailApp.createLabel("done")}

    let messages = [];

    threads.forEach(thread => {
        messages.push(thread.getMessages()[0].getPlainBody());
        label.addToThread(thread);
    });

    return messages;
}

function parseEmail(message){
    let parsed = message.replace(/,/g,'')
    .replace(/.+[a-z]:/ig,',')
    .replace(/Hey team/g,'')
    .replace(/.+-/g,'')
    .replace(/.+!/,'')
    .replace(/^,/,'')
    .split(',');

    let result = [0,1,2,3,4,5,7,8,9,10,11,12].map(index => parsed[index]);

    return result;
    }

Upvotes: 0

Views: 104

Answers (1)

Iamblichus
Iamblichus

Reputation: 19309

Issue and solution:

  • For each thread, you are only getting the first message: thread.getMessages()[0].
  • Instead, you should iterate through all message threads and push all its bodies to your messages array.

Modifications:

Replace this:

threads.forEach(thread => {
    messages.push(thread.getMessages()[0].getPlainBody());
    label.addToThread(thread);
});

With this:

threads.forEach(thread => {
    const threadMessages = thread.getMessages().map(m => m.getPlainBody());
    messages.push(...threadMessages);
    label.addToThread(thread);
});

Reference:

Upvotes: 1

Related Questions