назначение скрипта в гугл таблицах
5 функций скриптов Google Sheets, которые вам нужно знать
Google Sheets – это мощный облачный инструмент для работы с электронными таблицами, который позволяет вам делать практически все, что вы можете делать в Microsoft Excel. Но настоящая сила Google Sheets – это функция сценариев Google, которая поставляется вместе с ней.
Сценарии Google Apps – это инструмент для создания фоновых сценариев, который работает не только в Google Sheets, но и в Документах Google, Gmail, Google Analytics и почти во всех других облачных службах Google. Это позволяет автоматизировать эти отдельные приложения и интегрировать каждое из этих приложений друг с другом.
В этой статье вы узнаете, как начать работу со сценариями Google Apps, создать базовый сценарий в Google Sheets для чтения и записи данных ячеек и наиболее эффективные расширенные функции сценариев Google Sheets.
Как создать скрипт Google Apps
Вы можете начать прямо сейчас создавать свой первый скрипт Служб Google из Google Sheets.
Для этого выберите в меню Сервис, затем Редактор скриптов.
Это открывает окно редактора сценариев и по умолчанию используется функция myfunction (). Здесь вы можете создать и протестировать свой Google Script.
Чтобы попробовать, попробуйте создать функцию сценария Google Sheets, которая будет считывать данные из одной ячейки, выполнять вычисления и выводить объем данных в другую ячейку.
Функция для получения данных из ячейки – это функции getRange () и getValue (). Вы можете идентифицировать ячейку по строке и столбцу. Поэтому, если у вас есть значение в строке 2 и столбце 1 (столбец A), первая часть вашего сценария будет выглядеть следующим образом:
Это сохраняет значение из этой ячейки в переменной данных. Вы можете выполнить расчет данных, а затем записать эти данные в другую ячейку. Итак, последняя часть этой функции будет:
Когда вы закончите писать свою функцию, выберите значок диска для сохранения.
При первом запуске новой функции сценария Google Sheets, подобной этой (выбрав значок запуска), вам потребуется предоставить авторизацию для запуска сценария в вашей учетной записи Google.
Разрешить разрешения для продолжения. Как только ваш скрипт запустится, вы увидите, что скрипт записал результаты вычислений в целевую ячейку.
Теперь, когда вы знаете, как написать базовую функцию сценария Google Apps, давайте рассмотрим некоторые более сложные функции.
Используйте getValues для загрузки массивов
Вы можете перенести концепцию выполнения вычислений на данные в вашей электронной таблице с помощью сценариев на новый уровень, используя массивы. Если вы загружаете переменную в скрипт Google Apps с помощью getValues, переменная будет массивом, который может загружать несколько значений из листа.
function myFunction () <
var sheet = SpreadsheetApp.getActiveSheet ();
var data = sheet.getDataRange (). getValues ();
Переменная данных – это многомерный массив, который содержит все данные из листа. Чтобы выполнить расчет данных, вы используете цикл for. Счетчик цикла for будет работать через каждую строку, а столбец остается постоянным, в зависимости от столбца, в который вы хотите получить данные.
В нашем примере электронной таблицы вы можете выполнить вычисления для трех строк данных следующим образом.
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 таблицы как база данных для веб-приложения. Часть 1
Кому-то тема данной статьи может показаться странной, но тем не менее такая возможность существует. Информация в сети есть, но она почти вся на английском и в разрозненном виде. Чтобы сложить картинку воедино мне с коллегой пришлось немало побродить по интернетам, поэтому я бы хотел для себя лично зафиксировать эту информацию в письменном виде.
В рамках цикла статей по данной теме, мы пошагово создадим небольшое приложение в виде чек-листа задач, в котором будут возможности создания, чтения, обновления и удаления элементов (CRUD). В качестве БД мы будем использовать Google Sheets, а в качестве серверного языка — Google Apps Script.
У этого подхода есть ряд своих минусов и ограничений, о которых я обязательно расскажу в процессе.
Создание скрипта и подключение к таблице
Для начала нам необходимо создать новую таблицу (сделать это можно из Google Drive, авторизовавшись под учетной записью Google). Назовем саму таблицу “tasklist”, а единственный лист — “tasks”.
Возвращаемся в Google Drive и создаем новый Google Apps Script (“Создать — Еще — Google Apps Script”). Назовем его также: “tasklist”.
Теперь переходим к написанию первого кода. В самом начале объявляем две переменные. Первая будет отвечать за подключение к таблице по URL. (Я не проверял, но скорее всего для успешного подключения вы должны являться и владельцем таблицы и владельцем скрипта).
Роутинг
Наш скрипт будет выполнять более чем одну функцию, ведь мы запланировали возможности создания, чтения, редактирования и удаления данных. Для этого мы должны как-то объяснить нашему скрипту, какое действие при каком запросе ему следует выполнять. За прослушивание запросов, отправленных методом POST у нас будет отвечать стандартная функция doPost (e), а для запросов, отправленных методом GET doGet (e), соответственно. Для маршрутизации запросов мы разместим внутри них switch, которые будут принимать переменную operation и в зависимости от того, какое мы передадим туда значение, тот или иной switch будет перенаправлять входящие запросы на соответствующие функции.
Добавление новых строк
Теперь мы готовы к тому, чтобы написать код, позволяющий с нашей таблицей конкретно взаимодействовать. К сожалению, Google Apps Script не поддерживает классические SQL команды, типа: INSERT, SELECT, UPDATE, DELETE, но все это сделать возможно немного иными путями и командами. Давайте посмотрим на примере функции, где для добавления новой строки мы будем использовать метод appendRow():
Получение строк из таблицы
Получить строки из таблицы можно двумя разными способами. В этой части мы рассмотрим самый простой из них, который подразумевает, что мы можем просто выбрать массив данных и вернуть его в виде JSON. Для этого мы будем использовать комбинацию методов getRange() и getValues().
Если требуется получить список задач с определенным статусом или за определенные даты, то мы можем пробежаться по полученному массиву в цикле и выбрать только нужные строки.
Первая публикация
Теперь мы готовы для публикации и первого теста. Отмечу, что первая публикация потребует чуть больше времени и действий, чем последующие обновления. Это связано с тем, что для работы нашего приложения в сети мы, как владельцы таблицы должны предоставить необходимые разрешения скрипту для работы с ней. Поехали.
Первая версия нашего приложения развернута и уже готова к работе. Проверить это можно, например, через Postman. Для этого в качестве адреса используем недавно полученный URL-адрес нашего приложения.
Как видим, запись в табличке появляется.
Удаление строк
Для удаления строк нам по аналогии с первой функцией требуется добавить case в наш switch внутри doPOST(e) и написать соответствующую функцию. Поехали:
Теперь создадим функцию, отвечающую за удаление строк. Чтобы удалить строку, нам для начала необходимо ее найти. В нормальных СУБД для этого используются уникальные id, которые автоматически присваиваются строкам при добавлении, и по которым можно впоследствии однозначно найти элементы. В нашем случае такой роскоши не предусмотрено. Конечно, можно придумать какой-нибудь выход и, например, при добавлении строк генерировать уникальный хеш-код на основе текущей даты и еще чего-нибудь, но в рамках данной статьи мы будем считать, что название задачи должно быть уникальным. По нему мы и будем искать элементы, пробегаясь в цикле по нашим записям, и удалять записи при помощи метода deleteRow().
Обновление строк
Принцип тот же. Начинаем с doPost(e) и далее добавляем соответствующую функцию.
Алгоритм обновления данных похож на тот, что мы использовали при удалении элементов. Только в случае обновления нам потребуются два дополнительных параметра. Первый будет передавать новое значение, на которое мы будем заменять старое, а второй будет подсказывать нам в какой колонке это следует сделать. За обновление данных отвечает комбинация методов getRange() и setValue().
Обновление проекта
Если мы сохраним изменения и захотим протестировать написанный код, отправив запросы на удаление или обновление через Postman, то… ничего не произойдет, т.к. мы не переопубликовали наш проект. Чтобы опубликовать проект с последними изменениями делаем следующее:
Скрипты для новичков. Часть 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 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, дополнение
Но можно сделать небольшое дополнение, чтобы не приходилось каждый раз запускать функции вручную.
Они будут приходить в случае, если скрипт выполнялся с ошибками.
Конечный результат действа:
Послесловие
Как и всегда, будем рады вопросам и всему такому прочему в комментариях. Следующая часть этого материала, более детально обучающая процессу, теперь обитает по этой вот ссылке, а следующая по этой.
Продолжения раз-два-готовим и три. Ну и комментарии конечно содержат много вкусного.