Schedule on regular interval a field update in a table based on certain data in the same table

AppSheet AppSheet Q&A
1

Hello All,

I have a requirement, which I’m unable to find a solution. I have a google sheet based table “Package” where I have 3 columns; Start date, end date and booking status (enum type; Pending, Active and Expired).

Logic to update Status field:

  1. Pending, if start date is a future date. I’ve achieved this with “Initial value” while creating the Package.
  2. Automatically change the status to “Active” once the start date reached to today(). I need to schedule this because package data will not be changed.
  3. Once the end date is crossed (end date < today()) then status should automatically changed to “Expired”. This also needs to be scheduled.

I’ve created an action on “Package” table but not sure how to schedule it? “Bot” is not giving me any option to schedule an action on regular interval. Please help.

2

Create a Scheduled bot for daily and enable ForEachRowInTable option. Then in the filter condition use this formula [Start Date]<=Today()

In Run Process section choose run a data action and click Set Row Values. In Set these Columns set the value for Status like below

iFS(
AND([Start Date]<=Today(),[End Date]>=Today()),“Active”,
[End Date]<Today(),“Expired”
)

Run the automation to check the results.

2 Likes
3

Thanks Varun for the response. I was missing “run a data action” in the process section. It works now.

1 Like