Reputation: 83
I want to prevent changes to column K in google spreadsheet. Whatever value is there, I do not want it changed. I do not like the protection feature as it makes what I consider an ugly display.
My code. Unfortunately, it does absolutely nothing. The intent was to take whatever the current value is in the cell, save it, and then write it back on exit of the cell instead of saving whatever changes might have been made to the cell. The cell will either be blank to start, or will already have been modified to contain a date & time. Whatever the current contents blank or not, it should retain the same value after leaving the cell.
function onEdit() {
var s = SpreadsheetApp.getActiveSheet();
var r = s.getActiveCell();
var columnNum=r.getColumn()
// if column is K then prevent any changes
if (columnNum == 11) {
var dateCell = s.getRange(r.getRow(), 11);
var v=dateCell.getValue();
dateCell.setValue(v);
}
};
Upvotes: 1
Views: 838
Reputation: 569
This might help you. A workaround to your problem I came up with till Google have a proper script API to protect ranges. This one is using the validation function and it works.
function setRangeProtection(rangeToProtect) {
var firstRow = rangeToProtect.getRow();
var noOfRows = rangeToProtect.getHeight();
var firstColumn = rangeToProtect.getColumn();
var noOfColumns = rangeToProtect.getWidth();
var rangeToProtectValues = rangeToProtect.getValues();
var rangeToProtectFormulas = rangeToProtect.getFormulas();
var cellRow = firstRow;
for (var i=0 ; i<noOfRows ; ++i) {
var cellColumn = firstColumn;
for (var j=0 ; j<noOfColumns ; ++j) {
var cell = sheet.getRange(cellRow, cellColumn);
if (rangeToProtectFormulas[i][j] == "") {
var rangeToProtectContent = rangeToProtectValues[i][j];
} else {
var rangeToProtectContent = rangeToProtectFormulas[i][j];
}
var rules = SpreadsheetApp.newDataValidation()
.requireTextEqualTo(rangeToProtectContent)
.setAllowInvalid(false)
.setHelpText('Don\'t mess with the DJ!')
.build();
cell.setDataValidation(rules);
++cellColumn;
}
++cellRow;
}
}
The only small issue with this is that with cells that contain formulas a small red triangle appears and a message is displayed when you hover over it. Couldn't get rid of that one. If you find a solution for that let me know. Removing the help text doesn't help as it returns to a default mode.
Upvotes: 1