topic RE: Skill Google Sheets in Product Forum

Skill Google Sheets

Jose_Mariade_Fe — Mon, 06 Apr 2020 09:53:00 GMT

Hello colleagues,

Has anyone had the same?

I'm having trouble with google sheets skill.
I created my API in GCP and performed the steps to add credentials (Client ID / Client Secret) and I am getting this error.

Thank you.

------------------------------
Jose Maria de Federico
Analista
Atos
Europe/Madrid
------------------------------

RE: Skill Google Sheets

ewilson — Thu, 09 Apr 2020 16:08:00 GMT

Hi Jose,

So the account you created in Google is an OAuth2 client account for a desktop app, and you received a Client ID and Client Secret, correct? Did you add those to environment variables as expected by the Get API Tokens action on the Google Sheets VBO?

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

RE: Skill Google Sheets

ewilson — Thu, 09 Apr 2020 16:16:00 GMT

I tried a slightly different quick start guide. In this case I used the guide for .NET which makes more sense to me than Go since the Google Sheets VBO is using the .NET libraries. Here's the link to the guide:

https://developers.google.com/sheets/api/quickstart/dotnet

When I use the Enable Google Sheets API button in this guide, it creates a Service account client type ID for me.

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

RE: Skill Google Sheets

Jose_Mariade_Fe — Thu, 09 Apr 2020 16:35:00 GMT

Yes, add it to the environment variables as the Documentation says.

Thank you very much for answering.
I will also try your answer thank you very much for the help

Environment variable:
Google Sheets API Client ID (With the documentation data)
Google Sheets API Client Secret (As in the Documentation)

In the end I solved it by creating a credential, adding the following:

Credential Name => Google OAuth2 JWT Credential
Issuer => Service account email
Private Key => Key that is generated within the Json of the Api (----- BEGIN PRIVATE KEY ----- \ n .................... ..... \ n ----- END PRIVATE KEY ----- \ n)

Additional Properties:
Name => Api Key
Value => Key that is generated within the Json of the Api.

And finally to be able to read the Google sheet you need to share it by adding SHARE within the sheet and add the "SERVICE ACCOUNT THAT THE API GENERATED"

------------------------------
Jose Maria de Federico
Analista
Atos
Europe/Madrid
------------------------------

RE: Skill Google Sheets

MateuszBartczak — Fri, 18 Sep 2020 15:41:00 GMT

Hey Guys,

sorry for renewing the topic, but for some reason I'm not able to create new thread today, and my problem is quite urgent...
After configuring Google Sheets OAuth setup I'm not able to Authenticate myself, although I filled up the credentials details correctly (OAuth2.0 JWT) with Issuer and Private Key generated. I had to publish Google Sheet, which eventually I will need to restrict again. The error message from BP Skill (Google Sheets v4) is constantly saying 'PERMISSION_DENIED' while trying to reach restricted sheets. Only after publishing them I'm able to get and clear data. And here is my second problem: I'm not able to update/append nothing to any Google Sheet (even when published + editor mode enabled), because I'm still receiving such message:
Internal : Unexpected error Error during Web API HTTP Request
HTTP Status Code: 400
HTTP Response Content: {
"error": {
"code": 400,
"message": "'valueInputOption' is required but not specified",
"status": "INVALID_ARGUMENT"
}
}

All the setup inside is definitely correct, as for testing purposes I copied solution for that from Google Sheets API Tutorial. Do you have any ideas hot to pass 'valueInputOption' with Google Skill correctly? I have already tried to add it to URL, add as a header, add to a JSON template etc...

Thank you in advance.

Mateusz

------------------------------
Mateusz Bartczak
------------------------------

RE: Skill Google Sheets

ewilson — Fri, 18 Sep 2020 16:39:00 GMT

Hi @MateuszBartczak,

Just to be clear, you are using the Google Sheets VBO that ships with Blue Prism, correct? Can you tell me what version of BP you're on?

If you open the VBO in Object Studio you should see, within the code stage on the "Append Data" action, that ValueIputOption is being set based on whatever you set the input data item "AsUserEntered" to. Are you setting that flag to True or False?

Ultimately, this value is added to the query string parameters of the HTTP request, so it looks something like this:

https://sheets.googleapis.com/v4/spreadsheets/13123/values/A1%3AB1:append?includeValuesInResponse=true&valueInputOption=RAW

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

RE: Skill Google Sheets

MateuszBartczak — Mon, 21 Sep 2020 06:47:00 GMT

Hi @ewilson,

Thank you for your reply.

Actually I'm using Google Sheets Skill provided on Blue Prism DX portal, which is using Web Services definition. Where I can find Google Sheets VBO? It looks like from your screenshot that this is going to be easier solution, probably also in field of user authentication...
I'm using Blue Prism 6.6

Thank you so much!

Btw. I have already managed to append/update values to Google Sheets by modifying API Skill. However,I still cannot authenticate although everything is enabled. So if you can pass me the link to Google VBO I will highly appreciate that (I can't find it in BP neither in internet)!
------------------------------
Mateusz Bartczak
------------------------------

RE: Skill Google Sheets

MateuszBartczak — Mon, 21 Sep 2020 09:06:00 GMT

Hi @ewilson,

Update: I manage to find a way to get vbo on my own - it was initially not installed by my company and not imported to the client, so I wasn't able to see this. Thank you very much for indicating that there is something like this!

All best,

------------------------------
Mateusz Bartczak
------------------------------

RE: Skill Google Sheets

ErjolaMema — Wed, 02 Nov 2022 08:50:00 GMT

Hi @ewilson,

I can't find Get API Tokens action on the Google Sheets VBO.

Can you help me where can I find it?

------------------------------
Erjola Mema
------------------------------

RE: Skill Google Sheets

ewilson — Wed, 02 Nov 2022 10:48:00 GMT

Hello @ErjolaMema,

So you have no action on your Google Sheets VBO named Get API Tokens? Are you sure you're using the Google Sheets VBO and not the Google Sheets API connector?

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Skill Google Sheets

ErjolaMema — Wed, 02 Nov 2022 13:35:00 GMT

Hi @ewilson,

Yes It was ,but I am not get what problem does the "Google Sheet API Client Secret" environment variable have

------------------------------
Erjola Mema
------------------------------

RE: Skill Google Sheets

ewilson — Wed, 02 Nov 2022 14:36:00 GMT

@ErjolaMema,

So you have two environment variables defined like this under System -> Objects -> Environment Variables, correct?

And the Google Sheets API Client Secret data item, on the action, is defined like this?

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Skill Google Sheets

ErjolaMema — Wed, 02 Nov 2022 14:42:00 GMT

Yes @ewilson

------------------------------
Erjola Mema
------------------------------

RE: Skill Google Sheets

ewilson — Wed, 02 Nov 2022 14:58:00 GMT

@ErjolaMema,

That is very strange. Is this what your decision stage looks like?

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Skill Google Sheets

ErjolaMema — Wed, 02 Nov 2022 15:32:00 GMT

Yes @ewilson,

It is exactly just like your printscreen.

I am not getting what problem it may have .

------------------------------
Erjola Mema
------------------------------

RE: Skill Google Sheets

ewilson — Wed, 02 Nov 2022 16:09:00 GMT

@ErjolaMema,

There is no need to create a credential within Blue Prism. That's what the Client ID and Client Secret environment variables are for. I've just run some tests with the VBO, but I'm not seeing the issue you're seeing. What version of Blue Prism are you using?

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------