Capture Gmail messages in a Google Sheet


With Google Apps scripts, marketers can capture Gmail messages, analyze them for relevant information, and add them line by line in a Google spreadsheet.

This can be especially useful for capturing leads or contacts.

Here is a scenario. Imagine that your e-commerce has decided to work with a charity. Participants submit proposals via the charity's website. Your job is to read through these suggestions, select people your company can help with and contact them to get started.

The problem is that the charity's website was built in the 1990s on a locked, proprietary platform. Submissions to the site cannot be added to a database or otherwise stored. Instead, these posts can only be sent to you in plain text format.

Solution? Capture and analyze these emails for easy interaction by adding them to a Google Sheet. In this article I will explain how to do just that.

Leaf and script

The work begins with creating a new Google Sheet. My name is "Example Charity Email."

Start with a new Google Sheet.

Start with a new Google Sheet.

Go to the "Tools" menu in the sheet and select the "Script Editor" menu item.

Connect Google spreadsheets to custom scripts via

Link Google's spreadsheet to custom scripts via "Script Editor."

This link opens a new Google script editor, where we will do our work. I named this script the "Charity Email Example." The script editor allows us to write JavaScript functions that run in our spreadsheet.

Use the script editor to write JavaScript functions to run in the spreadsheet.

Use the script editor to write JavaScript functions to run in the spreadsheet.

There are many ways to write a script to retrieve Gmail messages and add them to a Google spreadsheet. In this example, I use four functions: onopen, collect, getGmail, also parsing email.

onopen

The onopen feature is unique in Google Apps scripts. It always runs when a sheet is opened. Thus, the name must be onopen. However, you can get it to do almost anything you want. In our case, we add a new menu and menu option to the sheet.

function onOpen() {
    const spreadsheet = SpreadsheetApp.getActive();
    let menuItems = (
        {name: 'Gather emails', functionName: 'gather'},
    );
    spreadsheet.addMenu('Charity', menuItems);
}

This feature has three parts.

First we will use SpreadsheetApp class to get the active sheet – ie. the sheet the user is viewing. This sheet object is assigned to the JavaScript constant "spreadsheet."

const spreadsheet = SpreadsheetApp.getActive();

Then I create a number of menu options. In this example, our array has only one menu option, but it may have more. Writing it this way will make it easier to add menu items as needed.

Make a note of it FUNCTION describes the redial feature that will be run when a user clicks the "Collect Emails" menu item. In this case, I will call a function called collect.

let menuItems = (
    {name: 'Gather emails', functionName: 'gather'},
);

Google documentation describes in detail how to add a menu. For our purposes, you just need to hire addMenu method – forward it, first to the name of the menu and secondly to the match with menu options.

spreadsheet.addMenu('Charity', menuItems);

In the script editor, we can run this feature by clicking the run button, the black triangle that looks like a play button for a video. Alternatively, we can update the spreadsheet, but it closes the script editor. If closed, we can open it again from the Tools menu.

The run button lets us run a selected function within the script editor.

The run button lets us run a selected function within the script editor.

The first time you run the app, you must allow it to access your Google Account.

Give script access to your Google Account.

Give script access to your Google Account.

When this feature has been run, we move to a new menu and menu item in the charity worksheet.

A new menu and menu item will appear on the spreadsheet.

A new menu and menu item will appear on the spreadsheet.

collect

Now let's write collect work.

Here the name should be logical. For me, "collecting" conveys the idea of ​​collecting and organizing. But you can name the function what you want.

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

    let curSheet = SpreadsheetApp.getActive();

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

The function completes three tasks.

First, it fetches all charity messages by calling getGmail feature, which I'll write next.

let messages = getGmail();

Then use it SpreadsheetApp class to get the active sheet.

let curSheet = SpreadsheetApp.getActive();

Finally, it iterates over all messages and adds desired content from each to the spreadsheet after sending it to parsing email feature that we have not yet written.

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

A lot happens in the last step, so let's review it.

For each message in messages, the feature gets the current sheet, curSheet.

Then use Google appendRow method of adding a row to the sheet. This method normally takes an array object. But in this case, we provide the result of another function, parsing email, which in turn will provide the required matrix.

getGmail

The getGmail the function does most of the work.

First, we set the value of a JavaScript constant called "query" to a Gmail search string.

const query = "from:a@e.com AND subject:Charity Example NOT label:done";

This search string is exactly what you would use for advanced Gmail search. Note that we specify the "from" address and the "subject" of the email address. We also ensure that the email does not contain the "done" label.

The query string is identical to an advanced search in Gmail.

The query string is identical to an advanced search in Gmail.

Then we use Google GmailApp class to run a search in the user's inbox. The result of that search is all threads from the charity with the right topic.

let threads = GmailApp.search(query);

If there are many messages in your Gmail account, you can get lots of ten. This speeds up the script.

let threads = GmailApp.search(query,0,10);

The next two rows of the feature prevent an email from being added twice to our spreadsheet.

Remember, in the search query, we excluded messages with the "done" label. So now we add the "done" label to the messages we just downloaded. When the script is run next it will ignore these messages.

The first line uses Google getUserLabelByName to find the label "done" and assign it to the variable "label." If that tag is not yet in the user's Gmail account, the next line will be created.

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

The last section of this feature collects each email message, adds it to the message "messages" and labels it "done."

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

Finally, the function returns the array of messages.

return messages;

The following is complete getGmail work.

function getGmail() {
    const query = "from:roggio@yahoo.com AND subject:Charity Example 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;
}

parsing email

The last feature will "analyze" the email and format it to fit our spreadsheet.

This feature must be specific to the message's regular text format. So let's look at what the charity website is sending us.

date: Thu, April 16, 2020

first_name: Olga
last_name: Razcok
email: oraxcok@example.com
phone: 202-456-1111
ip: 35.138.107.243
comment: I would really like to participate in this program.
fav_color: brown

I will apply JavaScript regular expressions to clean up and format this message copy. I will use it Regex Pal, a regular expression tool, to determine which expressions would do what I need.

Regex Pal lets you see which parts of text a certain regular expression will match.

Regex Pal lets you see which parts of text a certain regular expression will match.

I will use comma later to separate items on the list. Therefore, I remove the comma on the date to avoid confusion.

date: Thu, April 16, 2020

This regular expression will replace the comma with an empty string, which is really nothing.

let parsed = message.replace(/,/g,'')

In this case, the "message" is the message being sent to parsing email function from collect feature I wrote earlier.

The next regular expression is powerful because it removes most of the new line characters ( n) from our plain text email and removes all characters before, including colon.

As an example, it will make "first name: Olga" to ", Olga" (yes, it is a comma, a space, and the word "Olga").

let parsed = message.replace(/,/g,'')
    .replace(/n*.+:/g,',')

The previous step leaves an unwanted comma before the date. So next replace statement removes it.

let parsed = message.replace(/,/g,'')
    .replace(/n*.+:/g,',')
    .replace(/^,/,'')

There are still a few remaining newline characters. This next statement removes them.

let parsed = message.replace(/,/g,'')
    .replace(/n*.+:/g,',')
    .replace(/^,/,'')
    .replace(/n/g,'')

We now have a comma-separated string that looks like this:

Thu April 16 2020, Olga, Razcok, oraxcok@...

We can turn this into a matrix with JavaScript & # 39; s built-in share method.

let parsed = message.replace(/,/g,'')
    .replace(/n*.+:/g,',')
    .replace(/^,/,'')
    .replace(/n/g,'')
    .split(',');

Now, if you do, our email is like an array.

("Thu April 16 2020", "Olga", "Razcok", "oraxcok@...")

You can almost stop now. Add a simple statement so the script works.

return message;

However, there is another improvement we can make. The email contains information that we do not need, such as the Internet Protocol (IP) address and her favorite color.

ip: 35.138.107.243
fav_color: brown

We can remove these from our range with Map, The JavaScript array method. Here is the result of Map the method is assigned the variable "result."

let result = (0,1,2,3,4,6).map(index => parsed(index));

This line is tight, so let's take it bit by bit. First we have a lot of numbers.

(0,1,2,3,4,6)

The numbers in this group represent positions in message array. Remember that matrices are zero-based. When you calculate the positions in a matrix, you start at zero.

  • Date – position 0
  • First name – position 1
  • Surname – position 2
  • Email – Position 3
  • Telephone – position 4
  • IP position 5
  • Comment – Position 6
  • Favorite color – position 7

The Map the method takes each number in the array and assigns it to the "index" value and creates a new matrix minus unwanted IP address and favorite color data.

(0,1,2,3,4,6).map(index => parsed(index));

Here's what it completed parsing email feature looks.

function parseEmail(message){
    let parsed = message.replace(/,/g,'')
        .replace(/n*.+:/g,',')
        .replace(/^,/,'')
        .replace(/n/g,'')
        .split(',');

    let result = (0,1,2,3,4,6).map(index => parsed(index));

    return result;
}

Attempt

Assuming that the charity has sent a regular text email of the same format as above, when you click the "Collect email" menu item you will get a new line.

The script added a row to the spreadsheet. It will add a line for each email that the charity sends when the "Collect email" menu item is clicked.



Source link

Leave a Comment