HI,
I would like to know, how can grasshopper read data from external external Excel or Google sheet files?
Once the sheet read, I can integrate pricing on that.
Thank you!
You have several strategies to implement your workflow.
Using a csv file
You could use the Import Text component of the ShapeDiver plugin to import a csv file into your definition. It is then straightforward in Grasshopper to parse the data from this file and use it to drive pricing and other data updates.
You could then either work by uploading a new csv file to update the data, or link to a csv file stored in an online location and reference its URL (see the documentation of Import components).
Using an Excel file
If you need to work with native Excel files, it is possible but at the moment a little more complicated because Excel files are not supported as inputs for the ShapeDiver plugin currently. However, if you are working in Rhino 8, you can use the Stream Import component to import a .xlsl file as a .NET Memory Stream, which can then be read and parsed in a C# script component using the NPOI Nuget Package for example. This solution will involve more development inside Grasshopper.
I am attaching the example Rhino 8 Grasshopper file below.
240216_ExcelTest.ghx (170.8 KB)
Once again here, you could either work by uploading a new Excel file to update the data, or link to an Excel file stored in an online location and reference its URL.
Reading data from a Google Sheet
The last option I would recommend looking into is to read an online Google Sheet file remotely. There are again two ways to do this:
- Using a C# script like in the previous solution and the Google Sheets API Nuget package. Note that this solution will necessitate the most development to achieve results.
- Using the Switflet plugin to place GET requests to your Google Sheet. Since Switflet takes care of a big part of the API syntax, this solution is lighter in development effort.
I currently do not have code examples for the two solutions above but we will work on adding such examples to our documentation in the near future.
Caching considerations
Note that if you are referencing a csv or Excel file by URL, or calling an external Google Sheet, updating the data in the sheet will not be sufficient to update the computation results of the ShapeDiver model, since solutions for specific parameter sets are cached on our servers. After updating the sheet, it will be necessary to clear the cache of the model in order to recompute all solutions.
This step is not necessary if the chosen solution involves uploading new Excel/csv files to the ShapeDiver model.
1 Like
@savaliyakenil5 I took the time to create a quick example using the Switflet plugin and a Google Sheet. You can place a GET request to a google sheet by referencing its ID (part of the URL) and using an API key you need to create for the Google Sheets API (you can do that from a logged in google account here). In my example, only an API key is needed because the sheet is public. If you want to access a private sheet, you will need to set up OAuth2 authentication, which requires a little more work but is also possible using Switflet (see the authentication components).
240218_Switflet_GoogleSheets_Example.gh (10.9 KB)
If you go with this method, don’t forget the abovementioned caching considerations.
4 Likes
You seem to be missing a / at the end of the first url part (after /spreadsheet).
I also suggest you hide your API key from the screenshot above and future ones, for security reasons.
Hi @mathieu1 ,
This is url, SD larry - Google Sheets
I added this part “1ntlcBdXWcJUuPq797WCyNrQSFFGoVYh5vLOPxhKDFGQ”
I mean you are missing a / here:
Thank you so much @mathieu1
Hello @mathieu1 ,
How can I extract data with individual tree branch like,
2.5
22.6
10
1.5
3.9
2.7
Thank you!
Thanks @mathieu1 this is great! Is it possible to post back to the google sheet using Swiftlet?
Hi @eugene1 It should be possible if their API has that function. Swiftlet provides all what you need to get or send data.