Capture Gmail messages in a Google spreadsheet – ABOUT MAG 2020

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

This can be especially useful for capturing leads or contacts.

Here’s a scenario. Imagine that your e-commerce company has decided to work with a charity. Participants submit proposals through the charity’s website. Your job is to read these proposals, select people that your company can help 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 on the website cannot be added to a database or otherwise stored. Instead, these submissions can only be emailed to you in plain text format.

The solution? Capture and analyze these emails to facilitate interaction by adding them to a Google Sheet. In this article, I will explain how to do just that.

Sheet and Script

The work starts with creating a new Google spreadsheet. I’m going to call it “Example charity email”.

Start with a new Google spreadsheet.

Start with a new Google spreadsheet.

Go to the “Tools” menu in the spreadsheet and select the “Script editor” menu item.

Associate Google spreadsheets with custom scripts via

Associate Google spreadsheets with custom scripts using the “Script editor”.

This link will open a new Google script editor, where we will be doing our work. I called this script “Sample charity email.” The script editor allows us to write Javascript functions that will be performed in our spreadsheet.

Use the script editor to write JavaScript functions to execute on the spreadsheet.

Use the script editor to write JavaScript functions to execute on the spreadsheet.

There are several ways to write a script to retrieve messages from Gmail and add them to a Google spreadsheet. In this example, I will use four functions: onOpen, get together, getGmailand parseEmail.

onOpen

O onOpen The role is unique to Google Apps scripts. It will always be executed when a spreadsheet is opened. So the name must be onOpen. You can, however, get him to do what he wants. In our case, we will add a new menu and menu item to the spreadsheet.

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

There are three parts to this function.

First, we will use the SpreadsheetApp class to get the active spreadsheet, that is, the spreadsheet that the user is viewing. This spreadsheet object is assigned to the JavaScript “spreadsheet” constant.

const spreadsheet = SpreadsheetApp.getActive();

Next, I will create a variety of menu items. In this example, our matrix has only one menu item, but it could have more. Writing in this way will make it easier to add menu items as needed.

Notice that functionName describes the callback function that will be performed when a user clicks on the “Collect emails” menu item. In that case, I’ll call a function called get together.

let menuItems = [
    {name: 'Gather emails', functionName: 'gather'},
];

Of google documentation describes in detail how to add a menu. For our purposes, you only need to employ the addMenu method – passing first to the menu name and second to the array of menu items.

spreadsheet.addMenu('Charity', menuItems);

In the script editor, we can perform this function by clicking on the Play button, the black triangle that looks like a video playback button. Alternatively, we could update the spreadsheet, but that would close the script editor. If it closes, we can reopen it in the Tools menu.

The execution button allows us to execute a selected function in the script editor.

The execution button allows us to execute a selected function in the script editor.

The first time you run the app, you need to grant permission to access your Google account.

Grant the script access to your Google account.

Grant the script access to your Google account.

After this function is performed, we will move to a new menu and menu item on the charity worksheet.

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

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

get together

Next, let’s write the get together occupation.

Here the name must be logical. For me, “gathering” conveys the idea of ​​collecting and organizing. However, you can name the role as you wish.

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

    let curSheet = SpreadsheetApp.getActive();

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

The role completes three tasks.

First, it retrieves all charity email messages by calling the getGmail function, which I will write next.

let messages = getGmail();

Then it uses the SpreadsheetApp class to get the active spreadsheet.

let curSheet = SpreadsheetApp.getActive();

Finally, the function iterates through all messages, adding the desired content from each one to the spreadsheet after sending it to the parseEmail function that we still have to write.

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

A lot is happening in this last step, so let’s review it.

For each message in posts, the function gets the current worksheet, curSheet.

Then it uses Google appendRow method for adding a row to the spreadsheet. This method normally takes an array object. But in this case, we are providing the result of another function, parseEmail, which, in turn, will provide the necessary matrix.

getGmail

O getGmail function does more work.

First, we define the value of a JavaScript constant called “query” as 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 search in Gmail. Note that we specify the “from” address and the “subject” of the email. We also make sure that the email message does not include the “done” label.

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

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

Then we use Google GmailApp to perform a search in the user’s inbox. The result of this research will be all topics of the charity with the correct subject.

let threads = GmailApp.search(query);

If there are too many messages in your Gmail account, you can get batches of ten. This will speed up the script.

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

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

Remember that in the search query, we exclude messages with the label “done”. So now we add the “done” label to the messages we just retrieved. When the script is executed next, it will ignore these messages.

The first line uses Google getUserLabelByName method for finding the “ready” label and assigning it to the “label” variable. If that label doesn’t already exist in the user’s Gmail account, the next line will create it.

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

The last section of this function collects each email message, adds it to the “messages” matrix and labels it as “completed”.

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

Finally, the function returns the message array.

return messages;

What follows is the conclusion getGmail occupation.

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;
}

parseEmail

The last function “parses” the email message and formats it to fit our spreadsheet.

This function must be specific to the plain text format of the message. So, let’s see what the charity’s 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 clear and format this copy of the message. I will use Regex Pal, a regular expression tool, to determine which expressions would do what I need.

The Regex Pal allows you to see which parts of the text a specific regular expression will match.

The Regex Pal allows you to see which parts of the text a specific regular expression will match.

I will use commas later to separate items from the list. So, I will remove the commas from the date to avoid confusion.

date: Thu, April 16, 2020

This regular expression will replace the commas with an empty string, which is actually nothing.

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

In this case, “message” is the message passed to the parseEmail function of get together function that I wrote earlier.

The next regular expression is powerful, as it removes most of the newline characters ( n) from our plain text email message and deletes all previous characters, including a colon.

As an example, it will transform “first_name: Olga” into “, Olga” (yes, that is a comma, a space and the word “Olga”).

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

The previous step will leave an unwanted comma before the date. So the next replace The statement removes.

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

There are still some newline characters left. The next instruction removes them.

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

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

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

We can turn this into an array with the embedded JavaScript Divided method.

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

Our email message is now compartmentalized, if you prefer, as a matrix.

["Thu April 16 2020", "Olga", "Razcok", "oraxcok@..."]

You could almost stop now. Add a simple return statement, and the script will work.

return message;

There is, however, another improvement that we can make. The email message includes information that we don’t need, such as the sender’s Internet Protocol (IP) address and your favorite color.

ip: 35.138.107.243
fav_color: brown

We can remove them from our matrix with map, the JavaScript array method. Here the result of map The method is assigned to the “result” variable.

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

This line is dense, so let’s take it piece by piece. First, we have an array of numbers.

[0,1,2,3,4,6]

The numbers in this matrix represent positions in the message array. Remember that arrays are zero-based. When you count positions in a matrix, it starts at zero.

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

O map The method takes each number in the array and assigns it to the “index” value, creating a new array minus the unwanted IP address and favorite color data.

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

Here’s what the completed parseEmail function looks like.

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;
}

Try it

Assuming the charity has sent a plain text email in the same format as above, when you click on the “Collect emails” menu item, you will have a new line.

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

Paula Fonseca