Волшебная кнопка

Кирилл Соппа, финансовый консультант

Думаю, никто не будет спорить, что голубой мечтой собственников и руководителей бизнеса является управленческая отчетность, получаемая мгновенно нажатием одной волшебной кнопки. Под управленческой отчетностью, естественно, имеются ввиду показатели “Заработано” и “Можно забрать и потратить дивидендов”. А последнее время к ней плавно добавилась вторая голубая мечта: чтобы эта волшебная кнопка была на экране смартфона. В данном блоге я расскажу как реализовать эти две мечты с помощью подручных средств.

Мысль о реализации данного проекта мне пришла, когда финансовый консалтинг превратился из эпизодической халтуры в обычный бизнес. Для учета не хотелось городить новую базу в 1С и я завел таблицу в Microsoft Excel. Через некоторое время для повышения мобильности я перенес таблицу на Google Диск и преобразовал в формат Таблиц Google. Ну и еще через некоторое время дико захотелось упростить доступ к некоторым ключевым показателям. Например, чтобы посмотреть остатки на счетах приходилось сначала заходить на Диск, потом заходить в Таблицу, потом находить нужный лист, возможно чего-то листать. Захотелось просто ткнуть в экран смартфона и все. Так я узнал про Google Apps Script (что-то вроде макросов для Microsoft Office, только языком макросов является Visual Basic for Application, а скрипты Google основаны на JavaScript) и возможность опубликовать скрипт в Интернете, чтобы доступ к нему осуществлялся из любого браузера по ссылке. В итоге один потраченный выходной день (хватило бы и пары часов, но последний раз я программировал не на 1С еще в прошлом тысячелетии, пришлось осваивать HTML и JavaScript заново) — и у меня на рабочем экране смартфона есть иконка, которая открывает мне мой текущий управленческий баланс.

Итак, у нас есть таблица, в которой мы ведем управленческий учет. Как это сделать очень подробно рассказывает своих блогах и вебинарах мой коллега Игорь Николаев, поэтому останавливаться на этом не буду. Скажу только, что основная функция многофакторного суммирования СУММЕСЛИМН, на которой базируется ведение учета в электронных таблицах, в Google Таблицах имеет название SUMIFS. Также, чтобы не погружаться в методологию управленческого учета, в Google Таблице, которая приведена в качестве примера, учет ведется по оплате. Имеем: лист “Реестр операций”, на который вносятся все операции; лист “ОПиУ”, на котором автоматически считается финансовый результат по оплате; лист “Остатки”, на котором автоматически рассчитываются остатки по дням; лист “Справочники” — на нем приведены все справочники, используемые для отражения операций (в приведенном случае “Банки” и “Статьи”) и лист “Summary”, на котором сведены нужные нам показатели с предыдущих листов (можно было бы их и с соответствующих листов тянуть, но это усложнило бы код скрипта). Лист “Summary” имеет вид:

Дата отчёта: 31.10.2015      
         
Остатки на счетах: 33 300,00      
Операционная прибыль: 53 300,00      
         
Лимит по операции месяц карта лимит остаток
Снятие наличных 10 Сбербанк карта 300 000,00 280 000,00

Лимит снятия вносится руками из банковского договора, остаток считается исходя из суммы операций по указанным статье, месяцу и счету с помощью функции SUMIFS. Данные для публикации готовы.

Переходим к скрипту. Если у Вас платный аккаунт, то Google Apps Script у Вас скорей всего уже подключен. Если бесплатный — нажимаем правой кнопкой мыши в свободном поле на Google Диске и в контекстном меню “Еще/Подключить другие приложения” подключаем Google Apps Script (у меня на скрине он уже подключен):

После этого создаем новый проект в том же контекстном меню “Еще/Google Apps Script”. Сразу, с помощью пункта меню “Файл/Переименовать” меняем название так как нам надо. В текстовом поле уже создан файл кода “Код.js” (js означает код на языке JavaScript) и заготовка для функции myFunction(). Кстати, в JavaScript регистр букв имеет значение. Если мы хотим опубликовать свой скрипт, то нам надо поменять название функции на doGet(). Это предопределенное наименование. Функция с таким названием автоматически вызывается в момент, когда браузер осуществляет переход по ссылке, закрепленной за нашим скриптом. И результатом этой функции должна быть страница HTML, которая и будет отображена. Все просто. Вставляем следующий код:

function doGet() {
  return HtmlService.createHtmlOutputFromFile('Page')
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

Сильно вникать в эту абракадабру не стоит. Достаточно знать, что данный код создает интернет-страницу на основе HTML-файла “Page”, который мы сейчас с Вами создадим. Пугаться не стоит, HTML — это не язык программирования, это просто система обозначений, для создания макета страницы. По нему очень много справочного материала в Интернете, сложностей с ним возникнуть не должно.

Итак, “Файл/Создать/HTML-документ”. Вводим имя файла “Page” и в наш проект (слева вертикальная панель с файлами нашего проекта) добавляется еще один файл. Отметим, что тип файла .html добавился автоматически. HTML-файл состоит из тегов, заключенных в угловые скобки, закрывающий тег повторяет открывающий, но в начале названия стоит знак /. В добавленном файле у нас есть следующие теги: <html></html> — начало и конец самого HTML-файла; <head></head> — начало и конец заголовка, в котором задается формат выдачи, скрипты и другая служебная информация; <body></body> — начало и конец тела HTML-файла, собственно то, что и будет выдаваться на экран. Что означает надпись <!DOCTYPE html> в самом начале я толком не знаю сам, где-то читал что так должно быть. Ну и пусть, жить не мешает. Узнаю, расскажу обязательно. Тег <base target="_top"> в заголовке нашей страницы указывает браузеру формат окна для нашего скрипта.

Вставляем тело нашей HTML-страницы:

<body>
    <table width=50% border=1>
      <caption align="center"><h1><b> БАЛАНС </b> </h1></caption>
      <col width=70%>
      <col width=30%>
      <tr>
        <th align="center"> Показатель </th>
        <th align="center"> Значение </th>
      </tr>
      <tr>
        <td><b> Остатки на счетах: </b></td>
        <td align="right"><b> <span class="label" id="Cash"></span></b></td>
      </tr>
      <tr>
        <td><b> Финансовый результат: </b></td>
        <td align="right"> <span class="label" id="Profit">  </span></td>
      </tr>
      <tr>
        <td><b> Лимит снятия: </b></td>
        <td align="right"> <span class="label" id="Limit">  </span></td>
      </tr>
    </table>
  </body>

Наверное опытные верстальщики Web-приложений умеют какими-то хитрыми методами красиво размещать все на своих страницах. Я использовал для разметки простой рабоче-крестьянский метод: таблицу. Обрамляют таблицу теги <table></table>. В открывающем теге можно указывать так называемые опции. У меня это width=50% — ширина таблицы в процентах и border=1 — ширина границ таблицы. Полный список опций для таблицы легко найти в интернете, например, здесь. Дальше идут теги (для сокращения указаны без закрывающего тега): <caption> — заголовок таблицы с опцией align="center" для выравнивания по центру, <h1> — размер букв (допускаются теги от h6 до h1 — в сторону увеличения), <b> — жирный текст, <col> — формат колонки (в моем случае задается ширина, не имеет закрывающего тега), <tr> — строка таблицы, <th> — ячейка шапки таблицы, <td> — ячейка таблицы. Отдельно отмечу тег <span></span> — он позволяет “пометить территорию”. То есть, присвоить куску страницы имя, указанное в опции id, чтобы потом можно было в скрипте чего-нибудь туда поместить. Опция class="label" указывает браузеру, что в данном месте будет просто текст. Как я и говорил, ничего сложного.

Уже можно проверить что получилось. Выбираем в меню “Публикация/Развернуть как веб-приложение…”, в открывшемся окне жмем “Развернуть”, в следующем окне видим следующее:

В верхней части окна указана ссылка, по которой наш скрипт можно запустить из браузера. Под ней синим выделена ссылка “последнюю версию кода”, нажав на которую можно посмотреть как выглядит сверстанная нами страница.

Приступаем к написанию скрипта, который будет брать данные из нашей Google Таблицы и вставлять в HTML-страницу. Переходим в файл “Код.js”, добавляем туда код:

function setCurDate() {
  var files=DriveApp.getFilesByName("BalanceSheet (блог)");
  if (!files.hasNext()) return 0; 
  var sh=SpreadsheetApp.open(files.next()).getSheetByName("Summary");
  var curDate=new Date();
  sh.getRange(2, 2).setValue(curDate.getDate()+"/"+(curDate.getMonth()+1+"/"+curDate.getFullYear());
  return 1;
}

function getCash() {
  var files=DriveApp.getFilesByName("BalanceSheet (блог)");
  if (!files.hasNext()) return 0;
  var sh=SpreadsheetApp.open(files.next()).getSheetByName("Summary");
  return sh.getRange(4, 2).getValues();
}

function getProfit() {
  var files=DriveApp.getFilesByName("BalanceSheet (блог)");
  if (!files.hasNext()) return 0;
  var sh=SpreadsheetApp.open(files.next()).getSheetByName("Summary");
  return sh.getRange(5, 2).getValues();
}

function getLimit() {
  var files=DriveApp.getFilesByName("BalanceSheet (блог)");
  if (!files.hasNext()) return 0;
  var sh=SpreadsheetApp.open(files.next()).getSheetByName("Summary");
  return sh.getRange(8, 5).getValues();
}

Функция setCurDate() устанавливает на листе “Summary” текущую дату. Функции getCash(), getProfit() и getLimit() возвращают нужные нам показатели из соответствующих ячеек таблицы. Классы DriveApp и SpreadsheetApp предназначены для доступа к объектам Google Диска и Google Таблиц соответственно. Нумерация строк и колонок начинается с единицы, сначала указываются строки, потом колонки (аргументы getRange()). Можно было выбирать данные одной функцией и возвращать все показатели одним динамическим массивом, но я сознательно пошел по пути упрощения кода. Иначе здесь пришлось бы компоновать массив, потом на клиентской стороне его парсить.

С файлом “Код.js” закончено. Теперь нам надо его авторизовать, в данном случае дать доступ к Таблицам на нашем Диске. Выбираем пункт меню “Выполнить/setCurrDate” (можно любую функцию выбрать), появится сообщение, что требуется авторизация. Жмем “Далее”, Google сам по коду определит размер необходимых полномочий и выдаст окно:

Жмем “Разрешить”. Авторизация пройдена. Кстати, если Вы запускали функцию setCurDate(), то дата на странице “Summary” должна поменяться на текущую. Проверьте.

Возвращаемся в файл “Page.html”. Вставляем в заголовке между тегами <head></head> следующий код:

<script>
      function putCash(newCash) {
        var c = document.getElementById("Cash");
        c.innerHTML=newCash+" RUR";
      }
      function putProfit(newProfit) {
        var c = document.getElementById("Profit");
        c.innerHTML=newProfit+" RUR";
      }     
      function putLimit(newLimit) {
        var c = document.getElementById("Limit");
        c.innerHTML=newLimit+" RUR";
      }
      function ShowResults() {
        google.script.run.withSuccessHandler(putCash).getCash();
        google.script.run.withSuccessHandler(putProfit).getProfit();
        google.script.run.withSuccessHandler(putLimit).getLimit();
      }
      function Show() {
        google.script.run.withSuccessHandler(ShowResults).setCurDate();
      }
</script>

Тегами <script></script> выделяются вставки JavaScript-кода. С функциями putCash(), putProfit() и putLimit() все ясно. Они обращаются к “помеченной территории” и вставляют в нее переданный аргумент. На функциях Show() и ShowResults() требуется остановится поподробнее.

Когда мы пишем скрипт для публикации в Интернете, мы должны учитывать одну особенность, которой нет при написании, скажем, макроса для Microsoft Excel. В таких скриптах всегда участвуют два исполнителя: сервер Google, находящийся где-то в Дублине (может быть и не в Дублине) и смартфон в Вашей руке, называемый клиентом. И мы всегда должны четко себе отдавать отчет какая часть кода в каком месте будет исполняться. Выдачу страницы на экран осуществляет клиент (наш смартфон) на основании полученного от сервера HTML-файла (браузер автоматически делает так называемый GET-запрос на сервер, находящийся по указанному нами в адресной строке http-адресу; на данный запрос сервер отвечает результатом функции doGet()). Поскольку Google Таблица находится на сервере где-то в Дублине (или не в Дублине), то функции setCurDate(), getCash(), getProfit() и getLimit() должны выполняться там же на сервере. С точки зрения клиента (нашего смартфона) он вообще “не знает” о существовании нашей таблицы (может у нас там полноценная база данных). Для запуска функций на сервере и предназначена конструкция google.script.run., после которой указывается запускаемая функция. Но и это еще не все. Есть еще одна важная особенность. Если бы мы просто написали google.script.run.setCurDate(), то наш смартфон запустил бы на сервере функцию setCurDate(), но в отличии от обычного макроса, не стал бы ждать ее исполнения сервером!!! То есть, дальнейшее исполнение кода на клиенте шло бы уже параллельно с выполнением запущенных функций на сервере. Но нам то надо исполнять последовательно!!! Сначала установить дату, и только после установки и пересчета листа брать актуальные данные из ячеек, сначала запросить данные из ячеек на сервере и только после их получения разместить их на странице. Для этого и нужна конструкция .withSuccessHandler(). Ее действие объясню на примере строчки:

google.script.run.withSuccessHandler(putCash).getCash();

В данном примере клиент вызывает на сервере функцию getCash() и в случае успешного ее завершения (не 0) запускает на клиенте функцию putCash() с аргументом, который является результатом исполнения функции getCash().

Если грубо, наш код работает так.

  1. Сначала наш скрипт запускает на сервере функцию установки в учетном файле текущей даты setCurDate(), а на клиенте устанавливает правило, что при успешном завершении этой функции запустится функция ShowResults(). После этого скрипт работу прекращает (тем кто писал одни только макросы это нелегко понять);
  2. После возврата клиенту с сервера успешного результата работы функции setCurDate() браузер проверяет не оставлялись ли на этот счет какие-либо правила.
  3. В соответствии с оставленным правилом на клиенте запускается функция ShowResults(). Данная функция запускает три новых запроса на сервере (функции getCash(), etProfit() и getLimit()) и оставляет три новых правила для клиента, указывающих что делать с возвращаемыми сервером результатами.
  4. По мере возврата с сервера ответов на запросы клиент запускает соответствующие функции (putCash(), putProfit() и putLimit()) и передает им возвращенные с сервера значения.
  5. Запущенные на клиенте функции (putCash(), putProfit() и putLimit()) размещают полученные с сервера данные в соответствующие места на нашей странице.

Осталось последнее — запустить этот адский алгоритм. Для этого перед тегом </body> после тега </table> надо вставить вызов нашей основной функции:

<script>
    Show();    
</script>

Вот и все. Выбираем в меню “Публикация/Развернуть как веб-приложение…”, в выпадающем списке “Версия проекта:” выбираем “Новый” и жмем “Обновить”. В открывшемся окне копируем ссылку из поля “Текущий URL веб-приложения:” (она обязательно должна заканчиваться /exec), помещаем эту ссылку на рабочий экран смартфона и пользуемся!

В завершении хочу добавить еще один момент. Я потратил время и узнал сколько такой функционал стоил бы как самостоятельное Web-приложение. Один мой знакомый программист сказал что реализовал бы это за 30-40 тысяч рублей за две-три недели. А в ответ на официальный запрос в одну из компаний, занимающихся Web-разработкой, я получил смету на 80 тысяч рублей и сроком 3-4 недели (как пишут СМИ - коммерческое предложение есть в моем распоряжении). Так что автоматизация с использованием облачных сервисов Google поможет прилично сэкономить деньги. При том, что мое решение гораздо гибче. Например, если Вы через некоторое время передумаете и решите вести учет по начислению, то Вам достаточно будет лишь нужным образом переделать Google Таблицу и не придется нанимать программистов для переделки Web-приложения.

Ссылки:

Проект скрипта
Учётная таблица



Рейтинг@Mail.ru

Copyright (c) 2005-2015 by Igor Nikolaev. All Rights Reserved.