CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
MUHAMMAD WASIQ TANVEER
MUHAMMAD WASIQ TANVEER

Reputation: 1

Is there a way to restrict data entry in a cell of google sheets, only once?

I want to make an Mess Attendance System in google sheets. I will share the link with people where they can add the price of food they've eaten(breakfast) and attendance of their dinner. I want to make sure that once a person edited a value e.g. the price or the attendance(present,absent), they cannot edit it back AFTER A SPECIFIED TIME OF A DAY e.g. they cannot edit their selection after 4:00pm. Basically, the attendance of a whole month in a single sheet.

I've made the sheet BUT can't make it work like it should.

Upvotes: 0

Views: 495

Answers (1)

dwmorrin
dwmorrin

Reputation: 2734

The same restrictions you can apply manually using protected ranges you can apply using Apps Scripts and Protection.

You would use Tools > Script Editor and create a one function that applies protection so that only you (or specified users) can edit the sheet, and then another function that removes the protection.

In the Script Editor, you can click the trigger button which will take you to the Apps Script dashboard to install a trigger.

You'll want to create two triggers: once during the day to call the function to allow editing, and another trigger at 4 PM to call the function that restricts editing. Note that such a time based trigger will run sometime between 4 and 5 PM or between 3 and 4 PM, depending on what range you choose. Once it starts running, it will be consistent (e.g. 4:37 PM), but you cannot specify 4:00 PM using triggers.

If you require it to change at exactly 4:00 PM, you can call the Drive API for permissions from your own computer programmed to run at exactly 4:00 PM to remove permissions on the whole sheet dynamically.

Upvotes: 1

Related Questions