In Apps Script I made a code in which if a checkbox in cell C5 is true, then creates some words in the range C12:N41. That part of the code works perfectly, but what I want to do is to protect that range once all the words are created so noone can edit them. I added that code but it's not working. My code of the protection part is:
function createTable() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var cellC4 = sheet.getRange('C4');
if (cellC4.getValue() === true) {
tablaData = [
[One,Two,Three,Four,Five,Six,false,Eight,Nine,Ten,false,Twelve],
[One,Two,Three,Four,Five,Six,false,Eight,Nine,Ten,false,Twelve],
[One,Two,Three,Four,Five,Six,false,Eight,Nine,Ten,false,Twelve],
[One,Two,Three,Four,Five,Six,false,Eight,Nine,Ten,false,Twelve],
[One,Two,Three,Four,Five,Six,false,Eight,Nine,Ten,false,Twelve],
[One,Two,Three,Four,Five,Six,false,Eight,Nine,Ten,false,Twelve]
];
var startCell = sheet.getRange('C12');
for (var i = 0; i < tablaData.length; i++) {
startCell.offset(i, 0, 1, tablaData[i].length).setValues([tablaData[i]]);
for (var j = 0; j < tablaData[i].length; j++) {
if (tablaData[i][j] === false) {
startCell.offset(i, j).insertCheckboxes();
}
}
}
var protection = sheet.getRange('C12:N41').protect();
protection.setDescription('Protection of C12:N41');
}
I made a code in which if a checkbox in cell C5 is true, then creates some words in the range C12:N41
. But, when I saw your script, it seems that when the cell "C4" is true, you want to run the script in the if statement. If you want to run the script when the checkbox of "C5" is checked, it is required to modify it.setValues
and insertCheckboxes
are used in a loop. In this case, the process cost becomes high. Ref (Author: me)I want to do is to protect that range once all the words are created so noone can edit them
, it is required to edit the editors.When these points are reflected in your script, it becomes as follows.
In this modification, it supposes that the values of tablaData
are valid values. Please be careful about this.
function createTable() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var cell = sheet.getRange('C5');
if (cell.isChecked()) {
tablaData = [
[One, Two, Three, Four, Five, Six, false, Eight, Nine, Ten, false, Twelve],
[One, Two, Three, Four, Five, Six, false, Eight, Nine, Ten, false, Twelve],
[One, Two, Three, Four, Five, Six, false, Eight, Nine, Ten, false, Twelve],
[One, Two, Three, Four, Five, Six, false, Eight, Nine, Ten, false, Twelve],
[One, Two, Three, Four, Five, Six, false, Eight, Nine, Ten, false, Twelve],
[One, Two, Three, Four, Five, Six, false, Eight, Nine, Ten, false, Twelve]
];
sheet.getRange(12, 3, tablaData.length, tablaData[0].length).setValues(tablaData);
const columnIndexToLetter_ = index => (a = Math.floor(index / 26)) >= 0 ? columnIndexToLetter_(a - 1) + String.fromCharCode(65 + (index % 26)) : ""; // ref: https://stackoverflow.com/a/53678158
const offsetR = 12;
const offsetC = 3;
const rangeList = tablaData.reduce((ar, r, i) => {
r.forEach((c, j) => {
if (c === false) {
ar.push(`${columnIndexToLetter_(j + offsetC - 1)}${i + offsetR}`);
}
});
return ar;
}, []);
if (rangeList.length > 0) {
sheet.getRangeList(rangeList).insertCheckboxes(rangeList);
}
var protection = sheet.getRange('C12:N41').protect();
protection.setDescription('Protection of C12:N41');
protection.addEditor(Session.getEffectiveUser()); // This line might not be required to be used.
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
protection.setDomainEdit(false);
}
}
}
When this script is run, when the checkbox of cell "C5" is checked, the script in the if statement is run. And, the values tablaData
are put into cells from "C12", and false
values are replaced with the checkboxes. And then, the cells "C12:N41" are protected.
If in your actual situation, the checkbox is cell "C4", please modify var cell = sheet.getRange('C5');
of the above script.
createTable
. By this, when you check the checkbox of "C5", the script is automatically run. But, from your showing script, I guessed that you might want to directly run the script. So, in this modification, I modified your script without using the event object of the OnEdit trigger. Please be careful about this.