Страницы

понедельник, 30 декабря 2013 г.

Как визуализировать информацию об изменениях в Google Drive.

Продолжение темы мониторинга изменений в облачном хранилище Google ДискВ прошлый раз мы сохранили информацию об изменениях в таблице. Но для манагеров табличные данные информацией не являются. Поэтому в этот раз мы научимся создавать нарядные диаграммы и публиковать их в сети. Практически без программирования! Если не считать программированием создание формул в таблицах Google.


В завершении предыдущего разговора наше хранилище 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 (как написать свой укорачиватель ссылок я рассказывал в одном из предыдущих постов).
Диаграммы довольно доходчиво отображают динамику изменения объектов в целевом каталоге.

И это был последний из рассказов о Маугли пост в этом году. Всех с наступающими празниками!