Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
StephenW
Alteryx Alumni (Retired)

Have you ever wanted to bring data from a Google service into an Alteryx workflow?  You're in luck!  Use the Google API: Retrieve Access Token macro to generate the Authentication token needed to make calls against Google APIs.  Not familiar with Authentication tokens?  Read the recent blog post, REST APIs Part 2 - Authentication, for a great overview.  Don't have a specific Google API in mind?  Use the Google APIs Explorer to see what's available.

 

 

What's Needed

The Google API: Retrieve Access Token macro is configured to use a Client ID, Client Secret, and Refresh Token associated with a specific Google API and account.  The Alteryx Knowledge Base article, How to Create Google API Credentials, walks you through how to obtain these.

 

 

What Happens

The Google API: Retrieve Access Token macro takes Client ID, Client Secret, and Refresh Token fields from the macro input and POSTs them as a Payload to https://accounts.google.com/o/oauth2/token. A JSON response is received that contains access_token, token_type, and expires_in.  This response is parsed and output as an Authorization token.

 

JSON Response from API:

JSON Response from APIJSON Response from API

 

Screenshot of the Google API: Retrieve Access Token macroScreenshot of the Google API: Retrieve Access Token macro

 

 

How to Use It

The Google API: Retrieve Access Token macro is used in conjunction with an Append Field tool to add the newly acquired Authorization token to your data stream.  The Authorization token can then be used as a Header parameter within the Download tool you're using to make your API call.

 

A screenshot from a workflow containing the Google API: Retrieve Access Token macroA screenshot from a workflow containing the Google API: Retrieve Access Token macro

 

 

Google API Ideas

In addition to the Google APIs Explorer, another great resource, for exploring available API methods, is the Google OAuth 2.0 Playground.  After authorizing an API, you can list it's possible operations.  This can be helpful in understanding how to configure the Download tool.

 

Google OAuth 2.0 Playground:

Screenshot of the Google OAuth 2.0 PlaygroundScreenshot of the Google OAuth 2.0 Playground

 

 

Create Your Own

Download the attached Google API: Retrieve Access Token macro to begin accessing Google APIs.  Never installed a macro?  Reference How to Install a Macro.  Post interesting use cases you've used this macro and the Download tool for in the comments.

Stephen Wagner
Product Manager, Technology Alliances

Stephen Wagner is a Product Manager, Technology Alliances 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 Product Manager, Technology Alliances 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
MacRo
Alteryx
Alteryx

Great post -- love the workflow configuration screenshots, makes it so easy to see what's going on at a glance! Smiley Very Happy

hilton
7 - Meteor

Hi Stephen,

 

Have you developed any solutions within Alteryx that can sign OAuth signatures into API calls? I know for instance that Alteryx Server API uses OAuth signature generation for authentication, and I can use third party tools like Postman to generate the requests, but is it possible to do this within Alteryx, by calling for example an R function?

 

StephenW
Alteryx Alumni (Retired)

@hilton I haven't but I think there may be a person or two on the team who has experience with this.  I'll check around and see if they can provide any examples.

AndrewCrayford
8 - Asteroid

Hi

 

I don't know if i doing it right, as i download this workflow and put in my Client ID, Client_Secret and Refersh token into the macro input and it give me the following error message

 

Error: Message (22): Google API: HTTP/1.1 400 Bad Request

or

 

Error: Message (22): Google API: HTTP/1.1 401 Unauthorized

 

please advise

 

StephenW
Alteryx Alumni (Retired)

Hi @AndrewCrayford

 

Please review the How to Create Google API Credentials knowledge article and confirm that you're able to make an API call using Google's OAuth Playground.

WaldovL
5 - Atom

Hi Stephen

 

Brilliant work flow and works well. Very well!

 

The problem that I found often is the return of unauthorised tokens, which everyone just need to go back to their API and reset the Secret and or recreate the Project in the Developers page.

Re-paste your info into the Playground and back again to Alteryx. This works every time.

 

But I have a question please Stephen.

 

I am trying to extend this Macro to look into the NextPage Token for the GMail API. 

 

This Macro only returns 100 messages as this is the maximum per list in the GMail API.

 

The only way to allow this to complete the Inbox is to use a Token also generated for a NextPage. And the same query then needs to run again, again and again till your messages are done. And every loop needs to contain the NextPage token in order to access the rest of the Mail.

 

Has this been accomplished in Alteryx and would there be any assistance around this topic?

 

Many Thanks

Waldo

 

 

 

 

StephenW
Alteryx Alumni (Retired)

@WaldovL  I'd recommend creating an Iterative Macro that contains the GMail API call.  This will allow you to feed the NextPage token back into the macro for use on the next call.

WaldovL
5 - Atom

Thank you StephenW

 

Thank you, I will have to look into those macros. Thanks for supplying the link as well.

 

Will report back here with the results

Saranya
6 - Meteoroid

Hi Stephen,

 

I am following your post to retrieve gmail messages using Alteryx. I had successfully created(I think) Google API credentials(for Gmail API) by following the steps mentioned in How to Create Google API Credentials post. Please refer the below screenshot for the Google API credential results :

 

google api img.PNG

 

When I tried running my workflow by passing client ID, client secret and refresh token(not access token) to the Google API: Retrieve Access Token macro tool, I am getting the below error message:

google api download tool error.PNG

 

google api download tool error2.PNG

 

I tried creating new project and also, generated new client id and client secret couple of time. But still I am getting the same error. Could you please help me with some suggestions to resolve this issue?

 

Thanks,

Saranya

 

 

 

 

StephenW
Alteryx Alumni (Retired)

@Saranya

 

This is an issue with your credentials - either they were entered wrong in the workflow or setup incorrectly.  Try confirming that you're able to make an API call using Google's OAuth Playground.

 

 

Saranya
6 - Meteoroid

 

@StephenW

Thanks for the quick reply. 

 

I am able to see the expected results now in Alteryx. I just created new project and generated new client id,client secret and refresh token and also created new alteryx workflow to pass the gmail credentials. It worked!!! Thank you very much for the post.

 

Regards,

Saranya

carl_steinhilber
8 - Asteroid

Thank you for making this macro, Stephen. Really appreciate the work, and the step-by-step's as well.

 

I've create my client ID/secret and have the appropriate API's working in the OAuth2 Playground.

Now I'm creating the workflow to utilize the macro, and I'm confused as to what the "INCOMING STREAM" needs to be in the target of the Append Fields tool. I assume this is setting up the request. But does it have to be JSON? Do I just use the attribute names listed in the API reference (in my instance, I'm trying to use DCM)?

 

I've tried several variations, and haven't quite hit on anything that seems to work.

 

Nevermind. I had also entered my login information to the Download tool's Connection parameters. Not surprisingly that trumps the passed oauth header and causes a failed connection.

It's working for me now.

 

Thanks again!!

shouvikdas
6 - Meteoroid

Just wondering if it is possible to modify this macro for other platforms' OAuth2 process, such as for Amazon Seller Advertisement portal? To explain, could I change all the required parameters in this Macro to other platforms' values and expect this OAuth Macro to work? It would be nice to see a universal OAuth2 tool from Alteryx that works with most of the platforms? 

StephenW
Alteryx Alumni (Retired)

Hi @shouvikdas

 

You could definitely use this tool as a template for other platforms. If you do create something please share it in the Alteryx Gallery.

coderockride
8 - Asteroid

When you use one of the Google tools that uses this like to "Google Sheets Input Tool", is there a way to change the way the "User Login" authentication method works so that it would refresh credentials before the 60 minute timeout if you were to say run it on a scheduler?

 

2019-07-26 19_06_33-Alteryx Designer x64 - New Workflow8_.png

acowpy
8 - Asteroid

Hi mate, do you have a method to authenticate with a Google Service Account, which has a client_id but not a client_secret (instead has a JSON file containing private key)?

 

StephenW
Alteryx Alumni (Retired)

Hi @acowpy 

 

I don't have any firsthand experience with that, but I'm sure you could take this macro and re-configure it to handle your situation.

acowpy
8 - Asteroid

@StephenW Upon further review the authenticate looks quite different with a service account (eg you can't use OAth playground), it requires the creation of an RSA encrypted signature (JWT) sent via a different REST API.

https://developers.google.com/identity/protocols/oauth2/service-account

 

We are thinking of embedding the python library (https://google-auth.readthedocs.io/en/latest/reference/google.auth.jwt.html) into Alteryx via the python tool to achieve this. If we build a macro I'll post here for others to use.

 

 

acowpy
8 - Asteroid

Hi all,

 

Further to the above post, here is a link to a macro that allows upload to GCP buckets using service account JSON credentials.

https://drive.google.com/file/d/1yRse9ZZ5fqAhugRVRC8n_loGYtmaomz_/view?usp=sharing

 

It uses the 

google.oauth2.service_account and google.cloud.storage libraries