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.
Sample of google sheet enter image description here
Is there any way I can use App Script to update sheets after the borrow and return records have been updated?
To elaborate: 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.
Sample of borrow form enter image description here
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 return form enter image description here
