Looking for ideas to improve my current daily process re: managing references

murphyspccms

Hi all,

This is a very open question to see if I can get ideas from the community to improve my daily morning process with Smartsheet. Apologies for the lengthy question- this is outside of my usual very specific help request.

I'm responsible for our Company's data; in the property insurance- independent adjusting world. We use Smartsheet for our analytics for leading/lagging indicators, KPI's, and just the general pulse of the company.

In the mornings I have a python script that pulls all of my reports from our system (I'm not well versed in coding at all). I then upload the reports to Smartsheet, then manually change the references with the most recent data. The references are several reports and functions/equations. This process is very time consuming since there are over 50 tasks I have to do.

I'd like to improve this process- I know there are ways to improve it using API & python coding, but I don't have the the aptitude currently to go that route. I've attempted using ChatGPT for this, but that fella overcomplicates everything.

My process evolved from having a "main data source" with open and closed claims (which I scrapped because I'm over the 20K row limit, it just didn't work well likely due to the volume of reports and functions tapping into it. I would upload the fresh data and spend about 45min-1hr changing every reference and source to the new upload).

Next (& current process) I started splitting up open/closed. This allowed me to have a "main open data source" which works well because the volume of "open" claims are usually less than 3000, so I can overwrite the old data by copying and pasting 500 rows at a time until it's fully updated. This has saved me about 50% of the work from my original process (not mentioned). The "closed data source" having around 20k rows, I'm resorting to the original & time consuming route of just opening each reference/function and changing it to the newest data source.

My request/question: having read my current process, does anybody suggestions of overwriting an entire sheet- all rows (right at the 20k limit), daily with new data? if I could overwrite an existing sheet, and having all functions/references on the existing sheet (which is what I'm doing with the "open claims" sheet).

A few notes- I know that Data Shuttle would be perfect for this, unfortunately we're a small business, the Data Shuttle add-on is much too expensive for our budget. I have a Zapier Account, but unfortunately you have to map specific rows to replace, which is not an option due to the volume of rows, evolving door of new claims being added, and claims closing. I see potential in the built-in automation clearing cells when conditions are met, but I'm not sure how to replace those rows once they've been cleared.

Many thanks in advance for taking the time!

ccarriger

Do your claims have a unique ID number of some kind? I'm assuming it wouldn't be the email address for a customer since they could have more than one claim in the system.

Zapier is usually good about being able to search based on data fields, so you could do a conditional path--search for if the claim ID exists and if yes, update the fields; if no, then create a new row.

Then in Smartsheet, I'd have an automation set up to move the "closed" rows automatically to the "closed" sheet.

murphyspccms

Hi @ccarriger, they do have some unique identifiers- that's a good idea, I'll explore more of the Zapier functions. Though my first thought is running the zap once would exhaust all of my available tasks (I think, I'm still learning Zapier).

ccarriger

@murphyspccms, I'm not 100% on how that works anymore in Zapier as it's been a hot minute since I've had to run a large dataset through. You might want to confirm with support if you're not sure and see what options they might suggest. There are other automation platforms out there that could better support you too, if Zapier can't. Let us know how it goes?