Автоформат ячейки в google spreadsheets с помощью App Script

Форматирование поддерживается только для чисел, дат и валют.

Данная задача вскрывает некоторые недостатки облачных сервисов. Возможно, конечно, что add-in от Microsoft с этим справиться может.

Недостаток первый. Отслеживание событий пользователя

Как узнать, что пользователь ввел данные. Ок. Проблема не кажется такой страшной, когда он это сделал через клавиатуры. А если он добавил их из буфера обмена? Из контролируемого буфера обмена? А какая это была операционная система? А браузер? Пользователь изменил одну ячейку или целый диапазон? Он ввел только набор ожидаемых данных (там были номера телефонов) или в этот раз это не было связано с телефонами вообще? Триггер onEdit в этом случае - мертвому припарка. Пользователь добавит массив данных 1000х1000 и все загнется или вообще не сработает.

Недостаток второй. Ошибочное представление о назначении Таблиц

Таблицы - инструмент. Как и шуруповерту, требующему качественный метиз, Таблицам требуются структурированные, адекватные данные. Шуруповерт будет закручивать и откровенное г...о, но насколько это надежно?

Недостаток третий. Неверное сравнение настольных и облачных сервисов

Этот недостаток приводт к общей неверной оценке ситуации.

Что делать и как дальше жить?

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

0 триггер onEdit()

function onEdit(e) {
    try {
        if (!e.value || typeof e.value === 'object' || e.range.columnStart !== 4 || e.range.getSheet().getName() !== 'Лист')
            return;
        e.range.setValue(e.value.replace(/[^\d]/g, '').replace(/^./, '7'));
        e.source.toast(JSON.stringify(e.value, null, ' '));
    } catch (err) {
        e.source.toast(err);
    }
}

1

Как тут некоторые советовали добавить какой-то там нелепый функционал и заменить функцию

=ARRAYFORMULA(IF(LEN(REGEXREPLACE(A2:A;"[^\d]";""))=11;REGEXREPLACE(REGEXREPLACE(A2:A;"[^\d]";"");"^.";"7");""))

на скрипт, то это замена шила на мыло. Вы просто тратите время.

2

Другой вариант, создание боковой панели и добавление в активную ячейку данных через нее. Это более надежный, но очень непрактичный способ. Необходимо дождаться загрузки панели, указать курсор и прочее...

3

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

4

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

Решил задачу следующим скриптом:

function onEdit(e) { 
var  col = SpreadsheetApp.getActiveSheet().getActiveCell().getColumn(); 
if (col == '4') {    //номер столбца с телефонами     
  var range = e.range; 
  var value = range.getValue(); 
  value = value.replace(/[^\d.]/ig, '');   
  range.setValue(value); 
 } 
}

Но при вставке номера с плюсом вначале, скрипт правильно не отрабатывает. Данные воспринимаются как формула и в ячейку пишется ERROR!, который регулярным выражением заменяется, оставляя пустую ячейку. И еще подмену 8 на 7 нужно дописать.