Программирование в гугл таблицах. Google Apps script: получение данных из Flurry
Доброго времени суток, дорогие читатели, вредины, злодеи, доброжелатели и прочие личности. Сегодня мы про Google Scripts , точнее скрипты в таблицах как таковые.
Я думаю, что очень многие из Вас умеют пользоваться Excel "ем или , а некоторые, может, даже и .
Те, кто пользуется диском Google (Google Drive ), наверное уже использовали Таблицы (Spreadsheets ) и заметили, что по функционалу они немного уступают Экселю, но тем не менее это всё ещё мощный инструмент.
Так вот, в Экселе были макросы (этакие команды, упрощающие и автоматизирующие вычисления) , написанные на небезызвестном языке VBA (Visual Basic for Applications) . В Таблицах Google также есть макросы, которые именуются скриптами и пишутся уже на языке Javascript . С ними мы сегодня и познакомимся.
Я заранее Вас предупреждаю о возможной сложности дальнейшего примера, т.к. он не столько обучающий, сколько.. Мм.. Так сказать, конечный факт, которым Вы можете пользоваться и.. И развивать, если это Вам знакомо.
Соберитесь в комочек мозга.. И приступим:)
Создание таблицы Google Drive / Scripts и наполнение её контентом
Рассмотрим такую простенькую задачку:
У нас есть две колонки, в первой мы пишем названия фруктов, а во второй цвет, который соответствует этому фрукту. И мы хотим, чтобы при вводе цвета в колонке цветов автоматически менялся бы цвет названия фрукта.
Если Вы забыли как вообще пользоваться документами Google , то милости просим почитать соответствующую и уже упомянутую выше . Если Вам это не нужно совсем, то читать наверное и дальше даже нет смысла. Хотя, конечно, кому что:)
Так вот, создаем новую таблицу Google , именуем её, например, "Фрукты ". Ну, как, например.. Учитывая, что пример про фрукты, то.. Ну Вы поняли:)
Теперь добавляем на первый лист наши фрукты и цвета:
Примечание! Для того, чтобы считались фрукты, введите в ячейку А1 формулу:
Хотите знать и уметь, больше и сами?
Мы предлагаем Вам обучение по направлениям: компьютеры, программы, администрирование, сервера, сети, сайтостроение, SEO и другое. Узнайте подробности сейчас!
="фрукт ("&COUNTA(A2:A)&")"
Теперь создадим макрос. Для этого идем в меню "Инструменты " и выбираем "Управление скриптами ". Появится всплывающее меню, где мы жмем на кнопку "Создать ".
В появившемся окошке выбираем "Пустой проект ".
Откроется редактор, который на первый взгляд (да и на второй) может вызвать ступор.
Собственно, что дальше? А дальше мы начинаем писать наш собственный макрос ручками (да, всё самостоятельно). Как будет выглядеть наш макрос? Нужно составить схемку сего процесса (иначе этот процесс займет у Вас очень много времени).
Нам нужно:
- Достать значения цветов из второй колонки;
- В соответствии с этими значениями задавать цвета для первой колонки.
Итак.. Вроде бы всё просто.. Если знать, как это делать, конечно:)
шКоддинг
Перейдем к самому коду:
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]){ 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]){ 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 ). Также можно нажать на "Уведомления " и добавить/убрать свой почтовый адрес из списка уведомлений.
Они будут приходить в случае, если скрипт выполнялся с ошибками.
Конечный результат действа:
Продолжения - -готовим и три. Ну и комментарии конечно содержат много вкусного.
P.S. За существование оной статьи отдельное спасибо другу проекта и члену нашей команды под ником “barn4k“.
Google Apps Script — это язык для автоматизации работы с онлайн-приложениями, появившийся в 2009 году. Его основа — классический JavaScript, обогащенный расширениями для работы с сервисами Google. После прочтения этой статьи ты овладеешь основами использования этого языка, выучишь пару приемов манипуляции с почтой и документами, а также получишь представление о необозримых возможностях Google Apps Script.
Основы использования
Начать писать Google Apps скрипты очень просто. Первым делом надо открыть редактор скриптов в любом приложении, взаимодействие с которым будем автоматизировать. В Google Docs он находится в меню «Инструменты -> Редактор скриптов». Далее надо выбрать проект, внутри которого будет располагаться скрипт (см. рис. 1). В открывшемся окне пишем код:
Function FirstExampleFunc() { Browser.msgBox("Это таки JS! "); }
Декларированную функцию можно запускать из «Инструменты -> Управление скриптами» или сделать для нее отдельную кнопку. Для этого надо прописать еще одну функцию:
Function menu() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var entries = [ {name: "Моя единственная функция", functionName: "FirstExampleFunc"}, ss.addMenu("Мои функции", entries); }
Теперь в нашем меню есть пункт под названием ExampleFunc, при клике на который открывается однострочное подменю «Моя единственная функция».
Декларированные функции можно использовать в формулах, которые вводятся внутрь ячеек электронных таблиц (см. рис. 3). Теперь перейдем к более практически полезным примерам.
WARNING
Перед запуском скрипта не забудь сделать резервную копию важной информации. Действия, выполненные GS, нельзя отменить нажатием
Работа с Google Docs
Когда встает задача автоматизации работы с офисными документами, первым делом на ум приходит VBA, одно упоминание которого оказывает на многих тотальное антиэкстатическое воздействие, вызывая болезненные воспоминания из школьного и университетского прошлого. Google Script однозначно удобнее и доступнее для понимания. Особенно для веб-разработчиков, ведь это же родной, привычный и любимый JS! Разберем пару примеров скриптов для Google Docs. Приведенный код заполняет левую верхнюю ячейку первого листа активной таблицы:
Var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets(); sheet.getRange("A1").setValue("Содержимое ячейки");
А этот код создает копию текстового документа и кладет его в определенное место:
Var source = DocsList.getFileById("SOURCE_ID"); var newFile = source.makeCopy("новое имя файла"); var targetFolder = DocsList.getFolderById("ID папки, в которой будет размещен свежесозданный файл"); newFile.addToFolder(targetFolder);
А вот так можно провести замену строк в текстовом документе:
Var doc = DocumentApp.openById("ID редактируемого документа"); doc.editAsText().replaceText("старый текст", "новый текст"); Logger.log(doc.getText())
Следующий пример кода подсвечивает определенные слова в тексте:
Var doc = DocumentApp.openById("id документа");
var textToHighlight = "текст для подсветки";
var highlightStyle = {};
highlightStyle = "#FF0000";
var paras = doc.getParagraphs();
var textLocation = {};
for (i=0; i Подробности: Если пишешь скрипт, включающий в себя функции поиска/замены текста, помни о том, что GS поддерживает регулярные выражения. Письмо отправляется одной короткой строкой: MailApp.sendEmail(«[email protected]», «тема письма», «текст письма») Если добавить к ней еще немного кода, то можно организовать рассылку по списку адресов из электронной таблицы (исходник ищи в приложении): Var sheet = SpreadsheetApp.getActiveSheet();
var numRows = sheet.getLastRow();
var dataRange = sheet.getRange(1, 1, numRows, 2)
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i)
{
var row = data[i];
var name = row;
var email = row;
var subject = "Тема письма";
var message = "Здравствуйте, " + name + "!";
MailApp.sendEmail(email, subject, message);
}
Помимо рассылок, с помощью GS можно производить автоматизированную обработку содержимого почтового ящика. Пример — удаление всех писем от адресата, на которого ты обиделся: Var threads = GmailApp.search("[email protected]");
for(var i = 0; i < threads.length; i++)
{
var messages = threads[i].getMessages();
for(var j = 0; j < messages.length; j++)
{
messages[j].moveToTrash();
}
}
Имей в виду, что Gmail не только защищает от входящего спама, но и ограничивает рассылку исходящего. Больше 500 писем за сутки с помощью Google Apps Script не выйдет. С помощью Google Apps Script можно переводить текстовые строки с одного языка на другой. Пример: Var word = LanguageApp.translate("кукушка", "ru", "es");
Logger.log(word);
// Скрипт выведет в консоль "cuco" — кукушка по-испански
Коды для языков можно посмотреть в адресной строке сервиса Google Translate. Если выделить код и нажать Google Apps Script может работать с файлами пользователя, размещенными на Google Drive. Этот скрипт выводит в консоль имена всех файлов пользователя: Var files = DriveApp.getFiles();
while (files.hasNext()) {
var file = files.next();
Logger.log(file.getName());
}
К файлам можно применять несколько десятков различных методов. Вот некоторые из них: Адресная книга также может быть подвергнута автоматизированной обработке. Приведенный ниже код копирует все контакты из группы «Редакция» в лист Google Spread Sheet: Var group = ContactsApp.getContactGroup("Редакция");
var contacts = group.getContacts();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Контакты редакции");
for (var i in contacts) {
// Сохраняем данные о контактах в ячейки: имя, фамилия, номер телефона
sheet.getRange(i, 1, 1, 1).setValue(contacts[i].getGivenName());
sheet.getRange(i, 2, 1, 1).setValue(contacts[i].getFamilyName());
sheet.getRange(i, 3, 1, 1).setValue(contacts[i].getPhones());
// И еще есть метод для получения номера пейджера (!) контакта
sheet.getRange(i, 4, 1, 1).setValue(contacts[i].getPager());
}
С помощью Google Apps Scripts можно работать с сервисом Google Task — создавать новые задачи и парсить уже имеющиеся. Этот код создает новое дело в списке:
// Найти ID тасклиста можно внутри адресной строки в сервисе Google Task
var taskListId="id тасклиста,";
var newTask = {
title: "Выбросить финиковые косточки",
notes: "Не забыть косточки под кроватью"
};
newTask = Tasks.Tasks.insert(newTask , taskListId);
Logger.log ("Задача с ID "%s" создана", newTask.id);
А таким образом можно вывести список нумерованных задач в консоль:
// Кладем все задачи списка в массив
var tasks = Tasks.Tasks.list(taskListId);
for (var i = 0; i < tasks.items.length; i++)
{
var task = tasks.items[i];
Logger.log(i. ". ",%s, task.title, task.id);
}
Задачи можно перемещать из одного списка в другой с помощью метода move, дополнять с помощью метода update и удалять с помощью метода delete. Создавать события в календаре тоже можно автоматически (и так же, как в случае с рассылкой, формировать информацию о них из строк таблицы). Код для создания события: Var timeZone = CalendarApp.getTimeZone();
var description = Utilities.formatString("%s from %s to %s", "заголовок события", dateString_("дата начала события", "часовой пояс"), dateString_("дата конца события", "часовой пояс"));
CalendarApp.createEventFromDescription(description);
Гаджет — это приложение-контейнер, которое размещается на веб-странице и исполняет определенные функции. Примеры: мини-блок в углу страницы с прогнозом погоды или календарем. Чтобы поместить Google Script внутрь гаджета, необходимо в меню редактора скриптов выбрать пункт «Publish -> Deploy as web app». А если нажать Можно настроить скрипт так, чтобы он выполнялся после определенного события. К примеру, после открытия/редактирования электронной таблицы или отправки данных формы. Для этого существует сервис для работы с базами данных Google Cloud SQL. По сути — классический MySQL в облаке. Может взаимодействовать с Google Apps Script по стандарту Java Database Connectivity. Вот пример кода, который производит чтение записей из таблицы: Var conn = Jdbc.getConnection(dbUrl, user, userPwd);
var start = new Date();
var stmt = conn.createStatement();
stmt.setMaxRows(1000);
var results = stmt.executeQuery("SELECT * FROM entries");
var numCols = results.getMetaData().getColumnCount();
while (results.next()) {
var rowString = "";
for (var col = 0; col < numCols; col++)
{
rowString += results.getString(col + 1) + "\t";
}
Logger.log(rowString)
}
results.close();
stmt.close();
Стоимость использования сервиса — 88 долларов в год за 10 Гб свободного места. С другими базами данных Google Apps Script, к сожалению, работать не может. Если ты запланировал написать скрипт, который должен взаимодействовать с данными, не стоит сразу расстраиваться или истощать свой бюджет пожертвованиями на закупку квадроциклов для жителей Кремниевой долины. Есть два способа выкрутиться из этой ситуации: Я думаю, что далеко не каждый из наших читателей успел опробовать все онлайн-сервисы Google. В целях расширения кругозора и стимуляции творческого воображения приведем краткий обзор возможностей приложений, работу которых можно автоматизировать с помощью Google Apps Script. По старой доброй традиции, которой уже почти три месяца, мы выложили несколько исходничков на GitHub: У Google есть множество API для разработчиков, которые можно внедрять в программы, написанные на Google Apps Script. Для этого надо подключить в редакторе скриптов эту возможность (в меню Resources, далее Advanced Google services). После этого можно будет задействовать возможности следующих сервисов: Я немного смущен тем, как новые библиотеки должны быть опубликованы. Я создаю то, что я считаю очень классной и полезной библиотекой для скриптов Google Apps. Но я не знаю, где я могу опубликовать это, чтобы другие могли наслаждаться и предоставлять некоторую обратную связь. Я замечаю, что я могу опубликовать свой скрипт в качестве шаблона, но я считаю, что это позволит другим импортировать мой скрипт и редактировать по своему усмотрению, но если есть обновления, им нужно будет снова импортировать мой код. новая функция библиотеки, с другой стороны, требует, чтобы пользователи узнали о вашем идентификаторе приложения, чтобы импортировать его в свои проекты. На сегодняшний день единственным местом, где перечислены библиотеки, является « Notable Script Libraries » на сайте разработчиков Google. Но это только для выбранных вручную библиотек из Google. Поэтому мои вопросы здесь:
Когда следует публиковать мой скрипт в качестве шаблона, а не публиковать его как библиотеку? Они кажутся немного перекрывающимися для меня. Как другие люди делятся своими библиотеками? Планирует ли Google какой-то каталог?INFO
Работа с почтой
WARNING
Работа с Google Translate
INFO
Работа с Google Drive
Работа с Google Contacts
Работа с Google Tasks
Работа с календарем
Формы обмена скриптами
INFO
События
Работа с базами данных
Приложения, с которыми может взаимодействовать Google Apps Script
SRC
Advanced Google Services