Category: Technical
Posted on December 9, 2019 by Vincent Cadoret

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.

Setup

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.

Sample usage

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:

You can find this example and other in the GoogleSheetAPITest_v2.dyn Dynamo file in the Github repository.

Included nodes

  • GetGoogleSheetsInGoogleDrive

    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.

  • GetSheetsInGoogleSheet

    Get a list of the sheets (aka those "tabs" at the bottom) within a Google Sheet.

  • ReadGoogleSheet

    Read a specific range within a sheet within a spreadsheet. Optionally return raw values using unformattedValues

  • ReadGoogleSheetMultipleRanges

    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

  • CreateNewGoogleSheet

    Create a new Google Sheet (name must not already exist in your Drive) and optionally open it in the browser using openInBrowser boolean

  • CreateNewSheetWithinGoogleSheet

    Create a new sheet (or tab) within a spreadsheet.

  • BatchAppendDataToGoogleSheet

    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).

  • AppendDataToGoogleSheet

    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) .

  • WriteDataToGoogleSheet

    Will write data to sheet starting at the specified range. Warning: it will overwrite data. To be safe, start by using BatchAppend instead .

  • ClearValuesInRangeGoogleSheet

    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.

  • DeleteSheetByIdWithinGoogleSheet

    Deletes a sheet/tab within a spreadsheet based on the id passed in.

  • DeleteSheetByTitleWithinGoogleSheet

    Deletes a sheet/tab within a spreadsheet based on the title passed in.

Moving forward

If you have a feature request or have found a bug, please use the Github issues page to submit it. You are also welcome to submit a pull request to have your code integrated into the package.

We look forward to getting your feedback and hearing about how these nodes are being used!


Vincent Cadoret
BIM Specialist, BIM One

By clicking Subscribe, I agree that I have read and accepted the Privacy Policy.