0

Google script

Изучаем основные приемы взаимодействия Google Apps Script с Документами.

Это наша вторая статья про Google Apps Script — подвиде JavaScript, обогащенном классами и методами для работы с сервисами Google. В первой четыре готовых рецепта, код которых можно вставить в редактор в практически неизменном виде, запустить и мгновенно получить PROFIT.

У этого материала несколько другая задача. Приведенные ниже примеры кода, предназначены скорее для обучения и размышления, чем для быстрого решения проблем. На основе приемов, использованных в этих скриптах можно составить множество классных программ для эффективного решения задач автоматизации работы с Docs. Чтобы не повторяться, для ознакомления с основами использования GAS рекомендуем прочитать статью 4 лайфхака Gmail и Google Apps Script.

Автоматическая генерация документов

Предположим, что вам нужно собрать отзывы о каком-либо событии от всех своих коллег и чтобы один сотрудник не мог видеть записи другого. Один из способов это сделать — создать для каждого человека файл в Google Docs и открыть доступ ему одному.

Для этого создаем новую таблицу в Google Docs, в первый столбец запишем фамилии сотрудников, а во второй их почтовые адреса. Проверяем информацию и запускаем код.

Проверяем содержимое документов

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

Код выполняется из таблицы, созданной в предыдущем примере, так как в ней записаны ID нужных нам документов. С помощью Google Apps Script можно открывать файлы идентифицируя их по имени, дате создания, содержимому и т.д. Но работа с Google Drive тема отдельной подробной статьи, которая появиться на iphones.ru в ближайшее время.

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

Собираем текст нескольких документов в один

Автоматическое форматирование

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

Google Apps Script — это язык для автоматизации работы с онлайн-приложениями, появившийся в 2009 году. Его основа — классический JavaScript, обогащенный расширениями для работы с сервисами Google. После прочтения этой статьи ты овладеешь основами использования этого языка, выучишь пару приемов манипуляции с почтой и документами, а также получишь представление о необозримых возможностях Google Apps Script.

Основы использования

Начать писать Google Apps скрипты очень просто. Первым делом надо открыть редактор скриптов в любом приложении, взаимодействие с которым будем автоматизировать. В Google Docs он находится в меню «Инструменты -> Редактор скриптов». Далее надо выбрать проект, внутри которого будет располагаться скрипт (см. рис. 1). В открывшемся окне пишем код:

function FirstExampleFunc() { Browser.msgBox(«Это таки JS! «); } Рис. 1. Стартовое окно редактора скриптов Другие статьи в выпуске:

Хакер #187. Обходим Blizzard Warden

  • Подписка на «Хакер»

Декларированную функцию можно запускать из «Инструменты -> Управление скриптами» или сделать для нее отдельную кнопку. Для этого надо прописать еще одну функцию:

function menu() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var entries = ; 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<paras.length; ++i) { textLocation = paras.findText(textToHighlight); if (textLocation != null && textLocation.getStartOffset() != -1) { textLocation.getElement().setAttributes(textLocation.getStartOffset(),textLocation.getEndOffsetInclusive(), highlightStyle); } } Рис. 4 Вот здесь можно узнать ID документа

Подробности:

  • Google Apps Script References для Google Doсument
  • Google Apps Script References для Google Spreadsheet

INFO

Если пишешь скрипт, включающий в себя функции поиска/замены текста, помни о том, что GS поддерживает регулярные выражения.

Работа с почтой

Письмо отправляется одной короткой строкой: MailApp.sendEmail(«irairache@gmail.com», «тема письма», «текст письма») Если добавить к ней еще немного кода, то можно организовать рассылку по списку адресов из электронной таблицы (исходник ищи в приложении):

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; var name = row; var email = row; var subject = «Тема письма»; var message = «Здравствуйте, » + name + «!»; MailApp.sendEmail(email, subject, message); }

Помимо рассылок, с помощью GS можно производить автоматизированную обработку содержимого почтового ящика. Пример — удаление всех писем от адресата, на которого ты обиделся:

var threads = GmailApp.search(‘enemy@example.com’); for(var i = 0; i < threads.length; i++) { var messages = threads.getMessages(); for(var j = 0; j < messages.length; j++) { messages.moveToTrash(); } }

Больше информации ищи в Google Apps Script References для Gmail.

WARNING

Имей в виду, что Gmail не только защищает от входящего спама, но и ограничивает рассылку исходящего. Больше 500 писем за сутки с помощью Google Apps Script не выйдет.

Работа с Google Translate

С помощью Google Apps Script можно переводить текстовые строки с одного языка на другой. Пример:

var word = LanguageApp.translate(‘кукушка’, ‘ru’, ‘es’); Logger.log(word); // Скрипт выведет в консоль «cuco» — кукушка по-испански

Коды для языков можно посмотреть в адресной строке сервиса Google Translate.

Подробности: Google Apps Script References для Google Language.

Если выделить код и нажать <Shift + Tab>, то магическим образом расставятся все отступы для условий, циклов, объявленных функций и всех других мест, где им положено быть.

Работа с Google Drive

Google Apps Script может работать с файлами пользователя, размещенными на Google Drive. Этот скрипт выводит в консоль имена всех файлов пользователя:

var files = DriveApp.getFiles(); while (files.hasNext()) { var file = files.next(); Logger.log(file.getName()); }

К файлам можно применять несколько десятков различных методов. Вот некоторые из них:

  • addEditor(«email пользователя») — наделяет пользователя правами на редактирование файла;
  • getOwner() — узнать владельца файла;
  • makeCopy(«имя», «путь») — создать копию файла;
  • getLastUpdated() — возвращает пользователя, который внес последнее изменение.

Работа с Google Contacts

Адресная книга также может быть подвергнута автоматизированной обработке. Приведенный ниже код копирует все контакты из группы «Редакция» в лист 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.getGivenName()); sheet.getRange(i, 2, 1, 1).setValue(contacts.getFamilyName()); sheet.getRange(i, 3, 1, 1).setValue(contacts.getPhones()); // И еще есть метод для получения номера пейджера (!) контакта sheet.getRange(i, 4, 1, 1).setValue(contacts.getPager()); }

Подробности: Google Apps Script References для Google Contacts.

Работа с Google Tasks

С помощью 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; Logger.log(i. «. «,%s, task.title, task.id); }

Задачи можно перемещать из одного списка в другой с помощью метода move, дополнять с помощью метода update и удалять с помощью метода delete.

Всего есть несколько десятков методов для работы с задачами. Полный их список доступен вGoogle Apps Script References для Google Tasks.

Работа с календарем

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

var timeZone = CalendarApp.getTimeZone(); var description = Utilities.formatString( ‘%s from %s to %s’, «заголовок события», dateString_( «дата начала события», «часовой пояс» ), dateString_(«дата конца события», «часовой пояс» )); CalendarApp.createEventFromDescription(description);

Google Script References для Calendar

Формы обмена скриптами

Есть два основных способа поделиться своим скриптом с другим человеком (без учета непосредственного обмена исходным кодом) — ссылка и гаджет. В первом случае все просто: пользователь получает ссылку на программу, переходит по ней, и скрипт немедленно начинает выполняться (при условии, что человек авторизирован в своем Google-аккаунте).

Гаджет — это приложение-контейнер, которое размещается на веб-странице и исполняет определенные функции. Примеры: мини-блок в углу страницы с прогнозом погоды или календарем. Чтобы поместить Google Script внутрь гаджета, необходимо в меню редактора скриптов выбрать пункт «Publish -> Deploy as web app».

Больше информации о Google Gadgets

А если нажать <Ctrl + Space>, то включится режим автозавершения, то есть редактор будет дописывать код за тебя.

События

Можно настроить скрипт так, чтобы он выполнялся после определенного события. К примеру, после открытия/редактирования электронной таблицы или отправки данных формы. Подробности о работе с Events

Работа с базами данных

Для этого существует сервис для работы с базами данных 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 Drive;
  • второй — разместить базу на стороннем сервере, на нем же разместить {php}{hode.js}{python}{и т. д.} скрипт, который будет выполнять к ней запрос и возвращать ответ в формате JSON или XML, а его, в свою очередь, подвергать парсингу внутри GS. Подробности о Google Cloud SQL

Приложения, с которыми может взаимодействовать Google Apps Script

Я думаю, что далеко не каждый из наших читателей успел опробовать все онлайн-сервисы Google. В целях расширения кругозора и стимуляции творческого воображения приведем краткий обзор возможностей приложений, работу которых можно автоматизировать с помощью Google Apps Script.

  • Google Mail — почтовый клиент. Наиболее интересна в нем возможность отправлять письма людям, адреса которых неизвестны. Для этого надо включить настройку «Отправлять письма пользователям Google+» и ввести имя и фамилию получателя в поле «Кому».
  • Google Calendar — органайзер. Самое удобное в нем — возможность отправки SMS-уведомлений о событиях на номера российских операторов.
  • Google Contacts — приложение для хранения контактов. Самый цимес его в том, что если ты случайно синхронизируешь свой список контактов с новым смартфоном и все имена сотрутся, то сможешь попросить у Google резервную копию предыдущей версии, которая навечно сохранена в его архивах.
  • Google Drive — облачное хранилище данных. 15 Гб, на которых также размещаются данные всех других приложений, доступны бесплатно.
  • Google Maps — онлайн-карты. Жителям СНГ повезло, у них есть альтернативный инструмент для построения маршрутов и просмотра панорам улиц — Яндекс.Карты. Для жителей большинства других территорий альтернатив нет. Google Maps — единственная всемирная картографическая система, позволяющая искать населенные пункты, вводя названия на языке государства, в котором они находятся. Допустим, не Kotlas, а Котлас, не Vagharshapat, а Վաղարշապատ.
  • Google Docs — онлайн-редактор офисных документов. Во время написания статьи этот сервис совершил мега-прорыв — появилась возможность редактировать документы, созданные в Microsoft Office. Это произошло после интеграции сервиса с функционалом приложения Quickoffice. Ради интереса попробовала отредактировать в Google Docs пояснительную записку к диплому (как пример документа с простейшим форматированием). Преобразование docx в гугл-формат пришлось ждать около минуты, и внешний вид текста явно отличался от оригинала.
  • Google Forms позволяет создавать формы для сбора различных данных (онлайн-опрос, страницу регистрации на событие, обратную связь для сайта и прочее), которые можно привязать к таблицам в различных форматах (HTML, CVS, TXT, PDF, RSS, XLS, ODF). Собранные данные хранятся на Google Drive.
  • Google Sites — бесплатный хостинг (100 Мб) с предельно ограниченным функционалом и собственной wiki-разметкой. Полнофункциональный HTML, а также CSS и JS недоступны.

SRC

По старой доброй традиции, которой уже почти три месяца, мы выложили несколько исходничков на GitHub:

  • autodeletemail.gs — удаляет письма, с момента получения которых прошло n-ное количество дней;
  • snoozeemails.gs — скрипт для повторной отправки самому себе прочитанных писем через определенный промежуток времени;
  • sendsmsaboutemails.gs — настраивает отправку SMS в случае получения писем, соответствующих определенным критериям. Перед его использованием надо указать свой номер телефона Google Calendar;
  • savemailtopdfindrive.gs — сохраняет содержимое письма в файлах на Google Drive;
  • fromcalendartospreadsheet.gs — записывает информацию из календаря в электронную таблицу;
  • sendmailsfromspreadsheet.gs — рассылает письма по списку адресов из электронной таблицы;
  • createdocsfromspread.gs — генерирует текстовые документы из данных электронной таблицы.

Advanced Google Services

У Google есть множество API для разработчиков, которые можно внедрять в программы, написанные на Google Apps Script. Для этого надо подключить в редакторе скриптов эту возможность (в меню Resources, далее Advanced Google services). После этого можно будет задействовать возможности следующих сервисов:

  • Google AdSense — сервис для работы с контекстными рекламными объявлениями.
  • Google Analytics — осуществляет анализ посещаемости веб-сайтов и мобильных приложений.
  • Google BigQuery — позволяет производить различные манипуляции (запись, чтение, перемещение и так далее) над большими объемами данных, а также анализировать их.
  • Google Fusion Tables — экспериментальный сервис, позволяющий размещать данные в облаке, отправлять к ним запросы и получать результаты выполнения в формате JSON и CSV. Из которых, в свою очередь, можно формировать электронные таблицы, карты, графики и другие виды визуального представления данных.
  • Google Domains — сервис для регистрации доменов (новый проект, открылся в конце июня 2014 года).
  • Google Mirror — API для взаимодействия с Google Glass.
  • Google Prediction — сервис для анализа данных (основанный на технологии машинного обучения). Позволяет внедрять в приложения следующие фичи: классификатор документов и писем, расчет churn rate (показатель оттока пользователей), детектор спама, оптимизатор маршрутизации сообщений и множество других интересных вещей, достойных отдельной статьи.
  • Google Tasks — встроенный в Gmail сервис для составления списков дел.
  • Google URL Shortener — любимый нашим журналом сервис для сокращения длинных ссылок.
  • YouTube Analytics — сервис для анализа статистики просмотров видео на YotTube. Примечателен возможностью узнать демографические и географические характеристики пользователей, смотрящих определенный видеоролик. Ведь интересно выложить очередную копию клипа на песню «До свидания, кореша» и проанализировать, какого пола, возраста и места жительства ее слушатели. Подробности

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

Гай Юлий Цезарь

Древнеримский государственный и политический деятель, полководец, писатель…

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

Гай Юлий был способен спокойно надиктовывать двум-трем писцам послания государственной важности (на секундочку!), при этом находясь верхом на лошади, на марше, а в те времена не использовали стремян и удержаться в седле было совсем непросто.

Неплохо, правда? Если б каждый так мог…

Но не спешите вешать нос, у Юлия не было Google Apps Script.

Постановка задачи и инфа к размышлению

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

А вот тут мы дружно вспоминаем афоризм Цезаря и, не тратя время на страх и сомнения, с головой окунемся в дивный мир новых возможностей.

Для начала стоит определиться с задачей. Тут все просто:

  • Более-менее ознакомиться с функционалом редактора скриптов от гугл.
  • И попутно узнать и научиться чему-нибудь новому, что в будущем пригодиться.

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

  • Автономные сценарии — Standalone Scripts.
  • Скрипты, связанные с контейнером — Container-bound Scripts.
  • Веб-приложения — Web Apps

Автономные сценарии

Это отдельные файлы GAS, несвязанные с другими файлами приложений G Suite. Они отображаются системой и их без труда можно открыть прямо с Google Диска.

Конечно, при необходимости скрипт реально развернуть как веб-приложение или опубликовать его как add-on (при создании которых, кстати, google рекомендует как раз использование автономных скриптов).

Скрипты, связанные с контейнером

К примеру, есть у вас файл Таблицы или того же Документа, или Формы, или Слайдов, или Google Сайт, так вот, связанный скрипт прикручен к нему и работает как бы в его обеспечении. Файл к которому привязан скрипт называют родительским или контейнером.Такой сценарий не найти на Диске Google.

Редактировать его возможно через открытый файл-контейнер по пути Инструменты > Редактор скриптов.

Довольно продолжительное время это был единственный способ.

Однако не так давно, Google породил менеджер скиптов ( script.google.com ), который позволяет запускать любой доступный проект и вносить необходимые правки.

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

К связанным скриптам относят и пользовательские функции. Я думаю здесь как раз все понятно. Не нашли нужной формулы среди стандартных в Таблицах Google? Не беда! Напишем свою, собственную, которая будет работать по тому же принципу. Вызывать ее надо прямо в ячейке =myFunctionName() в точности как и стандартную.

Говоря о связанных скриптах нельзя не упомянуть надстройки (дополнения) — Add-ons for G Suite. Любой скрипт, будь он связанный или автономный можно опубликовать как дополнение. Так сказать, явить его миру. После чего скрипт станет доступен для установки всем желающим. Зайдя в хранилище аддонов гугл, вы обнаружите великое множество таких сценариев.

И наконец последний тип.

Веб-приложения

Любой скрипт можно развернуть как веб приложение. Главное чтобы скрипт содержал функцию doGet(e) или doPost(e). Проще простого. Фактически можно создать веб-сервер с помощью одной строки (Шутка!)

function doGet() { return HtmlService.createHtmlOutputFromFile(‘Index’); }

Веб-приложения открывают широчайшие возможности.

Если взяться перечислять способы применения данного типа программ, то на это может уйти весь день.

Лучше привести хороший пример. Перейдя по вы попадете на стартовую страницу очень любопытного проекта. Он еще пока в разработке, доступна только б-версия, но уже невозможно не оценить безграничный потенциал этого приложения.

Данный проект, Web-приложение от stomaks — это посредник между пользователем и Google Apps Script. Приложение дает шанс в полной мере насладиться возможностями продвинутой платформы от Google.

Среди доступных программ-модулей приложения уже доступны такие как:

  • Ведение домашней бухгалтерии (Google +)
  • Боты для социальных сетей Facebook, Telegram, LinkedIn, Twitter, Pinterest и др.
  • Автоматическая e-mail рассылка, с поддержкой отчетов
  • Парсер, синтаксический анализатор (Google +)
  • и другие.

Этот список не закончен. Проект на начальном этапе развития — это заготовка и добавить туда можно все что угодно. Хоть прямо сейчас свяжитесь с автором и предложите свой модуль или функцию.

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

Бесспорно, что каждый тип требует гораздо более подробных разъяснений и примеров. С другой стороны не переводить же все гайды* подряд…

(* от. англ. guide — руководство, мануал, справочник)

По ходу углубления знаний и навыков работы с этой гугловской платформой многие вопросы отпадут сами собой.

А поэтому давайте-ка приступим к практике.

Таблицы и редактор скриптов

По ходу изучения возможностей редактора предлагаю сделать что-нибудь полезное.

К примеру, научимся находить таблицу на Google Диске, доставать из нее значения и выводить их в журнал (по мере изучения GAS с полученными данными будем совершать более сложные манипуляции, а пока так…).

Поехали…

Предположим, что на вашем диске есть такая таблица (если нет, то надо бы ее создать или кликни сюда чтобы скопировать ее к себе на Google Диск) :

Самый простой способ получить доступ к содержимому таблицы — это знать ее уникальный идентификатор (ID).

Как получить id таблицы?

Можно, его просто посмотреть, он указывается всегда в одном и том же месте в адресной строке https://…/d/{id}/edit

( {id} и есть id таблицы, стоит отметить что принцип получения id документа сохраняется и для других сервисах гугл ).

можно его получить и с помощью скрипта.

/* * Функция getSpreadsheetID() возвращает идентификатор текущей таблицы * * @return {string} spreadsheet_id — Идентификатор (id) текущей таблицы */ function getSpreadsheetID () { var spreadsheet_id = SpreadsheetApp.getActive().getId(); Logger.log( spreadsheet_id ); return spreadsheet_id; }

Данный код работает только в скрипте связанным с таблицей.

И возвращает id-шник таблицы с которой связан.

SpreadsheetApp.getActive() возвращает ссылку на текущую таблицу, а затем уже получаем нужный нам идентификатор директивой — getId().

Или другими словами.

Cлужба SpreadsheetApp позволяет скриптам создавать, получать доступ и изменять файлы Google Таблиц.

Функция getActive() возвращает текущую активную электронную таблицу или null, если ее нет.

А функция getId() возвращает уникальный идентификатор (id) для этой таблицы..

Собственно, надо бы сказать и пару слов о редакторе….

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

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

Правда, надо не забыть нажать CTRL + Пробел. Кстати, табуляцию и отступы редактор тоже помогает расставлять, конечно, по мере своих сил.

Вернемся к коду, стало быть таблица у нас есть, ее id мы получили, так давайте получим и все остальное…

Пример 1. Как получить данные из активной таблицы активного листа?

Код ниже позволяет получить все данные из активной таблицы, активного листа.

Обратите внимание что это пример для скрипта, связанного с таблицей.

/* * Функция getValuesByActiveSheet() получает и возвращает все данные из активной * таблицы активного листа. * * @return {array} sheet_values — Данные в виде двумерного массива */ function getValuesByActiveSheet () { var spreadsheet_link = SpreadsheetApp.getActive(), sheet_link = spreadsheet_link.getActiveSheet(), sheet_values = sheet_link.getDataRange().getValues(); Logger.log( sheet_values ); return sheet_values; }

Разберем подробнее.

Строки 1-6. Уже знакомая нам конструкция комментария /* */. Кстати говоря, такой формат оформления комментария общепринят для описания к функций в языке JavaScript.

В строке 7 тоже знакомая нам директива function, которая создает новую функцию с названием getValuesByActiveSheet. Параметров в функцию не передаем, а значит в круглых скобках ничего не указываем () ну и { что значит что следующий код, это код данной функции вплоть до строки 15, где }, который символизирует о конце функции.

Строки 8-10. Нас встречает директива var что значит начало объявления переменных (и это мы тоже уже знаем).

С базовыми вещами разобрались теперь по сути кода.

  • Переменная spreadsheet_link в которую сразу же заносим ссылку на активную таблицу следующим кодом SpreadsheetApp.getActive(),
  • В переменную sheet_link заносим ссылку на активный лист getActiveSheet(),
  • и в переменную sheet_values занесем все данные с активного листа, getDataRange() так получаем ссылку на весь диапазон данных, а после берем значения из этого диапазона функцией getValues().

В строке 12 отправляем данные в журнал (это не обязательно).

Ну и в строке 14 возвращаем данные.

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

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

Это делает разработку более гибкой и комфортной.

Так можно вызвать любую функцию:

// … ваш код getValuesByActiveSheet(); // Вызываем функцию с именем getValuesByActiveSheet // ваш код …

А если функция возвращает данные, как наша, мы эти данные можем записать в переменную так:

// … ваш код var data = getValuesByActiveSheet(); // Вызываем функцию getValuesByActiveSheet, а результат ее работы заносим в переменную data // ваш код …

Результат работы скрипта (журнал):

Код выше работает в сценариях связанных с таблицами. А следующий код может работать как в автономных скриптах, так и в связанных с контейнером.

Пример 2. Как получить все данные из листа зная id таблицы?

Код ниже позволяет получить все данные из листа «Июнь» зная id таблицы.

/* * Функция getValuesBySheet() получает и возвращает все данные из таблицы * по ее id и листа с именем «Июнь». * * @return {array} sheet_values — Данные в виде двумерного массива */ function getValuesByActiveSheet () { var spreadsheet_id = «1bubLwq8J-hpvRywKH1m6lqRDWIJg2XmQTryrjff26jM», spreadsheet_link = SpreadsheetApp.openById( spreadsheet_id ), sheet_name = «Июнь», sheet_link = spreadsheet_link.getSheetByName( sheet_name ), sheet_values = sheet_link.getDataRange().getValues(); Logger.log( sheet_values ); return sheet_values; }

Разберем подробнее код выше, при этом пропустим описание аналогичных частей кода.

В строке 9 мы используем функцию openById(), которая получает ссылку на таблицу по ее id. При этом, как вы уже могли заметить, вызывая функцию, в круглых скобках, мы передаем id таблицы с которой хотим работать.

А в строке 12 мы используем функцию getSheetByName(), которая получает ссылку на лист по его имени, соответственно передав имя листа в функцию.

Результат работы скрипта такой же, как и в примере 1.

Пример 3. Как получить данные только из указанного диапазона?

Код ниже демонстрирует три способа получения данные только из указанного диапазона.

/* * Функция getValuesByRanges() получает данные из указанных диапазов. * * @return {boolean} false — Ничего не возвращаем */ function getValuesByRanges () { var spreadsheet_id = «1bubLwq8J-hpvRywKH1m6lqRDWIJg2XmQTryrjff26jM», spreadsheet_link = SpreadsheetApp.openById( spreadsheet_id ), sheet_name = «Июнь», sheet_link = spreadsheet_link.getSheetByName( sheet_name ), sheet_range1_values = sheet_link.getRange(«A3:E»).getValues(), sheet_range2_values = sheet_link.getRange(3, 1).getValue(), sheet_range3_values = sheet_link.getRange(3, 1, 2, 3).getValues(); Logger.log( sheet_range1_values ); Logger.log( sheet_range2_values ); Logger.log( sheet_range3_values ); return; }

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

А в этом примере мы явно укажем диапазон ячеек с которыми хотим работать. И делается это функцией getRange().

Данная функция может принимать разный набор параметров (входящих данных, диапазонов).

Первый вариант, строка 11. «A3:E» привычный вариант указания диапазона, при условии что у вас есть опыт написания формул для таблицы. Данный способ возвращает диапазон, указанный в нотации A1 или нотации R1C1.

Второй вариант, строка 12. номер строки, номер столбца возвращает диапазон с верхней левой ячейки в заданных координатах.

Третий вариант, строка 13. номер строки, номер столбца, количество строк, количество столбцов возвращает диапазон с верхней левой ячейки в заданных координатах с заданным количеством строк и столбцов.

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

Также, как вы могли заметить, при получении данных, в одном случае мы используем getValue(), а в другом getValues(). Разница в том что:

  • getValue() — возвращает значение ячейки верхнего левого диапазона. Значение может быть типа Number, Boolean, Date или String в зависимости от значения ячейки. Пустые ячейки возвращают пустую строку.
  • getValues() — Возвращает прямоугольную сетку значений для этого диапазона.Возвращает двумерный массив значений, индексированный по строке, а затем по столбцу. Значения могут иметь тип Number, Boolean, Date или String, в зависимости от значения ячейки. Пустые ячейки представлены пустой строкой в массиве. Помните, что, когда индекс диапазона начинается с 1, 1, массив JavaScript индексируется из .

Подробнее о массивах и других типах данных, а также об обработке данных мы поговорим в следующих статьях.

Не забываем заглядывать сюда, где скрываются ответы практически на все вопросы.

Результат работы скрипта (журнал):

Как наклацаете код и приметесь его тестировать, вспомните о комментариях. А точнее, о такой замечательной вещи как CTRL + /. Таким макаром очень удобно и главное быстро временно отключать участки кода в процессе его отладки.

Logger.log( sheet_range1_values ); // Logger.log( sheet_range2_values );

История версий скрипта

Также придется к месту полезная штука, которая прячется по пути Файл > Смотреть историю версий.

В случае, когда в скрипт закрался bug размером с носорога или по каким-то причинам требуется откатить немного назад (а CTRL + Z уже не помогает) — этот инструмент просто незаменим.

А знаете, что в этом самое замечательное? Это то, что эти бэкапы делаются автоматически! Конечно можно это сделать и вручную, вот только кто об этом вспоминает? На мой взгляд, просто чудесная функция, в критическую минуту сбережет ваши нервы, а возможно и спасет вашу технику от полета в окно…

Вернемся к нашему сценарию.

Предположим, что код написан, также предположим, что вы не забыли как его запустить и как посмотреть в Журналах ( CTRL + Enter ).

Предположим даже, что вам все понятно… 🙂

Если это так, то вы можете смело себя поздравить. Вы совершили еще один уверенный шаг к освоению Google Apps Script.

Только представьте, сколько всего можно автоматизировать и оптимизировать когда под рукой такой инструмент.
Конечно, еще многое предстоит узнать и попробовать, во многом придется разобраться, но это не беда.

Говорят, Гай Юлий Цезарь мог делать несколько дел одновременно…

Взять упорство плюс гугл аппс скрипт

и…что там несколько…

СОТНИ дел одновременно не хотите?

На этом пока все.

В следующей статье продолжим знакомство с редактором и пристальнее поглядим на работу с таблицами…

Итоги

А теперь, коротко, подведем итоги:

  • В этой статье мы узнали, что бывают три типа скриптов автономные скрипты, скрипты, связанные с контейнером и веб-приложения.
  • Узнали, что при помощи скриптов можно существенно расширить функционал базовых приложений. С помощью добавления самодельных менюшек, диалоговых окошек, боковых панелей, дополнений и пользовательских функций.
  • Научились получать id таблицы, да и не только таблиц, с помощью скрипта. И находить id в ссылке.
  • Разобрали некоторые возможности редактора.
  • Научились выводить в журнал нужные нам данные.
  • Выяснили, как можно откатить версию скрипта.
  • Написали несколько полезных скриптов.
  • Узнали, как можно получить данные из таблицы при помощи скрипта.

Полезные ссылки и источники

  • Официальная документация по всем методам класса Приложение для электронных таблиц (на английском).
  • Документация, тоже официальная, по всем методам класса Лист (на английском).
  • Ну и еще одна официальная документация по всем методам класса Диапазон (на английском).

Бонус (расширение для редактора скриптов)

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

Подробнее о расширении смотри .

Максим Стоянов

Разработчик Google Apps Script. Посмотреть все записи автора Максим Стоянов

Доброго времени суток, дорогие читатели!

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

Я не стал включать ту статью в этот, пока еще начинающийся, цикл, а решил начать его с самого нуля. Цель его довольно простая — показать, что всё не так уж и сложно, если знать некоторые особенности программирования в целом. Поэтому я постараюсь не столько расписать примеры, сколько объяснить как именно придти к такому решению.

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

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

С чего начинается любой скрипт? Очевидно, что с какого-то кода, но с какого? Вполне может так быть, что у вас уже появляются такие мысли при виде этого, а точнее того, белого экрана. Но это нормально. Когда мало опыта и много непонятного, хочется все бросить и лечь спать. Но не отчаивайтесь! И отойдите от кровати! Если я не буду писать это в некотором ироничном виде, то от моей заунывной статьи вы уже на третьей строчке усне..

Пишем и используем Google Scripts: Line Zero, — Beginning

Так вот, с чего нам начать? Я сейчас не имею в виду алгоритм действий, который вы держите в голове, или блок-схемы на листочке, нет. Нам следует начать с… объявления функции.

Что же это такое? Если говорить простым языком, то функция — это одна из множества позиций в торговом (вендинговом) автомате (ваш скрипт). Вы выбираете позицию автомата (указываете функцию), скармливаете ему деньги (параметры функции) и получаете какой-то предмет канцерогенной индустрии (какой-то результат). При этом вы можете создать таких позиций (функций) бесчисленное множество.

Объявление функции имеет определенный формат вида:

function FUNCTIONNAME () { };

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

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

Итак, вы определились с вашей первой функцией, теперь что? А теперь нужно объявить переменные. Для вашей позиции в торговом автомате переменными могут быть: номер ряда, номер колонки, картинка позиции, её цена.

Переменными в общем случае может быть любой набор символов любого типа. Объявляются они очень просто, достаточно сказать котелок вари

var variablename;

И ой, она уже объявлена. А для чего это нужно?

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

Грубо говоря, если вы напишете e = m * c ^2 без объявления переменных, то компилятор перед запуском кода выдаст ошибку о том, что он вообще не имеет ни малейшего понятия кто такие e, m, с и что им всем от него нужно. А вот если вы напишете:

var e = 0, m = 1, c = 2;

То он вам посчитает безо всякого труда, потому что он уже знает что e, m, c, — это числа, имеющие значения 0, 1, 2 соответственно.

Однако, если вы напишете:

var e = «энергия», m = «масса», c = 300000;

То он вам, конечно, посчитает выражение e = m * c ^2, но его ответом будет e = 2, потому что у вас несогласованны типы (m — это строка, c — это число) и он просто отбросит строки (VBA, например, здесь выдаст ошибку что у вас неправильные типы данных).

Если же вы объявите переменную, но не напишете чему она равна, то компилятор будет считать, что значение не определено (undefined).

Что касается самих выражений, здесь не совсем обычная математика (а кто говорил, что будет легко?). Если там выражение e = m*c^2 будет эквивалентно выражению m*c^2 = e, то здесь не так. Здесь вообще оператор под названием «равно» выглядит иначе и обозначается как «==». А то, что написано выше, это не «е равно эм * це квадрат», это читается как «е присвоить эм*це квадрат» и данный оператор называется оператором присваивания. Вот этот момент очень важен и здесь не следует путать.

Давайте подробнее остановимся на операторах. Есть несколько категорий:

Пишем и используем Google Script: Line 1, — I Don’t get it

Итак, в общем-то с основами разобрались. Теперь немного практики. Задача:

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

Таблица для нашего примера:

A B C D
1 2 3 4 1
2 -4 12 3 5
3 1 1 2 5

Итак, с чего начать? Нет, не с написания кода, сначала надо прикинуть план действий (хотя бы очень грубо).

  1. Как посчитать сумму? Очевидно, сложить каждое значение в ряду. Окей, кажется несложным.
  2. Как получить среднее значение? Можно сложить каждое значение ряда и поделить на кол-во значений. Но зачем заново считать, если мы сумму уже посчитали на предыдущем шаге? Окей, тоже легко.
  3. Остался один вопрос, — как получить значение из ячейки и как его потом туда записать? А вот тут-то и начинаются сложности.

Путем нехитрого гугления мы достаточно быстро выясним как вытащить значение ячейки из таблицы. Но какой смысл копировать код, ничего в нем не понимая? Правильно, никакого. С точки зрения.. ммм.. гугла таблица представляется собой объект (класс), состоящий из множества подклассов (типа матрешки). Иерархия там примерно такая:

  • SpreadsheetApp, — самый верхний уровень, указывает, что мы хотим работать с таблицами (создавать, удалять, изменять и вообще делать с ними все, что можно. По ссылке можно изучить все свойства и методы этого класса. Т.е. все, что с ним можно сделать);
  • Spreadsheet, — класс, который отвечает за действия с самим листом таблицы (копирование, переименование, защита, сокрытие, права доступа и тп);
  • Sheet, — класс, который отвечает за действия на каком-то листе таблицы ( добавление ряда, добавление графиков, определение размера таблицы и многие другие). Во многих случаях можно использовать его вместо класса Spreasheet;
  • Range, — класс, который отвечает за действия с каким-то определенным диапазоном ячеек на листе (включая выбор диапазона).

Таким образом, мы можем получить значение как одной ячейки, так и диапазона ячеек. Воспользуемся вторым вариантом. Для этого пишем соотв. код:

Создаем нашу функцию:

function myfunction(){ };

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

var sheet = SpreadsheetApp.getActiveSheet(); // Ссылаемся на текущий лист в соответствии с иерархией. В переменной sheet будет ссылка на наш лист

Да, можно вставлять комментарии, которые компилятор будет игнорировать (и подсветит оранжевым). Для этого перед словом/фразой/строчкой достаточно добавить два слеша // и вся оставшаяся строка будет закомментирована. Если же надо закомментировать много строк, то лучше воспользоваться вторым вариантом — поставить в начале комментария символы /*, а в конце */. Т.е. Комментарий целиком будет выглядеть примерно так:

/*————- —Комментарий— —————*/

Теперь достанем наши значения из ячеек:

var dataArray = sheet.getRange(«A1:D3»).getValues(); // Получаем значения ячеек A1:D3 в переменную dataArray

При этом особенностью метода getValues является то, что на выходе мы получаем матрицу (двумерный массив или по простому — таблицу), где первый разряд означает ряды, второй — колонки. В нашем примере, например, элемент массива dataArray будет означать… нет, не нулевой ряд, а первый ряд и вторая колонка (т.к. нумерация ячеек идет с единицы, а нумерация массивов всегда идет с нуля).

Теперь мы получили значения наших ячеек и можем с ними работать. Как мы будем суммировать? Самое простое — явно:

var sum1 = dataArray + dataArray + dataArray + dataArray; var sum2 = dataArray + dataArray + dataArray + dataArray; var sum3 = dataArray + dataArray + dataArray + dataArray;

Да, не очень красиво, но работает. Кстати проверить результат можно несколькими способами:

  1. Записать в журнал. Для этого добавить после var sum3 строчку Logger.log («\nСумма 1 ряда: «+sum1+»\nСумма 2 ряда: «+sum2+»\nСумма 3 ряда: «+sum3);
    Где \n — это распространенное обозначение перевода строки (Enter). После этого в том же редакторе скриптов переходим в пункт меню «Вид — Журналы»:
  2. Вывести оповещение в браузере: Browser.msgBox(«Суммы трех рядов: «+sum1+» «+sum2+» «+sum3);
    При выполнении скрипта перейдите на ваш лист и увидите всплывающее окно
  3. Поставить Breakpoint (1) напротив var sum3, после чего нажать не на старт, а на иконку с жуком (2, означает debug). У вас напротив переменной sum3 (4) будет написано «undefined» (на скрине этот этап пропущен). Это потому, что скрипт сейчас остановился перед этой строкой и он еще не отработал её. Для того, чтобы он сделал следующее действие, нажмите на кнопку со стрелкой (3) и вуаля (4), переменная посчитана (наиболее удобный способ отладки и поиска косяков). Но это сработает, если у вас в функции код на этой строке не заканчивается (я обычно добавляю функцию Logger.log и ставлю Breakpoint на ней:

У кого не получилось, код целиком ниже:

function myfunction(){ var sheet = SpreadsheetApp.getActiveSheet(); // Ссылаемся на текущий лист таблицы в соответствии с иерархией. В переменной sheet будет ссылка на наш лист /*————- —Комментарий— —————*/ var dataArray = sheet.getRange(«A1:D3»).getValues(); // Получаем значения ячеек A1:D3 в переменную dataArray var sum1 = dataArray +dataArray + dataArray + dataArray; var sum2 = dataArray +dataArray + dataArray + dataArray; var sum3 = dataArray +dataArray + dataArray + dataArray; Logger.log («\nСумма 1 ряда: «+sum1+»\nСумма 2 ряда: «+sum2+»\nСумма 3 ряда: «+sum3); };

Итак, первый пункт выполнен. Теперь приступаем ко второму. Учитывая, что мы (теперь) знаем сумму ряда и знали с самого начала кол-во чисел в ряду, добавляем еще три переменные и проверяем (значком {B} я обозначаю место, где можно поставить Breakpoint для быстрого просмотра результатов):

var avg1 = sum1/4; var avg2 = sum2/4; var avg3 = sum3/4; {B}Logger.log();

Что ж. Пункт 2 выполнен. осталось это дело сохранить, например в ячейках E, F. Мы уже знаем как достать данные из ячеек, а вот как их туда поместить? В общем-то тут никакой магии и нет, раз есть функция get, то можно догадаться, что есть функция и set.

При этом, мы должны указать данные точно в таком же формате, как мы их получили. Присваивание значений массивам в общем-то достаточно простая задача. Допустим у нас есть значения 1, 2, 3, 4, 5 и нам нужен массив. Объявляем:

var array1 = ; // если нам явно нужны числа var array2 = ; // Если нам неважно числа это или символы

И всё 🙂

Однако это массив с одним разрядом (одномерный), нам же нужен двумерный, как сделать его? Для этого используется конструкция вида ], как ниже:

var array2d = , , ];

Выглядит криповато, но массив можно записать еще таким образом:

var array2d = , , ];

А это уже другое дело! Хотя тем, кто на этих массивах уже не первую собаку съел, первый способ может быть удобней, т.к. быстрее.

Теперь же осталось дело за малым. Записать сумму и среднее для каждого ряда:

sheet.getRange(«E1:F3»).setValues(, , ]);

Запускаем и смотрим что у нас происходит на листе:

Послесловие

Итак, дабы не перегружать вас (дорогих читателей) информацией, первая часть на этом радостном событии завершена. Небольшой итог:

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

В следующей серии поговорим о том, как можно улучшить код, дабы не плодить кучу переменных и иметь возможность работать с таблицей значений переменной длинны (т.е. когда мы заранее не знаем сколько рядов и строк нам надо). А также немножко разобьем все это дело на несколько функций. Stay Tunned!

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

admin

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *