Periodically Sync Fields With Formula-Based Values

[HSAI]

I have a status field that uses an app formula to populate its value

I would like to be able to setup an automation so that every night at midnight, if a row has an empty value for the status field, it "refreshes" / syncs this field so that the status is updated using the current formula for auto-completion.

I'd like to avoid having to manually edit and save rows to achieve the same thing. 

[Joseph_Seddik · Accepted solution]

There are two ways to do this:

A. Using a virtual column, in this way the formula will be calculated for all rows with every sync. This might not be the best to do for it might affect the performance of your app.

B.  App formulas in normal columns will be calculated for a given row upon any data change to any column in this particular row. We will use this feature to force recalculation of your status column, in the following way:

1. Add a new column to your table, type Number, Initial value 0, let's name it "refreshColumn".
   
   
2. Create an action refreshStatus with the following settings:
   • For a record of table: yourTableName 
   • Data: Set values of some columns in this row 
   • Refresh column, with the expression: [refreshColumn] + 1 
     

There are three ways to trigger this action:

1. From a Bot, that you could launch periodically to execute the refreshStatus action on all rows of your table.
   
   
2. If you know that certain changes in this table or another should cause the status to be updated, you can set your bot to monitor this change and launch the refreshStatus action accordingly on relevant rows.
   
   
3. Or, as a Form Saved Action if this data changed is introduced due to user action through a form. 

[SkrOYC]

Just in case, here is a comprehensive explanation of something like what you say, although you place it on a bot, which is almost the same thing

Quick Update System - How to update records in you... - Google Cloud Community