community
cancel
Showing results for 
Search instead for 
Did you mean: 

Engine Works Blog

Under the hood of Alteryx: tips, tricks and how-to's.
Alteryx
Alteryx

Like so many tools, the idea for this sprang from a conversation in the Solutions Center, at Inspire 2016.  A user was regularly receiving emails that contained attachments.  He wanted to use them in his analytic workflows and encountered a lot of manual work related to getting the files into an accessible location.  I had a similar situation in a previous job, so it was easy for me to relate to his frustration.  Using tools that the Alteryx team had previously built, like the Google OAuth Macro and Google Sheets Tools, we were able to build a POC during our conversation.  The following walks through how this was accomplished and packaged as the Gmail Attachment Input tool.

 

HighLevelWorkflow.png

 

At a high-level, the tool makes three Gmail API requests that build on the prior requests.  The first request returns a list of email messages that match the search parameters specified.  The second request iterates through the message list and returns details (date, from, subject, attachment name, and attachmentId) for each message.  The final request returns the message attachment data.

 

Prerequisites

To access the account's email, the Gmail Attachment Input tool uses a Client ID, Client Secret, and Refresh Token associated with the Gmail API and account. The Alteryx Knowledge Base article, How to Create Google API Credentials, walks you through how to obtain these. To fit our use case, in Step 6 you'll want to use Gmail API and in Step 16 you'll want to use https://www.googleapis.com/auth/gmail.readonly.  

 

 

UserInputs.PNG













 

 

The initial step of any tool or macro is to gather user inputs.  For this tool, there are only four pieces of input that need to be gathered from the user:  Client ID, Client Secret, and Refresh Token (for authentication) and search parameters.  The search parameters are used to identify which emails we want to download attachments from.  An HTML frontend was used to capture this input from users. Using an HTML frontend allowed me to easily integrate search parameter tips and a link to the Gmail Help page, Search operators you can use with Gmail.  Creating an HTML frontend won't be covered in this blog post but you can find more information on the HTML SDK at Inspiring Ingenuity  (our CTO @Ned 's blog.)

 

 

Step1.PNG

 

 

 

 

 

 

 

 

 

 

 

The process begins with refreshing the Authentication token needed for accessing the Google APIs.  We'll gloss over it here because a previous blog post I wrote, OAuth Macro for Google API Authentication, covers it in detail.

 

 

Step2.PNG

 

 

 

 

 

 

 

 

 

 

 

The first Gmail specific API call made is to get a list of messages that match the search parameters specified.  Using the Download tool, a GET request from https://www.googleapis.com/gmail/v1/users/me/messages is made.

 

The URL is made up of three parts:

 GetMessageList.png

 

 

 

 

 

 

 

 

 

 

 

The request produces a JSON response which is parsed, using the JSON Parse tool, to produce a list of message_id's that match our search parameters:

MessageList.PNG

 

 

Step3.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Using the list of ids from the previous step, an API call is made for each message_id.  Each call is a GET request from https://www.googleapis.com/gmail/v1/users/me/messages with the message_id appended.

 

The URL is made up of two parts:

GetMessages.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The JSON response has many different parts and requires extensive cleanup:

MessageDetails.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Key pieces of data that we want to parse out of the messages response are:  Date, From, Subject, Attachment name, and most importantly attachmentId.  AttachmentId will be used to identify which attachment to download in the next step.

 

 

Step4.PNG

 

 

 

 

 

 

 

 

 

 

 

The last step creates a final API call, using all of the information gathered in the previous steps, that returns the raw attachment data.

 

A GET request is made for each attachmentId from https://www.googleapis.com/gmail/v1/users/me/messages/[messageId]/attachments/[attachmentId]?fields=....

 

The URL is made up of multiple parts:

GetAttachment.png

 

Each attachment data response is a Base64 encoded string:

 

AttachmentBase64.png

 










 

Using two Blob Convert tools, it's easy to convert the response into usable data.  The Base64 encoded string is first converted into a Blob and then converted from a Blob to a UTF-8 string.  The final step is to remove unnecessary fields and output the message details and attachment data using a Macro Output tool.

 

The FINAL PRODUCT

 

FinalOutput.png

 

 





















 

To demonstrate this tool, I configured it to search for all emails from steve.wagner@alteryx.com that have an attachment with a .txt or .csv extension.   The Gmail Attachment Input tool returned four files attached to three emails.  

 

Unfinished Business

A great addition that wasn't implemented in v1.0 of this tool is an automatic parsing/transform of the attachment data.  Right now, the attachment data is returned in a single record, requiring the user to build out the workflow to transform the data into a table.  Also, I haven't been able to explore which attachment file types are supported.

 

Two Challenges

1.  Download the tool and implement automatic parsing/transform of the attachment data based on file type.  Publish your attempt to the Alteryx Gallery and link to it in the comments.

2.  Discover which file types are supported.  Post any new finds in the comments.

 

Click to download the Gmail Attachment Input tool

 

Note that GmailAttachmentInput.yxi requires Alteryx Designer v10.1+

Stephen Wagner
Product Manager, Technology Alliances

Stephen Wagner is a Technology Alliances Product Manager with a passion for enabling users to take full advantage of the Alteryx platform. Stephen’s background includes analyzing sales, marketing, and operational data for a Fortune 50 retailer, as well as data visualization development as an Analytics Consultant.

Stephen Wagner is a Technology Alliances Product Manager with a passion for enabling users to take full advantage of the Alteryx platform. Stephen’s background includes analyzing sales, marketing, and operational data for a Fortune 50 retailer, as well as data visualization development as an Analytics Consultant.

Comments
Atom

Hi Stephen,

I have been trying to obtain my Refresh Token for this Gmail connection, but when I get to Step 18 here https://community.alteryx.com/t5/Alteryx-Knowledge-Base/How-to-Create-Google-API-Credentials/ta-p/11... - it does not provide me with a refresh token but in fact gives me this:

{
 
"error_description": "Unauthorized",
 
"error": "invalid_client"
}

Please let me know how to proceed. Thank you! 

Atom

Capture.JPGPlease let me know how to proceed.

Alteryx
Alteryx

@nnauraiz

 

Follow these steps to get your Client ID, Client Secret, and Refresh Token:

 

  1. Open https://console.developers.google.com
  2. Login with the Google account associated with the data you would like to analyze
  3. Create a new project by clicking the Select a project dropdown (top-left corner) and selecting Create a project
  4. Enter a Project name of your choosing and click Create
  5. Click ENABLE API
  6. Select Gmail API from the list
  7. Click Credentials
  8. Click Create credentials and select OAuth client ID
  9. Select the Web application radio button, add https://developers.google.com/oauthplayground as an Authorized redirect URI, and click Create
  10. A window will pop-up with your Client ID and Client Secret; save these for future reference and click OK
  11. Go to https://developers.google.com/oauthplayground
  12. Click on the gear icon in the top-right corner of the page, click the checkbox for Use your own OAuth credentials, enter the Client ID and Client Secret from step 10, and click close
  13. Copy/paste https://www.googleapis.com/auth/gmail.readonly into Step 1 Select & authorize APIs window on left side of page and click Authorize APIs
  14. Click Allow
  15. Click Exchange authorization code for tokens and save the Refresh token
  16. Use the Client ID, Client Secret, and Refresh Token with the Gmail Attachment tool
Atom

Thank you Stephen but I followed all these steps and received the Client ID, Client Secret, and Refresh Token but it is still giving me the attached error.Capture.JPG

 

 

Alteryx
Alteryx

@nnauraiz

 

Follow these steps while in the https://developers.google.com/oauthplayground

 

Click the gear icon in the upper right.  Click the User your own OAuth credentials and enter your Client ID and Client Secret.

Gear.png

 

For Step 1, select https://www.googleapis.com/auth/gmail.readonly and https://www.googleapis.com/auth/userinfo.profile and click Authorize APIs

Step1.png

 

For Step 2, click Exchange authorization code for tokens and click Refresh access token

 

For Step 3, update the Request URI to https://www.googleapis.com/oauth2/v2/userinfo and click Send the request.  Copy id from the response.

 

Step3.png

 

Update the Request URI to https://www.googleapis.com/gmail/v1/users/{userId}/profile.  Replace {userId} with the id copied from the previous response.  Click Send the request.

Step3.5.png

 

Being able to successfully perform the above should result in a Client ID, Client Secret, and Refresh Token that will work with the attached Gmail tool.  If this isn't successful please post a screenshot.

Alteryx Certified Partner
Alteryx Certified Partner

 Hi @StephenW


I appear not to be able to use the installer. When I try to run it I get the following error:

 

'Tool Installer Error: Tool Installer Error: Unable to find configuration file.'

 

I've tried it with a number of users though we are all on V11.

 

Have you ran into this issue yet?

 

Ben

Alteryx
Alteryx

@BenMoss

 

Version 11 made a change to the structure of yxi's that this tool wasn't updated for.

 

Here is an updated version of the tool that you should be able to install.

Atom

gmail_error.jpg

 

Any ideas why I'm receiving the above error?

Alteryx
Alteryx

@Purdue02

 

Here is a screenshot of the section that's causing the error; tool is highlighted:

Tool114.PNG

 

Without seeing your API response, I would guess that your query isn't returning any data.  To view the API response you could use Fiddler.

 

Also, you could try replicating your query in Gmail and see if it returns anything.

Atom

Below are the results from the JSON Parse tool.  It also doesn't appear the name variable is being passed through.  Any ideas?

 

 

NameVariable_NotListed.jpg

 

 

API_Response.jpg

Alteryx
Alteryx

@Purdue02

 

The error message makes me think that your Search Parameter includes something not supported.  Either, try without the q parameter or try re-obtaining your API credentials with additional Gmail scopes added (step 1 of my ‎03-16-2017 03:45 PM comment)

Atom

Thanks, that worked.  I do have another question... do you know if it is possible to download a file attachment and save it in a specified directory?

Alteryx
Alteryx

@Purdue02

 

You should be able to take the attachment and use an Output tool to save it.

Asteroid

Hi Stephen. 

Just wanted to give you a heads up regarding a bug in the tool. Google uses a different base64 encoding than alteryx (see this link) so you need to replace the character '-' with '+' and '_' with '/' before you can base64 decode it, atleast for excel files.

Thank you though for saving me a lot of time with this otherwise excellent tool. 

 

Best regards//Leif

 

@engdell

 

I'm interested in using this tool to download and use excel (xlsx) files...how did you replace the characters you referenced?

 

Best,

Rich 

Asteroid

Hi Rich. 

First I removed all the parsing of the results from the gmail tool (blob and b64 stuff after the dl tool). 

Then I added these steps to my wf:

JSON parse output: DownloadData

Formula: ReplaceChar([JSON_ValueString],'-', '+') and then ReplaceChar([JSON_ValueString],'_', '/')

(I also do a '%temp%\'+[Attachment_Name]  for saving my file where I can pick it up later on)

After that a blobconvert on JSON_ValueString

and lastly a blob output with settings (top to bottom):

"arb-placeholder"

Replacing entire path

Attachment_Name

JSON_ValueString

 

I dl a bunch at the time so I do a block until done on dl and a dynamic input on flow two after that. 

gmail hack.JPGGmail hack

@engdell,

 

Thank you for the detailed writeup.

 

I'm pretty new to Alteryx and am not sure how to "open" the gmail tool to make the modifications you detailed. Can you either:

  1. Tell me how I can open that package? OR
  2. Send me your version of the tool? OR 
  3. Send me a sanitized sample of your workflow where you ingest the file(s), save them locally, and then process the data?
    • I'd also like to save the files on the hard drive and then do further processing within Alteryx

Best,

Rich

Asteroid

Hi Rich. 

It's just a macro but when it is "installed" it is placed in (for a user installation) in you profile-path like:

C:\Users\yourusername\AppData\Roaming\Alteryx\Tools\GmailAttachmentInput\Supporting_Macros it isn't just a right click away. But if you copy both macros to your local folder you're good to go. 

 

Best regards//L

PS. I follow this thread.

Thanks Leif, this is exactly what I was looking for. Really appreciate it!

 

Best,

Rich

Asteroid

Will this work to download the email and not the attachment?

 

I would like to download the email and import the email into Alteryx so i can get a link from it and maybe other information from the email?

Asteroid

No, but it could easily be adapted, it is well documented. 

Asteroid

I am having a bit of trouble when i add my client ID, Client secret and refresh token it comes up with the following error 

 

Error: Gmail Attachment Input (1): Tool #2: Google API: HTTP/1.1 400 Bad Request
Error: Gmail Attachment Input (1): Tool #2: Tool #14: Parse Error at char(1): Unknown variable "token_type" (Expression #1)

 

Was wondering if anyone can help me on this?

Alteryx
Alteryx

@AndrewCrayford Sorry for the delay in responding - The error is caused by the Google OAuth tool.  Your credentials were likely not setup or input correctly.

Alteryx Partner

Hi Stephen,

 

I have finished extracting emails from my gmail account by following your blog. Now, my requirement is to fetch entire body of the email and currently I am using "snippet" column as email body column and it is just showing first 200 characters of the email body. Based on my analysis, I have found that "payload_parts_body_data" field has entire body of the email and it is encoded by default.

 

Do you have any idea how to decode "payload_parts_body_data" field to extract entire body of the email in Alteryx or can you please suggest any other possible solutions to extract entire body of the email?

 

gmail body encoded.PNG

 

 

Thanks,

Saranya

Asteroid

 

@StephenW Thanks for the reply and I got it working now, thanks

 
Alteryx
Alteryx

@Saranya

 

Use the Blob Convert tool to convert the Base64 encoded boyd into a Blob and then use another Blob Convert tool to convert the Blob into a UTF-8 string.  Here is an example of the flow:

large.pngBase64 conversion example

 

 

Alteryx Partner

@StephenW

 

Thank you very much for the quick reply. I am able to decode now. But still I am not getting the whole body of the email, the values are getting truncated after parsing. Is it possible to extract whole body of the email using Alteryx?

Could you please suggest any other possible solutions to extract entire body of the email? Thanks in advance!json parse truncate character.png

 

Thanks,

Saranya

Alteryx
Alteryx

@Saranya  Use a Select tool to increase the size of the JSON_ValueString field.

Alteryx Partner

@StephenW

 

I have already tried and it doesn't help :-(

 

Meteor

@StephenW, i think the dropbox link is dead for the v11+ version

Meteor

can anyone reupload the v11+ version? 

Alteryx
Alteryx

Hi @Paulo1300

 

There is an attachment at the bottom of the post - GmailAttachmentInput.yxi

Meteor

Hi @StephenW,

 

Thanks for the response. I assumed that was the old version because I am getting the same error that this person posted. Then you provided a different alteryx file that was v11+ on dropbox

 

Capture.PNG

Meteor

@StephenW, can you confirm that is the correct file?

Alteryx
Alteryx

@Paulo1300

 

Unfortunately, I can't confirm that.

Atom

@StephenW

 

I'd love to try out this tool. Think you can provide another dropbox link? The above link is giving me a 404.

 

Thanks!

 

Alteryx
Alteryx

@lmoore 

 

Sorry for the lagged response. Unfortunately, I don't have access to the file anymore so I will have to make some updates.  I'll try and post an updated tool.

Alteryx
Alteryx

 

@lmoore @Paulo1300 

 

I've been able to update the tool. You can find it at GmailAttachmentInput.yxi.

Labels