web-SuN –
создание и продвижение сайтов

Недавно столкнула с задачей, которая заставила изучить новую для меня область – макросы таблиц. При этом таблицы были не простые, а гугловские. Сначала я вспомнил о Microsoft Excel и о его VBA, на которых пишутся макросы для различного рода расширения стандартных возможностей.

Мне стало грустно от мысли, что придется вспоминать Basic, но при первом же знакомстве с документацией для разработчика Google Spreadsheet Service, я изменил свое мнение, созданное первым впечатлением. Для начала, оказалось, что макросы пишутся на JavaScript. Это в корне меняло сам подход к решению задачи, т.е. мне предстояло попрактиковаться действительно в полезном языке, применяя его в новой задаче, расширяющей мои знания.

Задача состояла в том, чтобы из исходных данных одного листа сделать данные для результирующего и правильно их разместить. Реализацию я начал с создания меню для документа, чтобы не вызывать функции каждый раз из редактора кода. Впрочем, именно с создания меню, я и начал ознакомление с документацией по макросам:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Export')
      .addItem('Export One', 'Ex1')
      .addSeparator()
      .addItem('Export Two', 'Ex2')
      .addToUi();
}

Данная функция срабатывает при открытии документа и создает пункт меню "Export" с двумя выпадающими менюшками "Export One" и "Export Two". Вместе с тем, каждому выпадающему меню присваивается функция срабатывающая при клике. Рассмотрим тело первой функции:

function Ex1() {
  SpreadsheetApp.getUi().alert('Начнем!');
  var newSheetName = 'Export_1';
  var sourceSheetName = 'Исходник';
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for (var sheet in sheets) {
    if(sheets[sheet].getSheetName() == newSheetName) {
      //Удаляем лист, если он уже создан ранее
      ss.setActiveSheet(sheets[0]);
      ss.deleteSheet(sheets[sheet]);
    }
  }
  //Создание нового листа
  ss.insertSheet().setName(newSheetName);
  //Создаем рыбу таблицы
  var newSheet = ss.getSheetByName(newSheetName);
  var header = [
    'Campaign',
    'Campaign Daily Budget',
    'Campaign Type'
    ];
  //Заголовки таблицы и авторесайз столбцов
  var i = 0;
  var row = newSheet.getRange('A1');
  for(var title in header) {
    var newrow = row.offset(0, i);
    newrow.setValue(header[title]);
    i++;
    newSheet.autoResizeColumn(i);
  }
}

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

15 строка создает новый лист с заранее заданным именем. Потом производится первоначальное заполнение таблицы, а именно, заполняется шапка: в цикле (26 строка) происходит сдвиг выделенной ячейки, в которую вставляется значение из заготовленного массива.

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

Однако такой подход к записи данных при больших объемах занимает много времени и может привести к ошибке Exceeded maximum execution time. По ссылке предложен более правильный способ работы с чтением/записью в Google Apps Scripts.