0

I'm attempting to include a button in Google Sheets that protects a range on button press, and then removes that protection when pressed again. This part is fine and I've gotten it to work, but only if I'm the one who presses the button.

Is there any way to set a script to run specifically with my permission?

Here's the code I've written (I know it's probably awful, I'm very new to this and I've basically gotten along through Trial and Error and the google developers documentation)

function lockFunctionTue() {
  var sheetWeekRun = SpreadsheetApp.openById("SheetID").getSheetByName("Weekly Runs");
  var codeSheet = SpreadsheetApp.openById("SheetID").getSheetByName("codeSheet");

var timeNow = Utilities.formatDate(new Date(), "GMT+0", "HH:mm dd/MM")

var noteLineCells = sheetWeekRun.getRange('I10:O10')
var outputCell = sheetWeekRun.getRange('I1:I3')
var runs = sheetWeekRun.getRange('I11:O50')
var lockCell = codeSheet.getRange('B5')
var lock = lockCell.getValue()

var buttonLock = codeSheet.getRange('A1:A3')
var buttonUnlock = codeSheet.getRange('B1:B3')

var protection = runs.protect().setDescription('Tue Runs are Locked');
var protections = sheetWeekRun.getProtections(SpreadsheetApp.ProtectionType.runs);

var me = Session.getEffectiveUser();

if(lock == 0){
  lockCell.setValue(1);
  buttonLock.copyTo(outputCell)
  noteLineCells.setValue("This days Runs are Locked as of " + timeNow)

  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
} else {
  lockCell.setValue(0);
  buttonUnlock.copyTo(outputCell);
  noteLineCells.setFormula('=IF(D7>0,"There are Prebooked Runs that need copying over to this day","No Prebooked runs outstanding")');
    for (var i = 0; i < protections.length; i++) {
      var protection = protections[i];
      if (protection.canEdit()) {
        protection.remove();
      }
    }
}
}
Ian Speke
  • 1
  • Although I'm not sure whether I could correctly understand your goal, for example, in your situation, how about using Web Apps? [Ref](https://developers.google.com/apps-script/guides/web) When Web Apps is used, the script can be run by the owner. I thought that this might be the direction you expect. If I misunderstood your question, I apologize. – Tanaike Jan 12 '22 at 13:09
  • I'm not experienced enough to use a Web App yet, and I think it'd take a fair amount of re-writing so that I can use the app with different sheets in the future. I was hoping for some simple fix like if there was a function for "Session.setEffectiveUser()" – Ian Speke Jan 12 '22 at 15:14
  • Does [this](https://stackoverflow.com/questions/70369266/delete-row-based-on-user-input-on-protected-sheet/70369812#70369812) help? – TheMaster Jan 12 '22 at 16:18

0 Answers0