Google Sheets script that automatically locks a sheet 24 hours after a cell is edited

Question

I have been banging my head on this issue but I just can't seem to find a proper response to help me. Can anyone give me any clues how I could manage to do this? So I have an attendance form that has the employees, and then each day it's a different sheet. I would like after the day has ended, the employees wouldn't be able to access the past day's sheet anymore to edit their hours.

Is that even possible?

This is how a day's sheet looks like.

I have tried all kinds of scripts I've found on google but I haven't managed to reach a proper result. At most, I've managed to get a script that would protect a sheet upon activation (not when a cell is edited) and it would activate later with a separate timer script. But that seems way too complicated and I am sure there must a simpler way.

Answer 1

You need to run at the end of each day a function that protects that day's sheet. To automate that, add a time-driven trigger and set it to fire at 23:00 hours.

The code to protect the current day's sheet could look like this:

function protectTodaysSheet() {
  const ss = SpreadsheetApp.getActive();
  const sheetName = new Date().getDate();
  const sheet = ss.getSheetByName(sheetName);
  protectSheet_(sheet);
}

You can find my implementation of protectSheet_() (and its friends) at Protecting Entire Sheet using apps script.