Google Spreadsheet Activities


#1

Hi all,

Recently I’ve developed a set of custom activities that would help us work easier with Google Spreadsheets.
For now, I’ve only added Read Range and Write Range along with Google Application Scope that will hold the other two.

You can find the code on my git repo here:

And also you can get directly the package from here: https://gallery.uipath.com/packages/UiPathTeam.GoogleSpreadsheet.Activities/

Please let me know if you have any ideas for improvement or you find any bugs to what we have now.


Extract Data table from Google Spreadsheet
GoogleSpreadsheet.Activities
Connector for Google spreadsheets
GoogleSpreadsheet.Activities
#2

Hi,
Could you describe in short how to fill Authentication part and what is “KeyPath”,

I am getting this error:
main has thrown an exception

Source: Google sheet application scope

Message: Could not load type ‘Initializer’ from assembly ‘Google.Apis.Auth, Version=1.13.1.0, Culture=neutral, PublicKeyToken=4b01fa6e34db77ab’.

Exception Type: TypeLoadException

System.TypeLoadException: Could not load type ‘Initializer’ from assembly ‘Google.Apis.Auth, Version=1.13.1.0, Culture=neutral, PublicKeyToken=4b01fa6e34db77ab’.
at GoogleSpreadsheet.Activities.GoogleSheetApplicationScope.Execute(NativeActivityContext context)
at System.Activities.NativeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.ActivityInstance.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

Thanks


#3

Hi there,

Thank you for your interest in using Google Spreadsheet Activities.

To cover the Authentication part in this scenario you will need a Google API Service Account.
https://developers.google.com/identity/protocols/OAuth2ServiceAccount
For an account, you will download a P12 Key. This key will have assigned a password that can be kept in plain text.

  • In the KeyPath you need to put the path to that key.
  • In the ServiceAccountEmail you need to put your Service Account address
  • In the Password you need to put the password needed for the P12 Key

However, now you are facing a different problem. Google Spreadsheet Activities are using Google.Apis.Auth, version 1.29.2.0. This comes along with the activity package so you don’t have to worry about it. But, in the UiPath Platform folder (Program Files x86) there is already another DLL with this name and with an older version (1.13.1.0). To fix this issue I deleted the one from UiPath Platform. A more elegant solution would be to not delete it but to replace it with the newer version (take it from the Google Spreadsheet Activities package).

Let me know if you manage to fix your problem.

Cheers!
Bogdan


How to read google sheet data in uipath
#4

Thanks, I followed steps got servie acount and .p12 file as well as its password, but now I get different error, something regarding Json

An error has occured

Source: Newtonsoft.Json

Message: ISerializable type ‘Google.GoogleApiException’ does not have a valid constructor. To correctly implement ISerializable a constructor that takes SerializationInfo and StreamingContext parameters should be present. Path ‘Error’, line 1, position 1431.

Exception Type: JsonSerializationException

Newtonsoft.Json.JsonSerializationException: ISerializable type ‘Google.GoogleApiException’ does not have a valid constructor. To correctly implement ISerializable a constructor that takes SerializationInfo and StreamingContext parameters should be present. Path ‘Error’, line 1, position 1431.
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateISerializable(JsonReader reader, JsonISerializableContract contract, JsonProperty member, String id)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.SetPropertyValue(JsonProperty property, JsonConverter propertyConverter, JsonContainerContract containerContract, JsonProperty containerProperty, JsonReader reader, Object target)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateObject(Object newObject, JsonReader reader, JsonObjectContract contract, JsonProperty member, String id)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.Deserialize(JsonReader reader, Type objectType, Boolean checkAdditionalContent)
at Newtonsoft.Json.JsonSerializer.DeserializeInternal(JsonReader reader, Type objectType)
at Newtonsoft.Json.JsonConvert.DeserializeObject(String value, Type type, JsonSerializerSettings settings)
at Newtonsoft.Json.JsonConvert.DeserializeObject[T](String value, JsonSerializerSettings settings)
at UiPath.Shared.JsonParser.DeserializeObject[T](String jsonValue)
at UiPath.Models.UiPathDuplexProxy.<>c__DisplayClass21_0.b__0()
at System.Threading.Tasks.Task.InnerInvoke()
at System.Threading.Tasks.Task.Execute()


#5

Hi,

Does your Google Service Account (used in the activity) have rights to access the spreadsheet you are intended to work on?

Thanks,
Bogdan


#6

Hi thanks, I got it to work. Thanks again for your help. Here’s a great guide how to do it. Because console is overwhelming at first.


#7

Hello, I am getting a CryptographicException error and I am not sure how to handle this.
Any advice?

Main has thrown an exception

Source: Google sheet application scope

Message: The system cannot find the file specified.

Exception Type: CryptographicException

System.Security.Cryptography.CryptographicException: The system cannot find the file specified.

at System.Security.Cryptography.CryptographicException.ThrowCryptographicException(Int32 hr)
at System.Security.Cryptography.X509Certificates.X509Utils._QueryCertFileType(String fileName)
at System.Security.Cryptography.X509Certificates.X509Certificate.LoadCertificateFromFile(String fileName, Object password, X509KeyStorageFlags keyStorageFlags)
at System.Security.Cryptography.X509Certificates.X509Certificate2…ctor(String fileName, String password, X509KeyStorageFlags keyStorageFlags)
at GoogleSpreadsheet.Activities.GoogleSheetApplicationScope.Execute(NativeActivityContext context) in C:\Users\bogdan.popescu\Documents\GitHub\Community.Activities\Google Spreadsheet\GoogleSpreadsheet.Activities\GoogleSheetApplicationScope.cs:line 46
at System.Activities.NativeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.ActivityInstance.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)


#8

Hi Mehul,

Did you put the p12 key file in the specified location?

Cheers!
Bogdan


#9

Hi Bogdan,

The p12 file is in my downloads folder, where can I find the specified location where the file has to be?

The error says “GoogleSpreadsheet.Activities.GoogleSheetApplicationScope.Execute(NativeActivityContext context) in C:\Users\bogdan.popescu\Documents\GitHub\Community.Activities\Google Spreadsheet\GoogleSpreadsheet.Activities\GoogleSheetApplicationScope.cs:line 46” Does this mean that it is trying to look into this file location? If so, how do I change the file location?

Thank you,
Mehul Jethva


#10

Hi Mehul,

You put the key path in the In the KeyPath argument of the activity.

Thanks,
Bogdan


#11

Hi Bogdan,

I have added the Key path in the KeyPath argument, but I am still getting the same error.


#12

Try adding the extension along with the path - since you need to specify full path to the file, extension is a part of it.


#13

Thank you for the help, I have gotten past this error and now I am getting a different error that @SynInvoice_Robot was getting.

An error has occured

Source: Newtonsoft.Json

Message: ISerializable type ‘Google.Apis.Auth.OAuth2.Responses.TokenResponseException’ does not have a valid constructor. To correctly implement ISerializable a constructor that takes SerializationInfo and StreamingContext parameters should be present. Path ‘Error’, line 1, position 1380.

Exception Type: JsonSerializationException

Newtonsoft.Json.JsonSerializationException: ISerializable type ‘Google.Apis.Auth.OAuth2.Responses.TokenResponseException’ does not have a valid constructor. To correctly implement ISerializable a constructor that takes SerializationInfo and StreamingContext parameters should be present. Path ‘Error’, line 1, position 1380.
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateISerializable(JsonReader reader, JsonISerializableContract contract, JsonProperty member, String id)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.SetPropertyValue(JsonProperty property, JsonConverter propertyConverter, JsonContainerContract containerContract, JsonProperty containerProperty, JsonReader reader, Object target)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateObject(Object newObject, JsonReader reader, JsonObjectContract contract, JsonProperty member, String id)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.Deserialize(JsonReader reader, Type objectType, Boolean checkAdditionalContent)
at Newtonsoft.Json.JsonSerializer.DeserializeInternal(JsonReader reader, Type objectType)
at Newtonsoft.Json.JsonConvert.DeserializeObject(String value, Type type, JsonSerializerSettings settings)
at Newtonsoft.Json.JsonConvert.DeserializeObject[T](String value, JsonSerializerSettings settings)
at UiPath.Shared.JsonParser.DeserializeObject[T](String jsonValue)
at UiPath.Models.UiPathDuplexProxy.<>c__DisplayClass21_0.b__0()
at System.Threading.Tasks.Task.InnerInvoke()
at System.Threading.Tasks.Task.Execute()

I have tried to resolve the issue just as @SynInvoice_Robot has said in the message but it is not working for me. UiPath is still giving me the same error.

Any adivce?

Thank you,
Mehul Jethva


#14

Hello…I am also getting the error same error as you encountered…I followed every step as you mentioned but still getting the same error. Please help…In middle of a crucial project.

The error is:
An error has occured

Source: Newtonsoft.Json

Message: ISerializable type ‘Google.GoogleApiException’ does not have a valid constructor. To correctly implement ISerializable a constructor that takes SerializationInfo and StreamingContext parameters should be present. Path ‘Error’, line 1, position 1438.

Exception Type: JsonSerializationException

Newtonsoft.Json.JsonSerializationException: ISerializable type ‘Google.GoogleApiException’ does not have a valid constructor. To correctly implement ISerializable a constructor that takes SerializationInfo and StreamingContext parameters should be present. Path ‘Error’, line 1, position 1438.
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateISerializable(JsonReader reader, JsonISerializableContract contract, JsonProperty member, String id)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.SetPropertyValue(JsonProperty property, JsonConverter propertyConverter, JsonContainerContract containerContract, JsonProperty containerProperty, JsonReader reader, Object target)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateObject(Object newObject, JsonReader reader, JsonObjectContract contract, JsonProperty member, String id)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.Deserialize(JsonReader reader, Type objectType, Boolean checkAdditionalContent)
at Newtonsoft.Json.JsonSerializer.DeserializeInternal(JsonReader reader, Type objectType)
at Newtonsoft.Json.JsonConvert.DeserializeObject(String value, Type type, JsonSerializerSettings settings)
at Newtonsoft.Json.JsonConvert.DeserializeObject[T](String value, JsonSerializerSettings settings)
at UiPath.Shared.JsonParser.DeserializeObject[T](String jsonValue)
at UiPath.Models.UiPathDuplexProxy.<>c__DisplayClass21_0.b__0()
at System.Threading.Tasks.Task.InnerInvoke()
at System.Threading.Tasks.Task.Execute()


#15

Hi Ankital,

Can you please provide log files?

Thanks,
Bogdan


#16

@Mehul_Jethva, @ankital
Just FYI and as a sidenote - the error you’re seeing is not the actual error, it’s just that Google’s exceptions are not handled well by Newtonsoft.Json (basically - they crash) and that’s the exception you’re seeing.


#17

@andrzej.kniola
How do I fix this issue so that Newtonsoft.Json does not crash?


#18

It’s a bug in Newtonsoft.Json itself, not much we can do about it.

But that a GoogleAPIException was thrown means that something else must’ve went wrong.

Could you post the log files as Bogdan asked? As the author of the activity he’s one of the best sources to see what’s happening.


#19

@andrzej.kniola
Thank you for your response.

@Bogdan_Popescu I have attached the log files below
Logs.zip (10.4 KB)


#20

I did not get any mail, thanks for thread