Google Scripts Library: Smartsheets + Google Sheets

For work, our project management office uses Smartsheets for a whole bunch of things.

So at work… we use Smartsheets.

It is basically the same as Google sheets, but with some different functionality (drop down for red/yellow/green harvey balls) and also it makes it really easy to do a “report” which is basically a recap of a single sheet with only certain fields/criteria.

BUT … smartsheets is sort of a monster. The front-end is really clunky once you get to several cells and there is no easy way to do reporting for things like “SumIF”… making it really hard to produce totals.

It’s an OK program and they are little bit money grabby.

Furthermore, we do most of our reporting out of Tableau (or we are supposed to anyway). Smartsheets charges a flat fee of $5k for their “live data connector”… but you can export the Smartsheet (via API) to Google sheets and then connect to Tableau for free(!).

So I made a Google Script Library

Initially, I just wrote code in different Google Sheet workbooks, but now we are doing all kinds of stuff with Smartsheet + Google Sheet integrations. The main business reasons are:

  • Extract data to be used with Tableau
  • Extract data, summarize and then push back into a summary sheet
  • Push spreadsheets of data to enable mass updates (e.g. costs for each project)

Anyway – the library is repubbed (and sanitized) from my personal Gmail with this project key: MynHXYOEs2S7TQNb4SaGM-nLXATMrobAL

If you use Smartsheets and Tableau or if you use Smartsheets and need some summaries … I highly recommend using this along with Google Script Triggers!

More on how to use Google Script Libraries is available here.

The library has a few functions:

  • updateGoogleSheetWithSmartsheetData – push a smartsheet directly into a google sheet (basically the Extract functionality from Smartsheets)
  • updateSmartsheetRows – publish a data object to Smartsheets
  • vlookup – searches through a google sheet and does a vlookup on your item. I use this when getting data from a user/flat-file/report and I need to push it into smartsheets based on some key.
  • sumAll – basically same as above; but a sum across all rows in the gSheet
  • Utility Functions
    • fetchSmartsheet – will pull down smartsheet data for you as an object
    • getSmartsheetColumn – find the column index for a given Smartsheet Column Header. Used in support of vlookups and updates
    • getSmartsheetColumnId – same as above (SAA) but returns Id rather than index
    • getSmartsheetRowId — SAA but for rows
    • getSmartsheetRow — SAA but for rows
    • getGsheetColumn – SAA but looks in Google Sheet. Used in support of vlookups

 

Anyway, was surprisingly easy to publish and then refactor my code to use the library! I’ll likely switch the work stuff over to my public library so I can use this going forward and also grant access to anyone when I leave one day.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s