как добавить скрипт в гугл таблицу
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 Таблицами, есть хорошая новость — ниже описано решение, которое позволит импортировать данные из таблицы на ваш сайт.
1. Открываем Таблицу Google
Для примера, я возьму таблицу, в которую падают результаты из Google Формы.
2. Открываем меню Tools — Script Editor…
3. Добавляем скрипт
При необходимости переопределите переменную “range”.
Если вы захотите доработать скрипт, ссылки на документацию:
4. Публикация как Web Application
Открываем меню Publish — Deploy as web app…
Вводим название новой версии проекта (например 1).
Меняем значение для “Who has access to the app” на “Anyone, even anonymous”
Нажимаем “Deploy”.
При первом деплое, нужно пройти процесс авторизации. Описывать не буду, покажу скриншотами.
Далее вам будет показан попап с ссылкой на ваш web app:
Вам нужно скопировать эту ссылку.
Можете сразу открыть её в браузере. Приложение должно вернуть содержание выбранного диапазона таблицы в формате JSON:
То есть любым GET-запросом к этому веб-приложению вы можете получить данные Таблицы. Можно с бекенда или фронтенда, в зависимости от потребностей бизнеса.
Пример работы на фронтенде
Ссылка на l.englishdom.com/google-s.html
Для простоты восприятия скрипта, я не стал делать дополнительное оформление выводимой информации.
На странице используются:
Бонусы для читателей
Мы дарим бесплатный доступ на три месяца изучения английского с помощью наших онлайн-курсов. Для этого просто перейдите по ссылке до 31 декабря 2017 года.
Будем рады видеть вас на индивидуальных занятиях курса «Английский для IT-специалистов».
Пройдите бесплатный вводный урок и получите комплексную обратную связь по своему уровню знаний, затем выбирайте преподавателя и программу обучения себе по душе!
Скрипт Google Sheets для разрешения множественного выбора в ячейках с проверкой данных
Как настроить работу множественного выбора в ячейка Google Docs
Добавляем скрипт
В своем Google Листе настройте проверку данных для ячейки (или ячеек), используя данные из диапазона. При проверке ячейки не выбирайте «Отклонить ввод».
Перейдите в Инструменты > Редактор скриптов …
В редакторе сценариев выберите «Файл»> «Создать»> «Файл сценария».
Добавляем HTML
В редакторе сценариев выберите «Файл»> «Создать»> «HTML-файл».
Используем решение в своих таблицах
Вернувшись в свою электронную таблицу, у вас должно появиться новое меню «Scripts». Обновите страницу, если это необходимо.
Выберите ячейку, которую вы хотите заполнить несколькими элементами из диапазона проверки (например из выпадающего списка).
Перейдите в Scripts > Multi-select for this cell… для этой ячейки … У Вас должна появится боковая панель, показывая контрольный список допустимых значений.
Отметьте нужные элементы и нажмите кнопку «Select», чтобы заполнить ячейку выделенными элементами, разделенными запятой.
Вы можете оставить боковую панель скрипта открытой. Когда вы выбираете любую ячейку, которая имеет проверку, нажмите «Обновить проверку» на боковой панели скрипта, чтобы вызвать контрольный список этой ячейки.
Видео инструкция от разработчика скрипта Александра Иванова
Саш, огромное тебе спасибо за твой труд и за создание оригинального сценария.
Google Apps Script: переносим расписание из таблицы в календарь
Для кого предназначена данная статья
Что понадобится
1. Создаем таблицу в Google Sheets
Это можно сделать, перейдя на страницу своего Google Drive.
Рис. 1. Правый клик мышью по рабочему пространству открывает контекстное меню, где нужно выбрать «Google Таблицы» — «Создать пустую таблицу»
В созданную таблицу нужно скопировать имеющееся у Вас расписание, в моем случае это расписание занятий в университете. Важно, чтобы в таблицы были следующие поля:
Вся остальная информация, в моем случае — преподаватель, тип занятия (лекция, семинар и т.д.), может быть отправлена в описание мероприятия.
Рис. 2. Таблица с расписанием
2. Создаем скрипт
2.1. Переходим в редактор скриптов
Нужно создать скрипт, который будет связан с только что созданной таблицей, будет иметь доступ к данным в ней и переносить их в календарь.
Рис. 3. Скрипт создается, через меню «Инструменты», в котором нужно выбрать пункт «Редактор скриптов»
Скрипты разрабатываются на языке JavaScript. Только что созданный скрипт содержит одну пустую функцию myFunction. Писать код можно в ней, но я предпочту дать ей более осмысленное имя SetCalendar.
Рис. 4. Созданный скрипт, дадим функции осмысленное имя — SetCalendar
Над редактором кода имеется выпадающий список, который позволяет выбрать основную функцию, которая будет являться точкой входа в запущенный скрипт. В нашем случае следуем выбрать функцию SetCalendar.
2.2. Разбираемся с отладкой
Часто для отладки скриптов на JavaScript мы выводим различные значения в консоль:
В Google Apps Script логировать значения следует немного иначе. Вместо объекта console следует использовать глобальный объект Logger:
После запуска скрипта, все выведенные в лог значения можно посмотреть из меню «Вид», выбрав пункт «Журналы» или же по сочетанию клавиш Ctrl+Enter:
Рис. 5. Модальное окно с логами последнего запуска скрипта
2.3. Извлекаем информацию о мероприятиях из таблицы
Сразу небольшой кусок кода
Рассмотрим строку 11.
Глобальный объект SpreadsheetApp — глобальный объект, содержащий методы, представляющие собой интерфейс для взаимодействия скрипта с приложением Google Sheets.
Первое, что нужно сделать — получить объект, представляющий активную (открытую в данный момент) страницу документа. Для этого необходимо вызвать метод getActiveSheet.
Метод возвращает объект sheet.
Далее, нужно получить объект range — он представляет собой диапазон ячеек таблицы, из которых мы будем извлекать данные. Это делается путем вызова метода getRange. В качестве параметров передаются:
Обратите внимание! Индексация строк и столбцов начинается с 1.
Строка 15: мы получаем из выбранного выше диапазона ячеек непосредственно данные путем вызова метода getDisplayValues.
Обратите внимание! Метод getDisplayValues() отдает данные в том виде, в каком он отображается в ячейках таблицы. Например, если ячейка содержит дату, то указанный метод вернет не какое-то внутреннее представление записанной в ячейку даты, а ее представление, которое видит пользователь в ячейке.
2.4. Извлекаем данные из ячеек
Прежде, чем работать с ячейками, удобно было бы дать номерам столбцов имена, дабы избежать ошибок при указании номеров столбцов с интересующими нас данными. Создадим для этого несколько констант:
Вы можете посмотреть какие столбцы стоят в таблице под данными номерами на рисунке 2.
Объект data представляет набор ячеек с данными, которые были получены. Теперь нужно получить данные из отдельных ячеек.
При использовании цикла for с переменной i по объекту data, переменная i на каждой итерации получает значение — целое число, которое является индексом очередной извлеченной из таблицы строки.
Строка 3: обращение к объекту data по индексу i возвращает объект row — текущую строку таблицы.
Строки 5-9: мы обращаемся к строке по индексу — номеру столбца с интересующими нас данными. Например, row[dateCol] — мы из текущей строки получаем дату мероприятия из столбца с индексом dateCol.
В строках 5-9 мы получили из текущей строки данные о дате занятия, времени начала и окончания, типе занятия, названии дисциплины и имени преподавателя.
В конце мы выводим полученные данные в лог, чтобы убедиться, что извлекли данные из таблицы правильно.
2.5. Работа с объединенными ячейками
В результате выполнения предыдущих шагов, мы получаем следующий скрипт
Выполним скрипт и посмотрим, что было выведено в лог:
Рис. 6. Не все даты были правильно извлечены из таблицы
Из лога видно, что из первой строки данные были извлечены, а в следующих строках, которые описывают мероприятия того же дня, поле с датой оказалось пустым. Почему так происходит?
Еще раз взглянем на таблицу:
Рис. 7. Даты вписаны в объединенные ячейки
При создании таблицы, дата вписывалась только в первую строчку расписания на конкретную дату. После чего, все ячейки из столбца, относящиеся к одной дате были объединены.
Когда мы двигаемся по строкам таблицы, мы получаем дату не из всех ячеек, которые были объединены в одну, а только из первой.
Немного изменим алгоритм, чтобы корректно извлекать дату из объединенных ячеек:
Мы вводим дополнительную переменную savedDate — в ней храним последнюю прочитанную дату.
При чтении даты из текущей строки. Если прочитанная дата — пустая строка, то берем дату из переменной savedDate, иначе обновляем значение переменной savedDate.
2.6. Парсим дату и время из строк
Как было отмечено выше — если мы используем метод getDisplayValues(), то значения из таблицы мы получаем в виде строк и ровно так, как они отображаются в ячейках, никаких внутренних представлений данных мы не получаем.
Как будет показано далее, чтобы создать событие в Google Calendar, нужно предварительно создать два объекта класса Date: дату-время начала события, дату-время окончания события.
В рассматриваемой таблице (см. рисунок 2), мы получаем дату занятия в формате dd.mm.yyyy, и временной промежуток, когда занятие длится, в формате hh:mm-hh.mm.
Из этих двух строк, нам нужно получить два объекта Date. Такая задача не касается Google Apps Script, с этим по идее должен справиться хоть сколько-нибудь опытный программист. Поэтому я не буду подробно останавливаться на алгоритме, а лишь приведу код двух JS-функций, которые парсят дату время и возвращают дату-время начала и дату-время окончания занятия.
Чтобы получить дату-время начала и окончания занятия, нужно вызывать функцию extractPeriod и передать две строки — дата и временной период, в который длится занятие.
2.7. Создание события в Google Calendar
Сразу небольшой листинг, пояснения будут дальше
Строки 1-6: получаем дату-время начала и окончания занятия, а также склеиваем в одну строку всю дополнительную информацию, которая будет записана в описание мероприятия в календаре.
Далее, к интерфейсу сервиса Google Calendar мы обращаемся через методы глобального объекта CalendarApp.
Посредством метода getCalendarsByName мы получаем массив календарей с указанным именем.
Получить объект-календарь можно также посредством метода getDefaultCalendar, тогда событие будет создано в основном календаре. Однако, я советую зайти в веб-версию Google Calendar и создать новый календарь, в который вы будете экспортировать мероприятия. Во-первых, вы получаете возможность скрывать события этого календаря, выделять события отдельным цветом.
Но, что более важно, в случае, если вы при написании скрипта где-то ошиблись, и экспортировали события как-то неправильно, то вместо того, чтобы удалять все многочисленные неправильные события вручную, вы можете просто удалить календарь, после чего создать новый, пустой, и попробовать запустить новый, исправленный скрипт.
В данном случае я создал календарь «Учеба», и собираюсь создавать события в нем. Поэтому в коде вызывается метод getCalendarsByName и в качестве параметра передается срока «Учеба».
Метод возвращает массив календарей с указанным названием, поэтому нужно взять из этого массива один элемент, в нашем случае нулевой:
У объекта-календаря необходимо вызывать метод createEvent. В качестве параметров нужно передать следующее:
Последняя строка в скрипте — задержка на 50 миллисекунд. Во время разработки скрипта и отладки я обнаружил, что иногда при выполнении кода выбрасывается исключение, суть которого в том, что Google Calendar не нравятся частые обращения к сервису, и в описании исключения рекомендуется вставить задержку между вызовами методов API календаря.
3. Итоговый скрипт
Ниже приведен полный скрипт, который получился
Обратите внимание! Данный скрипт заточен под структуру таблицы, которая приведена на рисунке 2. Если вы хотите использовать этот скрипт для себя как есть, убедитесь, что ваша таблица с расписанием имеет такую же структуру, или же измените скрипт под свою таблицу.
4. Немножко скриншотов
Что можно улучшить
Открыт к конструктивной критике, буду рад, если укажете на недостатки, посоветуете что можно улучшить, а что изменить в статье.
Как добавить скрипт в гугл таблицу
Этот пост будет актуальным для тех, кто хочет писать скрипты в гугл таблице (макросы) для себя или зарабатывать на этом. На фрилансерских сайтах бывают задачи по написанию скриптов и это умение может приносить доход. Готовые фрагменты помогут быстрее справиться с разными задачами по написанию макросов.
Время от времени я пишу макросы своим клиентам и знакомым для решения некоторых задач. Пример одной такой задачи и ее решение можно посмотреть в видео здесь.
Поскольку я делаю это не регулярно, кое-что забывается и приходиться открывать старые проекты и подсматривать реализацию того или иного метода.
Такой вариант выхода из ситуации увеличивает время, которое тратится на разработку, что в свою очередь снижает эффективность.
В связи с этим пришла идея сделать что-то наподобие памятки по написанию скриптов для гугл таблиц.
Я решил поделиться ею с нашими читателями, вдруг кому-то она также пригодится.
И конечно, вы всегда можете заказать у меня макрос под ваши потребности и задачи.
В данном случае в переменную MySpreadSheet записывается ссылка на активный лист открытой таблицы.
Работа с ячейками
В переменную a1 записывается значение ячейки “D4”.
В ячейку “F12” записываем слово “Qwerty”.
Расшифровка:
В переменную MySpreadSheet сохраняем ссылку на активный лист.
1. первый способ взять активную (выделенную) ячейку
2. второй способ взять активную (выделенную) ячейку
Ячейка (Cell) является частным случаем от диапазона (Range), при условии что диапазон (Range) указывает на одну ячейку. Если вы выделите несколько ячеек, то в этом случае во втором варианте (getActiveRange) в переменной MyCell будет храниться ссылка на выделенный диапазон.
В переменной IndexRow будет номер строки выделенной ячейки
В переменной IndexColumn будет номер столбца (колонки) выделенной ячейки.
CountRows – на сколько строк смещаться вниз от активной ячейки
CountColumns – на сколько колонок смещаться вправо от активной ячейки
Можно задавать отрицательные значения. Но нужно учитывать, чтобы координаты новой ячейки не выходили за пределы листа, иначе возникнет ошибка.
Форматирование применяется к активной ячейке или диапазону.
Во втором примере мы напрямую обратились к диапазону без присваивания его переменной.
Обратите внимание на форму записи параметров внешнего вида, которые мы меняем. Вначале определяем активную ячейку (или диапазон), затем после точки прописываем то, что меняем. Можно писать в одну строку, можно через перевод строки (так удобнее). В конце закрываем оператор точкой с запятой.
Также можно объединять ячейки, применить выравнивание, перенос текста и пр. (при объединении сохраняется значение левой верхней (первой) ячейки выделенного диапазона.
Подобная задача возникла у меня, когда я создавал макрос, который обрабатывал таблицу с данными всех участников песенного конкурса (их было около 100 человек) и по каждому их них генерировал специальный бланк, который потом раздавали судьям. Работу данного скрипта можно посмотреть в этом видео.
Если изображение большое, нужно либо объединить несколько ячеек в одну и потом вставлять изображение, либо сделать размер ячейки больше.
Еще пример реализации. Мы объединим несколько ячеек, увеличим размеры итоговой (объединенной) ячейки, закрасим ее в желтый и обведем черной рамкой:
Работа с диапазонами
В данном примере на каждом шаге цикла в переменную CurrentCell сохраняется текущее значение ячейки нашего диапазона с координатами (i,j).
Работа с листами таблицы
Данная функция возвращает true (истина), если лист (заданный переменной SheetName) существует, или false (ложь), если указанного листа нет.
Данная функция удаляет заданный лист (SheetName), если он существует.
Обработка некоторых событий таблицы.
В данном примере мы создаем меню для запуска функций (которые мы создали в нашем проекте).
Понятно, что в данной функции (кроме создания меню) мы можем запускать любые функции, которые имеются в нашем проекте.
Дополнительно хочу поделиться фишкой, которой я часто пользуюсь, если что-то не знаю (помимо Google).
Если мне что-то нужно реализовать, но я не знаю (или не помню), как это делается через макрос, то я включаю запись макроса, делаю нужные действия вручную, затем останавливаю и сохраняю макрос.
После достаточно зайти в редактор скриптов и посмотреть, какие команды записал макрос 🙂
На этом пока все. В данной статье я привел несколько методов, которые были использованы мной при написании скриптов гугл таблицы.
Статья будет дополняться. Если эта тема для вас актуальна, сохраняйте в закладки, чтобы не потерять.
Кстати, если наш сайт принес вам пользу и вы хотите сказать нам “Спасибо” и поддержать нас 🙏, то это можно сделать ЗДЕСЬ.
Готовое бесплатное решение учета клиентов и заказов в гугл таблице
Также хочу поделиться готовым решением гугл таблицы. В видео можно посмотреть пример ее работы.
Готовое платное решение учета клиентов и заказов в гугл таблице
Недавно у нас вышла платная версия готовой гугл таблицы по учету клиентов и заказов с расширенными возможностями. Детальную информацию по таблице смотрите ЗДЕСЬ.
Заказать макросы (скрипты) для Гугл таблиц
Вы также можете заказать макросы для Гугл таблиц, если хотите сэкономить время или нет возможности сделать их самостоятельно.
С тем, как можно сделать заказ и сколько они стоят, смотрите ЗДЕСЬ.
И еще на ютуб в отдельном плейлисте буду выкладывать некоторые примеры работы макроса.
Это один из примеров работы макроса.