Updating sheet data before copying row

Werner Gerstacker · 08/05/20

Thanks for looking into this, @Paul Newcome - I'll do that.

Werner Gerstacker · 08/26/20

Hi @Paul Newcome and everybody else who is interested.

Smartsheet Support suggested the following approach:

Locking and unlocking the sheet will trigger an update of the formula in my interim spreadsheet.

I tried this and it seems to work fine - with one exception:

I needed to move the lock & unlock actions into two separate automations (with the lock one running first) because Smartsheet doesn't seem to adhere to the sequences as mapped above; it will basically do all three actions at the same time

This might result in the alert being sent before the lock/unlock, i.e. the alert is sent (or in my real case: the row is copied) before the action that triggers the update is carried out, and/or the 'Lock rows' action is carried out last, leaving my sheet in a locked state at the end of the automation.

Due to the granularity of the trigger, i.e it can only be run at the full hour, splitting the lock/unlock actions will result in my sheet being locked for an hour, but that's still better than having yesterday's or last week's values in the archive.

Paul Newcome · 08/27/20

Thanks for the update. That's certainly an interesting approach. I wonder if we could leverage this lock/unlock to update the TODAY function too...

Werner Gerstacker · 08/27/20

Yup - that's what it does as well. 👌

Paul Newcome · 08/27/20

@Andrée Starå @Mike Wilday @L@123 @Genevieve P

Take a look at the solution for this thread. The basic idea is that you use an automation to lock then unlock cells in a sheet on a set time every day to force a refresh. One of the bonuses to this... It will also update the TODAY function!!! So now we have a way to have an automatically updated TODAY function without using Zapier or manually submitting a form or update request. I am going to test it for reliability, but I may end up replacing my current setup with something like this.

@Werner Gerstacker Thanks for sharing!

Genevieve P. · 08/27/20

@Paul Newcome

Thanks for the tag on this! I recently heard about this suggestion for how to trigger the TODAY function as well and thought it was pretty clever!

As Werner found, it worked for me when I set two different alerts - one to lock at 1am, one to unlock at 2am. It does mean the sheet is locked for an hour, but if you set it in the middle of the night sometime then this shouldn't be a problem.

Paul Newcome · 08/27/20

@Genevieve P I actually use a Today sheet. All it is for is housing the TODAY() function in a date cell. I have Zapier adding a new row every day to keep it fresh. Then any sheet where I need the TODAY function, I just use a date type column and cell link to the Today sheet. If I keep the Today sheet, I can maintain that type of linking and it won't matter if that particular sheet is locked or not because the cell link will still remain active.

Genevieve P. · 08/27/20

Paul - that's brilliant.

Paul Newcome · 08/27/20

Thanks! I can't take sole credit for it though. It was a combination of other posts here in the community that gave me the idea to use a Today sheet (I think it was the idea of submitting a form daily that started the ball rolling on the thought process), and @Andrée Starå helped with setting up the actual "zap".

Andrée Starå · 08/27/20

https://community.smartsheet.com/discussion/comment/261150#Comment_261150

Nice! Thanks for sharing! @Werner Gerstacker @Paul Newcome

Andrée Starå · 08/27/20

https://community.smartsheet.com/discussion/comment/261175#Comment_261175

I've also used a method where it's updated thru the Sheet Summary instead. The plus with that method is that it will always work as long as it's connected to the main sheet thru cross-sheet formulas.

Mike Wilday · 08/27/20

Brilliant. I love it. Thanks for sharing @Paul Newcome

Updating sheet data before copying row

Answers

Categories