0

I'm creating a fun little game in Google Sheets -- the part of code I'm working on now is basically locking the row after a specific cell in that row has been edited (essentially, not letting the user to go back and edit anything before that cell).

My full code is below, but I'm looking for a fix with lines specific to the protections. I want my sheet to add a data range that only allows the email amadle@strengthinsheets.com edit access. However, I'm testing with different accounts, and it seems the protection script is allowing the user to edit (not locking to the specific account).

Is something wrong with this code block? I basically just want to add a protection on the range that doesn't let anyone other than a specific user edit.

function onEdit(e) {
    var oldText = "-";
var newText = "=";
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var ss = spreadsheet.getActiveSheet();
// var cell = ss.getRange('AB1').getValue();
var answer = ss.getRange("AA2").getValue();
var syntax = ss.getRange("AC2").getValue();
var description = ss.getRange("AC1").getValue();
var type = ss.getRange("AC3").getValue();

if (ss.getRange("AB1").getValue() == "MATCH") { 

 SpreadsheetApp.getUi().alert(" Congratulations: "+ answer, "Type: "+type+"\n\nSyntax: "+syntax+"\n\n Description: "+description, SpreadsheetApp.getUi().ButtonSet.OK);
  
}


 if (e.range.getA1Notation() === 'F5') {

  

var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange("B6:N6").createTextFinder(oldText).matchFormulaText(true).replaceAllWith(newText);
    **var ss = SpreadsheetApp.getActive();


  var range = ss.getRange('B5:N5'); // Example of protecting Row 1
  var protection = range.protect().setDescription('GUESS 1');

  var me = "amadle@strengthinsheets.com";
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false); 
  }
  
  }**

  if (e.range.getA1Notation() === 'F7') {

  

var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange("B8:N8").createTextFinder(oldText).matchFormulaText(true).replaceAllWith(newText);
  
  }

  if (e.range.getA1Notation() === 'F9') {

  

var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange("B10:N10").createTextFinder(oldText).matchFormulaText(true).replaceAllWith(newText);
  
var hint = sheet.getRange('AC1').getValue();
  SpreadsheetApp.getUi().alert("Function Hint", hint, SpreadsheetApp.getUi().ButtonSet.OK);
  }

  if (e.range.getA1Notation() === 'F11') {

  

var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange("B12:N12").createTextFinder(oldText).matchFormulaText(true).replaceAllWith(newText);
  
  }

    if (e.range.getA1Notation() === 'F13') {

  

var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange("B14:N14").createTextFinder(oldText).matchFormulaText(true).replaceAllWith(newText);
  SpreadsheetApp.getUi().alert("Next Time! Correct Answer: "+ answer, "Type: "+type+"\n\nSyntax: "+syntax+"\n\n Description: "+description, SpreadsheetApp.getUi().ButtonSet.OK);
  }
}
aynber
  • 22,380
  • 8
  • 50
  • 63
Anthony Madle
  • 371
  • 1
  • 10
  • In your situation, when the installable OnEdit trigger is used, is that your expected result? [Ref](https://developers.google.com/apps-script/guides/triggers/installable) In that case, I would like to recommend renaming the function name from `onEdit` to another name. By this, the duplicated execution can be avoided. But, if I misunderstood your current situation, I apologize. – Tanaike Jan 05 '23 at 01:30
  • Hi @Tanaike thanks for the response - it's working how I want, I just am looking to have the protection lock the row to all users aside from that email. In other words, someone else will be in the Sheet, they will edit the cell, and then after editing the cell, they will no longer be able to edit that range (only the email listed will). This script is creating the protected range, but still allowing whoever has the sheet to edit – Anthony Madle Jan 05 '23 at 16:51
  • 1
    Does this answer your question? [Lock cell after 1st edit](https://stackoverflow.com/questions/73120044/lock-cell-after-1st-edit) – SputnikDrunk2 Jan 06 '23 at 00:57
  • 1
    Thank you for replying. About `This script is creating the protected range, but still allowing whoever has the sheet to edit`, I think that in your script, after you renamed the function name and when the script for protecting the range is run by the installed OnEdit trigger, the protected range cannot be edited by other users except for `protection.addEditor(me)`. So, I cannot understand your current situation. I apologize for this. Can I ask you about the detailed flow for correctly replicating your current issue? By this, I would like to confirm it. – Tanaike Jan 06 '23 at 01:08
  • @Tanaike thank you - the flow goes as follow -- user creates a copy of the sheet, user edits row, user edits designated cell, range is locked, user now cannot edit --- I'm reading that the owner of the sheet cannot be limited by a protected range? Seems like I may need to find a different route – Anthony Madle Jan 06 '23 at 02:54
  • Thank you for replying. I have to apologize for my poor English skill. Unfortunately, I cannot understand `user creates a copy of the sheet` and `I'm reading that the owner of the sheet cannot be limited by a protected range? Seems like I may need to find a different route`. Can I ask you about the detail of them? – Tanaike Jan 06 '23 at 03:00
  • Apologies - the first step in my process is sending a user a link for them to go to FILE and MAKE A COPY. At that point, they become the owner, and it seems I'm unable to use my script to protect data ranges from them (because they are the owner). I'm looking into possibilities of setting ownership back to myself via Apps Script. Ultimately, I want someone to make a copy of this sheet, but then when they edit cell F5, a data range is locked from them in which they can no longer edit. thanks for your help, it's much appreciated – Anthony Madle Jan 06 '23 at 03:07
  • Hey @Tanaike --- thought you might be interested in this. So I went a slightly different route: I created a code that copies the sheet, and ideally sets the owner to my own google account. It's very close, but I'm having some trouble. Just wanted to keep you updated. https://stackoverflow.com/questions/75044586/exception-access-denied-driveapp-while-running-script-from-separate-google-acc – Anthony Madle Jan 07 '23 at 23:54

0 Answers0