Вопрос или проблема
Мне нужна пользовательская функция, которая может автоматически форматировать единицы данных. Превращая 1000 м в 1 км. Проще говоря, я не могу заставить функцию форматировать ячейку, в которой она находится.
Я планирую добавить условия позже для форматирования числа в зависимости от входного значения. Цель состоит в том, чтобы воспользоваться свойствами пользовательского форматирования, такими как формат ‘0.0,,”M”‘, который возвращает 1230000 в виде 1.2M, но при этом сохраняет данные 1230000. Эти свойства были бы гораздо более полезными, если бы вам не приходилось вручную менять формат каждый раз, когда вы хотите другой формат.
Я также не хочу вручную менять код, чтобы подстроиться под разные диапазоны.
Вот упрощённая функция, которую я хочу заставить работать. Есть какие-нибудь советы?
function NEWFORMAT(input) {
var sheet = SpreadsheetApp.getActiveSheet();
var cell = sheet.getCurrentCell();
cell.setNumberFormat('#,##0,, "MV"');
return input;
}
Пользовательская функция не может использовать методы “set”, такие как Range.setNumberFormat().
Попробуйте вместо этого onEdit(e) простой триггер, вот так:
function onEdit(e) {
if (!e) throw new Error('Пожалуйста, не запускайте функцию onEdit(e) в окне редактора скриптов. Она запускается автоматически, когда вы редактируете таблицу. См. https://stackoverflow.com/a/63851123/13045193.');
if (!Number(e.value)) return;
e.range.setNumberFormat('#,##0,, "MV"');
}
Ответ или решение
Как создать функцию для автоматического форматирования данных в ячейках Google Sheets
Вам требуется пользовательская функция для автоматического форматирования единиц данных в Google Sheets, например, преобразование 1000 метров в 1 километр. Однако, как вы заметили, пользовательские функции в Google Apps Script не могут использовать методы "set", такие как Range.setNumberFormat(). Вместо этого вы можете использовать триггеры, чтобы достичь вашей цели. Поскольку вы рассматриваете использование функции onEdit(e), давайте рассмотрим, как это можно реализовать.
Проблема с пользовательскими функциями
Пользовательские функции в Google Sheets, такие как те, что вы пытаетесь создать, работают в ограниченном контексте. Они могут выполнять вычисления и возвращать значения, но не могут изменять формат ячейки, в которой они находятся. То есть метод setNumberFormat() недоступен для пользовательских функций.
Решение: Использование триггера onEdit
Для автоматического форматирования значений в ячейках можно использовать триггер onEdit(e), который автоматически срабатывает при изменении данных в таблице. Ниже приведен пример кода, который вы можете использовать для настройки автоматического форматирования.
Пример кода
function onEdit(e) {
// Проверка на наличие события редактирования
if (!e) throw new Error('Функция onEdit(e) запрашивается неправильно.');
// Проверка, является ли введенное значение числом
if (!Number(e.value)) return;
// Применение формата к ячейке, где произошло изменение
e.range.setNumberFormat('#,##0,, "M"');
}Принцип работы кода
-
Проверка события редактирования: Если
eне передается, будет выдано сообщение об ошибке. Это поможет избежать ручного запуска функции в редакторе. -
Проверка значения: С помощью условия
if (!Number(e.value)) return;мы гарантируем, что функция сработает только для числовых значений. Если введенное значение не числовое, функция завершит выполнение. -
Форматирование ячейки: Метод
e.range.setNumberFormat('#,##0,, "M"')применяется к ячейке, где произошло редактирование. Это позволяет отформатировать значение, закодированное в миллионах, без изменения исходного числа.
Проверка и тестирование
После добавления кода в редактор скриптов Google Sheets важно убедиться, что он работает корректно. Введите числовое значение в любую ячейку, и вы должны увидеть, что формат ячейки автоматически изменяется.
Будущие улучшения
В соответствии с вашими планами, вы можете добавить дополнительные условия в код, чтобы форматировать число в зависимости от введенной величины. Это может включать разные единицы измерения (например, километры, миллиарды и т.д.), что будет более универсальным и полезным.
Заключение
Используя триггер onEdit(e), вы можете легко настраивать автоматическое форматирование ячеек в Google Sheets, соответствующее вашим требованиям. Это решение эффективно обходит ограничения пользовательских функций, предлагая гибкость для вашего проекта. Не забывайте периодически проверять актуальность вашего кода и адаптировать его под ваши новые требования.