Как сделать скрипт в гугл таблицах

Обновлено: 07.07.2024

Решения конкретных задач программирования. Java, Android, JavaScript, Flex и прочее. Настройка софта под Linux, методики разработки и просто размышления.

понедельник, 7 мая 2012 г.

Пишем скрипты в Google Spreadsheet

В далёких 90-х я начинал осваивать программирование, и первые мои "коммерческие" приложения были написаны на VBA под MS Excel. До сих пор помню как удобно было работать, имея готовый интерфейс, функции для манипуляции данными и адресуемые ячейки. Этот, в принципе, нормальный путь для обучения программированию, сейчас имеет несколько недостатков. Во-первых, нехорошо с первых приложений связывать себя с поприетарной средой разработки и исполнения кода, а во-вторых, кому сейчас нужен Visual basic?. Вот JavaScript и GoogleDoc - другое дело. Да и возможностей, учитывая "web-интерфейсность" среды исполнения и открытые API, существенно больше.
Чтобы помочь тем кто заинтересуется программированием "под ячейки", предлагаю тут простой "урок" по созданию скриптов для Google Spreadsheet.

Создаём скрипт

Тут всё достаточно просто: Объект SpreadshettApp даёт нам экземпляр документа из которого мы берём первый лист. Объект Browser позволяет нам запрашивать данные у клиента и сообщать ему что-нибудь диалоговыми окнами. Для чтения/записи данных ячеек получаем её из листа методом getRange а затем используем методы getValue/setValue.

Вызываем нашу функцию

Есть несколько способов вызвать наш скрипт из Spreadshett-а. Вариант с вызовом через меню "Инструменты"->"Управление скриптами" мы не рассматриваем как неюзабельный.
Классический вариант - запуск кнопкой, делаем так: вставляем на лист рисунок через "Вставка"->"Рисунок" а затем в меню рисунка (показывается при клике на него правой кнопкой) выбираем "Назначить скрипт". В появившемся поле ввода указываем имя функции.
Также можно добавить свой пункт в меню документа, причём сделать это программно. Добавляем в нашем скрипте сточку:

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

Что такое макросы?

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

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

Макросы — это мощная функция, которая может выполнять практически все, что способен Sheets. Вот лишь несколько примеров его функциональности:

Примените форматирование и стили.
Создавайте совершенно новые таблицы.
Используйте любую функцию Google Sheets, панель инструментов, меню или функцию.

Небо это предел.

Как записать макрос в Google Sheets

Запустите Google Sheet и нажмите Инструменты> Макросы> Запись макроса.

Это открывает меню записи в нижней части окна, с двумя вариантами записи ваших действий:

Абсолютные ссылки: макрос будет выполнять задачи только в тех ячейках, которые вы записали. Если вы выделите курсором ячейку B1, макрос выделит только курсив B1 независимо от того, на какую ячейку вы щелкнули.
Относительные ссылки . Макрос выполняет задачи в выбранных ячейках независимо от того, где они находятся на листе. Если вы выделите курсором B1 и C1, вы можете повторно использовать один и тот же макрос для выделения курсором ячеек D1 и E1 позже.

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

Если вам нужно изменить имя макроса или ярлык, вы можете отредактировать макрос, щелкнув Инструменты> Макросы> Управление макросами.

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

Как запустить макрос в Google Sheets

Если ваш макрос является абсолютной ссылкой, вы можете запустить макрос, нажав сочетание клавиш, или перейдите в Инструменты> Макросы> Ваш макрос и затем щелкните соответствующую опцию.

Как импортировать макросы

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

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

Откройте Google Sheet с макросом, который вы хотите скопировать, а затем нажмите Инструменты> Макросы> Управление макросами.

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

Выделите макрос (ы), который вы хотите скопировать, затем нажмите Ctrl + C. Обязательно скопируйте все до и включая заключительную точку с запятой.

Нажмите Ctrl + S, чтобы сохранить сценарий, закрыть вкладку и вернуться к таблице.

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

Далее нажмите Инструменты> Макросы> Импорт.

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

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

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

Плюсов в работе с google таблицами много, вот только те немногие которые мне очень понравились:

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

Про собственные скрипты остановимся поподробнее и напишем простой скрипт для автоматической установки даты в определенные ячейки.

Задача:

Подскажите пожалуйста формулу для автоматической подстановки даты или времени в необходимую мне ячейку. к примеру. в ячейке А1 я пишу Иванов, в ячейке В1 автоматически подставляется текущая дата или время. далее пишу в ячейке А2 Петров, в ячейке В2 автоматически пишется дата текущая или время.

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

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

Если вкратце, то нужно создать функцию которая срабатывает в момент редактирования определенной ячейки. Для примера, предположим что нам нужно при добавлении записи в ячейку 17 автоматически подставлять текущую дату в ячейку номер 2, а так же текущее время в ячейку номер 3. Ещё немного усложним задачу и сделаем проверку, если в соседней ячейке уже есть какая-то запись, то данные не обновлять и не менять дату и время.

Если кому-то не понятен сей скрипт, пишите в комменты, постараюсь помочь.

Примеры использования Google Apps Script

В этой статье мы решили собрать воедино наши видео с примерами использования скриптов Google Apps Script.

Переход к последней заполненной ячейке при открытии таблицы с помощью Google Apps Script

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

Создание индивидуальных Google Презентаций с помощью скриптов Google Apps Script

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

Получение информации об изменении ячейки Google Таблиц с помощью Google Apps Script

Как сделать так, чтобы при изменении ячейки Google Таблицы фиксировалась информация о том, кто и когда эту ячейку изменил? Нам поможет Google Apps Script. Подробности в этом видео:

Создание индивидуальных писем в Google Документах с помощью Google Apps Script

В этом видео мы научимся с помощью сочетания Google Документы + Google Apps Script создавать письма по шаблону и заполнять их данными из Google Таблицы:

Работа с файлами на Google Диске с помощью Google Apps Script

В этом видео мы поговорим о том, как с помощью Google Apps Script получить список всех файлов на Google Диске (или в конкретной папке).

Как настроить работу множественного выбора в ячейка Google Docs

Добавляем скрипт

Перейдите в Инструменты > Редактор скриптов …

Назовите файл multi-select .gs и вставьте содержимое ниже. далее нажмите Файл> Сохранить.

Добавляем HTML

Назовите файл dialog .html и вставьте код который указан ниже. Далее нажмите Файл> Сохранить.

Используем решение в своих таблицах

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

Перейдите в Scripts > Multi-select for this cell… для этой ячейки … У Вас должна появится боковая панель, показывая контрольный список допустимых значений.

Видео инструкция от разработчика скрипта Александра Иванова

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

Читайте также: