Modification points:
- From your question and replyings, I understood that you might be using the functions of
Lock() and Unlock() for all "LOCK" and "UNLOCK" buttons, respectively. In this case, your script uses the active sheet. By this, even when each button is clicked, the active sheet is used. I think that this is the reason of your issue. - When you want to run the script for each sheet like "MTB_Q1", "MTB_Q2",,, by clicking each button, it is required to prepare each function for each button. Because in the current stage, when the button is clicked, the information of clicked button cannot be retrieved by the event object and so on.
When above points are reflected to your situation, I would like to propose the following flow.
Modified flow:
1. Prepare script.
In this case, it is required to prepare each function for each button. So when your script is modified, it becomes as follows. Please copy and paste the following script.
// These are used for the buttons of "LOCK" and "UNLOCK" at the row 10 in your image. const lock_row10 = () => Lock("MTB_Q1"); const unlock_row10 = () => Unlock("MTB_Q1"); // These are used for the buttons of "LOCK" and "UNLOCK" at the row 11 in your image. const lock_row11 = () => Lock("MTB_Q2"); const unlock_row11 = () => Unlock("MTB_Q2"); // These are used for the buttons of "LOCK" and "UNLOCK" at the row 12 in your image. const lock_row12 = () => Lock("MTB_Q3"); const unlock_row12 = () => Unlock("MTB_Q3"); // These are used for the buttons of "LOCK" and "UNLOCK" at the row 13 in your image. const lock_row13 = () => Lock("MTB_Q4"); const unlock_row13 = () => Unlock("MTB_Q4"); // IMPORTANT: If you have more buttons, please add the functions like above. function Unlock(sheetName) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); var protection = sheet.protect().setDescription('Sample protected range'); var unprotected = protection.getUnprotectedRanges(); unprotected.push(sheet.getRange('F9:O52')); unprotected.push(sheet.getRange('S9:AB52')); protection.setUnprotectedRanges(unprotected); var me = Session.getEffectiveUser(); protection.addEditor(me); protection.removeEditors(protection.getEditors()); if (protection.canDomainEdit()) { protection.setDomainEdit(false); } } function Lock(sheetName) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); var protection = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0]; if (protection && protection.canEdit()) { protection.remove(); } LockSheet(sheet); } function LockSheet(sheet) { var protection = sheet.protect().setDescription('Sample protected sheet'); var me = Session.getEffectiveUser(); protection.addEditor(me); protection.removeEditors(protection.getEditors()); if (protection.canDomainEdit()) { protection.setDomainEdit(false); } }
2. Set functions to buttons.
In your image, please set each function name to each button as follows.
- Set
lock_row10 and unlock_row10 to the buttons of "LOCK" and "UNLOCK" at the row 10 in your image. - Set
lock_row11 and unlock_row11 to the buttons of "LOCK" and "UNLOCK" at the row 11 in your image. - Set
lock_row12 and unlock_row12 to the buttons of "LOCK" and "UNLOCK" at the row 12 in your image. - Set
lock_row13 and unlock_row13 to the buttons of "LOCK" and "UNLOCK" at the row 13 in your image.
If you have more buttons, please add the functions like above.
3. Testing
After above settings were done, when you click a button, the sheet name corresponding to each row is used and the script is run with the sheet name.
Unlock()andLock(). I still don't know how to execute it.