Scaling up from smartsheet - need help

Mike L.

I really like smartsheet but I've just hit too many roadblocks. If you've had similar problems I'd like to hear what you use in order to scale up to larger amounts of data and also if you've managed to continue to use smartsheet. Thanks! Mike L.

• 20,000 row limit
• 500,000 cell limit
• 20,000 report export limit
• data connector cannot pass large reports to excel

coby

If you want to remain on Smartsheet and are pulling large amounts of external data, the API is the ticket.

You can create a script that pulls data from external sources (excel, database, wherever) and then once it hit's a sheet max it automatically creates a new sheet for spillover data, you can then view all the data in a Smartsheet Report. You can also use cross-reference functions to Vlookup() specific rows in all sheets and DataMesh if necessary. DataShuttle might also apply here but not too familiar with it.

The only real limitation is viewing more that 20,000 rows in one given Smartsheet object. Smartsheet is more about collaboration, sharing and analytics than acting as a data warehouse for large volumes of data.

coby

Yes-- the API can update column formula (link) and individual formulas (link). The steps to getting started (via Python) are as follows:

1) grab an API Key (info)

2) install Python (info)

3) Pip install the Smartsheet SDK (info)

4) Use something like Pycharm or VS Code and write a quick script that logs in pulls data from source, pushes to smartsheet and add's another sheet when hitting the row limit. You can modify formulas or add additional logic as well. (API Docs) (SDK Docs) (VS.Code)

5) Either place the script on a server and run it as a Cronjob or in Windows you can use the Task Scheduler and a .bat file to have it run on a regular basis as well. (info)

I do these kind of scripts all the time, so let me know if you were interested to discuss further.

coby

If you want to remain on Smartsheet and are pulling large amounts of external data, the API is the ticket.

You can create a script that pulls data from external sources (excel, database, wherever) and then once it hit's a sheet max it automatically creates a new sheet for spillover data, you can then view all the data in a Smartsheet Report. You can also use cross-reference functions to Vlookup() specific rows in all sheets and DataMesh if necessary. DataShuttle might also apply here but not too familiar with it.

The only real limitation is viewing more that 20,000 rows in one given Smartsheet object. Smartsheet is more about collaboration, sharing and analytics than acting as a data warehouse for large volumes of data.

Mike L.

Thank you this is helpful. I'll need to spend some time to learn how to use the API. Do you know if you can update formulas in sheets with a script instead of editing each individual sheet that contains the formula?

I don't have a big data set. The last 25 years of financial history is only 625,000 rows of data and only adding about 2500-3000 new rows per month.

coby

Yes-- the API can update column formula (link) and individual formulas (link). The steps to getting started (via Python) are as follows:

1) grab an API Key (info)

2) install Python (info)

3) Pip install the Smartsheet SDK (info)

4) Use something like Pycharm or VS Code and write a quick script that logs in pulls data from source, pushes to smartsheet and add's another sheet when hitting the row limit. You can modify formulas or add additional logic as well. (API Docs) (SDK Docs) (VS.Code)

5) Either place the script on a server and run it as a Cronjob or in Windows you can use the Task Scheduler and a .bat file to have it run on a regular basis as well. (info)

I do these kind of scripts all the time, so let me know if you were interested to discuss further.

Mike L.

Thanks for the roadmap!