Передача ссылок на ячейки в функции электронных таблиц

Когда я вызываю функцию электронной таблицы, скажем int(f2), функция работает со значением в ячейке. Если cell("F2") содержит 3.14159, результат будет равен 3. Но когда я называю другой тип функции - например: row(f8) - функция принимает ссылку на ячейку, а не значение в этом случае возвращает 8.

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

Я могу передать строку и использовать getRange(), но, если я перемещаю или обновляю ячейки на листе, строки не будут меняться.

Действительно простой пример:

function GetFormula(cellname) {
  return SpreadsheetApp.getActiveSheet().getRange(cellname).getFormula();
}

С этим в моем личном коде я могу получить формулу в C4 следующим образом: =GetFormula("C4")

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

Любые идеи?

Ответы

Ответ 1

Я работал над этим несколько месяцев назад и придумал очень простой kludge: создайте новый лист с названием каждой ячейки в качестве его содержимого: Ячейка A1 может выглядеть так:

= arrayformula(cell("address",a1:z500))

EDIT: Вышеизложенное больше не работает. Моя новая формула для 'Ref'! A1 -

= ArrayFormula(char(64+column(A1:Z100))&row(A1:Z100))

Назовите лист "Ref". Затем, когда вам нужна ссылка на ячейку в виде строки вместо содержимого, вы используете:

= some_new_function('Ref'!C45)

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

Ответ 2

(Из моего ответа в веб-приложениях.) Можно получить ссылку на пройденный диапазон, разобрав формулу в активной ячейке, которая является ячейкой, содержащей формула. Это делает предположение, что пользовательская функция используется сама по себе, а не как часть более сложного выражения: например, =myfunction(A1:C3), а не =sqrt(4+myfunction(A1:C3)).

Метод также поддерживает ссылки на другие листы, такие как =myfunction(Sheet2!A3:B5) или =myfunction('Another Sheet'!B3:G7).

В качестве демонстрации эта функция возвращает первый индекс столбца пройденного диапазона. Этот бит находится в самом конце функции; большая часть из них связана с извлечением диапазона.

function myfunction(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i)[1].split('!');
  try {
    if (args.length == 1) {
      var range = sheet.getRange(args[0]);
    }
    else {
      sheet = ss.getSheetByName(args[0].replace(/'/g, ''));
      range = sheet.getRange(args[1]);
    }
  }
  catch(e) {
    throw new Error(args.join('!') + ' is not a valid range');
  }

  // everything so far was only range extraction
  // the specific logic of the function begins here

  var firstColumn = range.getColumn();  // or whatever you want to do with the range
  return firstColumn;
}

Ответ 3

Когда вы передаете диапазон в пользовательскую функцию, параметр имеет тип Range, который затем можно использовать для извлечения значений, и др.

Изменить. Это неверно.