Google Apps Script - Is it possible to allow change to some cell in Sheets only through GoogleAppScript?

2
votes

My making a catalog in Google SpreadSheet. My spreasheet has a table where the user can add information. This table is dynamically created through a Script that gets the data from the database sheet.

The user can edit some cells in the table and click on a button that saves the changes back into the database sheet.

I want to limit the cells he can edit manually, but I can´t protect theses cells, because there is a script that modifies it (since the table is dynamically generated through a script).

Is there a way to protect cells so that they can only be edit by a script?

I can not just protect some ranges, because it is the user that runs the script that modifies the cells. If I protect the ranges, the user can´t change the range either manually or using the script. I want a way to protect the range against manual input, but allow the script, that the user runs (not the spreadsheet owner), to change the cells.

Thank you

2
google-apps-scriptgoogle-sheets
Possible duplicate of protecting range in anonymously shared doc not working as expected – Rubén
I believe my question has not been answered yet. – Ciro Shia
Possible duplicate of How to allow onEdit function to affect protected cell in a Google Sheet? – josle
You could try using an installable trigger - see stackoverflow.com/questions/36871241/… – josle

2 Answers

1
votes

I have solved this issue by the following steps

printsht.getRange("A1:Q22").setDataValidation(null);

Do whatever processing in the range

printsht.getRange(1,1,22,17).setValues(prin);

After all the processing and setValues etc

// block editing after printing var cell = printsht.getRange( "A1:Q22"); var rule = SpreadsheetApp.newDataValidation().requireTextEqualTo('$$').setAllowInvalid(false).setHelpText("Do not edit in print sheet").build(); cell.setDataValidation(rule);

Now no one can edit the range manually. All the formula etc will work normally.

But, if the user is having edit rights, he can easily remove the validation from the system menu. So, it is not foolproof.

0
votes

Instead of using a bounded script use a stand alone script to create a web app that runs with the owner permissions. This because the bounded scripts runs this the permissions of the user that execute the script.

To help you user to execute the webapp, I think that you could create a user dialog or side panel that will display the link to the webapp.