Google Script. Spreadsheet Integrated.

Мое знакомство с Google Apps Script началось с таблиц. Надоело возиться с важными для работы файлами: носить с собой на флешке, копировать, синхронизировать... Спустя некоторое время после загрузки нескольких таблиц в формате Excel на Google Drive (тогда сервис назывался Google Docs), возникла необходимость кастомизировать один из документов. Первым делом я вспомнил старый добрый VBA, но возвращаться из облака уже не хотелось. Так мы и познакомились...

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

Начнем с прелюдии. Отрываем копию шаблона документа (или создаем новый) и переходим в Редактор скриптов (Инструменты - Редактор скриптов).
Заполняем первый лист таблицы списком IP-адресов подсети 192.168.1.0/24:

// заполняем лист данными
function fillCompList() {
  var arr = [['IP','CompName']];
  for (var i = 1;i < 255; i++) {
    var val = [];
    if (Math.random() > 0.5)
      val = ['192.168.1.' + i,'Computer-' + i]
    else
      val = ['192.168.1.' + i,''];
    arr.push(val);
  }

  var ss = SpreadsheetApp.getActiveSheet();
  ss.clear();
  if (ss.getLastRow() > 0)   
    ss.getDataRange().clear();
  ss.getRange(1, 1, arr.length, 2).setValues(arr);

}

После выполнения функции первые два столбца листа заполняются IP-адресами от 192.168.1.1 до 192.168.1.254 и именами компьютеров соответственно.

Сформулируем задачу: отобразить информацию о свободных и используемых IP-адресах.
Первое что приходит в голову - использовать фильтр. Подходящее решение, но, как показывает практика, максимум на что может сподобиться пользователь - нажать на кнопку и/или выбрать пункт меню. В Excel я бы накидал несколько объектов на графический слой и привязал бы к ним VBA-функции. Здесь такой фокус не прокатит, придется добавлять кастомное меню. Впрочем, добавляется оно очень просто, но обо все по-порядку.

Предлагаю три варианта отображения информации:
- в отдельном столбце
- путем скрытия/отображения строк листа
- во всплывающем окне

1. Для отображения информации в отдельном столбце используем функцию фильтрации FILTER:

// отображаем свободные IP-адреса в отдельном столбце
function fiterFreeIPList() {
  var ss = SpreadsheetApp.getActiveSheet(); 

  clearIPList(); 

  var column = ss.getLastColumn() + 1;
  ss.getRange(1,column).setValue('FreeIP');
  var row = ss.getLastRow();
  ss.getRange(2, column).setFormula('=filter(A2:A' + row + ';B2:B' + row + '="")'); 
}
// отображаем используемые IP-адреса в отдельном столбце
function fiterBusyIPList() {
  var ss = SpreadsheetApp.getActiveSheet(); 

  clearIPList(); 

  var column = ss.getLastColumn() + 1;
  ss.getRange(1,column).setValue('BusyIP');
  var row = ss.getLastRow();
  ss.getRange(2, column).setFormula('=filter(A2:A' + row + ';B2:B' + row + '<>"")'); 
}
// очищаем список IP-адресов
function clearIPList() {
  var ss = SpreadsheetApp.getActiveSheet(); 
  if (ss.getRange(1, 3).getValue() != '')
    ss.getRange(1, 3, ss.getLastRow()).clear();
}

Для тех, кто впервые открыл Редактор скриптов, кратко объясню последовательность разработки: пишем код, выбираем функцию и запускаем (нажимаем на стрелочку - Выполнить), в случае ошибок дебажим (нажимаем на паучка - Отладка).

2. Для отображения информации путем скрытия/отображения строк используем методы листа hideRows/unhideRow соответственно:

// прячем строки с используемыми IP-адресами
function hideBusyIPRows() {
  unhideAllRows()

  var ss = SpreadsheetApp.getActiveSheet();
  var rng = ss.getRange(2, 2, ss.getLastRow());
  var values = rng.getValues(); 

  for (var i = 0; i < rng.getNumRows() - 1; i++) {   
    if (values[i][0] != '')     
      ss.hideRows(i+2);
  }
}
// прячем строки со свободными IP-адресами
function hideFreeIPRows() {
  unhideAllRows()

  var ss = SpreadsheetApp.getActiveSheet();
  var rng = ss.getRange(2, 2, ss.getLastRow());
  var values = rng.getValues(); 

  for (var i = 0; i < rng.getNumRows() - 1; i++) {   
    if (values[i][0] == '')     
      ss.hideRows(i+2);
  }
}
// отображаем все строки
function unhideAllRows() {
  var ss = SpreadsheetApp.getActiveSheet();
  var rng = ss.getDataRange();
  ss.unhideRow(rng);
}

3. Для отображения информации во всплывающем окне могу предложить три варианта:

// msgBox
function msgBoxShow() { 
  Browser.msgBox('Это текст'); //можно добавить заголовок и кнопки
}
// Label
function labelShow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var app = UiApp.createApplication().setTitle('Это заголовок приложения'); 
  app.add(app.createLabel('Это текст'));
  ss.show(app);
}
// Grid
function gridShow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var app = UiApp.createApplication().setTitle('Это заголовок приложения'); 
  app.add(app.createGrid(1,2).setText(0, 0, 'Это текст в первой колонке').setText(0, 1, 'Это текст во второй колонке'));
  ss.show(app);
}

Для отображения списка свободных IP-адресов используем Label:

// отображаем свободные IP-адреса во всплывающем окне
function showFreeIPList() {
  var ss = SpreadsheetApp.getActiveSheet();
  // получаем массив значений в диапазоне: [[IP, CompName],[IP, CompName]...]
  var arr = ss.getDataRange().getValues(); 

  var app = UiApp.createApplication().setTitle('Список свободных IP-адресов').setWidth(240).setHeight(300);
  var vPanel = app.createVerticalPanel();

  for (var i = 1; i < arr.length; i++){
    if (arr[i][1].toString() == '')           
      vPanel.add(app.createLabel(arr[i][0]));     
  }

  app.add(app.createScrollPanel(vPanel).setSize(240, 300));

  var ssh = SpreadsheetApp.getActiveSpreadsheet(); 
  ssh.show(app); 
}

Для отображения списка используемых IP-адресов используем Grid:

// отображаем используемые IP-адреса во всплывающем окне
function showBusyIPList() {
  var ss = SpreadsheetApp.getActiveSheet();
  // получаем массив значений в диапазоне: [[IP, CompName],[IP, CompName]...]
  var arr = ss.getDataRange().getValues(); 

  var app = UiApp.createApplication().setTitle('Список используемых IP-адресов').setWidth(240).setHeight(300);

  var j = 1;
  var grid = app.createGrid(j,2).setCellPadding(2).setText(0, 0, 'IP').setText(0, 1, 'CompName');

  for (var i = 1; i < arr.length; i++){
    if (arr[i][1].toString() != '') {   
      j += 1;
      grid.resize(j, 2).setText(j - 1, 0, arr[i][0]).setText(j - 1, 1, arr[i][1]);     
    }
  } 

  app.add(app.createScrollPanel(grid).setSize(240, 300));

  var ssh = SpreadsheetApp.getActiveSpreadsheet(); 
  ssh.show(app); 
}

В завершение создадим меню для всего безобразия, которое мы с вами наваяли:

// закидываем меню
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menu = [ {name: "Обновить данные", functionName: "fillCompList"},
              {name: "Свободные IP-адреса в отдельном столбце", functionName: "fiterFreeIPList"},
              {name: "Используемые IP-адреса в отдельном столбце", functionName: "fiterBusyIPList"},
              {name: "Очистить список IP-адресов", functionName: "clearIPList"},
              {name: "Спрятать строки с используемыми IP-адресами", functionName: "hideBusyIPRows"},
              {name: "Спрятать строки со свободными IP-адресами", functionName: "hideFreeIPRows"},
              {name: "Отобразить все строки", functionName: "unhideAllRows"},
              {name: "Свободные IP-адреса во всплывающем окне", functionName: "showFreeIPList"},
              {name: "Используемые IP-адреса во всплывающем окне", functionName: "showBusyIPList"} ];
  ss.addMenu("Мое меню", menu);
}

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

Таким образом мы изучили несколько приемов кастомизации таблиц в облаке Google. На мой взгляд ничем не хуже VBA. Плюс все сервисы Google к вашим услугам. Мне всегда нравился Microsoft Office, во многом за счет VBA. Но если сравнить облачные сервисы Google и Microsoft, то на текущий момент я ставлю на Google.