76707

Automatically locking a range (column) as each date passes in Google Sheets?

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!

Answer1:

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() ); }

Recommend

  • WCF Service Application returns 404 using WebGet
  • Can I run Robotium tests automatically in parallel on multiple emulators?
  • Restructure php contact form
  • Table field that holds row count from another table
  • Determining if a string has been entered with a leading single quote
  • Type mismatch: cannot convert from Connection to Connection
  • SCSS Language Injection in Pycharm
  • Drag and Drop for imageview not working
  • What is wrong in my MVC implementation?
  • iOS 9 custom transition - animationControllerForDismissedController not called
  • Differences between drawing an Ellipse in Android and Java
  • jQuery Mobile - Dialogs without changing hash
  • How to change default stop edit behavior in jtable
  • android-support-v7-appcompat has same attrs as actionbarsherlock library
  • goJS dropdown remove items
  • PHP multiple file uploads
  • Is there any way to call saveCurrentTurnWithMatchData without sending a push notification?
  • Unique Permutations - with exceptions
  • Most efficient way to move table rows from one table to another
  • Android cannot disable cut copy paste
  • Calculate time difference in hh:mm:ss with simple javascript/jquery
  • Center align outputs in ipython notebook
  • std::remove_copy_if_ valgrind bytes in block are possibly lost in loss record
  • WPF ICommand CanExecute(): RaiseCanExecuteChanged() or automatic handling via DispatchTimer?
  • How solve “Qt: Untested Windows version 10.0 detected!”
  • custom UITableViewCell with image for highlighting
  • Webgrid not refreshing after delete MVC
  • Functions in global context
  • Jquery UI tool tip close icon
  • Get data from AJAX - How to
  • Illegal mix of collations for operation for date/time comparison
  • What is Eclipse's Declaration View used for?
  • Javascript Callbacks with Object constructor
  • Sending data from AppleScript to FileMaker records
  • vba code to select only visible cells in specific column except heading
  • How can I use Kendo UI with Razor?
  • XCode can't find symbols for a specific iOS library/framework project
  • Calling of Constructors in a Java
  • Traverse Array and Display in markup
  • Qt: Run a script BEFORE make