Script для суммирования данных, не обновляющих

У меня есть таблица электронных таблиц Google. у него есть лист для каждого месяца, каждый лист содержит шесть блоков столбцов, по одному блоку на каждого клиента.

Я создал сводный лист, который идет и получает общее количество для каждого клиента и отображает его в списке:

function getClientTotals(sheetname, colcount)
{  
  colcount = colcount ? colcount : 6;
  var res;      
  var ss = SpreadsheetApp.openById('myid_goes_here');
  if(ss)
  {
    res = [];
    var totrow = ss.getRange(sheetname + '!A1:ZZ1').getValues()[0];
    for(var i = 0; i < totrow.length; i += colcount)
    {
      res.push([totrow[i], totrow[i + colcount - 1]]);
    }
  }   
  return res;
}

Я только что добавил ячейку к моему сводному листу, содержащему =getClientTotals($C$7,$C$8), который передает имя листа для месяца и количество столбцов для каждого клиента (в случае модификаций схемы).

Все работает отлично, однако при изменении исходных данных он не обновляется. Я добавил триггер onEdit; нет радости. Он обновляется, если вы перейдете в редактор script и нажмите "Сохранить", но это не полезно. Я что-то пропустил?

Ответ 1

Вам не хватает функции быстрого кэширования . Он работает следующим образом:

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

Учитывая это предположение, они могут оценивать ваши функции только при изменении параметра. например.

Предположим, что у нас есть текст "10" на ячейке B1, затем на некоторой другой ячейке мы набираем =myFunction(B1)

myFunction будет оценен и его результат будет восстановлен. Затем, если вы измените значение ячейки B1 на "35", пользователь будет переоцениваться как ожидалось, а новый результат будет восстановлен в обычном режиме. Теперь, если вы снова измените ячейку B1 на исходный "10", повторной оценки не будет, исходный результат будет получен сразу из кеша.

Итак, когда вы используете имя листа в качестве параметра для его динамического извлечения и возврата результата, вы нарушаете правило кэширования.

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

Ответ 2

другое решение проблемы кэширования.

имеют фиктивную переменную в вашем методе. пройти

Filter(<the cell or cell range>,1=1)

в качестве значения этого параметра.

например.

=getValueScript("B1","B4:Z10", filter(B4:Z10,1=1))

выход фильтра не используется. однако он указывает электронной таблице, что эта формула чувствительна к диапазону B4: Z10.

Ответ 3

У меня была аналогичная проблема, создающая панель для работы. Решение Chamil выше (а именно, использование функции фильтра листов, переданное как значение фиктивной переменной в вашей функции) отлично работает, несмотря на более свежий комментарий от Arsen. В моем случае я использовал функцию для контроля диапазона и не мог использовать фильтр в том же диапазоне, так как он создал круговую ссылку. Поэтому у меня просто была ячейка (в моем случае E45 в коде ниже), в которой я изменил номер в любое время, когда хочу, чтобы моя функция обновлялась:

=myFunction("E3:E43","D44",filter(E45,1=1))

Как указал Шамиль, фильтр не используется в script:

function myFunction(range, colorRef, dummy) {
  variable 'dummy' not used in code here
}

Ответ 4

Я использую фиктивную переменную в функции, эта переменная ссылается на ячейку в электронной таблице, тогда у меня есть сценарий Myfunction(), который записывает число Math.Ramdon в этой ячейке. Эта "MyFunction" находится в службе триггеров (триггеры редактирования/текущего проекта), и вы можете выбрать разные триггеры событий, например, при открытии или по времени, там вы можете выбрать, например, период времени, от 1 минуты до месяца

Ответ 5

Что вы можете сделать, так это настроить другую ячейку где-нибудь в электронной таблице, которая будет обновляться каждый раз при добавлении нового листа. Убедитесь, что он не обновляется для каждого изменения, но только когда вы хотите выполнить расчет (в вашем случае, когда вы добавляете лист). Затем вы передаете ссылку на эту ячейку на свою пользовательскую функцию. Как уже упоминалось, пользовательская функция может игнорировать этот параметр.

Ответ 6

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

Мое предложение основано на том, что ваш script не имеет расширенной работы, такой как выборка URL, просто работа с данными, так как без чтения реальных данных я не могу дать точное решение с QUERY.

Что касается функции кеширования, упомянутой Henrique Abreu (у меня недостаточно репутации для комментариев непосредственно под его ответом), я тестировал и обнаружил, что:

выглядит не работает кеш, функция тестирования script показана ниже:

сумматор (база) { Utilities.sleep(5000); основание возврата + 10; }

применяя этот сумматор специальных функций() в листе, вызвав ячейку, а затем изменил значение этой ячейки вперед и назад каждый раз, когда я вижу сообщение о загрузке и общее время более 5 секунд. Это может быть связано с обновлением, упомянутым в этом Проблема GAS:

Теперь эта проблема исправлена. Пользовательские функции в New Sheets теперь контекстно-зависимы и не кэшируют значения как агрессивные.

проблема, упомянутая в этом разделе, остается, мое тестирование показывает, что лист Google пересчитывает пользовательскую функцию каждый раз ТОЛЬКО КОГДА
- значение DIRECTLY, вызываемое функцией, изменяется.
функция getCellValue (имя листа, строка, столбец) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sh = ss.getSheetByName(sheetName); return sh.getRange(строка, col).getValue(); }

Изменение любого значения в желтых ячейках приведет к перерасчету пользовательской функции; реальное изменение значения источника данных игнорируется функцией.
- Функция
- содержащая ячейку, изменяется на листе. ех. вставить/удалить строку/столбец сверху или слева.

Ответ 7

Я не хотел иметь фиктивный параметр. YMMV на этом.

1 Ячейка, представляющая собой "Список элементов", одна из которых "Обновить"

2 Сценарий с "onEdit", если ячейка "Обновить":

а) Очистить кеш документов

б) Заполнить кэш документов внешними данными (таблица в моем случае)

c) Для всех ячеек с моей пользовательской функцией getStockoData (...)

получить формулу
установить '= 0'
установить офулу

г) Установите ячейку в (1) со значением "Готово"

Это обновляет биты, которые вы хотите, НО НЕ БЫСТРО.

Ответ 8

Поскольку скрипт приложения Google является расширением JS, функции должны иметь возможность обрабатывать больше аргументов, чем определено в сигнатуре функции, или меньше. Так что если у вас есть какая-то функция, как

function ADD(a, b) {
  return CONSTANTS!$A$1 + a + b
}

тогда вы бы назвали эту функцию как

=ADD(A1, B1, $A$2)

где $ A $ 2 - это какой-то флажок (вставка → флажок), который можно нажать, чтобы "обновить" после того, как вам нужно изменить значение из листа и ячейки. ПОСТОЯННО $ A $ 1

Ответ 9

Используйте функцию финансов Google в качестве параметра. Like = GOOGLEFINANCE ("ВАЛЮТА: КАДАРЫ")

Эти функции перезаряжаются каждые х минут

Ответ 10

Как сказал @Brionius, добавьте дополнительный динамический аргумент в функцию. если вы используете now(), у вас могут возникнуть проблемы с тайм-аутом, сделайте обновление немного медленнее...

cell A1 = int(now()*1000)
cell A2 = function(args..., A1)