How can a script disable the transfer of a cell, but at the same time allow copying and clearing the cell?

Question

Google Sheets

Tell me, please, how can a script prevent the transfer of cells in a sheet, but at the same time not prohibit copying, deleting, replacing with other text, etc.? The problem is related to formulas, that when moving a cell to a cell referenced by some other cells (for example, "=$A$1"), the formulas become "broken" (instead of "=$A$1" it becomes "=#REF !"). Therefore, it is necessary to make it so that it is not possible to move cells by dragging to other cells (if you copy a cell and paste in place of another, then there are no problems).

Here is an example. There are two lines in the photo. The first column contains the text, and the second and third columns refer to this text by reference.


But if the cell in the first column is moved to the place of the cell in the second line (to which the second and third columns of this line referred), then the formulas of the second line become broken.


At the same time, if the cell is simply copied and pasted in place of the cell in the second line, then no broken links arise


. Thank you!

Answer 1

In general, I agree with Alexander.
The script really can't do what you want
. But there is one function that can indirectly solve your problem. This is the INDIRECT or INDIRECT function.
If instead of =$A$1 you write =INDIRECT("A1"), then the formula will always take values ​​from cell A1, even if it is moved or, for example, a column or row is added

Answer 2

This is a misconception about Tables and Scripts.
Scripts are a server application for interacting with Table data , not for managing their interface .
Whatever you can do from the Tables menu, you can also do from scripts. The easiest way is to present Tables as an independent web application without the possibility of automating its options, and Scripts as a data processing service that is contained in Tables. This is not entirely true, but on closer examination it becomes clear that any thoughts in the direction of drop-down lists , reactions to user actions , etc. devoid of any decent examples of using Scripts.
Only Google can do this. Therefore, the answer to your question is the advice to submit an improvement proposal directly from the Tables menu.
Added

• Good comment from @ForestAndGarden
  

Answer 3

ilya kanavin , thanks for the option with =INDIRECT("A1"). It really works, but the performance of the table drops very much, since the INDIRECT function is launched every time any cell in the sheet changes, and there are more than tens of thousands of such links in it. However, after considering Alexander Ivanov
's comment (thank you for a competent explanation), the idea arose to create a solution not on the interface side, but on the server side. What if we make a script on the server side, which, for example, goes through the table (predefined rows) every 2 hours, finds cells with formulas and simply overwrites the correct formulas in them? If we take our example, in which cell A1 was shifted to cell A2 and the links went astray: in terms of formulas, it looks like this:


In it, the formulas of the first row now point to the cell of the second row (which is not true for our task), and the formulas of the second row have become broken (which is also not true for our task).
Therefore, you can make a script that on the server side went through these two lines and did the following:

it will look like this:

the result is this: the formulas in the first line again refer to cell A1, and the formulas of the second line to cell A2.
If we take a more approximate example of our task with several thousand links, then we have a block of cells with initial information B2:F4 and then many columns and rows of blocks with cells similar in size that refer to these initial cells

the script will need to run through all the cells of these rows and replace the formulas with new formulas according to the scheme = "=" + "Name of the column referenced by the cell" + "Row number in which this current cell is located"
Everything is clear with the row number , it is calculated from the processed cell itself. And with the column number is more complicated, here you need to think about how to calculate it.
What do you think about this option?