Google sheet read with gh

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:

  1. Go through the C# tutorial I linked to.
  2. Copy the resulting .dll files to a folder that can be read by GHPython.
  3. Implement/extend the C# code in IronPython/GHPython.
  4. 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 :confused:

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… :wink:

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 gUtilStore

Also, 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 = []
3 Likes