How to roll-back change events to a Google sheet or disallow only change events but allow edit rights to users?

Question

Trying to build a dashboard wherein, in certain sheets users need to be able to add rows, but shouldn't be able to change values of the preset cells. If I protect those cells, they cant add rows, if I dont protect the sheet, and revert changes to preset cells using a Google Apps Script onEdit trigger, they are able to insert rows, and cant change values, but that leaves my sheet open to unforseen modifications, like inserting columns, inserting more rows than required, changing formatting, etc.

Is there a way to let them add rows but not do anything else to the sheet except the limited cells they need to edit?

Answer 1

I see this issue/request often, you are trying to use Google Sheets in a form-like manner where users should only be permitted to do X actions (some people use Google Forms to feed into the spreadsheet for similar problems).

1. One idea here is to create a button to add a row. By default you can make everything protected. When the button is pressed, Google Apps Script can unprotect and create a row at that location. One issue with this solution is that buttons in Google Apps Script are like floating images - so you would need to create function names like "addRowAtSheet4". Here is what that button could look like, where ADD CITATION would create a new row on click:

Another issue is that a user that wants to damage your spreadsheet will be able to since the protections are done from the user's bound script and run from the user's account, which brings me to the second solution:

2. If you need true cell protection (assuming you are the spreadsheet owner), you can create a standalone webapp to do all of the admin stuff and lock everything down on the user side as desired. When the user clicks the button, a web request is issued from the GAS bound script of the spreadsheet to the published webapp, and in the webapp you add the row on behalf of the user from your own account (i.e., "Execute the app as: Me"). Something like this, where the parameters sent are the row to be added, for example:

Note that if there can be multiple simultaneous users adding rows on the same place, you may like to use LockService.