Google Docs, Google Drive, Google Scripts: шаманим с технической реальностью

Доброго времени суток, дорогие читатели, вредины, злодеи, доброжелатели и прочие личности. Минул период страшных страданий, начиная от сервера проекта, мосэнерго, clip2net-а и заканчивая прочими проблемами, что напали на нас за последнюю неделю. Кстати, это всё показало, что многие не совсем знакомы с нашим твиттером, форумом, новостным порталом, группой в контакте, фейсбуке и т.п., из-за чего мы монотонно отвечали всё это время на Ваши письма, смс и даже звонки.

Google Spreadsheet

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

script

Я думаю, что очень многие из Вас умеют пользоваться Excel'ем или его аналогом, а некоторые, может, даже и гугловскими таблицами, про которые писали здесь. Те, кто пользуется диском Google (Google Drive), наверное уже использовали Таблицы (Spreadsheets) и заметили, что по функционалу они немного уступают Экселю, но тем не менее это всё ещё мощный инструмент. Так вот, в Экселе были макросы (этакие команды, упрощающие и автоматизирующие вычисления), написанные на небезызвестном языке VBA (Visual Basic for Applications). В Таблицах Google также есть макросы, которые именуются скриптами и пишутся уже на языке Javascript. С ними мы сегодня и познакомимся.

Я заранее Вас предупреждаю о возможной сложности дальнейшего примера, т.к. он не столько обучающий, сколько.. Мм.. Так сказать, конечный факт, которым Вы можете пользоваться и.. И развивать, если это Вам знакомо.

Соберитесь в комочек мозга.. И приступим :)

Создание таблицы Google Drive и наполнение её контентом

Рассмотрим такую простенькую задачку:
У нас есть две колонки, в первой мы пишем названия фруктов, а во второй цвет, который соответствует этому фрукту. И мы хотим, чтобы при вводе цвета в колонке цветов автоматически менялся бы цвет названия фрукта.

Если Вы забыли как вообще пользоваться документами Google, то милости просим почитать соответствующую и уже упомянутую выше статью. Если Вам это не нужно совсем, то читать наверное и дальше даже нет смысла. Хотя, конечно, кому что :)

Так вот, создаем новую таблицу Google, именуем её, например, "Фрукты". Ну, как, например.. Учитывая, что пример про фрукты, то.. Ну Вы поняли :)

Теперь добавляем на первый лист наши фрукты и цвета:

список значений

Примечание! Для того, чтобы считались фрукты, введите в ячейку А1 формулу:

="фрукт ("&COUNTA(A2:A)&")"

Теперь создадим макрос. Для этого идем в меню "Инструменты" и выбираем "Управление скриптами". Появится всплывающее меню, где мы жмем на кнопку "Создать".

создание макроса

В появившемся окошке выбираем "Пустой проект".

создание скрипта

Откроется редактор, который на первый взгляд (да и на второй) может вызвать ступор.

Редактор Скриптов

Собственно, что дальше? А дальше мы начинаем писать наш собственный макрос ручками (да, всё самостоятельно). Как будет выглядеть наш макрос? Нужно составить схемку сего процесса (иначе этот процесс займет у Вас очень много времени).

Нам нужно:

  1. Достать значения цветов из второй колонки;
  2. В соответствии с этими значениями задавать цвета для первой колонки.

Итак.. Вроде бы всё просто.. Если знать, как это делать, конечно :)

шКоддинг

Перейдем к самому коду:

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{name : "Покрасить",functionName : "MakeMeHappy"}];
  sheet.addMenu("Скрипты", entries);
};

function MakeMeHappy(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getActiveRange();
  var data = range.getValues();
  if(range.getColumn() == 2){
    for (var i=0;i<data.length;i++){
      range.offset(i,-1,1,1).clearFormat();
      range.offset(i,-1,1,1).setHorizontalAlignment("center");
      range.offset(i,-1,1,1).setVerticalAlignment("center");
      switch (data[i][0]){
        case "зеленый":
        case "Зеленый":
          range.offset(i,-1,1,1).setFontColor("#00dd00");
          break;
        case "салатовый":
        case "Салатовый":
          range.offset(i,-1,1,1).setBackgroundColor("#87dd47");
          break;
        case "желтый":
        case "Желтый":
          range.offset(i,-1,1,1).setBackgroundColor("#ffff00");
          break;
        case "оранжевый":
        case "Оранжевый":
          range.offset(i,-1,1,1).setFontColor("#dd7711");
          break;
        case "красный":
        case "Красный":
          range.offset(i,-1,1,1).setFontColor("#dd0000");
          break;
        case "фиолетовый":
        case "Фиолетовый":
          range.offset(i,-1,1,1).setFontColor("#800080");
          break;
        default: break;
      }
    }
  }
};

Теперь я постараюсь Вам его объяснить. Функция onOpen добавляет меню "Скрипты" к таблице при открытии оной. И выглядит это дело так:

добавление своего меню

Теперь по коду:

var sheet = SpreadsheetApp.getActiveSpreadsheet();

Эта строчка добавляет в переменную sheet идентификатор открытого нами документа, чтобы потом по нему обращаться к документу.

var entries = [{name : "Покрасить",functionName : "MakeMeHappy"}];

Эта переменная-массив содержит список названий менюшек и функций, которые выполняются при клике на эти менюшки.

sheet.addMenu("Скрипты", entries);

Этот метод добавляет к нашему документу меню "Скрипты".

Функция MakeMeHappy, собственно, и будет нашей главной функцией, которая красит фрукты.
Сначала я объявляю переменные:

var sheet = SpreadsheetApp.getActiveSpreadsheet();
var range = sheet.getActiveRange();
var data = range.getValues();

Соответственно, в переменной sheet находится идентификатор нашего документа. В переменной range находится выделенная нами область (например, ячейки B2:B6), в переменной data находятся значения этих ячеек в виде массива.

if(range.getColumn() == 2){...}

В этом условии мы проверяем, что выбранный диапазон ячеек соответствует второй колонке (в которой цвета фруктов).

for (var i=0;i<data.length;i++){...}

В этом цикле мы проходимся по каждой ячейке из диапазона B2:B

range.offset(i,-1,1,1).clearFormat();
range.offset(i,-1,1,1).setHorizontalAlignment("center");
range.offset(i,-1,1,1).setVerticalAlignment("center");

Эти три свойства убирают форматирование ячеек A[i] (например, A1, A2, A3 и т.п., т.к. мы внутри цикла), а также центрируют значения в ячейке по вертикали и горизонтали.

Тут следует иметь в виду, что т.к. наш диапазон соответствует второй колонке (В2:В), а нам надо убрать форматирование и отцентровать первую колонку, то для этого используется метод offset (номер ряда диапазона, номер колонки, кол-во рядов, кол-во колонок). Например, метод range.offset(0,1,4,3) для ячейки B2 (т.е. range соответствует B2:B2 ) будет означать, что мы будем воздействовать не на ячейку B2:B2, а на диапазон [B+1][2+0]:[В+3][2+(4-1)] = C2:E5. Более подробно сморите в документации.

switch (data[i][0]){
        case "зеленый":
        case "Зеленый":
          range.offset(i,-1,1,1).setFontColor("#00dd00");
          break;
        case "салатовый":
        case "Салатовый":
          range.offset(i,-1,1,1).setBackgroundColor("#87dd47");
          break;
          ...
}

Функция switch является так называемым переключателем. Она смотрит значение переменной и в соответствии с тем, что в ней хранится, выполняет определенное условие "case". Можно её переписать в стандартном виде if else. Но получится очень неудобно. Например:

switch (c){
case 1: условие_1; break;
case 2: условие_2; break;
case 3: условие_3; break;
default: условие_4; break;
}

..Будет эквивалентно функции:

if (c == 1) условие_1;
else if (c == 2) условие_2;
else if (c == 3) условие_3;
else условие_4;

Т.к. можно ввести цвет как с большой, так и с маленькой буквы, то нам надо по два условия, что соответствует записи case "зеленый": case "Зеленый": действие; break; (у меня это записано блочной структурой). Нужно иметь в виду, что после каждого действия надо писать функцию break; т.к. иначе мы будем выполнять все условия по порядку, а не то, которое нам надо. Условие default используется в том случае, если для нашей переменной нет подходящего условия.

range.offset(i,-1,1,1).setFontColor("#00dd00");

Методы setFontColor и setBackgroundColor задают цвета текста и фона в виде #rrggbb (r-red, g-green, b-blue, диапазоны цветов) соответственно.

Теперь проверим функцию. Выделяем диапазон B2:B9, заходим в меню "Скрипты" и выбираем опцию "Покрасить". Смотрим, как наши фрукты обрели жизнь цвета :)

В общем-то на этом всё. Но можно сделать небольшое дополнение, чтобы не приходилось каждый раз запускать функции вручную. Для этого зайдите в редакторе скриптов в меню "Ресурсы" и выберите там "Триггеры текущего проекта". Откроется менюшка, в которой уже будет наша функция onLoad. Добавляем новую функцию (1) и задаем название функции (2) и тип активации оной (3). Также можно нажать на "Уведомления" и добавить/убрать свой почтовый адрес из списка уведомлений. Они будут приходить в случае, если скрипт выполнялся с ошибками.

добавление триггеров

Конечный результат действа:

результат

Ну, в общем.. Да.

Послесловие

Поздравляю с первым скриптом. Это лишь малая доля того, что можно сделать при помощи такого мощного устройства, как Google Scripts. Понятно, что наверное большинство читателей такая штука пугает, тем более, что другие статьи не так суровы и "ругаются" на Вас кодом, да и прочими ужасами жизни.. Но что уж делать.

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

P.S. За существование оной статьи отдельное спасибо другу проекта и члену нашей команды под ником “6aPHaK“.

Оценить —

  1. 5
  2. 4
  3. 3
  4. 2
  5. 1
(7 голосов, в среднем: 5 из 5)

Подписаться —

RSS Email Facebook Twitter G+ YouTube
Темы:  , , ,

Комментарии к статье

  1. Дмитрий

    Добрый день!
    Скрипт что Вы писали ранее работает , но он распространяется сразу на все листы и диапазоны. Не могли бы Вы подсказать как сделать так, чтобы он распространялся только на определенный лист и определенный диапазон ( к примеру: после изменения чего либо в столбце F в столбце G пишется дата изменения) ?
    Скрип был такой

    function myFunction() {
    ScriptApp.newTrigger('onEdit').forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet()).onEdit().create()
    }

    function onEdit(e){
    var range = SpreadsheetApp.getActiveRange();
    var a = range.getRow(); // Адрес строки активной ячейки

    if (SpreadsheetApp.getActiveSheet().getRange(a,1).getValues()==''){ //Если дата не заполнена
    SpreadsheetApp.getActiveSheet().getRange(a,1).setValues([[new Date()]]); //Заполняем текущую дату
    }

    }
    }

    • barn4k

      Добавьте после строки "var a" следующие строчки:

      var col = range.getColumn();
      var name = SpreadsheetApp.getActiveSheet().getName();
      if (name == "Лист1" && col == 6){ // Если изменения на листе "Лист1" и в колонке F

      дальше остальной код

      }

  2. Денис

    Добрый день! подскажите, для удобства работы дилеров с прайсом, хочу, чтобы при выборе любой ячейки с розничной ценой из диапазона D4:J36 значение этой выбранной ячейки попадало в ячейку АА3 и умножалось на 0,85. Т.е. чтобы в ячейке АА3 сразу показывалась дилерская цена выбранной позиции. С помощью стандартных функций такого не сделать, видимо, а js еще для меня как тайга сибирская для фашиста... Очень буду признателен за вашу помощь!

    • barn4k

      Добрый день!

      Не очень понятно для чего делать именно так. Чем не подходит добавление колонки с дилерскими ценами после колонки с розничными? Колонка АА3 находится далековато и прокручивать горизонтально прайс не очень удобно в любом случае :)

      Тем не менее, чтобы при нажатии на ячейку показывалась дилерская цена, так сделать не получится ни формулами, ни скриптами. Гугл не умеет обрабатывать такие события. Ближайший рабочий вариант - это выбрать ячейку, нажать на пункт меню и тогда она посчитается, но это лишние телодвижения и удобства не прибавит.

  3. Sonikelf

    Первая часть статьи, обучающая непосредственно процессу, специально для страждущих - https://sonikelf.ru/kak-pisat-skripty-makrosy-i-kod-v-google-scripts-chast-1/

  4. Сергей

    Добрый день! Статься очень полезная, спасибо большое! Но возник вопрос, ну или проблема :)
    Но Google Excel выбивает ошибку в строке 3.
    А именно:
    "var entries = [{name : "Покрасить",functionName : "MakeMeHappy"}];"
    Какая здесь ошибка - не могу понять. Подскажите пожалуйста!

* - комментарии могут появляться не сразу (или попасть в спам)