Help with onChange please

[Glen Bodie]

I want to prevent people other than myself from adding, deleting or hiding certain rows and columns on a sheet. Some questions, driven at least in part by the convoluted online documentation!!!

1 - I see that INSERT_ and REMOVE_ ROW and COLUMN are valid changeType enums but I can't find a complete list of that enum to know if there is a HIDE_ type as well.

2 - I'd also like to prevent changes to the View > Freeze setting but don't know if onChange gets triggered by that.

3 - I can install the onChange trigger through the onOpen function and in the onChange trigger I can get the event object (e). Is there any documentation of the members of the event object? I need to grab the e.range object (if there is one) and see what the row and column numbers are.

4 - does the onChange fire BEFORE the action is completed or AFTER? And if it is after then can I "undo" the change? And if it is before then can I prevent the change from happening?

5 - If I determine that a "bad thing" has been done (or attempted) then I want an "authorization" step to see if I should allow it or not. I was thinking to use the e.getEmail() function and compare to my email to prevent anyone else from making such changes. Is that a good design choice or can you suggest better?

[Glen Bodie]

Apparently this is a difficult bunch of questions!

Is there a better place for me to be asking these questions? 

Is there are language reference that is more complete / better than the "Google Sheets Training and Help" at https://support.google.com/a/users/answer/9282959 ?

[Alan Wells]

The Sheets "On Change" types are:

EDIT, INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, FORMAT, or OTHER

See: https://developers.google.com/apps-script/guides/triggers/events#change

The "OTHER" type might be the most interesting depending on what you want to detect.

You'll probably need to do some trial and error testing.

I haven't done much with protecting ranges, so I can't help much there.

Are you familiar with StackOverflow?

Do you have any code yet?

[Glen Bodie]

That events#change is the ONLY documentation I've found and it's pretty insufficient!

I saw the OTHER but without the members of the error object I havent a chance of figuring out what the OTHER might be telling me.

What is StackOverflow?

Sample code --->

// Purpose: to notice whenever anyone tries to add, delete or hide rows and columns

// In most cases I want to prevent others from doing it, but let me do it

function myFunction(e) {          // catch any onChange events, e is the event object

  if (typeof e != "object") Logger.log('Ooopps - no error object passed to the function');

  let r = e.range;

  if(typeof r != "undefined") {   // as long as we have range data to test

    if (AND(OR(e.changeType == INSERT_ROW, e.changeType == REMOVE_ROW), r.row <  12)) {

      testAuth(e);                // only I can do these things to the top 11 rows

    } else if (OR(e.changeType == INSERT_COLUMN, e.changeType == REMOVE_COLUMN)) { 

      testAuth(e);                // only I can do these things to any of the columns

    } else if (e.changeType == OTHER) {

      // figure out how to determine what OTHER change was made and if it is OK

    } else {

      // any other changes such as EDIT, FORMAT or GRIDs are OK, nothing to do

    }

  }

}

function testAuth(e) {

  if (e.user.getEmail().toUpperCase() == 'MY_EMAIL_ADDRESS') {

    Logger.log('Glen changed something - OK');

  } else {

    // user did one of the bad things and it wasn't me doing it so prevent it

    Logger.log('Something changed but not by Glen - prevent it!');

  }

}

[Michael Ellis]

Glenn,

I'm quite interested in your question as I've had similar needs in the past.

Stackoverflow.com is a great place to find a community of people who ask and answer questions about coding in many different settings.   I have found it immensely valuable.   Here's an example link of one problem I searched for and found:

https://stackoverflow.com/questions/16840038/easiest-way-to-get-file-id-from-url-on-google-apps-script/16840612#16840612

Explore the topics of interest to you there.  It's extensive.

Best of luck finding  your answer.  I'm watching the outcome for my own education.

Mike

[Glen Bodie]

Posted my query on StackOverflow and getting some response there but as yet no decent answers to my problems. Not going to monitor this forum further.

[Clark Lind]

In a case like this, I typically keep things separate; either keeping my protected data on a different tab (or spreadsheet) and import to a common sheet, or maybe create another tab that has a bunch of rules. Then the code can refer to the rules and respond to changes on the main tab if a rule is broken (and reset/undo the unauthorized change). Another solution I have done, when I really want to get tyrannical about what happens to a sheet (like with children who can [and do] find ways to make your day unpleasant), is to protect the whole tab and use an html modal to enter/edit data. So, they can select a row they want to edit, click edit from the custom menu (or button if you prefer that), and only the row in question becomes editable when the modal data is submitted. If, in your case, you want rows 1-11 protected, you simply check for that. 

You really can't go half way, either go full tyrant and protect your data, or open the flood gates and let it be potentially messed up. You either trust, or don't: there is no 'partial trust' when it comes to humans fat-fingering data. Come on, how often does McDonalds mess up an order? Humans can (and will) make mistakes, and they will do it often.

Also, keep in mind what the original purpose of a spreadsheet was: a tool for making calculations, not all the other things we use sheets for.

As an afterthought (as I was about to press Post Message), have you tried using named ranges? I haven't played much with them, but you might be able to watch for any changes within any named range(s) and react accordingly.

On Thursday, September 30, 2021 at 2:27:15 AM UTC-4 glen....@gmail.com wrote: