How to force a sheet to fully recalculate via the API

Rob Hagan

Hi there API Developer community,

I wish to use the API to force a sheet to recalculate.

Currently, we use a report that shows just the first row of every one of our 50 or so project sheets and then we manually copy a new value into a column on that report that we have put aside for the purpose. So, once a value like "Forced update 11-Jan-2017 03:18 PM" is copied down that column on the report and the report is saved, then every sheet shown on that report (appears) to be recalculated.

When I do the same action via the API, it does not appear to have the same outcome (but I am still testing).

Seeking some advice... (and I hope that I have expressed the situation sufficiently clearly)

Thanks in advance,

Rob.

PS, I just realised that you may be wondering why we need to do this recalculation.

 

In each of the 50 or so project sheets, there is a column called [Task Status]. [Task Status] shows values including "Completed", "In Progress", "In Progress Early" and "Overdue" and its value is calculated by formula using [Start Date], [End Date], [% Complete], [Row Type] and the function TODAY(). Trouble is that TODAY()'s value is (naturally) volatile.

 

We have many reports, but one particular report reads every project sheet and shows just the overdue tasks. If a project sheet is not opened for a few days, then the values in its [Task Status] column do not get recalculated and are days out of date. This leads to incorrect decision making. Our goal is to have a program that runs at least once per day and touches (causing recalculation) every project sheet. Thus, all of the reports that rely upon fields like [Task Status] will have reliable information.

Rob Hagan

Hi again,

 

Well, I may have been slightly premature but maybe others wanted to know the answer too.

 

Overnight (here in Australia), I ran an experiment that appears to show that the update via the API of one cell in a SmartSheet sheet does cause the formulas in that sheet to be recalculated.

 

The experiment was to:

Yesterday (11-Jan-2017)

1) Set up a sheet SheetA with a set of rows with the formula =TODAY()=[Date Field]n in a checkbox column and to put yesterday's date, today's date, tomorrow's date, etc as hard dates in the successive [Date Field]s.

2) Set up a report ReportA that shows all rows and all columns in SheetA and has "Highlight Changes since I last viewed" turned on.

Today (12-Jan-2017)

3) Run the Java program to touch a cell, other than those mentioned above, in SheetA.

4) Open ReportA, but not SheetA.

The report showed pretty yellow highlights where the cell representing yesterday went from checked to unchecked and the cell representing today went from unchecked to checked.

 

I would be grateful if you can fault this logic (especially from the SmartSheet operations or development teams) as I would rather find out now that I have a basic misunderstanding than when I am relying on this technique in full production use.

 

Cheers,

Rob.

Jason

HI Rob - I can confirm that editing a single cell in a sheet that contains formulas via the API will cause a formula recalc on the sheet. 

Rob Hagan

Thanks Jason,

Excellent to see this "official" SmartSheet response via the Community.

It really is appreciated (given my experience with products from other software vendors).

Cheers,

Rob.

Maxim Chemerkin

Dear Rob hi.

I've faced the same need (to update cells with Today() formula).

Could you, please, tell in a bit more details how you "Run the Java program to touch a cell"?

Thank you!

Maxim

P.S. I know nothing about API...

Rob Hagan

Hi Maxim,

As you probably know, an API has the ability to interact with your SmartSheet sheet via a program.

After experimenting with a number of the languages that are supported by the SmartSheet API, I chose Java.

All of the interaction with SmartSheet for this “touching” of the sheets takes place at 5am (local time) with no human interaction.

A cron job is run on a spare server in the office and that cron job runs a unix script that invokes the Java program. The unix script collects the reporting that the Java program makes about its progress and emails that to me. Thus, I get a written report of each “touch” each morning when I start work. If SmartSheet is not working properly, the report will show that the "touch" failed.

The program that I wrote retrieves all of the sheets in a nominated workspace and then updates a nominated cell in row 1 of each sheet with text like “Updated automatically at <dd-mmm-yyyy> <hh:mm>”. Every one of our sheets is structured in the same way, and we use row 1 to contain metadata about the sheet. This allowed us to designate one cell in the first row for the "touching". That cell is not used for any other purpose.

The unix script invokes the Java program once for each of four workspaces, as our production sheets are spread across four workspaces in order to restrict access to different classes of users.

There are about 200 lines of code in the Java program and about 100 lines of unix script.

This development required competence in unix commands, unix scripting and Java programming.

Cheers,

Rob. 

J. Craig Williams

If you only have a few sheets, Zapier is also an option.

This is one way I do it. This requires no programming knowledge as Zapier is fairly straightforward.

http://ronin-global.com/2017/03/15/forcing-today-refresh-in-smartsheet/

UPDATE: 2017-0917 - my solution has changed slightly and the post updated to reflect new method.

Craig

tcer

I know this is an old thread, hoping maybe a built-in solution has come to the app since.  Setting up new lists and having to add them to a API to refresh daily isnt really user friendly.  Is there a way to set a 'refresh formulas every x time' into each list?  Similar to how we have a refresh dashboard every x minutes.

tcer

I too would really like a feature that auto refreshes the formulas in a sheet without opening it so that the reports are accurate.  OR they refresh automatically when a report runs.

edit: this should apply to conditional formatting as well

Eva M

It's now July 2019 and still no fix. Ran into this problem AFTER spending weeks on setting up a super complicated set of sheets, reference sheets and ridiculous formulas. Now the whole hospital is getting inaccurate reports. Not cool. I submitted an enhancement ticket. I don't understand why the sheets would not automatically update when the sources change? What could possibly be the benefit of that???

Rob Hagan

Probably some deep design tradeoff to improve performance at the expense of the desired functionality.

For many years now, we have run the "touch" (via Java) of every active sheet at 5am Melbourne time to force all sheets to recalculate. We have a dedicated column (of a "hard coded" name) whose only purpose is to have its row 1 overwritten (ie. "touched") each early morning. No other cells in the sheet are modified. We also capture the success of each touch and email a digest of those outcomes to nominated individuals. If the digest shows fatal errors, then it is simple to do a manual "touch" via a report that shows every active sheet.

We do have the situation where every one of our sheets has the same structure, but given that the "touch" program finds the column that it is to touch by its "hard coded" name then you may be OK with a design which has the column in different places in each sheet.

We do have all of our active sheets in the one overall hierarchy, so the program does a tree traversal from the one root to find all of the candidate sheets.

tcer

check out this thread.  There is a solution using cell linking you might find useful.

https://community.smartsheet.com/discussion/auto-refresh-data