как вставить скрипт в гугл таблицу
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, дополнение
Но можно сделать небольшое дополнение, чтобы не приходилось каждый раз запускать функции вручную.
Они будут приходить в случае, если скрипт выполнялся с ошибками.
Конечный результат действа:
Послесловие
Как и всегда, будем рады вопросам и всему такому прочему в комментариях. Следующая часть этого материала, более детально обучающая процессу, теперь обитает по этой вот ссылке, а следующая по этой.
Продолжения раз-два-готовим и три. Ну и комментарии конечно содержат много вкусного.
Cоветы и руководства по Google Таблицам
Мастер Google Таблиц с полезными советами и руководствами
Как записать макрос в Google Таблицы (простое пошаговое руководство)
Google Таблицы уже сами по себе довольно эффективны (со всеми этими удивительными формулами и функциями). Но что выводит их на следующий уровень, так это то, что теперь они позволяют вам записывать и использовать макросы в Google Таблицах.
В этом руководстве я расскажу все, что вам нужно знать о записи и использовании макросов в Google Таблицах, а также приведу несколько примеров, когда это может быть полезно. Но прежде чем мы начнем, позвольте мне быстро ответить на этот основной вопрос и убрать его с нашего пути.
Что такое макрос в Google Таблицах?
Макрос — это фрагмент кода в бэкэнде Google Таблиц (не волнуйтесь, это совсем несложно).
Этот макрос (фрагмент кода) представляет собой последовательность шагов, которые вы определили, и как только вы запустите этот макрос, он автоматически выполнит все эти шаги.
В качестве примера предположим, что вы получили набор данных, в котором вам нужно сделать три вещи:
Теперь вы можете выполнять все эти три действия в Google Таблицах вручную (шаг за шагом).
Но что, если вам придется делать это снова и снова каждый день или несколько раз каждый день. В этом случае вы можете быстро записать макрос и автоматизировать эти шаги. Поэтому в следующий раз, когда у вас будет набор данных, все, что вам нужно сделать, это запустить макрос, и он выполнит следующие действия.
Не волнуйтесь! Для этого вам не нужно знать код. Когда вы записываете макрос, вы просто показываете в Google Таблицах шаги, которые необходимо выполнить (делая это один раз). Google Sheets автоматически создает для вас код и использует его позже, когда вы запускаете макрос.
Итак, давайте начнем и запишем наш первый макрос.
Запись макроса в Google Таблицы
Запишем простой макрос в Google Таблицы, который будет делать следующее:
Вот шаги, чтобы записать этот макрос в Google Таблицы:
Вышеупомянутые шаги запускают запись макроса. С этого момента (пока вы не остановите регистратор макросов) Google Sheets будет отслеживать все, что вы в нем делаете, и преобразовывать ваши шаги в код в серверной части.
Теперь, когда Google Таблицы записывают каждый шаг нашей работы, давайте сделаем три вещи, которые мы хотим автоматизировать:
Выполнив эти три действия, нажмите кнопку «Сохранить» в диалоговом окне «Макрос».
Откроется диалоговое окно «Сохранить новый макрос», в котором нужно указать имя макроса. Желательно сделать его кратким, но достаточно информативным, чтобы вы знали, что делает этот макрос. В этом примере я назову эту ячейку Color Hello.
У вас также есть возможность указать ярлык для этого макроса. Когда вы устанавливаете ярлык, вы можете использовать это сочетание клавиш для запуска макроса. Таблицы Google позволяют использовать ярлык в следующем формате — Control + Alt + Shift + Number (где число может быть от 0 до 9)
После сохранения макроса может потребоваться несколько секунд, чтобы сохранить его в Google Таблицах. В настоящий момент Google Sheets преобразует ваш макрос в скрипт Google Sheets (что-то, что понимает Google Sheets).
Как только макрос будет сохранен, вы увидите уведомление в левом нижнем углу документа Google Sheets. Он также показывает параметр «Изменить сценарий», и если вы щелкнете по нему, он откроет редактор сценариев Google Apps и покажет вам записанный код.
Запуск макроса в Google Таблицах
После того, как вы записали макрос, вы можете запускать его в любое время, и он будет следовать шагам, которые вы показывали при записи макроса.
Есть несколько способов запустить макрос в Google Таблицах:
Давайте быстро рассмотрим каждый из этих способов запуска макроса в Google Таблицах.
Использование параметров макроса
После того, как вы записали макрос, вы можете легко запустить его, разместив его на вкладке «Инструменты».
Щелкните вкладку «Инструменты» и наведите курсор на параметр «Макросы».
В появившихся дополнительных параметрах вы увидите все макросы, перечисленные внизу (после первых трех параметров).
Как только вы нажмете на любое имя макроса, он будет немедленно выполнен.
Google Таблицы не так быстр, как другие инструменты для работы с электронными таблицами (например, Excel). Поэтому, когда вы нажимаете на любое имя макроса, это может занять несколько секунд. Это также зависит от автоматизации, которую вы пытаетесь достичь с помощью макроса. Если к нему много шагов, это может занять больше нескольких секунд.
Использование сочетания клавиш
Когда вы записываете макрос в Google Таблицы, вам также предлагается указать сочетание клавиш (необязательный шаг). Это сочетание клавиш можно назначить в диалоговом окне, в котором вы даете макросу имя.
После настройки клавиатуры вы можете просто использовать ярлык, и Google Sheets мгновенно запустит макрос.
Опять же, это может занять несколько секунд в зависимости от количества шагов в макросе.
Из редактора скриптов Google
Когда вы записываете макрос в Google Таблицы и сохраняете его, шаги автоматически сохраняются в редакторе скриптов Google Apps.
Теперь, если вы хотите выполнить макрос, вы также можете сделать это из редактора GAS.
Для этого вам сначала нужно открыть редактор GAS (щелкнув вкладку «Инструменты», а затем «Редактор скриптов»).
Когда откроется редактор сценариев, выберите макрос, который вы хотите запустить (из раскрывающегося списка «Выбрать функцию»), и нажмите кнопку воспроизведения на панели инструментов.
Назначив макрос фигуре
Вы также можете вставить фигуру на лист, а затем назначить макрос этой кнопке.
Чтобы вставить кнопку, щелкните вкладку «Вставка», а затем щелкните «Рисунок».
Откроется диалоговое окно «Рисование».
Щелкните значок Фигуры в диалоговом окне и вставьте фигуру, которую хотите использовать в качестве кнопки для запуска макроса.
Когда вы нажимаете на любую фигуру, вам нужно будет нарисовать ее в отведенной области. Как только вы нарисуете его и нажмете «Сохранить и закрыть», фигура будет вставлена в рабочий лист.
Чтобы назначить макрос этой форме, выберите фигуру и щелкните три точки, которые появляются в правом верхнем углу.
Из появившихся опций нажмите «Назначить скрипт».
Приведенные выше шаги откроют диалоговое окно «Назначить сценарий», в котором вам нужно будет ввести имя сценария, который вы хотите запустить. К сожалению, на момент написания этого руководства не было возможности получить список всех названий макросов прямо в этом диалоговом окне. Вам нужно знать точное имя и ввести его здесь.
Теперь, когда вы нажимаете на фигуру, она мгновенно запускает макрос.
Абсолютная ссылка на относительную ячейку при записи макроса
Когда вы используете ссылку на ячейку в Google Таблицах, она может быть абсолютной или относительной.
В этой формуле используются абсолютные ссылки на ячейки. Это означает, что если я скопирую и вставлю эту формулу из ячейки B1 в ячейку B2 (или любую другую ячейку на листе), она не изменит ячейки, на которые она ссылается.
Напротив, допустим, у меня в ячейке B1 есть следующая формула: = СУММ (A1: A10). Поскольку здесь используются относительные ссылки на ячейки, когда я копирую и вставляю формулу из ячейки B1 в ячейку B2, формула изменится на = СУММ (A2: A11).
Это происходит потому, что использование относительной ссылки на ячейку не блокирует ссылки на ячейки и относится к позиции, в которой она используется. Если я скопирую эту формулу в ячейку C1, она изменится на = СУММ (B1: B10). Это потому, что я сдвинул формулу на один столбец вправо (с B на C), ссылка в формуле также сдвинута на один столбец.
Теперь, переходя к макросам в Google Таблицах, вы можете записывать макрос с абсолютными или относительными ссылками на ячейки. Эта опция появляется, как только вы начинаете запись макроса.
Когда вы записываете макрос с абсолютной ссылкой на ячейки, он запоминает выбранные вами ячейки или диапазоны и использует те же ячейки при запуске макроса. Например, если вы записываете макрос для ввода текста «Hello» в ячейку A1, при повторном запуске этого макроса он всегда будет возвращаться в ячейку A1 и вводить в нее текст «Hello».
Но с относительными ссылками на ячейки он запоминает позицию, с которой вы начали, а затем перемещается относительно этой позиции. Например, если я начну записывать макрос, когда у меня выделена ячейка B1, и я сначала выберу A1, а затем введу в него текст «Hello», макрос Google Sheet запомнит, что я переместил одну ячейку влево.
Таким образом, в следующий раз, когда вы запустите этот макрос и выберете ячейку K1, он введет текст в ячейку J1 (которая находится на одну ячейку слева от выбранной ячейки).
Преимущества использования макроса записи
Вот преимущества использования записанного макроса:
Чего макрос не может делать в 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 Таблиц на вашем сайте
Для тех, кто пользуется 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-специалистов».
Пройдите бесплатный вводный урок и получите комплексную обратную связь по своему уровню знаний, затем выбирайте преподавателя и программу обучения себе по душе!