как написать скрипт в гугл таблицах

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

Мастер Google Таблиц с полезными советами и руководствами

Скрипты Google Таблиц 101 — руководство для начинающих

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

С помощью скриптов Google Sheets вы можете автоматизировать многие вещи и даже создавать новые функции, которые, по вашему желанию, существовали.

В этой статье я расскажу об основах Google Apps Script с некоторыми простыми, но практичными примерами использования скриптов в Гугл Таблицах.

Что такое скрипт Google Apps (GAS)?

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

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

Этот язык кодирования Google Apps Script (GAS) использует Javascript и написан в серверной части этих Гугл Таблиц (есть аккуратный интерфейс, который позволяет вам писать или копировать / вставлять код в серверной части).

Поскольку Гугл Таблицы (и другие Google Apps) являются облачными (т. Е. Могут быть доступны из любого места), ваш скрипт Google Apps также является облачным. Это означает, что если вы создадите код для документа Google Sheets и сохраните его, вы сможете получить к нему доступ из любого места. Он находится не на вашем ноутбуке / системе, а на облачных серверах Google.

Что делает скрипт Google Apps полезным?

Есть много веских причин, по которым вы можете захотеть использовать скрипты Google Apps в Google Таблицах:

Позволяет автоматизировать работу

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

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

И это то, что вы можете делать с помощью скрипта Google Apps.

Все, что вам нужно сделать, это потратить некоторое время на то, чтобы подготовить код один раз, и всякий раз, когда вам нужно повторить те же шаги снова, вы просто запускаете код сценария в Google Sheets и позволяете GAS делать всю тяжелую работу за вас.

Может создавать новые функции в Google Таблицах

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

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

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

Может взаимодействовать с другими приложениями Google

Поскольку скрипт Google Apps является распространенным языком программирования для многих приложений Google, вы также можете использовать его для взаимодействия с другими приложениями.

Например, если у вас есть 10 документов Google Таблиц на вашем Google Диске, вы можете использовать GA, чтобы объединить все это, а затем удалить все эти документы Google Sheets.

Это возможно, потому что вы можете использовать GAS для работы с несколькими Google Apps.

Другим полезным примером этого может быть использование данных в Гугл-таблицах для быстрого планирования напоминаний в вашем Календаре Google. Поскольку оба этих приложения используют GAS, это возможно.

Расширьте функциональные возможности Google Таблиц

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

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

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

Редактор скриптов — это место, где вы можете писать скрипты в Google Таблицах, а затем запускать их. Для разных приложений Google будет отдельный редактор сценариев. Например, в случае Google Forms будет «Редактор сценариев», в котором вы можете писать и выполнять код для форм Google.

Анатомия редактора скриптов Google Таблиц

В Google Таблицах вы можете найти редактор скриптов на вкладке «Инструменты».

После того, как вы нажмете на опцию «Редактор скриптов», откроется редактор скриптов в новом окне (как показано ниже).

Вы можете изменить имя проекта, щелкнув в верхней левой части экрана с надписью «Проект без названия». Когда вы щелкаете по нему, откроется диалоговое окно, в котором вы можете ввести название проекта. Изменение имени займет несколько секунд.

В проекте сценария может быть несколько файлов сценария. Например, если у вас есть три разные вещи, которые вы хотите автоматизировать в Google Таблицах, и вы создаете для них три разных скрипта, у вас могут быть все три разных скрипта в одном файле проекта.

На левой панели проекта у вас есть файл сценария по умолчанию — Code.gs. В этом файле сценария вы можете писать код. В одном файле сценария может быть несколько сценариев, а также несколько файлов сценариев.

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

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

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

Панель инструментов редактора скриптов

Панель инструментов редактора скриптов имеет следующие параметры:

Параметры меню редактора скриптов

Помимо панели инструментов, есть много других опций, доступных в Google Apps Script в Google Таблицах.

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

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

В этой статье я рассмотрел основы скрипта Google Apps и общую анатомию интерфейса.

Источник

Пишем скрипты для автоматизации работы с приложениями Google

Содержание статьи

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

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

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

Теперь в нашем меню есть пункт под названием ExampleFunc, при клике на который открывается однострочное подменю «Моя единственная функция».

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

Рис. 2. Редактор кода, заполненный автогенерируемым сырьевым материалом для работы с электронной таблицей Рис. 3. Вызов пользовательской функции с аргументом

WARNING

Работа с Google Docs

Когда встает задача автоматизации работы с офисными документами, первым делом на ум приходит VBA, одно упоминание которого оказывает на многих тотальное антиэкстатическое воздействие, вызывая болезненные воспоминания из школьного и университетского прошлого. Google Script однозначно удобнее и доступнее для понимания. Особенно для веб-разработчиков, ведь это же родной, привычный и любимый JS! Разберем пару примеров скриптов для Google Docs. Приведенный код заполняет левую верхнюю ячейку первого листа активной таблицы:

А этот код создает копию текстового документа и кладет его в определенное место:

А вот так можно провести замену строк в текстовом документе:

Следующий пример кода подсвечивает определенные слова в тексте:

Рис. 4 Вот здесь можно узнать ID документа

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

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

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

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

WARNING

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

Работа с Google Translate

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

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

Работа с Google Drive

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

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

Работа с Google Contacts

Адресная книга также может быть подвергнута автоматизированной обработке. Приведенный ниже код копирует все контакты из группы «Редакция» в лист Google Spread Sheet:

Работа с Google Tasks

С помощью Google Apps Scripts можно работать с сервисом Google Task — создавать новые задачи и парсить уже имеющиеся.

Этот код создает новое дело в списке:

А таким образом можно вывести список нумерованных задач в консоль:

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

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

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

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

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

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

События

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

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

Для этого существует сервис для работы с базами данных Google Cloud SQL. По сути — классический MySQL в облаке. Может взаимодействовать с Google Apps Script по стандарту Java Database Connectivity. Вот пример кода, который производит чтение записей из таблицы:

Стоимость использования сервиса — 88 долларов в год за 10 Гб свободного места. С другими базами данных Google Apps Script, к сожалению, работать не может. Если ты запланировал написать скрипт, который должен взаимодействовать с данными, не стоит сразу расстраиваться или истощать свой бюджет пожертвованиями на закупку квадроциклов для жителей Кремниевой долины. Есть два способа выкрутиться из этой ситуации:

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

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

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

Advanced Google Services

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

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

шКоддинг

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Послесловие

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

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

Источник

Как писать скрипты, макросы и код в Google Scripts — часть 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В чем же разница между первым и вторым оператором? (ответ в следующей строке, выделите мышкой)

falseВ первом случае мы сравниваем с типом «число»

truefalse (по значению)+false (по типу) = false

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Послесловие

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

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

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

Источник