Google Docs, Google Drive, Google Scripts: как писать скрипты, макросы и код — часть 0

статьи
интернет

Доброго времени суток, дорогие читатели, вредины, злодеи, доброжелатели и прочие личности. Сегодня мы про Google Scripts, точнее скрипты в таблицах как таковые.

 

Google Docs, Google Drive, Google Scripts: как писать скрипты, макросы и код - часть 0 - иконка статьи

Я думаю, что очень многие из Вас умеют пользоваться Excel'ем или его аналогом, а некоторые, может, даже и гугловскими таблицами, про которые писали здесь.

Те, кто пользуется диском Google (Google Drive), наверное уже использовали Таблицы (Spreadsheets) и заметили, что по функционалу они немного уступают Экселю, но тем не менее это всё ещё мощный инструмент.

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

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

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

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

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

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

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

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

список значений - Google Docs, Google Drive, Google Scripts: как писать скрипты, макросы и код - скриншот 1

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

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

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

создание макроса -  Google Docs, Google Drive, Google Scripts: как писать скрипты, макросы и код - скриншот 2

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

создание скрипта -  Google Docs, Google Drive, Google Scripts: как писать скрипты, макросы и код - скриншот 3

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

Редактор Скриптов -  Google Docs, Google Drive, Google Scripts: как писать скрипты, макросы и код - скриншот 4

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

Нам нужно:

  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 добавляет меню "Скрипты" к таблице при открытии оной. И выглядит это дело так:

добавление своего меню -  Google Docs, Google Drive, Google Scripts: как писать скрипты, макросы и код - скриншот 5

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

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, заходим в меню "Скрипты" и выбираем опцию "Покрасить". Смотрим, как наши фрукты обрели жизнь цвета :)

В общем-то на этом всё. Но не совсем.

к содержанию ↑

Скрипты и макросы таблиц Google, дополнение

Но можно сделать небольшое дополнение, чтобы не приходилось каждый раз запускать функции вручную.

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

Они будут приходить в случае, если скрипт выполнялся с ошибками.

добавление триггеров -  Google Docs, Google Drive, Google Scripts: как писать скрипты, макросы и код - скриншот 6

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

результат -  Google Docs, Google Drive, Google Scripts: как писать скрипты, макросы и код - скриншот 7

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

к содержанию ↑

Послесловие

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

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

Продолжения раз-два-готовим и три. Ну и комментарии конечно содержат много вкусного.

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

сказать «спасибо»подписатьсяобучаться
Хотите знать и уметь, больше и сами?

Мы предлагаем Вам скачать бесплатные книги от автора. Компьютеры, программы, администрирование, сервера, сети и другое. Не является рекламой. Предложение от sonikelf.ru

Скачать книги
Sonikelf's Project's логотип Sonikelf's Project's логотип Космодамианская наб., 32-34 Россия, Москва (916) 174-8226

191
Комментарии — присоединяйтесь!

10000
72 Цепочка комментария
119 Ответы по цепочке
1 Последователи
 
Популярнейший комментарий
Цепочка актуального комментария
63 Авторы комментариев
  Подписаться  
Уведомление о
Sonikelf

Первый типа :)

HardDisk

Второй :)

cthdth

Третий :)

ProQ.Lucky

Четвертый :)
За написание статьи Андрею и 6aPHaK спасибо)
Извините что не в тему. Хотел спросить когда собираетесь и собираетесь ли вообще выпускать свой сборничек с программами, который карманный софт. Про него говорилось в аудиокасте.
И объясните пожалуйста куда такие вопросы задавать в следующий раз, чтобы темы не засорять.

DmN

"Всё такое прочее в комментариях!" Радуйтесь! =)

SerGe

Уважаемый Андрей, здравствуйте! А по Excel у вас не будет чего-нибудь похожего на эту статью?:)

SerGe

Очень хочется! Кажется это будет интересно многим, не только мне. Excel для меня вообще лес дремучий:)

Сергей

Где справочник по по этим скриптам можно найти? И примеры все приводят по таблицам, а хотелось бы возможности и ограничения по формам.
В любом случае, спасибо! полезно.

том

Большое спасибо за подробное расписывание скрипта. Как раз то, что нужно тому, кому охота разобраться. Только одного примера мало. Очень хотелось бы увидеть разные примеры с такими же подробными описаниями! В екселе в этом плане немного проще - можно записать последовательность действий в скрипт, а потом только подправить, чего не хватает.

Stijit

Отличная статья, спасибо. А как сделать такой скрипт: если в ячейка покрашена в желтый цвет, то копировать всю строку на другой лист

Заранее спасибо, Stijit

Дмитрий

я офигел, спасибо! я даже не стал читать до конца, ахаха, но это реально мотивирует изучить скрипты!

если будет возможность подскажите, как считать сумму ячеек, которые окрашены в один цвет?

Серега

Круть! А скажите, может ли быть такая фича:
Нужен скрипт для Google docs, который при изменении каждой ячейки сохраняет предыдущую информацию, новую добавляет перед старой, в квадратных скобках пишет : дата, время, пользователь. В конце «;». И так каждый раз. Исключение 1я строка – название столбцов
Пример:
- добавили фразу «заявка принята» получилось:
«заявка принята [26.03.14 14:58 GoodAvto@gmail.com];»
-добавили «одобрено», получилось:
«одобрено [26.03.14 14:59 GoodAvto@gmail.com]; заявка принята [26.03.14 14:58 GoodAvto@gmail.com];»

Tom

Замечательная идея. получится, что в ячейке будет видно только начало строки, актуальное на сейчас, но предыдущая информация не будет потеряна.

Кирилл

Доброго времени суток. Очень нужна Ваша помощь.
Нужен скрипт который блокирует изменение данных в ячейках по такому принципу:
Если (например) на листе 10 в ячейке А1 проставлено "STOP" (или там цифра 1), то определенные диапазоны ячеек на листах 1,2,3,4... защищены от изменений и удаления в т.ч. и у автора документа (во избежание случайного удаления или изменения), а если проставлено "RUN" (или например 0), то данные в этих же диапазонах можно редактировать и удалять. Обычными средствами этого достигнуть не удалось. И было бы шикарно, если бы добавить еще и введение пароля.

Михаил

Доброго времени! Спасибо за хорошую статью, просто и доступно.
Столкнулся с такой проблемой - Google формы при сохранении данных в таблицу изменяют в числах разделитель десятичной части с "запятой" на "точку". В итоге, вместо цифр в таблице имеем текст. Сейчас меняю в ячейках "точку" на "запятую" вручную через Меню-Правка-Найти и заменить. Но хотелось бы это автоматизировать. Как правильно написать команду замены? Готов на любую помощь. Заранее спасибо.

Дмитрий

Доброго время суток, бодаюсь с такой задачей :

Необходимо что бы в ручном режиме либо автоматическом (ежедневно в 23-00 данные из столбца G переносились на второй лист в определенные ячейки), т.е. не замещались друг другом а на регулярной основе дополнялись.

Есть у кого идеи как реализовать ? :) Приветствуется любая помощь ибо в дополнениях гугла не нашел готового решения

LordBeheliar

Можно ли сделать уведомление через смс сервис: что бы приходило сообщение об изменении данных в определенной ячейке?

Раиль

Доброго времени суток всем. Пишу с просьбой. Знаний в google скриптах = 0. Но хочу сделать импорт данных из google таблиц (Имя, Телефон, Примечание)в google контакты в автоматическом режиме.

На данные момент на сайте есть форма обратной связи на ней посетитель оставляет свои данные, имя, телефона и т.д. Данные сразу же попадают в таблицу Google. И соответсвенно автоматически заполняется. Хотелось бы чтобы они также импортировались в кнтакты Google. чтобы призвонке уже знать что клиент с нами контактировал.

Заранее благодарен за ответ.

Tim

А есть ли у кого-нибудь идеи, как сделать так, чтобы при создании нового листа, ему приваивалось имя в форме даты и времени создания листа?

Александр

Здравствуйте!

А как считать скриптом формулу (не значение - getvalue) ячейки?
И потом перенести ее (вставить в другое место).

Спасибо.

Юра

Здравствуйте!.
Как написать скрипт, что бы он срабатывал когда в ячейку А№(№-1,2,3....) что то записывали, и тогда в ячейку В№(№-1,2,3....) записывалась дата, когда ввели эти данные.

Виталий

Помогите пожалуйста, задача такая: в диапазоне ячеек F2:F21 есть проверка данных с определенными значениями, как сделать так что бы можно было выбирать не одно а несколько значений?

Виталий

еще раз большое спасибо!

Виталий

Подскажите пожалуйста, вписал ваш скрипт, все замечательно работает, но есть вопрос.
Скрипт работает по всей колонке номер 6, это колонка F в моей таблице, можно ли сделать так что бы он работал с F2 по F20?
function onEdit(e) { // функция добавления значений в строку. Срабатывает автоматически, когда какое-то действие c ячейками происходит
var sheet = SpreadsheetApp.getActive();
var sheetName = sheet.getSheetName();
if (typeof e.value != "object"){ // "Сводная" - название вашего листа, где требуется проверка данных
var range = e.range;
var flag = false;
var newValue = e.value;
var oldValue = e.oldValue;
var column = range.getColumn();
if ((column == 6) && oldValue && newValue){ // 3 - номер колонки с проверкой данных
var strAr = oldValue.split("\n"); // разделитель между значениями
for (var i=0; i<strAr.length; i++){ // Если прошлое значение и нынешнее существует, то
if(strAr[i] == newValue) { // если в ячейке такое значение есть,
range.setValue(oldValue); // то новое не добавляется
flag = true;
break;
}
}
if (!flag) range.setValue(oldValue+"\n"+newValue) // если нету, то оно добавляется
}
}
};

Виталий

Спасибо! :)

Илья

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

Илья

Файлов много и это могут быть не Гугл таблицы, а эксель.

Илья

Файлы могут быть в разных папках. Т.е. нужно создать отдельную папку куда добавить все нужные файлы?

Андрей

Добрый день. Отличная статья.
Подскажите, как сделать следующее:
Есть несколько столбцов с датами напротив событий. Например, время гонки разных пилотов в разных пилотов. То есть

Планируемое время 0 12 20 27
Фамилия москва новосибирск хабаровск владивосток
Петров 01.01.2016 12.01.2016 25.01.2016
Иванов 06.01.2016 25.01.2016
Сидоров 12.01.2016 25.01.2016 27.01.2016

Я захожу в таблицу, у меня установлено планируемое время в пути. Скрипт проверяет, просрочил ли кто-то время. Ищет пустую строку, вычисляет прошедшее время с момента старта и если прошло дней больше, чем планировалось, то выделяет пустую ячейку красным цветом и ставит, сколько дней прошло (=РАЗНДАТ(A7;СЕГОДНЯ();"D")). Как использовать функцию РАЗНДАТ в скрипте?

Milla

Здравствуйте

Подскажите пожалуйста как мне создать кнопку, при нажатии которой будут сворачиваться/разворачиваться строки, к примеру, с 9 по 24 и расположить её в ячейке, к примеру, А8?