Skip to main content
Post Closed as "Duplicate" by Rubén - Volunteer Moderator -
added code fences and rewrote code to observe onEdit(e) best practices
Source Link
doubleunary
  • 15.7k
  • 9
  • 23
  • 59

Lock google sheet Prevent edits to an Apps Script from being editedproject bound to a Google Sheet

I have a google sheet where I require users to edit certain cells with all other cells locked and with a tick to reset script button, but I would like to know if its possible to lock the script from being edited, everything works fine just need to lock the script.

Link to script to have an idea: https://docs.google.com/spreadsheets/d/1j6mf3bt3K2TZS3lzhYWLDeoovWiWs1I3lyLHItJCVMg/edit#gid=0

Script below

function onEdit(e) { var ss = e.source; var activeSheet = ss.getActiveSheet(); var cell = e.range;

if (activeSheet.getName() == "Items" && cell.getA1Notation() == "F6" && cell.isChecked())

{ [{ sheetName: "Items", range: "E182:E189" }, { sheetName: "Items", range: "E148:E152" }, { sheetName: "Items", range: "E156:E163" }, { sheetName: "Items", range: "E167:E178" }, { sheetName: "Items", range: "E66:E76" }, { sheetName: "Items", range: "E80:E104" }, { sheetName: "Items", range: "E108:E125" }, { sheetName: "Items", range: "E129:E133" }, { sheetName: "Items", range: "E137:E144" }, { sheetName: "Items", range: "E26:E36" }, { sheetName: "Items", range: "E40:E51" }, { sheetName: "Items", range: "E55:E62" }, { sheetName: "Items", range: "E9:E22" }

]function onEdit(e) {
  if (e.mapvalue !== 'TRUE') return;
  if (obje.range.rowStart =>!== clearTable6 || e.range.columnStart !== 6) return;
  let sheet;
  if (obj'Items' !== (sheet = e.range.getSheet()).getName()) return;
  const rangeList = sheet.getRangeList([
 cell   'E182:E189',
    'E148:E152',
    'E156:E163',
    'E167:E178',
    'E66:E76',
    'E80:E104',
    'E108:E125',
    'E129:E133',
    'E137:E144',
    'E26:E36',
    'E40:E51',
    'E55:E62',
    'E9:E22',
  ]);
  rangeList.clearContent();
  e.range.setValue(false);
}

}

function clearTable(obj) { SpreadsheetApp.getActive().getSheetByName(obj.sheetName).getRange(obj.range) .clearContent(); }

}

Lock google sheet Script from being edited

I have a google sheet where I require users to edit certain cells with all other cells locked and with a tick to reset script button, but I would like to know if its possible to lock the script from being edited, everything works fine just need to lock the script.

Link to script to have an idea: https://docs.google.com/spreadsheets/d/1j6mf3bt3K2TZS3lzhYWLDeoovWiWs1I3lyLHItJCVMg/edit#gid=0

Script below

function onEdit(e) { var ss = e.source; var activeSheet = ss.getActiveSheet(); var cell = e.range;

if (activeSheet.getName() == "Items" && cell.getA1Notation() == "F6" && cell.isChecked())

{ [{ sheetName: "Items", range: "E182:E189" }, { sheetName: "Items", range: "E148:E152" }, { sheetName: "Items", range: "E156:E163" }, { sheetName: "Items", range: "E167:E178" }, { sheetName: "Items", range: "E66:E76" }, { sheetName: "Items", range: "E80:E104" }, { sheetName: "Items", range: "E108:E125" }, { sheetName: "Items", range: "E129:E133" }, { sheetName: "Items", range: "E137:E144" }, { sheetName: "Items", range: "E26:E36" }, { sheetName: "Items", range: "E40:E51" }, { sheetName: "Items", range: "E55:E62" }, { sheetName: "Items", range: "E9:E22" }

].map(obj => clearTable(obj))

 cell.setValue(false);

}

function clearTable(obj) { SpreadsheetApp.getActive().getSheetByName(obj.sheetName).getRange(obj.range) .clearContent(); }

}

Prevent edits to an Apps Script project bound to a Google Sheet

I have a google sheet where I require users to edit certain cells with all other cells locked and with a tick to reset script button, but I would like to know if its possible to lock the script from being edited, everything works fine just need to lock the script.

Link to script to have an idea: https://docs.google.com/spreadsheets/d/1j6mf3bt3K2TZS3lzhYWLDeoovWiWs1I3lyLHItJCVMg/edit#gid=0

Script below

function onEdit(e) {
  if (e.value !== 'TRUE') return;
  if (e.range.rowStart !== 6 || e.range.columnStart !== 6) return;
  let sheet;
  if ('Items' !== (sheet = e.range.getSheet()).getName()) return;
  const rangeList = sheet.getRangeList([
    'E182:E189',
    'E148:E152',
    'E156:E163',
    'E167:E178',
    'E66:E76',
    'E80:E104',
    'E108:E125',
    'E129:E133',
    'E137:E144',
    'E26:E36',
    'E40:E51',
    'E55:E62',
    'E9:E22',
  ]);
  rangeList.clearContent();
  e.range.setValue(false);
}
Source Link
DZDU
  • 11
  • 1

Lock google sheet Script from being edited

I have a google sheet where I require users to edit certain cells with all other cells locked and with a tick to reset script button, but I would like to know if its possible to lock the script from being edited, everything works fine just need to lock the script.

Link to script to have an idea: https://docs.google.com/spreadsheets/d/1j6mf3bt3K2TZS3lzhYWLDeoovWiWs1I3lyLHItJCVMg/edit#gid=0

Script below

function onEdit(e) { var ss = e.source; var activeSheet = ss.getActiveSheet(); var cell = e.range;

if (activeSheet.getName() == "Items" && cell.getA1Notation() == "F6" && cell.isChecked())

{ [{ sheetName: "Items", range: "E182:E189" }, { sheetName: "Items", range: "E148:E152" }, { sheetName: "Items", range: "E156:E163" }, { sheetName: "Items", range: "E167:E178" }, { sheetName: "Items", range: "E66:E76" }, { sheetName: "Items", range: "E80:E104" }, { sheetName: "Items", range: "E108:E125" }, { sheetName: "Items", range: "E129:E133" }, { sheetName: "Items", range: "E137:E144" }, { sheetName: "Items", range: "E26:E36" }, { sheetName: "Items", range: "E40:E51" }, { sheetName: "Items", range: "E55:E62" }, { sheetName: "Items", range: "E9:E22" }

].map(obj => clearTable(obj))

 cell.setValue(false);

}

function clearTable(obj) { SpreadsheetApp.getActive().getSheetByName(obj.sheetName).getRange(obj.range) .clearContent(); }

}