How to lock a Google Sheet during a specific time period everyday
google sheets
My google spreadsheet should only be editable between 12 AM to 8 AM everyday.
How do I accomplish this?
Best Answer
With a script, one can add sheet protection using protect method, and remove it using remove method. Using time-driven triggers, set the following functions to run daily, protectAll should run at 8am and unprotectAll at 12am.
Both functions loop over all sheets in the active spreadsheet, protecting or unprotecting them.
function protectAll() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
for (var i = 0; i < sheets.length; i++) {
sheets[i].protect().setDescription("Temporary protection");
}
}
function unprotectAll() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
for (var i = 0; i < sheets.length; i++) {
var protection = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
if (protection) {
protection.remove();
}
}
}
It is not currently possible to lock a given cell. However, you can create new sheets in the spreadsheet and lock those. If you really want to prevent her from changing the formulas, you could have all of the results/formulas be in a second sheet, referring to the first sheet's data, then lock the second sheet so it would be read-only to her.
In order to reference data from one sheet in another, use the syntax sheetname!range. For example, to take the average of sheet1's column A in rows 2 through 10, you would use Average(sheet1!A2:A10).
To add sheets, use the + sign on the bottom right side of the spreadsheet. You can switch between and rename sheets using the tabs and dropdown arrows on the lower left side of the spreadsheet.
Google has added a new number format called Duration. Start by formatting the start and end fields to Format -> Number -> DateTime or Format -> Number -> Time and your calculation field to Format -> Number -> Duration
Once you have done that you can subtract the fields to get the difference as noted by Stefano Palazzo in his answer.
Best Answer
With a script, one can add sheet protection using protect method, and remove it using remove method. Using time-driven triggers, set the following functions to run daily,
protectAll
should run at 8am andunprotectAll
at 12am.Both functions loop over all sheets in the active spreadsheet, protecting or unprotecting them.