Last Week's Value

Cierra Ray

Hi All,

Does anyone know of a formula that can pull the values from an existing column from the week prior using the cell history or activity log?

e.g. I can see the value last week in the cell history but I want to pull it automatically via a column to create a last week vs this week view. My sheet is not date based so I cannot simply use a formula to find a prior date's value.

Thank you,

Cierra

Mike Wilday

Cell History and Activity data are not accessible data in smart sheet and can't be captured and shared in the sheet dynamically.

JeremiahHorstick

@Cierra Ray

This can be done with the API.

Peter Spung

How does one use the API from a formula?

Or asked another way... The API is a heavy lift for most of us. We have to get approval to use a language coding platform that our org supports, be granted API access to sheets, then have test tools to debug, then learn an additional language (we already learned the formula language), etc. We already have in Smartsheet the approved authority to use formulas, and know how to use them. And, we want the result of an API call to end up back in a Smartsheet cell, not in the memory space of the language environment. Just like this question to the community -- "I want to lookup a prior value of a cell in this or another sheet, on this date (or formula), and return the result in this cell where the formula is located". ...Why not make every API function available in a formula?... Once decided, that's indeed a heavy lift for Smartsheet. That could be done in phases -- starting with the most requested functions, or most used APIs (Smartsheet undoubtedly tracks API use in its website logs).

No offense intended -- I observe this answer quite often in the Community -- "Just use the API". Doing so should be made a lot simpler, and a lot more accessible.

Any thoughts? Peter

P.S. Yes, I will write an enhancement request for this.

Peter Spung

Two new product enhancement requests are in the hopper:

Provide a formula to look up prior cell values: https://community.smartsheet.com/discussion/100795/formula-to-lookup-prior-cell-values/

Make APIs available as formulas: https://community.smartsheet.com/discussion/100785/call-apis-from-a-formula/

Thanks, Peter Spung | pspung@northcarolina.edu | @paspung