Can I protect cells (user not allowed to edit) and run script that makes changes in these cells (e.g. hide row, unhides row)?

Question

Is it possible to protect some cells form a user and at the same time give the user an opportunity to run a script that makes changes in the cells, e.g. copy, hide, unhide cells? This way cells could be changed by the script but not by the user himself.

I write scripts in tools -> script editor. This way a script is run always from the account of the user. Maybe a solution would be to run a script from the account of the admin? Is it possible? Because admin has access to all protected cells.

According to the help in comments from AMolina Web Apps can be helpful, but i still can't adopt it to my problem. I went through some materials regarding Web Apps, but normally those are used to send data between html website and google sheet, but not as a tool to run some script from admin level. I'm stuck :(

In my sheet it looks like this:

• I create one sheet for users to operate on

• all scripts are triggered by the buttons created on the sheet

• each row of that is in usage has some cell that are protected form the user

• in the future I would like to duplicate sheets and send it to different users to work on, so it would be nice that the solutions would work after duplication of the sheet; scripts and workflow wouldn't change, only the data the the users will fill in

Examples of the scripts that I use:

1)

function copyRow() {
var sheet = SpreadsheetApp.getActiveSheet(); 
var currentRow = sheet.getCurrentCell().getRow();  

sheet.insertRowsBefore(currentRow, 2);
var rangeToCopy = sheet.getRange(currentRow + 2, 1, 2, sheet.getMaxRows());
rangeToCopy.copyTo(sheet.getRange(currentRow, 1));

sheet.getRange(currentRow, 2,1,6).clearContent();
sheet.getRange(currentRow, 12,1,sheet.getMaxColumns()).clearContent();
sheet.getRange(currentRow,50).setValue("d");

sheet.getRange(currentRow, 2,1,6).setBackground("white");
sheet.getRange(currentRow + 1, 12,1,26).setBackground("white");

var protection = sheet.getRange(currentRow, 1,1,1).protect();
protection.addEditor("[email protected]");
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
protection.setDomainEdit(false);
}

var protection = sheet.getRange(currentRow, 8,1,sheet.getMaxColumns()).protect();
protection.addEditor("[email protected]");
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
protection.setDomainEdit(false);}}

2)

function deleteRow()
var sheet = SpreadsheetApp.getActiveSheet();
var selection = sheet.getSelection();
var currentCell = selection.getCurrentCell().getRow();

var cell2 = sheet.getRange(currentCell,50);
var cellBefore = sheet.getRange(currentCell - 1, 50);
var cellAfter = sheet.getRange(currentCell + 1, 50);

if(cell2.getValue() == "r" && (cellBefore.getValue() != "r" && cellAfter.getValue() != "r") == false){

var ui = SpreadsheetApp.getUi();
var response = ui.alert('Napewno chcesz usunąć pozycję?', ui.ButtonSet.YES_NO);

if (response == ui.Button.YES) {

  sheet.deleteRow(currentCell);
 }
}
else{
Browser.msgBox('Nie można usunąć wiersza', Browser.Buttons.OK); 
}
}

Answer 1

Thanks to @AMolina I was able to solve the problem. Thank you!!!

I used WebApp to allow user to run a script as admin.

Example of the code inCode in Google script:

function copyRow(){

var sheet = SpreadsheetApp.getActiveSheet();

var currentRow = sheet.getCurrentCell().getRow();

//url web app

var urlScript = SpreadsheetApp.getActiveSpreadsheet().getUrl();
var sheetName = SpreadsheetApp.getActiveSheet().getName();

var type = "addRow";

var url = "https://script.google.com/macros/s/AKfycbwC7og_____1Bf0WP8v5tQ/dev";

var urlParam = (url+"?urlScript="+urlScript+"&sheetName="+sheetName+"&currentRow="+currentRow
              +"&type="+type);  

//przesłanie do webapp
var html = "<script>window.open('" + urlParam + "');google.script.host.close();  </script>";
var userInterface = HtmlService.createHtmlOutput(html);
SpreadsheetApp.getUi().showModalDialog(userInterface, "skrypt w trakcie działania...");
}

Script in WebApp

function doGet(e) {

//parametry
var urlScript = e.parameter.urlScript;
var sheetName = e.parameter.sheetName;
var currentRow = Number(e.parameter.currentRow);
var type = e.parameter.type;

//odniesienie do mojego sheet

var file = SpreadsheetApp.openByUrl(urlScript);
var sheet = file.getSheetByName(sheetName);

//funkcje właściwe
if(type == "addRow"){
//kod właściwy
sheet.insertRowsBefore(currentRow, 2);
var rangeToCopy = sheet.getRange(currentRow + 2, 1, 2, sheet.getMaxRows());
rangeToCopy.copyTo(sheet.getRange(currentRow, 1));

sheet.getRange(currentRow, 2,1,6).clearContent();
sheet.getRange(currentRow, 12,1,sheet.getMaxColumns()).clearContent();
sheet.getRange(currentRow,50).setValue("d");

sheet.getRange(currentRow, 2,1,6).setBackground("white");
sheet.getRange(currentRow + 1, 12,1,26).setBackground("white");

var protection = sheet.getRange(currentRow, 1,1,1).protect();
protection.addEditor("[email protected]");
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
protection.setDomainEdit(false);
}

var protection = sheet.getRange(currentRow, 8,1,sheet.getMaxColumns()).protect();
protection.addEditor("[email protected]");
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
protection.setDomainEdit(false);}}