Parse gmail to google sheets

Parse gmail to google sheets DEFAULT

Are you dreaming of having all your Gmail data as records in a spreadsheet? This may be beneficial in many ways, for example to analyze your email subscriptions, to track communication history with customers in your self-made Google Sheets CRM, or simply as a back-up archive. However, there is no button that will extract Gmail to Google Sheets with one or a couple of clicks. Yet, you have a few options and workarounds to get this job done. Read on and apply the one you like the most. 

Let’s start with a mediocre solution that consists of three high-level steps:

  • Exporting Gmail data as an MBOX file (email messages) or a JSON file (user settings)
  • Converting the exported file to CSV 
  • Uploading the CSV file into Google Sheets

Feel free to jump to the next section, Export Gmail to Google Sheets using the API, if you don’t want to deal with this.

Step 1: Export Gmail data using Google Takeout

Select data

Go to https://takeout.google.com/ and click Deselect all. 

Now you can scroll down and select Mail

If you don’t want to export all data from your Gmail, then click the All Mail data included button, deselect Include all messages in Mail, and select the specific label for your export. Click OK in the top-right corner.

After that, you can click the Next step button at the bottom.

Select file type, frequency, and destination

Select the Delivery method:

  • Send download link via email
  • Add to GDrive
  • Add to Dropbox
  • Add to OneDrive
  • Add to Box

Select the Frequency:

  • Export once
  • Export every two months for 1 year

Select the File type (.zip or .tgz) and its size (1, 2, 4, 10, or 50 GB).

Create the export

Click the Create export button, and Google will start creating a copy of files from your Gmail. This process can take a long time to complete, depending on the amount of data to be exported. You’ll receive an email when your export is done. And here is how it looks once completed.

Now you need to download the MBOX file to your device. If you selected the option of exporting to cloud storage, you’ll be able to open the file from it. For example, here is how it looks with GDrive:

Step 2: Convert the exported file into CSV 

Unfortunately, you can’t import MBOX files into Google Sheets (we’ve already tried). 🙂 

So, you need to convert MBOX to CSV. This can be done using a dedicated tool, such as an online MBOX to CSV converter or Sysinfo MBOX Exporter Tool. In our example, we used the latter option. Although it requires installation, it worked well, and transformed the MBOX file exported from Gmail into CSV.

Step 3: Import CSV to extract Gmail date and body to Google Sheets

The last step is to import the resulting CSV into Google Sheets. Open your spreadsheet, go to File => Import and upload the CSV file with your Gmail data. Here is what it looks like:

Frankly, we don’t like this option because it’s manual, burdensome, and dependent on the MBOX to CSV converter. Let’s check out some other solutions.

Export Gmail to Google Sheets using the API (WITHOUT CODING)

Some readers may be scared of the term API, but it’s the most actionable option for linking Gmail to Google Sheets. Besides, with Coupler.io, you won’t have to do any coding. The solution consists of the following steps:

  • Enable the Gmail API and get credentials
  • Obtain a Google OAuth Access Token
  • Extract data from Gmail to Google Sheets

Enable the Gmail API and get credentials

We need the access token to access Gmail and other Google services (if necessary) via API. Follow the instructions below to obtain your access token in less than 5 minutes. It may look tricky since Google put in a lot of effort to protect your data under 7 locks. So, while you are following this guide, you can imagine that you are a hacker trying to break into the Pentagon 🙂

Step 1: New project on Google API Console

Use this link to create a new project on Google API Console (log in to your Google account if requested). Name your project and click Create.

Step 2: Enable API

Select your project from the dropdown list, then click Enable APIs and Services.

Select the Gmail API in the API library, since we need to export Gmail to Google Sheets. Then click Enable.

Click Create Credentials and then go to the OAuth consent screen.

Step 3: Set up OAuth consent screen

Select External as the user type and click Create

Configure the following parameters:

  • App information
    • App name – insert the name of the app asking for consent (e.g. Coupler.io or Personal)
    • User support email – select your email address
  • Developer contact information
    • Email address – insert your email address

Click Save and Continue.

On the Scopes page, you don’t need to configure anything, so just click Save and Continue.

On the Test users page, click Add users and insert your email address. You may also specify other users if necessary => click Add. After that, click Save and Continue

On the Summary page, click Back to Dashboard.

Step 4: Create credentials

Go to the Credentials menu and click Create Credentials. Choose OAuth Client ID.

Choose Web application as the application type, name it if you want (or you can leave the default name), and add an Authorized redirect URI:

https://coupler.io

Click Create. You’ll get your Client ID and Client Secret

We’ll need these credentials, so copy them to some text editor and click OK

How to get a Google OAuth Access Token to connect Gmail to a Google Sheets spreadsheet

Step 1: Get an authorization code

Insert your Client ID and Redirect URI into the following URL:

You should get something like this:

https://accounts.google.com/o/oauth2/auth?scope=https://www.googleapis.com/auth/gmail.readonly&response_type=code&access_type=offline&redirect_uri=https://coupler.io&client_id=idj4haiu2vmrmuu58rccgl7k1.apps.googleusercontent.com

Copy the URL and paste it in an incognito window of your browser, then press Enter. You’ll need to log in to your Google account and grant coupler.io permission to View your email messages and settings.

Click Allow and you’ll be redirected to the Coupler.io home page. Copy the code from the URL on the address bar.

It may look as follows:

The authorization code is needed to obtain a Google OAuth access token.

Step 2: Get a Google OAuth access token 

To get an access token, you need to send a POST request using the following URL template:

  • – Insert the authorization code obtained in the previous step
  • – Insert your client ID 
  • – Insert your client secret 
  • – Insert your redirect URL

Here is what the URL should look like:

https://accounts.google.com/o/oauth2/token?grant_type=authorization_code&code=4%2F0AY0e-g6N37embD-8RPXRk2D3cg6t8PoTmn37JOYj9TMAKPa5WQ8hHY7t6pR3kBtRjQg&client_id=idj4haiu2vmrmuu58rccgl7k1.apps.googleusercontent.com&client_secret=Ol_dCEoCn1CEb91d9ERY7&redirect_uri=https://coupler.io

To send a POST request, we’ll use Postman, a Google Chrome app for interacting with HTTP APIs. Do the following:

  • Enter the request URL in the field
  • Change the request type from GET to POST
  • Click Send

Here is the response with your access token and refresh token:

Once it expires, you’ll have to repeat steps 1 and 2 to get a new one. Alternatively, you can use the refresh token to update the access token and get it right in your Google Sheets as follows.

Step 3: Automate updates of Google OAuth access token in Google Sheets

Sign in to Coupler.io, click the +Add Importer button and choose JSON Client as a source application. You can also name your importer as you like. Click Continue.

Coupler.io is an out-of-the-box solution to synchronize data between your source and destination on a schedule. It provides ready-to-use connections, such as Google Sheets to Google Sheets, as well as customizable integrations that you can configure yourself.

Insert the following URL into the JSON URL field. Do not forget to specify the highlighted parameters. 

  • – Insert the refresh token obtained in the previous step
  • – Insert your client ID 
  • – Insert your client secret 
  • – Insert your redirect URL

Click Continue.

Select POST as the HTTP Method and click Proceed to Destination Settings.

In the Destination section, you need to:

  • Select your Google account
  • Select an existing spreadsheet and a sheet to import data to

Click Proceed to Schedule Settings.

Since the Google OAuth access token expires in one hour, let’s set the one hour interval for our importer, so it will update your access token every hour.

The last thing to do is click Save and Run. Now you’ll always have a valid access token in your spreadsheet.

Using the OAuth access token, we can export Gmail to Google Sheets using Coupler.io, in a way similar to the above. The following data is available:

Type of data JSON URL resource
Current user’s Gmail profile.
Lists the drafts in the user’s mailbox.
Gets the specified draft.
Lists the history of all changes to the given mailbox
Lists all labels in the user’s mailbox.
Gets the specified label.
Lists the messages in the user’s mailbox.
Gets the specified message.
Lists the threads in the user’s mailbox.
Gets the specified thread.

Refer to the Gmail API documentation for more information on available resources and parameters.

You will need to create a JSON URL using the basic Gmail URL attached with the resource from the table:

  • – your email address
  • – type of data to export

Insert the JSON URL into the JSON URL field and click Continue

Select GET as the HTTP Method and insert the following string into the HTTP Headers field:

  • – insert your access token

In the URL query string field, you can use the parameter. It allows you to query specific emails. For example, 

q: from:[email protected] is:unread

For more on this, read the API documentation.

Then, you’ll need to choose the destination and importing schedule and run your importer. The data will be extracted from Gmail to Google Sheets. Let’s check this out in the following example.

Gmail to Google Sheets example

Let’s import a list of emails from a specific sender. For this, we’ll need the following parameters:

JSON URL

https://gmail.googleapis.com/gmail/v1/users/{user-id}/messages/

HTTP method

GET

HTTP headers

Authorization: Bearer {your-access-token}

URL query string

q: from:{sender’s-email}

Here is what the flow looks like:

Can you automate Gmail to Google Sheets?

The short expiration period of the OAuth access token does not let you enjoy the full functionality of the automatic data refresh by Coupler.io. However, you can use our Gmail to Google Sheets template with the preset importers. 

For this, you need to install the Coupler.io add-on from the Google Workspace Marketplace, obtain the Gmail API credentials, as well as the refresh token, and follow the instructions explained in the Readme sheet.

If you need this Gmail to Google Sheets integration ready to use, tell us about it by filling out this form. We’re always working on new importers, and we need your opinion on which importer to release next.

Export Gmail contacts to Google Sheets

Gmail API does not let you export contacts. For this, you need to enable People API and add a respective scope to your app, and it will work. Here is what you need to do in brief:

Step 1: Enable the People API

The flow is the same as we did with the Gmail API. Select the Google People API in the API library and click Enable

Note: You don’t need to create credentials, since you already have them.

Step 2: Get an authorization code

Insert your Client ID and Redirect URL into the following URL with the updated scope:

The rest of the flow is the same:

  • Copy the URL and paste it in an incognito window of your browser, then press Enter.
  • Get the authorization code and use it to obtain the Google OAuth access and refresh tokens as we did before.

Step 3: Import email addresses from Gmail to Google Sheets

With a new access token, you can go to Coupler.io and use the following parameters to get your Gmail contacts:

JSON URL

https://people.googleapis.com/v1/people/me/connections?personFields=names,emailAddresses

HTTP Method

GET

HTTP headers

Authorization: Bearer {your-access-token}

Click Save and Run, and welcome your Gmail contacts into your spreadsheet. 

Other options to import Gmail to Google Sheets

In this blog post, we did not touch the App Script option, since we promised to explain how you can import Gmail to Google Sheets without coding. However, if you’re interested in this solution, we may add it here later. Let us know if this sounds interesting, and good luck with your data!

Back to Blog
Sours: https://blog.coupler.io/gmail-to-google-sheets/

With Google Apps Script, marketers can capture Gmail messages, parse them for relevant information, and add them row-by-row to a Google spreadsheet.

This can be especially helpful for capturing leads or contacts.

Here is a scenario. Imagine your ecommerce business has decided to work with a charity. Participants submit proposals via the charity&#;s website. Your job is to read through those proposals, select folks your business can help, and contact them to get started.

The trouble is that the charity&#;s website was built in the s on a locked-down, proprietary platform. Submissions on the site cannot be added to a database or otherwise stored. Instead, those submissions can only be emailed to you in plain text format.

The solution? Capture and parse those emails for easy interaction by adding them to a Google Sheet. In this article, I&#;ll explain how to do just this.

Sheet and Script

The work starts with creating a new Google Sheet. I&#;ll name it &#;Charity Email Example.&#;

Start with a new Google Sheet.

Start with a new Google Sheet.

Head to the &#;Tools&#; menu in the sheet and select the &#;Script editor&#; menu item.

Associate Google spreadsheets with custom scripts via the "Script editor."

Associate Google spreadsheets with custom scripts via the &#;Script editor.&#;

This link will open a new Google script editor, which is where we will be doing our work. I named this script &#;Charity Email Example.&#; The script editor allows us to write JavaScript functions that will run on our spreadsheet.

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

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

There are numerous ways to write a script to retrieve Gmail messages and add them to a Google spreadsheet. In this example, I&#;ll use four functions: onOpen, gather, getGmail, and parseEmail.

onOpen

The onOpen function is unique in Google Apps Scripts. It will always run when a sheet is opened. Thus, the name must be onOpen. You can, however, make it do just about anything you want. In our case, we&#;ll add a new menu and menu item to the sheet.

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&#;ll use the SpreadsheetApp class to get the active sheet — i.e., the sheet the user is looking at. This sheet object is assigned to the JavaScript constant &#;spreadsheet.&#;

const spreadsheet = SpreadsheetApp.getActive();

Next, I&#;ll create an array of menu items. In this example, our array has just one menu item, but it could have more. Writing it this way will make it easier to add menu items as needed.

Note that functionName describes the callback function that will run when a user clicks on the &#;Gather emails&#; menu item. In this case, I&#;ll call a function named gather.

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

Google&#;s documentation describes in detail how to add a menu. For our purposes, you need only to employ the addMenu method — passing it, first, to the name of the menu and, second, to the array of menu items.

spreadsheet.addMenu('Charity', menuItems);

In the script editor, we can run this function by clicking the run button, the black triangle that looks like a play button for a video. Alternatively, we could refresh the spreadsheet, but that would close the script editor. If it does close, we can reopen it from the Tools menu.

The run button allows us to execute a selected function within the script editor.

The run button allows us to execute a selected function within the script editor.

The first time you run the app, you&#;ll need to grant it 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 runs, we&#;ll move to a new menu and menu item in the charity spreadsheet.

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

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

gather

Next, let&#;s write the gather function.

Here the name should be logical. To me, &#;gather&#; conveys the idea of collecting and organizing. However, you can name the function whatever 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 retrieves all of the charity email messages by calling the getGmail function, which I&#;ll write next.

let messages = getGmail();

Next, it uses the SpreadsheetApp class to obtain the active sheet.

let curSheet = SpreadsheetApp.getActive();

Finally, the function iterates across all messages, adding the desired content from each to the spreadsheet after sending it to the parseEmail function that we have yet 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 messages, the function gets the current sheet, curSheet.

It then uses Google&#;s appendRow method to add a row to the sheet. This method normally takes an array object. But in this case, we&#;re providing the result of another function, parseEmail, which will, in turn, provide the required array.

getGmail

The getGmail function does the most work.

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

const query = "from:[email protected] AND subject:Charity Example NOT label:done";

This search string is exactly what you would use for advanced search in Gmail. Notice that we specify the &#;from&#; address and the email&#;s &#;subject.&#; We also ensure 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.

Next, we use Google&#;s GmailApp class to run a search on the user&#;s inbox. The result of that search will be all the threads from the charity with the correct subject.

let threads = GmailApp.search(query);

If there are many messages in your Gmail account, you can obtain 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, in the search query, we excluded messages with the &#;done&#; label. So now we add the &#;done&#; label to the messages we just retrieved. When the script runs next, it will ignore these messages.

The first line uses Google&#;s getUserLabelByName method to find the &#;done&#; label and assign it to the variable &#;label.&#; If that label does not yet 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&#; array, 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;

What follows is the completed getGmail function.

function getGmail() { const query = "from:[email protected] 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 will &#;parse&#; the email message and format it to fit our spreadsheet.

This function has to be specific to the message&#;s plain text format. So let&#;s look at what the charity&#;s website is sending us.

date: Thu, April 16, first_name: Olga last_name: Razcok email: [email protected] phone: ip: comment: I would really like to participate in this program. fav_color: brown

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

Regex Pal lets you see what portions of text a particular regular expression will match.

Regex Pal lets you see what portions of text a particular regular expression will match.

I&#;ll use commas later to separate items on the list. Thus I&#;ll remove the commas in the date to avoid confusion.

date: Thu, April 16,

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

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

In this case, &#;message&#; is the message passed to the parseEmail function from the gather function 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 of the characters before, including colons.

As an example, it will turn &#;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 statement removes it.

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

There are still a couple of 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 the following:

Thu April 16 , Olga, Razcok, [email protected]

We can transform this into an array with JavaScript&#;s built-in split method.

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

Now, our email message is compartmentalized, if you will, as an array.

["Thu April 16 ", "Olga", "Razcok", "[email protected]"]

You could nearly stop now. Add a simple return statement, and the script would work.

return message;

There is, however, another improvement we can make. The email message includes info that we don&#;t need, such as the submitter&#;s internet protocol (IP) address and her favorite color.

ip: fav_color: brown

We can remove these from our array with map, the JavaScript array method. Here the result of the map method is assigned to the variable &#;result.&#;

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 array represent positions in the message array. Remember that arrays are zero-based. When you count the positions in an array, you start at zero.

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

The map method will take each number in the array and assign 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 is 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 with the same format as above, when you click the &#;Gather emails&#; menu item, you will have a new row.

The script added a row to the spreadsheet. It will add a row for each email the charity sends whenever the &#;Gather emails&#; menu item is clicked.

Armando Roggio

Armando Roggio

Bio   &#;   RSS Feed



Sours: https://www.practicalecommerce.com/capture-gmail-messages-in-a-google-sheet
  1. Baja go kart for sale
  2. Surf fishing cocoa beach florida
  3. Husqvarna 350 chainsaw price
  4. Fat free cheddar cheese calories
  5. Lexus rx 350 hybrid 2017

prasanthmj/messages.html

<!DOCTYPE html><html><head><basetarget="_top"><title>Data parsed from emails</title><linkrel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"></head><bodystyle="padding:3em;"><h2>Parsed data from Messages</h2><tablestyle="widthpx"><thead><tr><th>Date</th><th>Merchant</th><th>Card</th><th>Amount</th></tr></thead><tbody><? for(var m=0;m<records.length;m++){?><tr><td><?= records[m].date ?></td><td><?= records[m].merchant ?></td><td><?= records[m].card ?></td><td><?= records[m].amount ?></td></tr><? }?></tbody></table><divclass="block"><buttonid="save_to_sheet" class="blue">Save Data to Sheet</button><spanid="saving_label" style="visibility:hidden">Saving </span></div><script>document.querySelector("#save_to_sheet").addEventListener("click",function(e){varself=this;self.style.visibility='hidden';varsaving_label=document.querySelector("#saving_label");saving_label.style.visibility='visible';google.script.run.withSuccessHandler(function(){alert("Saved expense data");self.style.visibility='visible';saving_label.style.visibility='hidden';}).withFailureHandler(function(){alert("an error occured while saving");self.style.visibility='visible';saving_label.style.visibility='hidden';}).processTransactionEmails();});</script></body></html>
Sours: https://gist.github.com/ecde3bc3caaadd
Gmail Parsing To Google Sheets Using Google Apps Script [Demo]

How to parse and extract data from Gmail to Google Sheets

Extracting data from Gmail messages and then routing that data to other automation steps is an easy task to do using Google Apps Script.

My bank sends transaction confirmation emails for each transaction in my account. I will parse and collect the expense data into a Google Sheet. By the end of the month, I will have a nice overview of all the expenses organized in a Google Sheet ready to process and analyze.

With a little customization for your own email messages, you can automate this process for your own requirements.

Create a standalone web app in Google Apps Script

For this project, we need to create a standalone web app in Google Apps Script. Go to https://script.google.com. Create a new script. Give a name to the script.

Filtering the emails

The search feature in Gmail is quite powerful and you can get to the right emails using the gmail search operators. Read more about the Gmail search operators in this article.

You can use the search operators to filter and get the transaction emails. For the purpose of this project, we need only the transaction confirmation emails and not the marketing emails from the bank.

function getRelevantMessages() { var threads = GmailApp.search("from:citicorp.com AND subject:Transaction confirmation",0,10); var messages=[]; threads.forEach(function(thread) { messages.push(thread.getMessages()[0]); }); return messages; }

This function searches for messages from &#;citicorp.com&#; and with emails having subject containing &#;Transaction confirmation &#;&#;.
So the search function becomes:

GmailApp.search("from:citicorp.com AND subject:Transaction confirmation",0,10);

Note that I am passing the second and third parameter to the search() function. This is to limit the number of messages returned and to make the script run faster. If there are too many messages matching the criteria, the search will take a long time to fetch all the messages. Setting a limit makes it faster.

Let us display the messages returned by the search.

First, add an HTML template. Do: File → New and create a new HTML file. Let us name it &#;messages.html&#;

<!DOCTYPE html> <html> <head> <base target="_top"> <title>Message Display Test</title> <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"> </head> <body style="padding:3em;"> <h1>Messages</h1> <ul> <? for(var m=0;m<messages.length;m++){ ?> <li><?= messages[m].getSubject() ?></li> <p><?= messages[m].getPlainBody() ?></p> <? } ?> </ul> </body> </html>

Now, here is the script to fetch and display the messages:

function getRelevantMessages() { var filter = "from:citicorp.com AND subject:Transaction confirmation"; var threads = GmailApp.search(filter,0,10); var messages=[]; threads.forEach(function(thread) { messages.push(thread.getMessages()[0]); }); return messages; } function getMessagesDisplay() { var templ = HtmlService.createTemplateFromFile('messages'); templ.messages = getRelevantMessages(); return templ.evaluate(); }

You can customize the search filter so that it fetches the right email messages.

Do File → Save.

Go to the menu item: Publish → Deploy as web app.

Then provide the permissions. At the end of the sequence, it will give you the link to open the script in the browser. Open the link. (click on the link for the latest development version)

The web page should display the messages.

Parse the message

The next step is to parse and extract the data we are interested in, from the message. Regular expression is one of the easily available feature in such situations.

There is one tool that will make it quite easy to build the regular expression. It is regexr.com.

parse using regular expression

parse using regular expression

Copy and paste the message text to the text area of the tool and then compose the regular expression step-by-step. The tool will show the matches live. This makes it easier to build the regular expression.

Once the regular expression is ready, parsing out the data is easy.

function parseMessageData(messages) { var records=[]; for(var m=0;m<messages.length;m++) { var text = messages[m].getPlainBody(); var matches = text.match(/Rs\.\s+([\d\,\.]+)\s+(?:spent on card)\s+(\d+)\s+(?:on)\s+([\d\-A-Z]+)\s+(?:at)\s+([\w\s]+)\./); if(!matches || matches.length < 5) { //No matches; couldn't parse continue with the next message continue; } var rec = {}; rec.amount = matches[1]; rec.card = matches[2]; rec.date= matches[3]; rec.merchant = matches[4]; //cleanup data rec.amount = parseFloat(rec.amount.replace(/,/g, '')); records.push(rec); } return records; }

Let us verify that the parsing actually gets the correct data.

Create another HTML file and name it parsed.html. We will send the parsed records to the HTML template and display it.

<!DOCTYPE html> <html> <head> <base target="_top"> <title>Data parsed from emails</title> <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"> </head> <body style="padding:3em;"> <h2>Parsed data from Messages</h2> <table style="widthpx"> <thead> <tr> <th>Date</th><th>Merchant</th><th>Card</th><th>Amount</th> </tr> </thead> <tbody> <? for(var m=0;m<records.length;m++){ ?> <tr> <td><?= records[m].date ?></td> <td><?= records[m].merchant ?></td> <td><?= records[m].card ?></td> <td><?= records[m].amount ?></td> </tr> <? }?> </tbody> </table> </body> </html>

Here is the script:

function parseMessageData(messages) { var records=[]; for(var m=0;m<messages.length;m++) { var text = messages[m].getPlainBody(); var matches = text.match(/Rs\.\s+([\d\,\.]+)\s+(?:spent on card)\s+(\d+)\s+(?:on)\s+([\d\-A-Z]+)\s+(?:at)\s+([\w\s]+)\./); if(!matches || matches.length < 5) { //No matches; couldn't parse continue with the next message continue; } var rec = {}; rec.amount = matches[1]; rec.card = matches[2]; rec.date= matches[3]; rec.merchant = matches[4]; //cleanup data rec.amount = parseFloat(rec.amount.replace(/,/g, '')); records.push(rec); } return records; } function getParsedDataDisplay() { var templ = HtmlService.createTemplateFromFile('parsed'); templ.records = parseMessageData(getRelevantMessages()); return templ.evaluate(); } function doGet() { return getParsedDataDisplay(); }

Note: I didn&#;t parse the date to a Date() object. This is to keep the code simple. It will take only a few extra steps to parse the date.

gmail parsed data

Save the data to a Google Sheet

Now that we have the data extracted, the next step is to save the data to a Google Sheet.

function saveDataToSheet(records) { var spreadsheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1ql-wf1OMoZ5zGb"); var sheet = spreadsheet.getSheetByName("Sheet1"); for(var r=0;r<records.length;r++) { sheet.appendRow([records[r].date,records[r].card, records[r].merchant, records[r].amount ] ); } } function processTransactionEmails() { var messages = getRelevantMessages(); var records = parseMessageData(messages); saveDataToSheet(records); }

The steps are pretty straight forward. Open the spreadsheet, then append the rows to the sheet.

Preventing the same message from getting processed again and again

If you call processTransactionEmails() multiple times, it will keep adding the same rows multiple times.

One way to prevent that from happening is to add a label to the processed emails.

function labelMessagesAsDone(messages) { var label = 'payment_processing_done'; var label_obj = GmailApp.getUserLabelByName(label); if(!label_obj) { label_obj = GmailApp.createLabel(label); } for(var m =0; m < messages.length; m++ ) { label_obj.addToThread(messages[m].getThread() ); } } function processTransactionEmails() { var messages = getRelevantMessages(); var records = parseMessageData(messages); saveDataToSheet(records); labelMessagesAsDone(messages); return true; } function getRelevantMessages() { var filter = "from:citicorp.com AND subject:Transaction confirmation AND -label:payment_processing_done"; var threads = GmailApp.search(filter,0,10); var messages=[]; threads.forEach(function(thread) { messages.push(thread.getMessages()[0]); }); return messages; }

We add the label payment_processing_done to the messages that are processed. Notice that we updated the search filter .

This means get the messages, but without the label &#;payment_processing_done&#;.

Processing the emails as it arrives in Gmail

In order to completely automate it, this script has to keep running whenever there is a new email. However, there is no easy way to trigger the script when a new email arrives.

The other alternative is to trigger this script on a timer. Let&#;s run this script every hour.

For each run, we have to check for the emails received in the last one hour. However, there is no direct search filter for that.

This Gmail search: newer_than:1d will get emails in the last one day. Combine it with the other operators and we have a sufficiently narrow filter.

So the updated filter becomes:

var filter = "newer_than:1d AND from:citicorp.com "+ " AND subject:Transaction confirmation "+ " AND -label:payment_processing_done";

Timer Trigger

The next step is to trigger this script every 1 hour. We have to add a timer trigger. Go to the menu item: Edit → Current projects triggers

Click on the new trigger button.

Then create a timer trigger. Select the &#;processTransactionEmails&#; function for &#;the function to run&#;.

gmail check email timer

See also

Sours: https://blog.gsmart.in/parse-and-extract-data-from-gmail-to-google-sheets/

Google parse gmail sheets to

Popular Email Parser by Zapier + Google Sheets workflows

Our most popular Zap!arrow pointing down
  • Store new parsed emails in Google Sheets rows

    Zapier

    by Zapier

    Email Parser by Zapier + Google Sheets

  • Create Google Sheets spreadsheets rows for new emails

    Zapier

    by Zapier

    Email Parser by Zapier + Google Sheets

  • Create Google Sheet rows for new emails received to a mailbox

    Zapier

    by Zapier

    Email Parser by Zapier + Google Sheets

  • Create Google Sheets rows with phone line type verified by Phone Validator when new parsed emails arrive

    Zapier

    by Zapier

    Email Parser by Zapier + Phone Validator + Google Sheets

Do Even More with Email Parser by Zapier + Google Sheets

With Zapier, you can do more than just connect 2 apps—you can automate entire processes from beginning to end! Here are some popular ways users make their Email Parser by Zapier + Google Sheets workflows do more for them.

Sours: https://zapier.com/apps/email-parser/integrations/google-sheets
Gmail Google Sheets Integration - Connect Gmail to Google Sheets \u0026 Create Backup for Your Emails

Google Sheets Upgrade &#; email parsing to Google Sheets

Parse Your Emails & send them to Google Sheets Version

At Mailparser, we strive to make it easier for our users to extract and parse their emails to different integration platforms. The latest addition to that kit is Google Sheets Version  With our Google Sheets native integration upgrade to , you can easily extract the data from your emails and send it to Google Sheets.

Email Parsing to Google Sheets

Mailparser has always had native webhook integration with Google Sheets, but recently we upgraded our Google Sheet integration to the latest version of
As per Google the Version , also known as The Google Sheets API V3, lets you develop client applications that can read and modify worksheets and data in Google sheets. This latest version comes in handy when:

  • you need to compute the data stored in an inaccessible system
  • you need to use the sheets data to be presented in a browser
  • you need to consume the data entered by a user in the spreadsheet

Now that mailparser.io supports Google Sheets API V3, you can perform all the above on sheets that have been updated with data you receive in emails.
So, go ahead. Sign-up for an account with Mailparser for email parsing, extract the data, and experience email parsing to Google Sheets. You now have all the functionality of older versions, with the added benefits of V3 automate your workflow.
Create a Mailparser account
Above we’ve described some of the basics of sending parsed emails to Google Sheets. We also have a video tutorial for using Google Sheets, if you have advanced needs, please reach out to our support team with specifics. We are always excited to hear how you are intending to use this native integration. You can also send emails from Google sheets, which is covered in our blog.

Follow the steps below to begin email parsing to Google Sheets

1. Log in to Mailparser with your account details
2. Send or forward your emails to your mailparser.io inbox
3. Click on Parsing Rules on the left-hand side panel
4. Click on ‘Add parsing rule’
5. Select where the data is located in your email. (Header, Body, etc.)
6. When the email is displayed, click on our add text filter
7. Define the data you want to extract, (this can be as simple or complex as your needs dictate).
8. Review the results, add more rules if you like.
9. Click on Webhook Integrations on the left-hand side panel and select Google Sheets
Sign in to your Google Account that is linked with the sheet you want your data sent to
Select the Google Sheet and Worksheet you want to be updated with the data, do the field mapping, and save. Your data is extracted for future emails sent to the inbox that these rules are built off.

Sours: https://mailparser.io/blog/email-google-sheets-upgrade/

Similar news:

Cause her pain. Then he began to do it faster, caressing her lower lips and clitoris with his hands. Judging by the reaction, it gave her Summer holidays came and my sister from another city came to spend the night with me. I immediately drew attention to her when she touched me, my riser rose, her soft skin was so exciting, I inadvertently.



6745 6746 6747 6748 6749