Running formulas

Richard Stanton

It appears as though you have to open a sheet for a formula to run. Is this correct?

I have reports that show out of date data due to the sheet not being opened therefore causing the formula to activate.

Any suggestions?

Thanks in advance.

Richard

Robert S.

Hello Richard,

 

Formulas run when the sheet is open or saved. If you have formulas on sheets that aren't being opened or saved, then that data will not be updated until that sheet is opened or saved.

 

When you open a report that pulls data from that sheet, that doesn't open or save the sheet, meaning the formula won't run. If you change something to one of the rows on the sheet from the report however, that will save the sheet causing it to run the formula. This is a bit of a workaround, however it would work to run all of the formulas on that sheet without having to go open the sheet itself.

Richard Stanton

Thanks Robert. I expected that would be the answer but just wanted to check if I was missing something. 

Cheers 

Richard 

J. Craig Williams

See my post here on how to fix this with Zapier:

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

It is also possible to do this with the API.

Craig

Richard Stanton

Hi Craig

That looks like it may solve my problem. Thank you.

What do you suggest I use as a trigger if all I really want to do is open the sheet and resave with the new formula calculations?

Richard

J. Craig Williams

I've been using the Scheduler by Zapier to trigger mine now for just shy of a month without a glitch.

Mine run at 3am and even with the moving around (I was in British Columbia, then Florida, now Germany) I haven't noticed any problems. 

(Which is kind of weird, now that I think about it).

Craig

Richard Stanton

Hi Craig

I am sorry to be a pain on this. I can't seem to get it right in my head.

I have created a link between Zapier & Smartsheet which places a timestamp using Zapier's terminology ({{zap_meta_human_now}}). When I test it the data appears in my sheet all good.

When scheduled to run hourly no further changes occur in that cell. What am I missing?

Thanks Craig.

Richard

J. Craig Williams

I've never seen that {{zap_meta_human_now}} before.

If you copy the contents of a Zap source field, you should see something like this:

{{11189305__pretty_date}}

where the number is the Zap number and the 'pretty_date' is the field / content name.

When I switch my Schedule By Zapier to run hourly, I do not see a "now" option for entering the timestamp. It you want the whole time stamp (I use just the date), then select the ID.

Have you checked your Zap history?

Lastly, hourly triggering of a successful Zap will result in 5000 transactions which is more than the minimum required Starter Plan (3000 transactions for me). Do you need it to trigger hourly?

Craig

Richard Stanton

Hi Craig 

No once a day is fine for me. I experimented with hourly to see if it was working.