Hi All,
Flux.io is down,
leafcutter is down,
TT Toolobx newest version delete Google Sheets Components
I would like read a google sheet with gh…
Same a video
Thanks for all
I’d recommend going through the new Google Sheet .NET API (using C# or the GHPython component). It’s pretty straightforward, but of course requires a bit of coding (not much though). Posted a few examples here:
4 Likes
It’s possible to you share this component ?
I’m not actually sure (we do not have an explicit IP agreement regarding code at my job). Will check and get back to you. This certainly is quite generic stuff and might be useful for a lot of Grasshopper/Dynamo users I’d imagine. All that said, it really was/is quite straightforward to get going. What I did:
- Go through the C# tutorial I linked to.
- Copy the resulting .dll files to a folder that can be read by GHPython.
- Implement/extend the C# code in IronPython/GHPython.
- Have the funz.
Implementing it directly in C# (as a scripting or a compiled component) is likely even easier.
1 Like
I would like to integrate it directly into a C# component but I’m not very comfortable with that.
Is there a reason why Google Sheet integration is not being supported by these plugins?
2 Likes
Bumping this back - A live link from Grasshopper to Google Sheets would be very useful!
capitalism
ISN’T IT WONDERFUL 
They API they implemented has been discontinued (I think), one would have to implement the new one (i.e. the one I fiddled with above) and develop a new version based on this instead. This may or may not be straightforward. But as I said above, the new API is pretty neat (but does of course require coding to implement).
McNeel are Microsoft shills? They push Visual Studio, use mainly C# (.Net), and the best or most finished Rhino is the Windows version! There probably is only a Mac version, because there is an armada of architecture and design students out there that are too hip to use anything, but Apple MacBook Pros. Also, there is no Linux Rhino. Just saying… 
Hello, could anyone help me with this?
I tried the steps mentioned above, but with no luck…
I created the .dll files using the quickstart script for C# in Visual Studio and copied them to the Grasshopper libraries folder and added it to Ironpython path, tried the following script in GHPython:
import rhinoscriptsyntax as rs
import clr
from os import path
api_dir = "C:\Users\helder.zuchinalli\AppData\Roaming\Grasshopper\Libraries\Google Sheets API"
clr.AddReference("Grasshopper")
import Grasshopper
clr.AddReferenceToFileAndPath(path.join(api_dir, "Google Sheets API.dll"))
clr.AddReferenceToFileAndPath(path.join(api_dir, "Google.Apis.Auth.dll"))
clr.AddReferenceToFileAndPath(path.join(api_dir, "Google.Apis.Auth.PlatformServices.dll"))
clr.AddReferenceToFileAndPath(path.join(api_dir, "Google.Apis.Core.dll"))
clr.AddReferenceToFileAndPath(path.join(api_dir, "Google.Apis.dll"))
clr.AddReferenceToFileAndPath(path.join(api_dir, "Google.Apis.Sheets.v4.dll"))
clr.AddReferenceToFileAndPath(path.join(api_dir, "Newtonsoft.Json.dll"))
import pickle
import os.path
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
Apparently loading the dlls go ok, but in the import function I get:
Runtime error (ImportException): No module named googleapiclient.discovery
Traceback:
line 30, in script
Can anyone help me? Thanks
@helder.zuchinalli it seems that you are mixing the Google Sheets Python API with the previous example, which uses clr to import Google Sheets .NET API libraries to IronPython.
As @AndersDeleuran mentions above, you should probably be looking at the .NET API instead of the python client: https://developers.google.com/sheets/api/quickstart/dotnet
1 Like
Hello, David, thanks for you answer!
I am not quite sure I understood what you mean, I’ve gone through both quick start tutorials, Python and .NET, and I can work with the API outside Grasshopper.
Maybe I mixed up the 3rd step in Anders comment?
What I want to achieve is being able to use the API from grasshopper using Python code, I am not comfortable with .NET, and without installing packages for IronPython, as the code might be used by other people in my company, so if I could just tell them to copy a folder with some dlls to the grasshopper libraries directory that would be simpler…
Is there a way to achieve this?
I went through the .NET tutorial and compiled the code to a folder on my computer, copied the resulting DLLs to the GH>libraries folder and tried calling them in python, did not work as shown in my previous comment… Then I tried copying the DLLs from the nuget installed package, now I am able to import them in Grasshopper, but the namespaces are different from the ones in the Python quickstart tutorial, I am guessing they will be the same as in the .NET tutorial, but I don’t know how to implement them in a Python script.
Any help is much appreciated, thank you!
But Speckle is up!
A hacky workaround exists for Google Sheets and there is also a client for Excel.
1 Like
I think so, though it’s been a while. Based on the screenshots I posted last year, the namespaces that I imported then in GHPython are different from the ones you’re importing.
Edit: What David said, looks like you’re mixing up the .NET API and the Python API. If you’re in Rhino (i.e. C#/IronPython) implement the .NET API, and if you’re in standard Python (i.e. CPython) implement the Python API. Afraid I don’t have my .NET tests handy, but did find this screenshot that demonstrates how to write and read to/from a sheet (as you can see, I generate a credential token in a separate component and pass this to the write/read components):
2 Likes
Hi all,
I was having similar issues myself, so I wrote a plugin to connect grasshopper with airtable.
if you’re interested in checking out the source code, it’s currently here:
2 Likes
Hi Anders,
This is something really interesting and gives new ideas.
Can you tell me how to generate the credential token?
I suppose you use the very same created along with the dlls. I tried several approaches, and each time I got a message that I am passing a string instead of IConfigurableHttpClientInitilizer.
Thank you for posting this already.
Best regards,
deskripta
It’s been a while, and I’m not actually sure that one needs ALL these dependencies. There’s probably also a more elegant method for adding them. But, just found my old test files (which all still worked) and here’s how I made the token:
"""
Ported to IronPython by Anders Holden Deleuran from this C# tutorial:
https://developers.google.com/sheets/api/quickstart/dotnet
"""
import os
import clr
import System
# Define paths
credentialsPath = r"C:\GoogleSheets"
dependenciesPath = r"C:\GoogleSheets\Dependencies"
dep00 = os.path.join(dependenciesPath,"Google.Apis.Auth.dll")
dep01 = os.path.join(dependenciesPath,"Google.Apis.Auth.PlatformServices.dll")
dep02 = os.path.join(dependenciesPath,"Google.Apis.Core.dll")
dep03 = os.path.join(dependenciesPath,"Google.Apis.dll")
dep04 = os.path.join(dependenciesPath,"Google.Apis.PlatformServices.dll")
dep05 = os.path.join(dependenciesPath,"Google.Apis.Sheets.v4.dll")
dep06 = os.path.join(dependenciesPath,"Newtonsoft.Json.dll")
# Add references and import namespaces
clr.AddReferenceToFileAndPath(dep00)
clr.AddReferenceToFileAndPath(dep01)
clr.AddReferenceToFileAndPath(dep02)
clr.AddReferenceToFileAndPath(dep03)
clr.AddReferenceToFileAndPath(dep04)
clr.AddReferenceToFileAndPath(dep06)
import Google.Apis.Auth.OAuth2 as gOauth2
import Google.Apis.Sheets.v4 as gSheets
import Google.Apis.Util.Store as gUtilStore
def authoriseSheetsClient(clientPath,clientSecretFile,localUserName):
# Define client scope (ie. read only, Sheets or Drive etc.) and path
scopes = System.Array[str]([gSheets.SheetsService.Scope.Spreadsheets])
clientSecretPath = System.IO.Path.Combine(clientPath,clientSecretFile)
# Authorise and save client credential token to clientPath
with System.IO.FileStream(clientSecretPath, System.IO.FileMode.Open,
System.IO.FileAccess.Read) as stream:
credential = gOauth2.GoogleWebAuthorizationBroker.AuthorizeAsync(
gOauth2.GoogleClientSecrets.Load(stream).Secrets,
scopes,
localUserName,
System.Threading.CancellationToken.None,
gUtilStore.FileDataStore(clientPath,True)).Result
return credential
# Authorise
localUserName = System.Environment.UserName
credential = authoriseSheetsClient(credentialsPath,"client_secret.json",localUserName)
# Output to GH
if credential:
Credential = credential
LocalUser = credential.UserId
Edit: I think you only need to add a reference to Google.Apis.Auth.dll like so, but there’s probably something iffy with the order in which one adds/imports here:
# Define paths
credentialsPath = r"C:\GoogleSheets"
dependenciesPath = r"C:\GoogleSheets\Dependencies"
authDllPath = os.path.join(dependenciesPath,"Google.Apis.Auth.dll")
# Add references and import namespaces
clr.AddReferenceToFileAndPath(authDllPath)
import Google.Apis.Auth.OAuth2 as gOauth2
import Google.Apis.Sheets.v4 as gSheets
import Google.Apis.Util.Store as gUtilStoreAlso, here’s how I read and write stuff:
"""
Read a Google Sheet.
Inputs:
Credential: {item,Google.Apis.Auth.OAuth2.UserCredential}
SpreadsheetID: e.g. "1qHgTZY3hnVN_ilBD5TCZhVC3lKL81vYQPvgEeSoDEPw" {item,string}
SheetRange: e.g. "Foo!A3:C8" {item,string}
Outputs:
Data: Read data {tree,system.object}
Remarks:
Author: Anders Holden Deleuran
Rhino: 6.24.20055.18581
Version: 200310
"""
import sys
import clr
import System
import Grasshopper as gh
# Define paths
dependenciesPath = r"C:\GoogleSheets\Dependencies"
# Add Google dependencies folder to path
if dependenciesPath not in sys.path:
sys.path.append(dependenciesPath)
# Reference and import Google assemblies/namespaces
clr.AddReferenceToFile("Google.Apis.Sheets.V4")
import Google.Apis.Sheets.v4 as gSheets
clr.AddReferenceToFile("Google.Apis.dll")
import Google.Apis.Services as gServices
def listToTree(nestedList):
""" Convert a nested python iterable to a datatree """
dt = gh.DataTree[object]()
for i,l in enumerate(nestedList):
dt.AddRange(l,gh.Kernel.Data.GH_Path(i))
return dt
def makeSheetsService(credential):
""" Initialise a Google sheets service using a credential token """
initializer = gServices.BaseClientService.Initializer()
initializer.HttpClientInitializer = credential
service = gSheets.SheetsService(initializer)
return service
if Credential and SpreadsheetID and SheetRange:
# Make service and get the sheet data
service = makeSheetsService(Credential)
request = service.Spreadsheets.Values.Get(SpreadsheetID,SheetRange)
response = request.Execute()
# Extract values
values = response.Values
if values:
Data = listToTree(values)
else:
Data = []
And how to write to one:
"""
Write to a Google Sheet.
Inputs:
Credential: {item,Google.Apis.Auth.OAuth2.UserCredential}
SpreadsheetID: e.g. "1qHgTZY3hnVN_ilBD5TCZhVC3lKL81vYQPvgEeSoDEPw" {item,string}
SheetCell: e.g. "Foo!A3" {item,string}
Data: {tree,system.object}
Outputs:
Report:
Remarks:
Author: Anders Holden Deleuran
Rhino: 6.24.20055.18581
Version: 200310
"""
import sys
import clr
import System
# Define paths
dependenciesPath = r"C:\GoogleSheets\Dependencies"
# Add Google dependencies folder to path
if dependenciesPath not in sys.path:
sys.path.append(dependenciesPath)
# Reference and import Google assemblies/namespaces
clr.AddReferenceToFile("Google.Apis.Sheets.V4")
import Google.Apis.Sheets.v4 as gSheets
clr.AddReferenceToFile("Google.Apis.dll")
import Google.Apis.Services as gServices
def makeSheetsService(credential):
""" Initialise a Google sheets service using a credential token """
initializer = gServices.BaseClientService.Initializer()
initializer.HttpClientInitializer = credential
service = gSheets.SheetsService(initializer)
return service
def makeReport(response):
""" Get and output a response report as a string """
r = ""
r += "Range: " + str(response.UpdatedRange) + "\n"
r += "Cells: " + str(response.UpdatedCells) + "\n"
r += "Columns: " + str(response.UpdatedColumns) + "\n"
r += "Rows: " + str(response.UpdatedRows) + "\n"
return r
# Check input params
if Credential and SpreadsheetID and SheetCell and Data:
service = makeSheetsService(Credential)
# Unpack DataTree and convert it to ValueRange
dataList = [list(b) for b in Data.Branches]
valueRange = gSheets.Data.ValueRange()
valueRange.MajorDimension = "columns"
valueRange.Values = dataList
# Define the update call and execute it
update = service.Spreadsheets.Values.Update(valueRange, SpreadsheetID, SheetCell)
update.ValueInputOption = gSheets.SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW
response = update.Execute()
# Make and output report
Report = makeReport(response)
else:
Report = []
4 Likes