Daily Update Solution without the need to open the sheet, use Zapier, or similar

Tim Starkey · 04/08/24

Can anyone else confirm that this solution is still working? I just implemented it on our templates, and it doesn't seem to be refreshing the rest of the sheet... maybe I am missing something?

I have an automation recording a date in a hidden "Date Helper" column.

I have four formula cells at the top of the sheet in the summary section that reference the 'date modified' column.

one formula cell references the same row as automation (verification the automation worked)
Two formula cells that reference date modified for two primary sections of the sheet (using formulas LARGE and CHILDREN).
one last formula that looks at the entire sheet (including other sections)

In general, last sheet change would match last automation, until someone makes a change in the sheet after the automation.

I was looking through a report this afternoon that excluded any sheets that had been modified today at 2:30pm MST, and the sheets with the new template were showing up, to my surprise, since they should have updated this morning.

It was showing dates of 4/5/24... but as I was looking, it changed to below... and you can see that the changes happened in the past hour, even though the automation ran at 4am and its now after 2:30pm.

here is what I am wondering... when the date is recorded, the existing formulas in the sheet also update.

automation opens sheet
existing formulas in the sheet update
automation records date
automation saves sheet
date modified for the 'record a date' automation updates after the sheet is saved/closed by the automation

So, any formulas dependent upon the automation date modified will not update until after it is opened a second time.

If this is the case, that is fine... I am not really after checking the automation, I just need the other formulas to update.

FWIW, here are the formulas in those cells. (The output is in a text cell, hence the + "" in the formula)

Paul Newcome · 04/09/24

@Tim Starkey It is still working just fine for me, and I implement this on almost every sheet I build.

Andrée Starå · 04/09/24

Hi @Tim Starkey

I hope you're well and safe!

Yes! It works for me, and I haven't heard from any clients having any issues.

Strange!

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Tim Starkey · 04/09/24

Thanks @Andrée Starå and @Paul Newcome

I am going to track it a few more days and see what is happening. Something is acting weird... I have a set of projects that were provisioned in Control Center on 4/5 using this new template. The record a date happened yesterday, and this morning... so twice. When I open some of them, they are still updating some of the cells... some are cross sheet links to the project summary sheet, some are in sheet cell references. But others that I open show updates happening during the automation at 4am.

I'll look further into it... I still think its related to the date modified... I don't think that is recorded until after it is saved... if its immediately closed after the save, I am wondering if the other cells don't have time to update based on the date modified. If I can't figure it out, I will certainly post back asking for help.

Tim Starkey · 04/09/24

Andrée Starå · 04/09/24

@Tim Starkey

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Trang Turtletraxx · 04/12/24

@Andrée Starå is there a solution to auto refresh a sheet by minutes versus only once a day? I am looking for a more real time (or as close to real time) update of calculations. My formulas are NOT date or TODAY() related. They are simple SUM or AVG formulas that still require someone to open and refresh the source sheet or use your suggested auto refresh solution. Thanks!

Paul Newcome · 04/17/24

@Trang Turtletraxx Keeping it strictly in Smartsheet, the best you can do is every hour. You would set up a change cell automation to run on the hour. I usually use letters. So the first one would be "12:00am", set to run at 12:00am every day, and change the cell in the [Sheet Refresh Column] to "A". Then I would set up another to run at 1:00am and change cell to "B", so on and so forth for each hour of the day.

Andrée Starå · 04/17/24

Hi @Trang Turtletraxx

I hope you're well and safe!

Yes, there's a limit at the moment, and it can only trigger one every full hour.

A workaround could be to use the 3rd-party service Zapier or Smartsheets API or the Premium app, Bridge.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Trang Turtletraxx · 04/18/24

@Paul Newcome @Andrée Starå Unfortunately, my client doesn't have Control Center, Bridge or API. I am trying to keep everything within their Smartsheet solution.

@Paul Newcome I was actually able to come up with something. I created a workflow to record a date in a hidden helper column whenever there is a change in any row. The caveat and educational moment for my client is they have to SAVE and REFRESH the page for the calculations to appear. So far it has worked. It's not automatic and there isn't an hour or day delay to see the formula calculations.

Paul Newcome · 04/18/24

@Trang Turtletraxx That will work for only the first change though I would think. If I change a cell multiple times in a day, the same date would be recorded which I would think wouldn't make any changes to push the refresh, but...

Someone making an actual change to the sheet should push the refresh. The hourly piece is for when no one opens the sheet to activate it.

Trang Turtletraxx · 04/18/24

Oh I didn't know it wouldn't refresh even if the same date is recorded. I thought it was still a "push" it activates the refresh.

In my instance, they are updating data through a report, not the sheet. When I made an update to the report, saved the update and refreshed, the calculation appeared. I didn't even think about making a change to the same row to see if the calculation would perform. It's good to know though :)

Trang Turtletraxx · 04/18/24

@Paul Newcome I just tested updating the same row, same cells and after I saved my updates and refreshed the report, the new calculations still appeared. I made an update of the same row and cells 3 additional times and the calculations updated each time.

Paul Newcome · 04/19/24

@Trang Turtletraxx Glad it is working for you! I hadn't tested it myself, but I have had experience in the past with the Modified (date) type column not updating if I reentered the same data into a cell.

When you make your update though (even though it is from a report), the update you are making should be forcing a refresh even without an automation.

Neil Egsgard · 07/22/24

Great solution, Andrée Starå. Works well for me.

Neil

Daily Update Solution without the need to open the sheet, use Zapier, or similar

Comments

Categories