Question:

I am having an issue and would really love some pointers, this has been a headache for a few days.

I have a sheet that employees will update daily with information about tasks done that day. Each column has a date in the header row (row 3 in this case), and after the end of the following day I want that column to lock so it cannot be edited further except by myself and one other. This is to prevent people from covering up mistakes or accidentally changing or deleting data.

SO I am looking for a script or something that will accomplish this. This sheet has about 45 tabs and I need the same thing applied to all of them. My idea is possibly a script that triggers at a certain time based off the date in the header row, so if the date is May 5th 2017, the respective column would lock itself at midnight on the 6th.

A link to a copy of my sheet, minus data is <a href="https://docs.google.com/spreadsheets/d/12rNB5qRP_lh-VdED6v2f3foUWhK09DDPNEZHLX8EJLs/edit#gid=882062735" rel="nofollow">here</a>.

Alternatively, if there is a way to simply lock any cell 24 hours after the most recent data is entered into it, and prevent further editing by everyone except select people, that could work too if the ideal method isn't doable.

Thank you all in advance for your advice, this project will really help my company and I really appreciate this community for being so helpful!

Yes, there is a way to do this.

I will briefly describe the solution:

<ol><li>Let's say that the first row has 1:1 contains consecutive dates.</li> <li>Create function lockColumns which would create new <a href="https://developers.google.com/apps-script/reference/spreadsheet/protection" rel="nofollow">protected</a> range.</li> <li>Add function lockColumns to time trigger, which triggers every day between 0:01 and 1:00 am.</li> </ol>

And now some code:

function lockColumns() { var ss = SpreadsheetApp.getActive().getSheetByName('Sheet 1') var range = ss.getRange('1:1').getValues()[0]; var today = new Date(); var todayCol = null; for (var i=0; i<range.length; i++) { if (today.isSameDateAs(range[i])) { todayCol = i; break; } } var rangeToProtect = ss.getRange(1, todayCol +1, ss.getMaxRows(), 1) var protection = rangeToProtect.protect().setDescription('Protected range'); // Ensure the current user is an editor before removing others. Otherwise, if the user's edit // permission comes from a group, the script will throw an exception upon removing the group. var me = Session.getEffectiveUser(); protection.addEditor(me); protection.removeEditors(protection.getEditors()); if (protection.canDomainEdit()) { protection.setDomainEdit(false); } protection.addEditor('email@gmail.com'); // second person with edit permissions } /* http://stackoverflow.com/a/4428396/2351523 */ Date.prototype.isSameDateAs = function(pDate) { return ( this.getFullYear() === pDate.getFullYear() && this.getMonth() === pDate.getMonth() && this.getDate() === pDate.getDate() ); }