Validate from List of Over 20,000 Items

Smartsheet can't swallow more than 20,000 rows, but I need to check for the presence of an item on a list of roughly 100,000. Any suggestions?

Specifically, in Smartsheet I want to take action on an item only if the contributor via a form has a US-government-recognized domain in their email address. The list of state-level-only government entities (haven't tackled federal yet) shows there are 91,385 of them (as of the latest census data).

Example, if johndoe@usacity.gov submits a form I want to check a list to see if usacity.gov is legit before I automate the next action in a workflow.

Find more posts tagged with

Accepted answers

All comments

Andrée Starå

Hi Troy,

I’d recommend splitting the list and use multiple VLOOKUP or INDEX/MATCH formulas to check if the domain is found in one of the sheets.

Make sense?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Cleversheet

I was afraid that might be the only within-standard-parameters option. Do you know if there's a way to achieve this using APIs? I can’t expect direct help with that effort but would be pleased to learn whether further exploration in that direction was warranted—rather than burrowing down an empty rabbit hole.

Andrée Starå

After thinking about it a little more I think we could maybe do it in one sheet after the limit enhancements has been released.

We could use multiple columns in the sheet for the lookup.

Make sense?

Cleversheet

Just searched for "Smartsheet limit enhancements" but no meaningful results. Is there some news I haven't heard?

Andrée Starå

Yes, maybe. See below!

Hi Andrée,

We are reaching out to give you early visibility into an exciting update for sheets. In the coming weeks, sheets will be bigger, faster, and more responsive to help your team achieve more in Smartsheet. Here is what is changing:

New sheet limits:

20,000 rows
400 columns
500,000 cells
100,000 cross-sheet referred cells

Improved performance:

2x improvement on sheet load time
6x improvement on sheet scrolling
2x improvement on speed to load cross-sheet references

Now, you can scale your solutions confidently with increased sheet capacity and improved performance.

Thank you,

Smartsheet Team

Cleversheet

Alas, the 20K rows remains a significant limitation. While you're right that I could construct a series of columns (asking the available 400) that could sorta replicate a talker stack, I fear that maintaining it could be touchy, like when adding a new "row" (entry) somewhere in the third set of columns, thus creating breaks in the first, second and fourth sets.

So back to the API approach: do you happen to know if that's feasible?

Ryan K

@cleversheet ,

I haven't tried something like this yet but it seems feasible. According to the documentation, you can "Search a specific sheet or search across all sheets that a user can access." So in theory, you could create X number of sheets where each time you hit 20,000 records you copy the sheet and start a new set. And then you could perform a search using the API across the sheets.

I have a lot of experience building solutions like this if you would like to discuss further.

Ryan

Cleversheet

I'll be looking for a way to maintain a single table, if possible. I'll check back with you if I have to give up on that ideal. Thanks, Ryan.

Troy

Ryan K

@cleversheet

Best of luck! I have built some processes for other clients where I archive data off Smartsheet and vice-versa. It's common that you may get records that no longer need to be maintained in active grids or simple have a service with a database in the back that can easily store millions of records in which you could call.

Ryan

Cleversheet

Ha, just saw what happens when I forget to double-check spell-check:

"I could construct a series of columns (asking the available 400) that could sorta replicate a talker stack, ..." was supposed to read,

"I could construct a series of columns (among the available 400) that could sorta replicate a taller stack, ..."

No reply awaited.

Andrée Starå

@cleversheet

Haha!

I saw that Ryan answered already!

Let me know if I can help with anything else!

Best,

Andrée

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.