В завершении предыдущего разговора наше хранилище Google Диск выглядело как-то так:
В каталоге Folder-1 создадим новый файл, назовем его Doc-1-2.
Открываем скрипт Dog-3, выполняем функцию myFunction():
Отредактируем только что созданный файл, выполним функцию еще раз.
Открываем таблицу по имени Dog, в которой наш скрипт сохраняет информацию об изменениях в каталоге Folder-1, наблюдаем приблизительно следующую картину:
Добавим в таблицу еще один лист, в котором будем хранить источники данных диаграмм, а также сами диаграммы.
Начинаем работать с формулами. В ячейку A1 нового листа вставим ссылку на ячейку B1 листа по имени Sheet1, получив таким образом заголовок столбца с именами файлов и каталогов:
=Sheet1!B1
So far so good. Дальше - сложнее.
Здесь я должен сразу же сделать оговорку - я не спец по формулам, возможно где-то по ходу изложения можно было решить вопрос проще, возражения принимаются.
Продолжаем разговор. В ячейку A2 вставим формулу, которая вытащит список имен файлов, упоминаемых в диапазоне B2:B листа Sheet1:
=UNIQUE(Sheet1!B2:B)
Полагаю было бы неплохо превратить эти имена в ссылки. В ячейку B1 вставим ссылку на значение в ячейке A1: =A1 , а в ячейку B2 вставим формулу "превращения", после чего "протащим" формулу до ячейки B6:
=HYPERLINK(VLOOKUP(A2;Sheet1!B$2:E;4;FALSE);A2)
Для тех, кто не понял что значит "протащим" формулу: подведем указатель мыши к правому нижнему углу ячейки B2 так, чтобы он превратился в крестик, нажмем левую кнопку мыши и выделим диапазон ячеек B2:B6.
Теперь посчитаем количество изменений каждого файла. Для этого напишем в ячейку C1 заголовок столбца - Количество, в ячейку C2 вставим формулу, после чего также "протащим" ее до ячейки C6:
=COUNTIF(Sheet1!B$2:B;A2)
Данные об изменениях объектов (файлов и каталогов) в разрезе их имен готовы.
Получим данные, отражающие изменения объектов по датам.
Столбец D пропустим, в ячейку E1 вставим ссылку на ячейку A1 листа Sheet1, а в ячейку E2 - формулу:
=UNIQUE(Sheet1!A2:A)
Сомневаюсь, что такой разрез сможет кого-нибудь устроить, так как дата у нас хранится в формате даты и времени.
В общем мне хотелось бы вытащить уникальные даты. Для этого вставляем в ячейку F2 формулу:
=UNIQUE(ARRAYFORMULA(DATEVALUE(Sheet1!A2:A)))
Вроде все как надо, но меня смущает появившаяся непонятно откуда ошибка.
В ячейку F4 вставим формулу, которая слегка прояснит ситуацию с ошибкой:
=COUNTBLANK(Sheet1!A2:A)
Функция COUNTBLANK() считает количество пустых ячеек в диапазоне.
Становится понятно, что в выборку уникальных значений попадает пустое значение.
Попытаемся его исключить. Для этого в ячейку G2 вставим формулу:
=UNIQUE(ARRAYFORMULA(IF(NOT(ISBLANK(Sheet1!A2:A));DATEVALUE(Sheet1!A2:A))))
Похоже на правду, но выглядит на мой вкус не достаточно эстетично.
Предлагаю в ячейку H2 вставить немного отредактированную формулу ячейки G2:
=UNIQUE(ARRAYFORMULA(IF(NOT(ISBLANK(Sheet1!A2:A));DATEVALUE(Sheet1!A2:A);"")))
Лучше, но все равно не решает, а всего лишь маскирует наличие пустого значения:
В итоге в качестве решения я остановился на следующей формуле:
=UNIQUE(ARRAYFORMULA(DATEVALUE(INDIRECT(CONCAT("Sheet1!A2:A";(COUNTA(Sheet1!A:A)))))))
Оставим столбец I пустым для того, чтобы визуально отделить решение от рабочего пространства. Вставим формулу в ячейку J2:
Напишем в ячейку K1 заголовок столбца - Количество. Посчитаем количество изменений файлов в разрезе дат - вставим в ячейку K2 формулу, после чего "протащим" формулу до ячейки K3:
=COUNTIF(ARRAYFORMULA(DATEVALUE(Sheet1!A$2:A));H2)
Переходим к построению диаграмм.
Выделяем диапазон B1:C6, нажимаем иконку "Вставить диаграмму":
Выбираем рекомендованную диаграмму:
Вставляем, редактируем заголовок, а также наименования осей, получаем вот такую красоту:
Подобным образом строим вторую диаграмму - по диапазону J1:K3:
Переходим к публикации. Открываем меню "Файл - Опубликовать в интернете":
Выбираем Лист2, нажимаем "Начать публикацию", в разделе "Ссылка на опубликованные данные" копируем ссылку:
Вставляем ссылку в адресную строку браузера...
Заметьте, до сих пор никакого программирования!
Должен признаться, что дальше без программирования мы долго не протанцуем, так как формулы, которые мы с вами писали большую часть текущего повествования не будут волшебным образом обновляться в случае обновления данных.
Попробую объяснить:
- добавим еще одну запись об изменении документа Doc-1-2:
- отложим пока наши диаграммы в сторону и обратим внимание на изменение данных, которые служат источником для диаграмм:
- переименуем документ - назовем его Doc-1-3:
- вернемся на Лист2 - если формула =UNIQUE(Sheet1!B2:B) подцепила новую запись, то формулы создания гиперссылки и подсчета значений за нас никто не "протащил":
- отредактируем дату последней записи:
- обращаем внимание на источник данных второй диаграммы:
Такая же история. Только кодинг, Шура (позволю себе перефразировать великого слепого - Паниковского).
Не исключаю существование варианта обойтись формулами, но для меня он не очевиден, кто сумеет - молодец :).
"Что же делать?", - спросите вы меня. Открываем меню "Инструменты - Редактор скриптов":
Пишем код скрипта, который будет воссоздавать формулы, "протаскивать" где нужно, а также изменять диапазон данных диаграмм.
Начнем с первой диаграммы:
function myFunction() {
var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');
sh.getRange('A1:A2').setFormulas([['=Sheet1!B1'], ['=UNIQUE(Sheet1!B2:B)']]);
sh.getRange('B1').setFormula('=A1');
var rng = sh.getRange('C1').setValue('Количество');
var i = 2; // протаскиваем формулы
while (rng.offset(1, -2).getValue() != '') {
rng.offset(1, -1, 1, 2).setFormulas([
['=HYPERLINK(VLOOKUP(A' + i + ';Sheet1!B$2:E;4;FALSE);A' + i + ')',
'=COUNTIF(Sheet1!B$2:B;A' + i + ')']]);
i++;
rng = rng.offset(1, 0);
}
var ch0 = sh.getCharts()[0]; // первая диаграмма
ch0 = ch0.modify()
.removeRange(ch0.modify().getRanges()[0])
.addRange(sh.getRange('B1:C' + (i - 1))).build();
sh.updateChart(ch0);
}
Сохраняем, выполняем:
Авторизуем:
Здесь я вспомнил, как в свое время рисовал VBA-приложения, поэтому не смотря на то, что данные обновились, предлагаю нарисовать кнопку - элемент интерфейса, который будет по событию нажатия выполнять функцию, код которой только что отработал.
Эта фича появилась сравнительно недавно в таблицах Google, поэтому хотелось бы отведать как она будет работать.
Настоятельно рекомендую: на мой взгляд, если решать через меню - пользователю придется лишний раз щелкать мышью: "Меню - Ваше наименование", которое будет выполнять вашу функцию. По-моему кнопка проще - один клик.
Открываем меню: "Вставка - Рисунок":
Выбираем "Фигура":
Дважды щелкаем мышью внутри выбранной фигуры - пишем текст:
Переместим наш элемент интерфейса в левый верхний угол таблицы:
Назначим кнопке обработчик события нажатия на эту самую кнопку:
Пишем имя функции, которая будет выполняться по событию:
Нажимаем на кнопку...
Функция myFunction() отработала еще раз.
Теперь предлагаю отредактировать код этой самой функции с тем, чтобы она обновляла источник данных второй диаграммы:
function myFunction() {
var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');
sh.getRange('A1:A2').setFormulas([['=Sheet1!B1'], ['=UNIQUE(Sheet1!B2:B)']]);
sh.getRange('B1').setFormula('=A1');
var rng = sh.getRange('C1').setValue('Количество');
var i = 2; // протаскиваем формулы
while (rng.offset(1, -2).getValue() != '') {
rng.offset(1, -1, 1, 2).setFormulas([
['=HYPERLINK(VLOOKUP(A' + i + ';Sheet1!B$2:E;4;FALSE);A' + i + ')',
'=COUNTIF(Sheet1!B$2:B;A' + i + ')']]);
i++;
rng = rng.offset(1, 0);
}
var ch0 = sh.getCharts()[0]; // первая диаграмма
ch0 = ch0.modify()
.removeRange(ch0.modify().getRanges()[0])
.addRange(sh.getRange('B1:C' + (i - 1))).build();
sh.updateChart(ch0);
// вторая диаграмма
sh.getRange('J1:J2').setFormulas([['=E1'],
['=UNIQUE(ARRAYFORMULA(DATEVALUE(INDIRECT(CONCAT("Sheet1!A2:A";(COUNTA(Sheet1!A:A)))))))']]);
rng = sh.getRange('K1').setValue('Количество');
i = 2; // протаскиваем формулы еще раз
while (rng.offset(1, -1).getValue() != '') { // протаскиваем формулы
rng = rng.offset(1, 0).setFormula('=COUNTIF(ARRAYFORMULA(DATEVALUE(Sheet1!A$2:A));H' + i + ')');
i++;
}
var ch0 = sh.getCharts()[1];
ch0 = ch0.modify()
.removeRange(ch0.modify().getRanges()[0])
.addRange(sh.getRange('J1:K' + (i - 1))).build();
sh.updateChart(ch0);
}
Возвращаем диаграммы на родину, переходим по ссылке...
Остается настроить триггер для того, чтобы формулы нашей таблицы, а также источники данных наших диаграмм обновлялись с определенной периодичностью.
Для тех, кто смотрел предыдущие видеомануалы не составит труда. Тем не менее напомню как это сделать:
- открываем меню "Ресурсы - Триггеры текущего проекта":
- щелкаем по ссылке "Добавить триггер":
- выбираем "Мероприятия - Динамический":
- выбираем "Минутный таймер":
- выбираем периодичность - "Каждые 5 минут":
- сохраняем
Все. Теперь манагеры могут заходить по ссылке, которую можно укоротить с помощью сервиса Goo.gl (как написать свой укорачиватель ссылок я рассказывал в одном из предыдущих постов).
Диаграммы довольно доходчиво отображают динамику изменения объектов в целевом каталоге.
И это был последний
Комментариев нет:
Отправить комментарий
Комментарий будет опубликован после модерации