We are pleased to announce the release of our BIMOneGoogleAPI package for Dynamo that allows you to interact with Google Sheets via Dynamo. Not only are we making it publicly available on dynamopackages.com but also making it open source as a contribution to the Dynamo community.
What you can do with this set of Dynamo nodes:
- 📃 List all Google Sheets in a Google Drive
- ➕ Create a new Google Sheet
- 📖 Read from Google Sheets
- ✍ Write to Google Sheets (including a nifty “batch append” node).
- 🧹 Clear values within a given range
- And more!
Potential use cases
- Sync property values of Revit elements back-and-forth with the cloud.
- Make Revit data accessible in the cloud.
- Export Revit project data for machine learning.
Because of the way authentication works with the Google Sheets API, you will need to create your own credentials and place them in the correct location. You can find detailed instructions on how to do this under the “Installation & Config” section of our Github repository. Once it’s done, it’s done for good and you will not have to do it again.
Here’s a simple example of how to get a specific sheet from your Google Drive and append some data to the first and second sheets:
Result as seen on Sheet2:
Get a list of all the Google Sheets that you can see in your Google Drive. Optionally, pass a string to the filter parameter to narrow down the list of results.
Get a list of the sheets (aka those "tabs" at the bottom) within a Google Sheet.
Read a specific range within a sheet within a spreadsheet. Optionally return raw values using unformattedValues
Read multiple ranges within a spreadsheet. The format of each range in the input list must be SheetTitle!A:Z. Optionally, return raw values using unformattedValues
Create a new Google Sheet (name must not already exist in your Drive) and optionally open it in the browser using openInBrowser boolean
Create a new sheet (or tab) within a spreadsheet.
This is the preferred way of writing data. It will optimize the number of requests needed to write the data to the Sheets. It will append the data at the end of each sheet passed in. Take a list of sheets (tabs) and matching data input (a list of lists of lists containing the data to append to the table in Google Sheets. Outer list corresponds to sheets, first inner to rows and innermost to columns within the rows).
Similar to the batch append but can specify a range. It’s simpler to use, however, it is not optimized for large chunks of data (will create many requests) .
Will write data to sheet starting at the specified range. Warning: it will overwrite data. To be safe, start by using BatchAppend instead .
Use with caution. Clears the values within the given search range. Optionally, provide a search string. If the search string is present and a match is found (case sensitive but partial matches are valid) then the entire row where that cell was found will be deleted.
Deletes a sheet/tab within a spreadsheet based on the id passed in.
Deletes a sheet/tab within a spreadsheet based on the title passed in.
We look forward to getting your feedback and hearing about how these nodes are being used!