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

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

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

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

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

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

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

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

Есть идеи?

Источник

3 ответа

Решение

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

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

РЕДАКТИРОВАТЬ: выше, больше не работает. Моя новая формула для 'Ref'!A1

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

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

= some_new_function('Ref'!C45)

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

Источник

(Из моего ответа по веб-приложениям.) Можно получить ссылку на переданный диапазон, проанализировав формулу в активной ячейке, которая является ячейкой, содержащей формулу. Это делает предположение, что пользовательская функция используется сама по себе, а не как часть более сложного выражения: например, =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;
}
Источник

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

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

Источник
Другие вопросы по тегам