CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
PhilomathJ
PhilomathJ

Reputation: 21

In Google Sheets, how to lock or prevent sorting

I have a shared Sheet that multiple people edit. The data is in a very specific order for our needs. I need to prevent people from sorting this sheet which messes up the data. I am open to an Apps Script or settings-based solution.

Protecting the sheet really isn't an option as so many ranges take edits from numerous people

Ideally, the sort menu item will be grayed out. Secondarily, override the sort button to have no action. Alternatively, have code to do something like cancel the sorting action or reverse the sort.

Upvotes: 1

Views: 10832

Answers (2)

Aurelien Orelse
Aurelien Orelse

Reputation: 11

I just found out how to limit some users to sort the entire sheet or even sort a column. If you want to restrict user "JOHN" you need to :

  1. Give him editor rights to the sheet.

  2. Create an empty column (let's say it's column A)

  3. Click Data. Protected sheets and ranges. Restrict JOHN from editing column A.

When John will try to sort the sheet or a column, instead of being sorted, it will create a temporary filter because he has not access to the entire sheet :)

You can also deny him access to the sheet's first line (or create an empty one), so he cannot filter any column (you need to have access to the entire range to do so)

Let me know if that works for you as well!

Upvotes: 1

Mark B
Mark B

Reputation: 423

How about this, it counts the number of rows and locks them from sorting

function freezeRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getDataRange().getValues().length;  //gets # of rows

  ss.setFrozenRows(range);//freezes all rows
}

can use the onOpen and onEdit triggers so whenever it's opened it's ran, and also runs when anything is edited.

Upvotes: 0

Related Questions