Trying to work around the 5000 row limit

Karl_In_Oz

Hi Guys,

I know there are other questions on this, such as here: https://community.smartsheet.com/discussion/rss-feed

But in the interests of etiquette I thought I better start my own thread.

So I have a sheet where I add a new row at the top. Every three months or so I reach the 5000 row limit and have to move rows out manually. I'm looking for a way to automate that. I currently use a Zapier Zap to move rows out of a different sheet so I know that moving rows out is possible.

So what I'd like to do is move rows out if they are older than say 2 months to a different sheet. And I'd have to have a zap running on that sheet too - cascading to several sheets over time....

However to do it I'd need to trigger when rows are updated - triggering on new row doesn't work because it's always the latest date. Triggering on updated rows is good because new rows added at the top causes each row below to update, then I would check each row's date and if older than 2 months move it out. The problem lies with the fact that Zapier, once a certain number of tasks are hit, puts the zap on hold. And each updated row causes a Zapier task. So I  get a task for each row of the sheet, which of course I'm hoping to get up to around 4,900 to take advantage of the whole spreadhseet.

Can anyone think of a way of getting around this? For me the API isn't a solution as I have no clue how to program for that. I guess I could learn but it would be a steep learning curve.

Cheers for your help,

Karl

Shaine Greenwood

Hi Karl,

First off—our team is interested in knowing more about your process and why you'd want to see this type of feature directly in Smartsheet. When you have a moment, please submit a product enhancement using the form under Quick links on the right of the Community site.

Secondly—I believe that Zapier has a paid option that allows you to get around this restriction. Otherwise, you might explore using another automation service like Azuqua that has a higher limitation. If neither of these options work out, you might try staggering your zaps using a different criteria. For example, if you have some type of category or project type, you could create separate zaps for each project type, and have some move at 1 month and others move at 2 months.

Karl_In_Oz

Hi Shaine, thanks for your response. 

Sadly the paid option with Zapier does not allow me to do what we need to accomplish. Here is the response I got from their support:

Across all apps, we have no Action that affects more than one item at a time. So even if we did have a Trigger on the number of Rows, you couldn't then combine it with an action that would move 1,000 Rows, let alone 2 rows!

I'm afraid you'll have to keep doing this manually. Sorry I don't have a better answer today! 

Looking at Azuqua's What can Azuqua do for Smartsheet? page it doesn't look as if they could do it either. And at 14 x the price of Zapier's "Professional" plan, with no free trial, we'd need to think hard about that.

Looking back at my original post here it does sound a little odd - I'm asking a question of you guys relating to Zapier. I guess what I'm asking is: is the limitation as quoted from Zapier above due to a limitation in the API or is it a limitation Zapier have placed? And if it is a limitation in the API can it be modified?

Shaine Greenwood

Hi Karl,

This is not a Smartsheet API limitation: https://smartsheet-platform.github.io/api-docs/#get-row

Taking from the note in our API documentation: with move row in the API, up to 5,000 rows can be specified. Of course, the destination sheet is subject to the same 5,000 row limitation—meaning you could only move 5,000 rows to a blank sheet. 

But you can definitely specify multiple rows in the Move Row request.

Karl_In_Oz

Ok thanks for that Shaine, I'll have to try and find a different solution to Zapier.

J. Craig Williams

Hello Karl_in_Oz,

Here is my understanding of why Zapier (and other automation tools like IFTTT, Azuqua, and so on) have such limitations on triggers. This may be more brief than it deserves, but it is Friday at 9pm and I started to wind down for the day (there may be alcohol involved)

 

The typical trigger is 'updated row'. When this causes a row to be moved off of the sheet, then all of the rows beneath it are shifted ... causing them to satisfy the criteria for 'updated row', which trigger a whole new set of tests and their system bogs down (a bit or a lot) based on the processing.

Worse, this could, if set up improperly by the user, cause a whole set of unexpected successful TRANSACTIONS, which is how Zapier determines if the Zap counts against the cost. 

Attempting to trigger on 5000 rows is going to be a problem, even if done well. However... in my experience, I have managed to convince Zapier support to raise the limit on SPECIFIC Zaps based on a detailed description of how it is used, how it will be triggered, and why ONLY the rows that I want to be acted upon (using Filters in Zapier) will be acted upon, ie a fairly detailed use case and explanation for them. That wasn't 5000, so it may still be problematic.

I do have several Zaps that I force to activate sheet wide at 3am (most of them are based on some alternative to the method I documented here: http://ronin-global.com/2017/03/15/forcing-today-refresh-in-smartsheet/) and some of those are more than 100 rows but not more than 1000.

I hope that helps. I try to approach Zapier with a "OK, ... it CAN be done ... now to figure out how"

Craig