Automating AppSheet form to update specific Google Sheet cell

[Haypipi]

I'm creating a simple inventory management system that updates when an item has been borrowed and returned by staff using AppSheet. I would like to know which staff_id has borrowed and returned which tool_id.

Currently in my Google Sheet for Staff, I have the columns staff_id, staff_name, tool1, tool2, tool3. Staff will use an AppSheet form to borrow and return tool_id. Is there any way I can use App Script to update sheets after the borrow and return records have been updated?

After borrowing record updated - We will search 'Staff' sheet for the staff_id under the 1st column, and update the tool_id under the 3rd column. If the 3rd column is filled, then fill the 4th. If 4th is filled, then the 5th. The form has a validation to prevent new entries if 5th is filled.

After return record updated - We will search 'Staff' sheet for tool_id under 3rd/4th/5th column, and just delete that cell (making it null)

Sample of borrow form

Sample of return form

[Denny774]

Hey man,

why are they only allowed to borrow 3 different tools max? 😄