Особенности копирования формул

Варианты копирования формул

Существует 4 основных способа копирования формул при помощи Google Apps Script:

1. Комбинирование методов  getFormulas()/setFormulas()
2. Использование универсального метода  copyTo()
3. Комбинирование методов getFormulaR1C1()/setFormulaR1C1()
4. Опосредованное применение комбинирования методов  getFormulas()/setValues()

Все эти способы вытекают из возможностей использования методов класса Range.

Примеры

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

Из ячейки F3 необходимо сопировать формулу в F4. В данном месте стоит обговорить некоторые детали. Первый, третий и четвертый способы очень похожи. Опосредованность четвертого заключается в том, что при вставке данных Таблица автоматически определяет контент как формулу и превращает ее в таковую. Этот способ полезен тогда, когда необходимо заполнить Таблицу массивом данных, и для ускорения процесса возможно использовать опосредованность, чтобы избежать дополнительных вызовов при вставке.
Т.к. способы 1 и 2 заведомо могут вернуть разные результаты, в пример добавлена колонка G3, и для сравения она будет копироваться в ячейку G4.
Основная композиция комбинированного способа

// Комбинированный способ 1
  function combinedMethod(sheet){
    var from = sheet.getRange("F3");
    var to = sheet.getRange("F4");
    to.setFormula(from.getFormula());
  }
  

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

// Комбинированный способ 2
  function combinedMethod(sheet){
    var from = sheet.getRange("F3");
    var to = sheet.getRange("F4");
    to.setFormulaR1C1(from.getFormulaR1C1());
  }
  

Универсальный метод copyTo() требует дополнительных обязательных параметров, как тип вставки и признак необходимости транспонирования массива перед вставкой. В даненом случае транспонирование не требуется, поэтому значение этого аргумента будет false. За тип вставки выбрано значение SpreadsheetApp.CopyPasteType.PASTE_FORMULA, которое указывает, что копирование должно произойти с применением правил, принятых в Табличных процессорах. К таким правилам относят смещение диапазонов и учет абсолютных значений адресов.

// Универсальный метод
  function universalMethod(sheet){
    var from = sheet.getRange("G3");
    var to = sheet.getRange("G4");
    var copyPasteType =
      SpreadsheetApp.CopyPasteType.PASTE_FORMULA;
    var transposed = false;
    from.copyTo(to, copyPasteType, transposed);
  }
  

Выводы

Результатом работы программы будет копирование двух ячеек с разными итогами

Как видно, при универсальном способе копирование формул произошло с ожидаемым результатом. Так же важно заметить, что форматирование, а значит и условное форматирование" и типы данных, сохранили свое состояние при изменении данных ячеек.

• setFormula/setValues - добавляют формулу "как текст"
• setFormulaR1C1 - добавляет формулу с учетом относительности указанных диапазонов
• copyTo - сильно зависит от собственных настроек. Может добавлять как сами формулы, так и их текстовые выражения для последующей автоматической интерпретации

Направление диапазонов в copyTo

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

  var from = sheet.getRange("A1:C1");
  var to = sheet.getRange("B8");

Дополнительно

• Так же можно проверить, как работает метод copyTo(), если будет задан абсолютный адрес в копируемой формуле, например, =JOIN(" ";C$3:E3)
• Интересно наблюдать за разницей работы метода copyTo(destination) без дополнительных параметров. Каков будет результат?
• Получить ссылку на полный пример из поста можно в сообществе G+  Google Apps Script. Russian или чате  @googleappsscriptrc