Как скриптом запретить перенос ячейки, но при этом разрешить копирование и очищение ячейки?

Question

[Александр]

Подскажите, пожалуйста, как можно скриптом запретить перенос ячеек в листе, но при этом не запрещать копирование, удаление, замещение другим текстом и т.д.? Проблема связана с формулами, что при переносе ячейки на ячейку, на которую ссылаются какие-либо другие ячейки (например, "=$A$1"), формулы становятся "битыми" (вместо "=$A$1" там становится "=#REF!"). Поэтому и нужно сделать так, чтобы не было возможности передвигать ячейки перетаскиванием на другие ячейки (если копировать ячейку и вставить на место другой, то проблем не возникает).

Вот пример. На фото две строчки. В первой колонке указан текст, а вторая и третья колонка ссылаются на этот текст по ссылке.


Но если ячейку в первой колонке передвинуть на место ячейки второй строчки (на которую ссылались вторая и третья колонка этой строчки), то формулы второй строки становятся битыми.


При этом, если ячейку просто скопировать и вставить на место ячейки во второй строке, то никаких битых ссылок не возникает


Спасибо!

Comments

[Александр]

Если вам защититься от себя нужно, то только внимательностью и крепким усвоением принципов работы.

Если от пользователей, то дополнительным листом с расчётами, используя абсолютно абсолютные ссылки на ячейки.

Answer 1

[ilya kanavin]

В целом, я согласен с Александром.
Скрипт, действительно, не может сделать то, что вы хотите

Но есть одна функция, которая косвенно может решить вашу проблему. Это функция ДВССЫЛ или INDIRECT.
Если вы вместо =$A$1 напишете =INDIRECT("A1"), то формула всегда будет брать значения из ячейки A1, даже если она будет передвинута или, например, будет добавлен столбец или строка

Comments

[Александр]

=INDIRECT("A1")

Это и есть один из вариантов абсолютно абсолютной ссылки.

[Alexander Ivanov]

Не реклама, вы есть у нас в чате https://t.me/googleappsscriptrc? Приходите.

[ilya kanavin]

Alexander Ivanov, да уже нашел и подписался, спасибо)

Answer 2

[Alexander Ivanov]

Это неверное представление о Таблицах и Скриптах.

Скрипты - серверное приложение для взаимодействия с данными Таблиц, а не для управления их интерфейсом.

Все что вы можете сделать из меню Таблицы, вы можете сделать и из скриптов. Проще всего представить Таблицы как самостоятельное веб-приложение без возможности автоматизации его опций, а Скрипты как сервис обработки данных, которые содержатся в Таблицах. Это не совсем так, но при детальном изучении становится понятно, что любые размышления в сторону выпадающих списков, реакций на действия пользователя и т.п. лишены каких-либо приличных примеров с использование Скриптов.

Только Гугл может это сделать. Поэтому ответом на ваш вопрос будет совет отправить предложение по улучшению непосредственно из меню Таблиц.

Добавлено

• Хороший комментарий от @ForestAndGarden
  

Answer 3

[Александр]

ilya kanavin , благодарю за вариант с =INDIRECT("A1"). Он, действительно работает, однако очень сильно падает производительность таблицы, так как функция INDIRECT запускается каждый раз при изменении любой ячейки в листе, а таких ссылок в нем более десятка тысяч.

Однако обдумав комментарий Alexander Ivanov (благодарю за грамотное объяснение), возникла идея создать решение не на стороне интерфейса, а на стороне сервера.

Что если сделать скрипт на стороне сервера, который, например, каждые 2 часа проходит по таблице (заранее заданным строкам), находит ячейки с формулами и просто перезаписывает в них правильные формулы? Если брать наш пример, в котором ячейку А1 сдвинули на ячейку А2 и ссылки сбились:

с точки зрения формул выглядит так:

В нем формулы первой строки теперь указывают на ячейку второй строки (что для нашей задачи неверно), а формулы второй строки стали битыми (что для нашей задачи также неверно).

Поэтому можно сделать скрипт, который на стороне сервера прошелся по этим двум строкам и сделал следующее:

это будет выглядеть вот так:

результат получился такой: формулы в первой строке вновь ссылаются на ячейку А1, а формулы второй строки на ячейку А2.

Если брать более приближенный пример к нашей задачи с несколькими тысячами ссылок, то у нас есть блок ячеек с исходной информацией B2:F4 и далее множество аналогичных по размеру колонок и строк блоков с ячейками, которые ссылаются на эти исходные ячейки


скрипту необходимо будет пробежать по всем ячейкам этих строк и заменить формулы на новые формулы по схеме знак равно "=" + "Название столбца, на который ссылается ячейка" + "Номер строки, в которой находится эта текущая ячейка"

С номером строки тут все понятно, он вычисляется из самой обрабатываемой ячейки. А с номером столбца посложнее, тут надо подумать как его вычислять.

Что скажете насчет такого варианта?

Comments

[Alexander Ivanov]

Это очень-очень хороший вариант в текущей объективной реальности. Как минимум, так точно делают. Еще вариант, просто перезаписывать формулы с листа-шаблона.

[Александр]

Да, вариант с листом-шаблоном тоже очень хороший. Не нужно будет вычислять колонки.
Александр, не подскажете как можно скриптом из заданного диапазона строк листа-шаблона перенести все ячейки с формулами на аналогичные ячейки основного листа при условии, что в заменяемой ячейке тоже формула находится? Основной лист и шаблонный лист могут расширяться вправо (то есть количество колонок может увеличиваться), а количество и номера сток с заменяемыми ячейками всегда одинаковое

[Владимир]

Александр, сделайте именованные диапазоны "Source" и "Destination" и копируйте из первого во второй.

[Александр]

Александр: очень сильно падает производительность таблицы, так как функция INDIRECT запускается каждый раз при изменении любой ячейки в листе

Спасибо за дельное замечание.