Modify column data of a sheet when an other sheet is modified

[HugoJousselin]

Hello eveyone,

I would like to create a bot that will automatically modify a column in a Google Sheet when a user is creating a new case in an other Sheets.

An example : Someone creates and fills a new case for borrowing a key. Then i have a Sheets that lists all the rooms and which says if they are available or not. I would like that when a user is creating a "Borrowed key" case, the bot modifies the column "availability" of the Room list to say that this room is not available anymore.

I don't know how to do that because, right now, when I select the event "A borrowed key report is created", the only modifications that i can do in the automation concerns the Sheets "Borrowed Key" and i can't modify an other Sheets. 

I try to use the step "Run a data action" and then chosing the option "Set row values" in the automation. Maybe i'm doing wrong.

Thanks for your help, I hope i was clear.

[dbaum · Accepted solution]

The basic design is to create two actions:

• Create a Data: set the value of some columns in this row action for the Rooms table that updates the Availability column.
• Create a Data: execute an action on a set of rows action for the Keys table that runs the action for the Rooms table.