Issues with Google Sheets

[GustavoKlein]

Hello guys, how are you?

We are having some difficulties while getting and editing content related to google sheets.

We are using API calls (Google Sheets API V4), however, the data return is not satisfatory to we manage information via collection with blueprism.

For those who are using google sheets, how are you managing (reading, editing) the data after having the API return?

Which is the best format to use the data after api return? We need parse, convert array? What?

Thank you very much!

------------------------------
Gustavo Klein
System Analyst
UTC
------------------------------

[ewilson]

Hello @GustavoKlein,

I assume you're using the Google Sheets API connector from the DX, or have you written your own connector for that API? The existing connector returns the raw JSON responses from Google. You should be able to use the Utility - JSON​ VBO to convert that raw JSON to Blue Prism Collections. Have you tried that?

Also, have you looked at the Google Sheets VBO?

Cheers,

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

[GustavoKlein]

Hello @ewilson

First of all, thanks for the attention

Yes, we are using the Google Sheets API from DX and Utility - JSON too

For example, what we are doing is the call in api, get content as return and pass it into action "JSON to Collection", but, we are facing difficulties for get the structured data, in this format we get the collection with just one column and all content/columns of google sheets in line by line

I will try to attach some steps of what we tried

About Google Sheets VBO, we face another connection issue, i will share here too, in resume, when we call some actions, the google chrome open in the window for we select desired account, if we select, open next window for allow actions and after it, a message appear "Received verification code. You may now close this window." and nothing happens

If you know about it, it will be great too

Again, much thanks till now!!

​

------------------------------
Gustavo Klein
System Analyst
UTC
------------------------------

[ewilson]

@GustavoKlein,

I'll have to do some tests to check the outcome, but based on the endpoint you're calling the JSON response seems like it would be a single column of values.. It's really an array of values according to the Google docs.

On the authentication stuff, I'd suggest using a service account if you can. Service accounts don't require user consent, especially if you set up domain-wide approval.

Cheers,
​

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

[ewilson]

Hi @GustavoKlein,

Apologies for the delay, but I finally found some time to look over this. Based on the layout of the JSON Google returns, the output Collection from the JSON VBO is correct. Within the values column​ it's laying out each row of the spreadsheet for you. The first row corresponds to the header row (assuming you have one) and then each row after that.

If you want a different format to the data (ex. a collection that lays out the columns specifically based on the just the values data of the returned JSON) I'd suggest creating a copy of the sheets.spreadsheets.values.get action and then change the response handling to Custom Code. With that, you could write your own C# code to parse the response JSON directly into a Collection of your own design.

Does that make sense?

Cheers,

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

[GustavoKlein]

Hello @ewilson

Hello @Eric Wilson

Again, very thanks for the support and for the tests

Yes, it make sense!

We still trying to configure Google Sheets VBO, i think its a better way than the conversion with custom C#.

About google sheets VBO, we are using a service account, but when we do the call with the private key, the screen with consent opens and show our our machine user as the example in attachment, we think it is some config, but till the moment we dont find which

------------------------------
Gustavo Klein
System Analyst
UTC
------------------------------

[ewilson]

@GustavoKlein,

Ah, that's interesting. The response in your screenshot is indicative of the Google toolkit attempting to authenticate via the user consent OAuth2 flow​. Does the service account you're using have domain-wide delegation enabled?

Cheers,

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