Disable Sort functionality in a Google Sheet

[VickiMaloney]

Does anyone know of a way to disable the sort functionality in a Google Sheet.  I have a Google Sheet that has a formula in A2 that populates all the date for Column A.  Then several columns contaain Vloookups() formula based on column A.  Then I have free form fields for users to put additional data in.   The users need to have Edit access to the sheet to fill in data for the data in Column A, so I can't just give them View access, which restricts sorting.  They also need to be able to filter.  I tried locking the specific data columns/rows but then they can not filter.

=ARRAY_CONSTRAIN(ARRAYFORMULA(UNIQUE(FILTER(HPAMServers!$A2:$L,HPAMServers!$L2:$L=$R$1))),1000,1)

When someone sorts the data, on any column, the formula in A2 gets moved to a lower row and then my data in column A is messed up, which then in turn messes up the data in the columns that have the VLOOKUP formulas.

I am already using App Scripts in the spreadsheet, so it would be easy enough to add script in to it to not allow sorting, but I can not figure out what commands to use or if it is even possible.

I want to completely disable the "Sort A-->Z", "Sort Z-->A", "Sort by color" that is available from the "Filter" dropdown.

I want to completely disable the "Sort sheet" and "Sort range" that is available in the "Data" Menu.

[Axel_Pro · Accepted solution]

Good afternoon!
It seems to me that these features in Google Sheets are not disabled.

Put the formula in the column heading so it will always be on top when sorting:

={"Column header"; ARRAY_CONSTRAIN ( ARRAYFORMULA ( UNIQUE ( FILTER ( HPAMServers!$A2:$L , HPAMServers!$L2:$L = $R$1 ) ) ) , 1000 , 1 )}

P.S.
It seems to me that the approach to organizing work with data in Google Sheets is a little erroneous.

Column A - will be filled with an array formula (dynamically).
Users enter data into some columns.

Let's imagine that the data on the HPAMServers sheet has changed - it has become smaller / larger / their sorting has changed.

At the same time, column A was recalculated according to the formula, the columns with Volocup too, but the data entered by the user remained in its place.

It's a mistake to work with data in Google Sheets - to use dynamic array columns on the same sheet where users enter data.

In my opinion, this can be done in reports - where only viewing / analysis / filtering.

[Axel_Pro · Accepted solution]

Good afternoon!
It seems to me that these features in Google Sheets are not disabled.

Put the formula in the column heading so it will always be on top when sorting:

={"Column header"; ARRAY_CONSTRAIN ( ARRAYFORMULA ( UNIQUE ( FILTER ( HPAMServers!$A2:$L , HPAMServers!$L2:$L = $R$1 ) ) ) , 1000 , 1 )}

P.S.
It seems to me that the approach to organizing work with data in Google Sheets is a little erroneous.

Column A - will be filled with an array formula (dynamically).
Users enter data into some columns.

Let's imagine that the data on the HPAMServers sheet has changed - it has become smaller / larger / their sorting has changed.

At the same time, column A was recalculated according to the formula, the columns with Volocup too, but the data entered by the user remained in its place.

It's a mistake to work with data in Google Sheets - to use dynamic array columns on the same sheet where users enter data.

In my opinion, this can be done in reports - where only viewing / analysis / filtering.

[VickiMaloney · Accepted solution]

@Axel_Pro  - Thank you for the reply.  I have changed my formula and moved it to A1. I do understand the concern about the User input data in the sheet as it has caused me an issue a time or two.  The HPAMServers sheet is fairly static but,  I will see what I can do to move that data to the HPAMServers Sheet. And then just report on it in the tab where I have the array formula.  Thank you!

[OptimiX_XcrY]

This is the Appsheet community, so I don't think your thread belongs here.

In google sheets, from what I remember , you can block column A from being editable/sortable, so maybe try that

[Marc_Dillon]

I agree with both other posters. But also, no I don't believe it is possible to disable sorting like that.