Как сделать скрипт в гугл таблицах
Решения конкретных задач программирования. 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… для этой ячейки … У Вас должна появится боковая панель, показывая контрольный список допустимых значений.
Видео инструкция от разработчика скрипта Александра Иванова
Саш, огромное тебе спасибо за твой труд и за создание оригинального сценария.
Читайте также: