готовые скрипты для гугл таблиц
Готовые скрипты для гугл таблиц
Этот пост будет актуальным для тех, кто хочет писать скрипты в гугл таблице (макросы) для себя или зарабатывать на этом. На фрилансерских сайтах бывают задачи по написанию скриптов и это умение может приносить доход. Готовые фрагменты помогут быстрее справиться с разными задачами по написанию макросов.
Время от времени я пишу макросы своим клиентам и знакомым для решения некоторых задач. Пример одной такой задачи и ее решение можно посмотреть в видео здесь.
Поскольку я делаю это не регулярно, кое-что забывается и приходиться открывать старые проекты и подсматривать реализацию того или иного метода.
Такой вариант выхода из ситуации увеличивает время, которое тратится на разработку, что в свою очередь снижает эффективность.
В связи с этим пришла идея сделать что-то наподобие памятки по написанию скриптов для гугл таблиц.
Я решил поделиться ею с нашими читателями, вдруг кому-то она также пригодится.
И конечно, вы всегда можете заказать у меня макрос под ваши потребности и задачи.
В данном случае в переменную 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).
Если мне что-то нужно реализовать, но я не знаю (или не помню), как это делается через макрос, то я включаю запись макроса, делаю нужные действия вручную, затем останавливаю и сохраняю макрос.
После достаточно зайти в редактор скриптов и посмотреть, какие команды записал макрос 🙂
На этом пока все. В данной статье я привел несколько методов, которые были использованы мной при написании скриптов гугл таблицы.
Статья будет дополняться. Если эта тема для вас актуальна, сохраняйте в закладки, чтобы не потерять.
Кстати, если наш сайт принес вам пользу и вы хотите сказать нам “Спасибо” и поддержать нас 🙏, то это можно сделать ЗДЕСЬ.
Готовое бесплатное решение учета клиентов и заказов в гугл таблице
Также хочу поделиться готовым решением гугл таблицы. В видео можно посмотреть пример ее работы.
Готовое платное решение учета клиентов и заказов в гугл таблице
Недавно у нас вышла платная версия готовой гугл таблицы по учету клиентов и заказов с расширенными возможностями. Детальную информацию по таблице смотрите ЗДЕСЬ.
Заказать макросы (скрипты) для Гугл таблиц
Вы также можете заказать макросы для Гугл таблиц, если хотите сэкономить время или нет возможности сделать их самостоятельно.
С тем, как можно сделать заказ и сколько они стоят, смотрите ЗДЕСЬ.
И еще на ютуб в отдельном плейлисте буду выкладывать некоторые примеры работы макроса.
Это один из примеров работы макроса.
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 Apps Script:
дрессированные коды
Пользовательское меню
Функция onOpen является простым триггером. Выполняется при открытии документа.
Авторизация пользователя для запуска этой функции не требуется, поэтому возможности её ограничены лишь несколькими действиями.
В частности, для onOpen доступно создание пользовательского меню документа, таблицы, слайдов и форм в контейнерном скрипте.
Функция addItem добавляет элемент меню.
Автоматическое письмо после заполнения формы
Использование html-разметки в письме не обязательно, это всего лишь дополнительный атрибут.
Тескт письма разделен на блоки, помещенные в кавычки и соединенные оператором плюс (‘текст ‘ + ‘текст’). Это сделано для удобства чтения кода, чтобы обеспечить возможность переноса строк.
Так тоже правильно (‘текст текст’), но если перенести часть текста на следующую строку, не закрывая кавычки и не добавляя оператор плюс (+), то код не понимает, что продолжается текст и воспринимает перенесенную часть текста, как новую команду.
Поэтому перенос текстовых строк в коде выглядит так:
«текст текст текст » +
«текст текст»
Альтернативный равнозначный вариант кода выглядит так:
«текст текст текст текст текст»
Причем, данный перенос строки (в первом варианте), повторюсь, нужен только для удобства чтения кода, но в самом письме переноса строки не будет.
Для того, чтобы добавить в письмо перенос строки, нужно в конце строки добавить тег
. Это мы уже плавно перешли к тегам html-разметки.
Скрипты для новичков. Часть 1 | @google_sheets
Привет! Открываем серию, в которой мы планируем вам показывать простые приёмы работы с Таблицей (и не только) скриптами, также будем слега касаться JS (языка, на основе которого написан Google Apps Script, язык, на котором мы пишем скрипты в Google Docs)
В этой статье: обращаемся к текущей Таблице, к выбранному листу, забираем диапазон заполненных ячеек и разбираемся, что нам возвращается.
Обращаемся к Таблице, Листу, забираем данные
//определяем активную таблицу
const ss = SpreadsheetApp.getActive();
//определяем в ней Лист1
const sheet = ss.getSheetByName(‘Лист1’);
//определяем на Лист1 диапазон с данными и забираем его значения
const data = sheet.getDataRange().getValues();
//возвращаем эти значения
Запускаем функцию в редакторе скриптов – функция выводит в лог заполненные ячейки.
Помимо этого функция возвращает массив заполненных ячеек ( return data) и его можно использовать в другой функции, что нам пригодится дальше.
Что нам вообще возвращается
Функция возвращает несколько массивов (array) в массиве.
Строк с данными несколько, они отделены от друг-друга запятыми и объединены внешним массивом.
Если бы у вас было только две строки с данными по две ячейки в каждой, то эта конструкция выглядела бы так:
А ЕЩЕ наша функция сможет работать как пользовательская функция, её можно вызвать просто на листе, не запуская скрипт (смотрите скриншот)
Итак, у нас есть прекрасный массив с данными, как обращаться к нему, как посчитать количество строк с данными, как найти последнюю строку вообще, последнюю строку и выбранном столбце, последний столбец – мы разберёмся в следующей главе.
Данные из 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-специалистов».
Пройдите бесплатный вводный урок и получите комплексную обратную связь по своему уровню знаний, затем выбирайте преподавателя и программу обучения себе по душе!