Runmyprocess integration with Google Spreadsheet using OAuth 2.0

[c...@bcsglobal.com]

Hey everyone,


Could anyone help me with the integration between RMP and Google Spreadsheets?
The idea is：I have a private excel file in my drive which contents a table of records about employees, their vacation token days and available days.

I need to make a connection from the RMP, so each of end users could see there available vacation days for themselves.
Also, another web interface is used for each end user to apply a vacation.

Basically, the RMP read Google spreadsheet and post data on web page, and user enter data to save into that spreadsheet.



For now, I have followed the user's guide to get client ID & secret
http://docs.runmyprocess.com/Integration_Guide/OAuth2/Google
http://docs.runmyprocess.com/Integration_Guide/OAuth2/Dropbox
However, I am not sure about the RMP configuration for Google. Could anyone point out the mistakes? (The screenshots show attached)

I do have two web interface as redirector and callback, but the URL shows me error "400 invalid_request missing required parameter:scope".
I have response_typr, client_id, state, redirect_uri in that URL.

Could anyone help me?
Thanks very much.

[Bidisha Das]

Hi,

You need to first generate a code for Google spreadsheet using the below script in a button in WI.

var my_wi_url = "your redirect_url";

var my_client_id = "your client_id";

var my_scope = "https://spreadsheets.google.com/feeds/";

var my_url = "https://accounts.google.com/o/oauth2/auth?redirect_uri=" + encodeURIComponent(my_wi_url) + "&response_type=code&client_id=" + encodeURIComponent(my_client_id)+ "&scope=" + encodeURIComponent(my_scope) + "&approval_prompt=force&access_type=offline";

window.open(my_url, "mywindow");

On button click, the script will get executed and the code will be generated in the url.You can set the code in a variable in WI so that you can use it further in Process.

After generating the code,you need to exchange code for access token. In order to do that:

• You need to create a Provider with URL: https://accounts.google.com/ and Authentication scheme "None".

•  Under this provider,create a connector with URL: o/oauth2/token and Content : code=${code?url}&redirect_uri=${redirect_uri?url}&client_id=${client_id?url}&scope=${scope?url}&client_secret=${client_secret?url}&grant_type=authorization_code.

           Result format:JSON and Content type:application/x-www-form-urlencoded

      

Using this,you will generate refresh_token & access_token which is to be used for your further activities on Spreadsheet from RMP.

Now,you need to build provider using this access_token for other operations on spreadsheet like you mentioned: Retrieve data from Spreadsheet to RMP and Vice-versa.

In order to create provider, you need to use : URL: https://spreadsheets.google.com/ with Authentication Scheme: Custom and Scheme name: Bearer & Credentials:${access_token}

Under this provider, you need to create connectors according to your requirement.The apis(Connector URLs) for different operations would be available in respective Google Spreadsheet site.

Foe ex: If you want to Get All the Rows of a Spreadsheet into RMP, the Connector URL would look like :

feeds/list/${spreadsheet_id}/${worksheet_id}/private/full.

In the similar way, you can create as many Connectors under the above mentioned Provider(https://spreadsheets.google.com/) according to your requirement.

Hope this helps.

Thanks & Regards

Bidisha

       

[c...@bcsglobal.com]

Hey Bidisha,
Thank you so much for reply.
I got the records after following your steps.

I am trying to generate access_token by refresh_token every time end user access to the spreadsheet.
So one more thing I want to know about
When will the refresh_token expire?

Thanks again for your help.

Chan

[Bidisha Das]

Hi,

In order to generate access_token using refresh_token,you can use Provider URL:https://accounts.google.com/ & Connector URL: o/oauth2/token with Content: client_secret=${client_secret}&grant_type=refresh_token&refresh_token=${refresh_token}&client_id=${client_id}

Result format:JSON &  Content type: application/x-www-form-urlencoded.

Refresh_token is permanent.It never expires though the access_token does within 1 hour.

Thanks & Regards

Bidisha

[c...@bcsglobal.com]

Hey Bidisha,

Thank you so much.
I got them all!

I am very appreciated for all your help!

Thanks and regards

Chan