Alerts for max rows

Background:

Shift information is typed into a main sheet in multiple columns/rows by multiple people, then at the end of the shift, the manager confirms the date, shift number, and shift information before checking a box that changes a value on each row, and triggers a couple copy to workflows.

This is segmented into two sections, shift information (production, issues encountered, ect) and parts produced. Parts produced can be many different types, even if the part is the same for our production due to how it is assembled.

when the manager checks the checkbox and saves, operational data gets copied to a dedicated sheet for each operation. But the production gets delivered to a single sheet that maintains the production. On this end of shift there are 30 or so different products, each with a dedicated row. This means that there are a large amount of rows being added every day.

The production sheet stopped gathering info once it was at the maximum size. I wasn't alerted that the sheet had stopped gathering data, production had to tell me that it had stopped and their dashboard that they base what they produce on wasn't being updated.

Questions:

What is the most efficient way to alert my team to delete rows, and why does smartsheet not alert me automatically to when my workflows fault out???

Also why do I have to change the workflow even after I've removed rows in the sheet? It wouldn't run until I changed the flow, even hours later. I had to edit the workflow, change the destination sheet, then change it back.

@Genevieve P. @Paul Newcome

Find more posts tagged with

Accepted answers

Paul Newcome

To alert when getting close to max rows, I generally use the solution to put the row numbers on the sheet as useable and then set up an alert to trigger when that number reaches a certain amount (depending on all of the various limitations).

I usually get an email when a workflow breaks, so I am not sure why you didn't.

And I have to do the same thing when workflows break. I can't just fix the problem in the sheet. I have to go into the workflow and basically reset it.

Genevieve P.

Hi @L_123

I agree with what Paul said. I haven't personally had this issue yet, but I would add an auto-number column to the destination sheet to identify how many rows there are (or a formula if you're using the number column for something else) and use this as a basis for an Alert.

All comments

Paul Newcome

I usually get an email when a workflow breaks, so I am not sure why you didn't.

And I have to do the same thing when workflows break. I can't just fix the problem in the sheet. I have to go into the workflow and basically reset it.

Genevieve P.

Hi @L_123

Paul Newcome

I have thought on this some more, and even though I was initially thinking auto-number would be the easiest to implement, I don't think it is the most reliable long term because it will keep increasing even after rows are removed.

If you have 250 rows on the sheet and then delete 249, then next auto number is still 251. Even a MOD function wouldn't be a reliable way to track this unless you had some additional rules in place such as always leaving the same exact number of rows in the sheet each time you delete.

Unfortunately I think the most reliable way is to use one of the solutions for outputting the actual row number, but those put a huge draw on the limit to the number of cells that can be referenced in a formula. If you go the column formula route for outputting the row number, that increases your referenced cells exponentially for every row. If you go the non-column formula route it increases less than exponentially but still very quickly. I can't remember what the pattern is called but it is basically the

1 = 1

2 = 3

3 = 6

4 = 10

5 = 15

6 = 21

Where you add up all of the lower numbers.

If you are wanting to flag the number of rows because of other limitations such as a large number of columns it may not be so bad, but the higher the row count the closer you get to the limit of how many cells can be referenced by formulas (25,000,000). I know that seems like quite a lot, but for some reason I have been reaching that more and more lately which has forced me to parse out data across multiple sheets. Fortunately grouping and summaries in reports have made pulling that data back together much easier, but even that has its limitations.

L_123

I feel like I've reached an oversight from smartsheet on this issue.

Autonumber doesn't change values, so I can't simply use a greater than value as a workflow trigger
unless I dedicate a formula to this. I can't put said dedicated formula on the summary page, because I need to trigger a workflow on it. so
I need to have a sheet level formula. Because I am deleting rows to clean up data,
it needs to be a column formula. I already have a large amount of unnecessary data on the sheet due to the fact that I am using copy workflows to populate it. So i'm going to limit the total number of rows even further

This is actually the first time i'm experiencing an overloaded smartsheet (maxing out the rows, I've reached the max of the performance many times). I usually design them specifically to utilize as few rows as possible in order to avoid even getting close to the sheet maximum.

I'm going to have to go with a count formula based on an autonumber as suggested, but I really feel like this is something that should have a direct answer from smartsheet. Every time something fails to send (when I lose data), the workflow failure should trigger and alert me instead of simply disabling itself.

Thanks for the responses.

Genevieve P.

Hi @L_123

I absolutely agree with you about the workflow failure alert so I've just completed a test - I filled out a sheet to the max, then set up a Copy Row Workflow over to the full sheet. The workflow ran for the three rows that it could, then it disabled itself. When this happened, I did actually receive an immediate email notification.

Are you the owner of the sheet with the workflow? Is it possible this notification was caught in a spam filter?

L_123

I am the owner of the sheet and workflow. I doubt it was caught in a spam filter, as I've not seen that happen in a few years.

What is the title of the alert? I might have missed it among all of the alerts that I get. I'll try to recreate the problem as well.

Genevieve P.

It's a generic "A workflow has become invalid" Subject Line, then the body of the email identifies the name of the Workflow that's invalid:

Screen Shot 2021-11-19 at 4.23.44 PM.png

L_123

I did get it. I just missed it while scrolling through my email due to some other workflows having the same name for employees that have been removed from the account in some workflows. My test did give me an email as well. I'm going to have to find a way to have a persistent error posting on the dashboard if we aren't updating the information in case (when) this happens again.

Thanks

Genevieve P.

I'm glad to know the notification is at least working, but I understand that it would be helpful to have an alert before the sheet is fully filled, too (like when you're working in the sheet and you get the pop-up message, but sent as an email instead). I'll submit this feedback to the Product team, but it sounds like the formulas discussed above will be the only way to do this for now.

A. Rose

Hi,

I found the following solution to be the best so far for getting alerted before reaching the cell maximum.

Create a column to "Count Cells" as the first sheet column,
(add a description on another column "Do not delete", and lock the row).
Create 2 sheet summary Columns, 1 to "Count Cells" and another to "Count Rows".
Under "Count Cells" add a formula to count all cells from the column after "Count Cells" until the last column, including blank and non-blank cells, + the next sheet summary column that counts all rows:
=COUNTIF([Column #2]:[Last Column], "") + COUNTIF([Column #2]:[Last Column], <>"") + [Count Rows]#
In the sheet summary column "Count Rows", add a formula to count all cells in a column that always contains a value, such as an automatic date column or a row count column:
=COUNT([Date Added]:[Date Added]).
In the "Count Cells" column, link the Sheet summary column "Count Cells": =[Count Cells]#.
Create a daily alert and filter "Count Cells" to be greater than 400,000.

With this, the sheet summary column counts all blank and non-blank cells, as well as the number of cells in the first column - which can't be included to avoid a circular reference,

And you get an alert BEFORE hitting the 500K cell limit.

PLEASE vote up this Smartsheet idea below - requesting to get an automatic alert!

(Click View post, then vote arrow up).

https://community.smartsheet.com/discussion/137406/alert-before-reaching-smartsheet-sheet-limits

I hope this helps!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.