Google sheet read with gh

Grasshopper
21

Thank you, Anders.
I only needed the variable type in which credential is parsed to read/write. :blush:
I learned that secret pass should be exchanged for the tokens, but I did not know it was possible inside Grasshopper. Your method authorizeSheetCredentials would be really helpful. I cannot wait to try this out.

Thanks again,
deskripta

1 Like
22

Note that the exact same code will also work in e.g. Revit/Dynamo since it’s just straight up IronPython. As I recall it was quite the implementation challenge getting this working, as I could find zero precedence for this kind of coding in IronPython. In retrospect, doing it in C# would likely have been simpler, but hey here we are :slight_smile:

23

I understand that pain.
That is why I am so thankful :blush:

24

thanks for sharing this. I feel I got it almost to work. I made a simple test to read from one sheet and write the same to another, but the output of data is in this form:
List[IList[object]]([List[object]([‘555’, ‘5555’]), List[object]([‘556777’, ‘444’]), List[object]([‘7777’, ‘89’])])

and this is what I also get in my second sheet

image
image1088×363 31.1 KB

what am I doing wrong?

25

Can you share your implemention? Looks like you might not be unrolling your DataTrees.

26

Hi Anders,
This seems to be hot topic these days, I did run your script and it works (read sheets), with one exception, it will not update data after i change them in spreadsheet.
i get new values in grasshopper only when i change SheetRange. But as soon as i change it back to original Range, i recieve old values. The restart of rhino usually does not help, Have you got similar issues before?

to illustrate:
Google sheet : Row1[1,2,3,4,5]
Grasshopper : Sheet2!A1:E1 -> [1,2,3,4,5]
Google sheet : Row1[1,2,3,4,XX]
Grasshopper : Sheet2!A1:E1 -> [1,2,3,4,5] - no update
Grasshopper : Sheet2!A1:X2 -> [1,2,3,4,XX] - update
Grasshopper : Sheet2!A1:E1 -> [1,2,3,4,5] - back to old values

27

@AndersDeleuran
sheet-read-write.gh (13.1 KB)

Ok I solved the datatree issue, by adding another output above “Data” (do you know why?)

but ran into another issue:

nevermind it works now :slight_smile: ( was feeding range of cells instead of first cell)

in case someone else needs it: here is a working gh file:
sheet-read-write.gh (12.9 KB)

3 Likes
28

Apologies @Gijs , I should have uploaded my implementation in the first place:

200310_DotNET_GoogleAPIs_Sheet_MWT_00.gh (15.0 KB)

4 Likes
29

@AndersDeleuran n/p the posted scripts were already very helpful

30

@Gijs @AndersDeleuran Hi any of you experince, that data does not update in Grashopper as i described over? it seem s for me that my issue is on Google side rather than in GH?

thanks

Update: it seems to update using BatchGet instead of Get

31

Sorry missed your post @Vladimir_Ondejcik. I’ve not experienced this behaviour, but would agree that it does sound like an issue on the Google side (perhaps related to how the credential/client configuration is defined :man_shrugging:).

32

@Vladimir_Ondejcik I haven’t used it yet extensively. Only did a quick test to get the basics working for planned later use but I will try and report back.

33

i have resolved this issue by calling BatchGet() instead of Get() function.

34

Ok… I am confused as hell, especially because I do not really understand the ins and outs of coding.

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

So I followed the link above, and followed the steps with visual studio code.

This is how my folder looks now:

image
image877×340 16.3 KB

I am missing dependencies folder.

Could anyone explain to me how to get this up and running as if you were explaining it to a 12 year old with no coding experience?

Thank you for your help in advance!

Regards,

Jonas Blazinskas

35

No one? @AndersDeleuran, @Gijs

36

Hi Jonas, the .dll files will be in the bin folder if I recall. I’ve attached the dependencies folder/files I used for reference.

Dependencies.zip (388.4 KB)

With my file/folder structure looking like this:

2020-05-07 08_55_16-GoogleSheets
2020-05-07 08_55_16-GoogleSheets1543×647 67.4 KB

1 Like
37

Cool I found it!

But now where do i get client_secret.json from?

image
image964×117 14.8 KB

image
image890×509 35.8 KB

Thanks for your help!

38

Yaaay I did it!!! found the secret at the very start of the process that I had to download!

39

Hey Anders,

Is there a way to make the autorise component automatically authorise when starting a gh script? As every time I close down gh script open back up, it requires me to go into authorise component click test and ok for it to work.

Regards,

Jonas Blazinskas

40

You can create an update button, just add

from datetime import datetime
updtime = datetime.now().strftime('%H:%M:%S')
if Run:
    ghenv.Component.Message = updtime
    updateComponent()

to an authorisation script and input for button.2020-05-19_22-27-43
2020-05-19_22-28-07

1 Like