42 Replies Latest reply: Sep 8, 2017 1:41 AM by subeeshar ar RSS

    REST CONNECTOR  locate the Authorization token

    Karl Fredberg

      I am trying to connect to google analytics by following this guide http://help.qlik.com/Connectors/en-US/connectors/#../Subsystems/REST_connector_help/Content/1.0/Create-REST-connection/C…

      But I get stuck on step 7. Open Developer tools in Google Chrome and locate the Authorization token

      How do i locate the token?

        • Re: REST CONNECTOR  locate the Authorization token
          Hemanth Gandrothu

          Hi,

           

          Which browser you are using?

          • Re: REST CONNECTOR  locate the Authorization token
            Meelis Eesoja

            I have the exact same problem. Can anyone please answer to this question?

            • Re: REST CONNECTOR  locate the Authorization token
              Maria Halley

              After reading a lot of googles help on this it seems like you have to use OAuth 2. Here you get a token that are valid for a certain time and then expires.

              The token is generated by google on request when asked for  by the client.

              I can generate a token from the OAuth Playground but I can't get that to work using the rest connector in Qlik View.

              Is it still possible to connect to google APIs?

                • Re: REST CONNECTOR  locate the Authorization token
                  Bj�rn Wedbratt

                  Hi Maria,

                  That's correct, you need to use OAuth2 and get an access token to access google APIs. One can use OAuth 2.0 Playground as mentioned above to request an access token, together with a refresh token.

                  For a full understanding of he OAuth2 process there is great documentation at Using OAuth 2.0 to Access Google APIs  |  Google Identity Platform  |  Google Developers

                   

                  Now, there are some challenges with some of the steps in the process when using the REST Connector, although the playground will get you started.

                  One is how to deal with the refresh token. As you pointed out, the access token will expire after 3600 seconds and needs to be renewed. To request a new access token, basically a POST request needs to be sent to https://www.googleapis.com/oauth2/v4/token including:

                  • refresh_token
                  • client_id
                  • client_secret
                  • grant_type (with value = refresh_token)

                   

                  This will return a new access token with an expire time:

                  {
                    "access_token":"1/fFBGRNJru1FQd44AzqT3Zg",
                    "expires_in":3920,
                    "token_type":"Bearer",
                  }
                  

                   

                   

                  A script in QlikView using the REST Connector to perform the above could look like:

                   

                  SET vClient_id = 'your client id';
                  SET vClient_secret = 'your client secret';
                  SET vRefresh_token = 'your refresh token from OAuth playground';
                  
                  
                  IF vTokenExpires <= now() THEN // if access_token expired request a new one using the refresh_token
                  
                    LET vRequestBody ='';
                    LET vRequestBody = vRequestBody & 'grant_type=refresh_token';
                    LET vRequestBody = vRequestBody & '&client_id=' & '$(vClient_id)';
                    LET vRequestBody = vRequestBody & '&client_secret=' & '$(vClient_secret)';
                    LET vRequestBody = vRequestBody & '&refresh_token=' & '$(vRefresh_token)';
                  
                    CUSTOM CONNECT TO "Provider=QvRestConnector.exe;
                    url=https://www.googleapis.com/oauth2/v4/token;
                    timeout=30;method=POST;
                    autoDetectResponseType=0;
                    keyGenerationStrategy=0;
                    useWindowsAuthentication=false;useCertificate=No;certificateStoreLocation=CurrentUser;
                    certificateStoreName=My;
                    queryHeaders=Content-Type%2application/x-www-form-urlencoded;
                    PaginationType=None;XUserId=IWRHaYA;XPassword=dAXccDB;";
                  
                    access_token:
                    SQL SELECT 
                    "token_type",
                    "access_token",
                    "expires_in"
                    FROM JSON (wrap on) "root"
                    WITH CONNECTION (
                    BODY "$(vRequestBody)"
                    );
                  
                    LET vExpiresIn = peek('expires_in',0,'access_token');
                    LET vAccessToken = peek('access_token',0,'access_token');
                    LET vTokenExpires = timestamp(now() + $(vExpiresIn)/86400);
                  ENDIF
                  

                   

                  You know have a script the checks expires_in to see if the access_token is still valid, if not a new token is requested.


                  According to Google documentation, the access token can be used to make calls to Google API on behalf of a user or service account. To do this the access token should be included in the request as an Authorization: Bearer HTTP header.


                  To use our refreshed access token when making calls to Google API, we need to include it in the request. This can be achieved using the WITH CONNECT statement in the SELECT statement generated by the REST CONNECTOR. As we have our access token in a variable (vAccessToken) from above, we can inject this using a script similar to:


                  RestConnectorMasterTable:
                  SQL SELECT 
                    "kind" AS "kind_u0",
                    "username",
                    "totalResults",
                    "startIndex",
                    "itemsPerPage",
                    "__KEY_root",
                    (SELECT 
                    "id",
                    "kind",
                    "selfLink",
                    "name",
                    "created",
                    "updated",
                    "__KEY_items",
                    "__FK_items",
                    (SELECT 
                    "__KEY_permissions",
                    "__FK_permissions",
                    (SELECT 
                    "@Value",
                    "__FK_effective"
                    FROM "effective" FK "__FK_effective" ArrayValueAlias "@Value")
                    FROM "permissions" PK "__KEY_permissions" FK "__FK_permissions"),
                    (SELECT 
                    "type",
                    "href",
                    "__FK_childLink"
                    FROM "childLink" FK "__FK_childLink")
                    FROM "items" PK "__KEY_items" FK "__FK_items")
                  FROM JSON (wrap on) "root" PK "__KEY_root"
                  WITH CONNECTION (
                    HTTPHEADER "Authorization" "Bearer $(vAccessToken)"
                  );
                  


                  Hope the above helps on how to use the access token and refresh token when connecting to Google APIs

                    • Re: REST CONNECTOR  locate the Authorization token
                      Karl Fredberg

                      Great post Björn,

                       

                      The custom connect approach doesn't seem to be implemented in Qlik Sense. "CONNECTs other than LIB CONNECT are not available in this script mode"

                       

                      I got it working in Qlik Sense, using the OAuth2 Playground before, but I wasn't able to set the parameters in the script so I could change the period I requested so I stopped trying. I needed to change the parameters manually in the connection, so using a schedule task didn't work.

                       

                      Any ideas?

                       

                      Vänta på funktioner att bli implementerade är Qlik Senses användarens vardag.

                        • Re: REST CONNECTOR  locate the Authorization token
                          Karl Fredberg

                          Legacy mode is not an option, since I am running Qlik Sense Server

                          • Re: REST CONNECTOR  locate the Authorization token
                            Bj�rn Wedbratt

                            Hi Karl,

                            Sorry about the delay in responding. I've been playing around a bit with this in Qlik Sense as well, and I managed to alter the parameters for the connection using WITH CONNECTION, in a similar way as above. This worked even in Legacy mode (which was a surprise to me).

                            There is one big hurdle though and that is when creating Data connections; the connection must be successful or you cannot save the settings. Even when pressing "Save" the connection is checked and if it fails settings won't be saved.

                            When working with APIs you cannot hardcode parameters in the URL when setting up the connection, basically because not all parameters may be known at this stage.

                             

                            To get around this I simply created two connections using the REST Connector, one for GET (LIB: Google Analytics API) and one for POST (LIB: Google Authorization) and used an open JSON server at jsonplaceholder.typicode.com wheen creating the connections (you cannot alter the Method GET/POST using WITH CONNECTION). I then followed the same process as above, and used WITH CONNECTION to alter the LIB settings and pointed the URL to the entry-points for Google APIs. Here's a small example script for Sense:

                             

                            IF vTokenExpires <= now() THEN
                              LET vRequestBody ='';
                              LET vRequestBody = vRequestBody & 'grant_type=refresh_token';
                              LET vRequestBody = vRequestBody & '&client_id=' & '$(vClient_id)';
                              LET vRequestBody = vRequestBody & '&client_secret=' & '$(vClient_secret)';
                              LET vRequestBody = vRequestBody & '&refresh_token=' & '$(vRefresh_token)';
                            
                              LIB CONNECT TO 'Google Authorization';
                            
                              access_token:
                              SQL SELECT
                              "access_token",
                              "token_type",
                              "expires_in"
                              FROM JSON (wrap on) "root"
                                WITH CONNECTION (
                                URL "https://www.googleapis.com/oauth2/v4/token",
                                HTTPHEADER "Content-Type" "application/x-www-form-urlencoded",
                              BODY "$(vRequestBody)"
                              );
                              LET vExpiresIn = peek('expires_in',0,'access_token');
                              LET vAccessToken = peek('access_token',0,'access_token');
                              LET vTokenExpires = timestamp(now() + $(vExpiresIn)/86400);
                            
                            
                            
                            
                            ENDIF  
                            
                            

                             

                            Next step would be to dynamically render the URL to query Google Analytics, using variables. Not done here yet, so will get back to you with that part.

                          • Re: REST CONNECTOR  locate the Authorization token
                            john gu

                            Hi Bjorn,

                             

                            I just have a simple question.

                             

                            From this script, I just need to change Client ID, Client Secret and Refresh token.

                             

                            However, I am getting error message. Customer read fail.

                             

                            You may confirm that script is still working?

                             

                            thanks.

                              • Re: REST CONNECTOR  locate the Authorization token
                                john gu

                                I really have to say thanks to Karl and Bjorn,

                                 

                                I could achieve multiple things from this posting.

                                 

                                GA data by campaign, Device, Screen Resolution, location, date, Source and etc.

                                 

                                Scheduling wasn't too difficult, if you are familiar with QMC.

                                 

                                As GA provides 50K API call per day, even I could do ETL at every 30 seconds to show how web performs on the day with sales figure included.. (only from 8AM to 8PM).

                                 

                                there are few things that you need to aware when you use GA real time data of the day. that may differ from historical data table. (showed me 2~3% error)

                                 

                                if there are huge number of campaigns occur every week, you may need to talk to Marketing campaign manager to make that ETL is possible as there is limitation.

                                 

                                thanks again Karl and Bjorn.

                                 

                                John

                              • Re: REST CONNECTOR  locate the Authorization token
                                Anders Eriksson

                                Don't get the Post request to refresh token working.

                                Have client id & secret from the project and got refresh and access token from Playground.

                                Access token works for fetching Google Analytics data but expires after a while.

                                I'm trying to use the token refresh script in QlikView after correcting the obvious misspelling vRequestBodyvRequestBody, but I get an error;

                                QVX_UNEXPECTED_END_OF_DATA: HTTP protocol error 401 (Unauthorized):

                                        'Bearer' authentication schema provided by the web-service is not supported or your credentials are not valid.Try using the 'Force basic authentication' connection option if the server accepts the OAuth authentication schema.

                                        Error: Custom read failed

                                 

                                Bearer is used with Authorization and Access token when reading from Google Analytics but now we are trying to get the Access token?

                                The token is expired thus we can't use it to authenticate to get a new one can we?

                                There is XUserId and XPassword in the Connect statement, should these be altered?

                                  • Re: REST CONNECTOR  locate the Authorization token
                                    john gu

                                    Hi Anders,

                                     

                                    Did you this part as well?

                                     

                                    Bjorn and Karl explained at very detail about API connection to GA in this discussion.

                                     

                                    It took some time but I could create the connection.

                                     

                                    Ahh, Try and use a Custom OAuth endpoint instead of Google in OAuth2 Playground when setting up custom credentials. That will support a bearer token for the credentials.

                                     

                                     

                                    See if that works

                                      • Re: REST CONNECTOR  locate the Authorization token
                                        Anders Eriksson

                                        Vacation got in the way of this.

                                        You could create the connection BUT which connection was that?

                                        Fetching data from GA or refreshing the Access token?

                                         

                                        I could create the connection to GA using a token and fetch some data but the Access token timed out after a short while.

                                        My question is how to automate the refresh of the Access token used when connecting to GA?

                                          • Re: REST CONNECTOR  locate the Authorization token
                                            john gu

                                            This will give you clues.

                                             

                                            https://developer.chrome.com/extensions/tut_oauth

                                             

                                            also, Bjorn explained well in one of his reply as shown below.

                                             

                                            Hi Karl,

                                            Sorry about the delay in responding. I've been playing around a bit with this in Qlik Sense as well, and I managed to alter the parameters for the connection using WITH CONNECTION, in a similar way as above. This worked even in Legacy mode (which was a surprise to me).

                                            There is one big hurdle though and that is when creating Data connections; the connection must be successful or you cannot save the settings. Even when pressing "Save" the connection is checked and if it fails settings won't be saved.

                                            When working with APIs you cannot hardcode parameters in the URL when setting up the connection, basically because not all parameters may be known at this stage.

                                             

                                            To get around this I simply created two connections using the REST Connector, one for GET (LIB: Google Analytics API) and one for POST (LIB: Google Authorization) and used an open JSON server at jsonplaceholder.typicode.com wheen creating the connections (you cannot alter the Method GET/POST using WITH CONNECTION). I then followed the same process as above, and used WITH CONNECTION to alter the LIB settings and pointed the URL to the entry-points for Google APIs. Here's a small example script for Sense:

                                             

                                             

                                             

                                            1. IF vTokenExpires <= now() THEN 
                                            2.   LET vRequestBody =''; 
                                            3.   LET vRequestBody = vRequestBody & 'grant_type=refresh_token'; 
                                            4.   LET vRequestBody = vRequestBody & '&client_id=' & '$(vClient_id)'; 
                                            5.   LET vRequestBody = vRequestBody & '&client_secret=' & '$(vClient_secret)'; 
                                            6.   LET vRequestBody = vRequestBody & '&refresh_token=' & '$(vRefresh_token)'; 
                                            7.  
                                            8.   LIB CONNECT TO 'Google Authorization'; 
                                            9.  
                                            10.   access_token: 
                                            11.   SQL SELECT 
                                            12.   "access_token", 
                                            13.   "token_type", 
                                            14.   "expires_in" 
                                            15.   FROM JSON (wrap on) "root" 
                                            16.     WITH CONNECTION ( 
                                            17.     URL "https://www.googleapis.com/oauth2/v4/token", 
                                            18.     HTTPHEADER "Content-Type" "application/x-www-form-urlencoded", 
                                            19.   BODY "$(vRequestBody)" 
                                            20.   ); 
                                            21.   LET vExpiresIn = peek('expires_in',0,'access_token'); 
                                            22.   LET vAccessToken = peek('access_token',0,'access_token'); 
                                            23.   LET vTokenExpires = timestamp(now() + $(vExpiresIn)/86400); 
                                            24.  
                                            25.  
                                            26.  
                                            27.  
                                            28. ENDIF   

                                             

                                            Next step would be to dynamically render the URL to query Google Analytics, using variables. Not done here yet, so will get back to you with that part.

                                              • Re: REST CONNECTOR  locate the Authorization token
                                                Rohit Kumar

                                                Hi John,

                                                 

                                                Its quite obvious you have a great knowledge of scripting, I will try your above method and try to connect to GA and lets see how data comes , Yes it was most critical part to settle a connection which don't need to create auth key , I will follow your above script and lets see how data comes , will inform you

                                                  • Re: REST CONNECTOR  locate the Authorization token
                                                    john gu

                                                    Thanks.

                                                     

                                                    QV source is great application.

                                                     

                                                    there is no doubt. Only down side is you don't experience every step of API connection which is very interesting to be honest.

                                                     

                                                    not only my knowledge of scripting is good but also very importantly Karl and Bjorn explained A to Z "every thing" in this post you need follow.

                                                     

                                                    So, read from the top to bottom. I read 10+ times

                                                     

                                                    Thanks,

                                                     

                                                    John

                                      • Re: REST CONNECTOR  locate the Authorization token
                                        Mitali Ajgaonkar

                                        Hi,

                                         

                                        I am facing the same problem Auth 2.0 for linkedin. while trying to test connection i get error message HTTP protocol error 401 (Unauthorized) requested resource requires authorization.

                                        • Re: REST CONNECTOR  locate the Authorization token
                                          Rohit Kumar

                                          Hi karl,

                                          I been working on the same task and what I found that Rest connector is not a right practice with Google Analytics. You can get the data once with using all the methods above(Google API methods) but same data is not as you can design something. There will be no structure in that rec vied data .I Suggest please use QV Source to get data with proper format and you can automate it too

                                          • Re: REST CONNECTOR  locate the Authorization token
                                            subeeshar ar

                                            How can I give token instead of url in qlik rest connector