что такое скрипты в гугл таблицах
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 и общую анатомию интерфейса.
Как с помощью js и google sheets стать соседом Билла Гейтса по гольф клубу
В последнее время на хабре стали появляться статьи, начинающиеся с рассказов про свободное время на самоизоляции и, как итог, появившихся троллейбусов из буханки. Возможно, администрации стоит задуматься о добавлении нового хаба — Самоизоляция..
Вот и у меня появилось свободное время, которое я посвятил анализу своих сделок в Тинькофф Инвестициях. Есть 2 типа людей: одни прекрасно строят многомерные массивы у себя в голове, пробегаясь по ним for-циклом в IPython Notebook, другим же нравится «щупать» цифры, раскладывая их по полочкам в Excel. Себя я отношу ко второй категории, поэтому все свои сделки аккуратно заносил в Google Sheets.
Под катом я расскажу, как автоматизировал свою рутину при помощи Google Apps Script и API от Тинькофф Инвестиций.
Перед тем как мы перейдём к сути, маленький словарик терминов, которыми я пользуюсь в статье:
Задача
У каждого инвестора или трейдера есть свой особый способ вести аналитику сделок. Кому-то достаточно тех инструментов, которые предоставляет брокер — дэшборд в личном кабинете или еженедельные отчёты. Мой способ такой: я веду отдельную таблицу по каждому инструменту, которым торгую. В этих таблицах я рассчитываю прибыль/убыток, определяю стратегию будущих сделок и всячески учусь на своих ошибках.
Какое-то время я заносил сделки вручную, перепечатывая их из мобильного приложения ТИ. Мне захотелось оптимизировать этот процесс. В поисках решения я наткнулся на статью хабраюзера OvkHabr. Из неё я узнал, что брокер предоставляет API, который полностью покрывает мои нужды и принялся за разработку.
Google Apps Script
OpenAPI
Методы взаимодействия с ТИ реализованы с помощью OpenAPI, а сама документация представлена через swagger-ui
Авторизация представляет собой http-заголовок с токеном, который нужно посылать при каждом запросе. Процедура получение токена описана в ещё одной документации.
Для начала нужно набросать простенький клиент для http походов в ТИ.
Какие методы нам понадобятся?
Сделаем клиент классом, чтобы потом создать его единственный экземпляр и обращаться во всём верхнеуровневом коде:
Получение цены инструмента
Custom Functions
Давайте сделаем функцию getPriceByTicker, которая будет возвращать текущую цену инструмента. Её мы будем использовать в качестве формулы в любой ячейке ( =getPriceByTicker(«YNDX») ).
Для этого нам сначала нужно получить figi инструмента, а потом получить его стакан, из которого мы и вытащим цену:
Здорово! Теперь в нашем распоряжении есть отличный тул, при помощи которого мы можем получать текущую цену акции, и использовать её в расчётах.
Автообновление формулы
Для того, чтобы данные в таблице всегда были актуальными, хочется сделать эту формулу автообновляемой. Прямого способа сделать это нет, но GAS комьюнити придумало вот такой хак:
Cache Service
Как мы видим, получая цену мы делаем аж 2 API-вызова. И если в случае похода за стаканом это оправдано, так как цена постоянно меняется, то figi у инструмента является константой. Чтобы сделать нашу формулу чуть быстрее и надёжней, воспользуемся Apps Script Cache Service. Это простое key-value хранилище, которое отлично справится с нашей задачей:
Получение списка сделок
Взвешенное среднее
Исходя из документации, объект Operation возвращается нам в виде:
Меня же интересует сводная информация по конкретной сделке, поэтому для определения цены, по которой она произошла, мы будем пользоваться взвешенным средним.
Для нашего примера с яндексом, взвешенное среднее считается так
А в коде это будет выглядеть так
Работа с таблицей
Как было описано выше, если custom функция возвращает двумерный массив, данные займут всё необходимое свободное пространство под ячейкой с формулой. Соответственно, нам необходимо сформировать такой массив.
Мы будем итерироваться по операциям и биржевым сделкам, чтобы этот массив заполнить. Нас не интересуют отменённые операции, а также операции списания комиссии, но интересует само значение комиссии. Помимо этого, мы «на лету» будем присваивать минус операциям покупки (символизируя списание с нашего брокерского счёта) и плюс продажам. Таким образом, нам будет проще понимать текущую стоимость позиции (просто просуммировав столбец)
Остаётся только вернуть массив values. Итоговый код функции выглядит так:
Проверяем работу в бою:
Заключение
В рамках статьи мы познакомились с API Тинькофф Инвестиций, возможностями, которые предлагает Google Apps Script, а также решили задачу автоматизации заполнения Google Sheets реальными сделками с брокерского счёта. Надеюсь, вам было интересно)
Весь код и короткий how-to выложен на github
Для тех читателей, кто хочет вступить на дорогу инвестирования, но не знает с чего начать — могу посоветовать бесплатный курс от Тинькофф Журнала https://journal.tinkoff.ru/pro/invest/ — он короткий, информативный и доходчивый.
А при открытии брокерского счёта в ТИ по моей ссылке вы получите акцию стоимостью до 20000 рублей в подарок.
Google Apps Script: полезные функции и фишки для SEO (часть первая)
В этой рубрике (если зайдет, конечно), я буду демонстрировать свои небольшие скрипты, полезные функции и фишки Google Apps Script. В конце каждого поста будет бонус — небольшой скрипт.
Так как это первый пост, думаю, нужно написать о языке и ограничениях.
Скрипт работает в сервисах:
Какой уровень владения языками программирования должен быть, чтобы понимать и самостоятельно писать скрипт? Ответ простой: любой, главное желание, терпение и смекалка =)
Я буду приводить примеры скриптов с подробными комментариями, чтобы было понятно.
Сразу скажу: я не программист и отдельно какие-то курсы не проходил. Возможно, где-то в коде откровенные «велосипеды». Буду рад, если в комментариях вы мне укажете на них.
Как создать скрипт Google Apps Script для Google Sheets
По традиции, выводим надпись «Hello world»:
1. Заходим в таблицу.
2. Переходим во вкладку «Инструменты» — «Редактор скриптов».
3. Мы видим рабочую область, где и нужно писать скрипты.
5. Запускаем нашу функцию:
6. Сохраняем проект и проходим этап авторизации.
Шаг 1:
Шаг 2:
Шаг 3:
Шаг 4:
Шаг 5:
Разбор кода скрипта в Google Apps Script
В этой строке мы создаем объект класса SpreadsheetApp, чтобы в дальнейшем мы могли использовать различные методы этого класса.
В этой строке грубо говоря мы присваиваем переменной sheetOne так называемый адрес вкладки, которая называется Пост 1. Это упрощает работу с таблицами.
В этой строке мы выводим надпись «Hello world» в ячейку A1 на вкладке «Пост 1». Тут нужно разобраться подробно:
Бонус: как спарсить тег Title и метатег Description
Скрипт можно получить по ссылке. Обязательно делаем копию.
1. В колонке А Вставляем наши URL.
2. Запускаем скрипт.
3. Забираем результаты.
Какие сайты скрипт не сможет спарсить:
Вывод
Google Apps Script помогает бесплатно автоматизировать много ручной работы, например:
Конечно, в скриптовом языке Google Apps Script есть много других полезных проверок и расчетов. Их я продемонстрирую в следующих статьях.
Если хотите поделиться своими фишками или задать вопросы, добро пожаловать в комментарии.
Google Apps script: получение данных из Flurry
Вариантов использования Google Apps Scripts множество. Мой друг работал с иностранным заказчиком и тот просил каждый день отчет ему на почту с текстом кто и что делал. В результате он попросил сотрудников трэкать все в гугл таблицу не позднее 21:00, потом стартовал скрипт и собирал данные и слал по нужному адресу.
В статье я приведу простой и, как мне кажется, полезный пример использования Google Apps Scripts.
Что такое Google Apps Scripts?
Если коротко, то это сервис который позволяет на JavaScript писать скрипты, работающие в облаке Google. Сервис предоставляет широкий API, к примеру в данной статье мы создадим гугл таблицу на основе метрик приложения из Flurry.
В чем состоит решаемая проблема и что хотелось бы получить?
Я работаю в компании которая занимается разработкой игр под мобильные платформы для статистики мы используем данные своего сервера + данные от Flurry. Чтобы как-то все это совместить я решил использовать сервисы предоставляемые Google и в результате получить один отчет с данными и графиками.
Что вы получите в конце этой статьи?
Скрипт который по расписанию запрашивает метрики приложения от Flurry сервиса, парсит их и делает простую гугл таблицу(аналог Microsoft excel) c этими данными и простым графиком.
Основная цель
Я не ставлю цели создания эталонного отчета статистики. Я был удивлен доступностью такого функционала и решил поделиться небольшим опытом виде тестового скрипта.
Начнем с простых шагов
Что понадобится?
Настройка gmail
Настройка Flurry
Я подразумеваю, что у вас уже есть сконфигурированный аккаунт Flurry с данными.
Для программного доступа к данным от Flurry выполните инструкции по ссылке:
support.flurry.com/index.php?title=API/GettingStarted
В итоге вы получите ключ, который нужно сохранить для использования в скрипте.
Еще понадобится “ProjectApiKey” от Flurry, который вы уже используете в мобильном приложении.
Пишем скрипт
Точнее уже все написано, тут я просто опишу, как это запустить.
Идем по ссылке и забираем тексты скриптов github.com/chapayGhub/flurrymetricstospreadsheet
В проекте, который мы создали до этого, нужно создать такие же.
Теперь, после того как все сделали, откройте Settings.gs и в нем
Введите свои ключи от Flurry
Тут введите свое имя электронной почты, лучше от гугл(другие я не проверял)
Скрипт настроен на получение метрик приложения из Flurry + на ваш почтовый ящик придет письмо, что вы добавлены как редактор.
Запускаем и дебажим
В левой панели выберем скрипт «СreateStatisticReport»
В верхней панели около кнопки с жуком в выпадающем списке выбираем «makeStatisticReport»
Теперь все просто верхняя панель имеет две «супер» кнопки понятные любому программисту.
Запуск по расписанию
Выполните действия на картинках
Нюансы
Сделать так чтобы скрипт работал каждый раз с одним документом
Сразу запустите на выполнение не на дебаг. Должен создаться документ в папке «Statistic» и к вам на почту придут письма, в одном из которых будет идентификатор созданного документа
Присвойте его переменной расположенной в скрипте Settings.gs.
Теперь скрипт будет обнолять именно этот документ, а не создавать каждый раз новый. Если данный параметр не введен, то при старте скрипт пытается найти документ по несуществующему идентификатору, естественно не находит его, бросает исключение(в дебаге может быть остановка в этом месте выполнения), но это исключение отлавливается и создается новый документ.
Логирование
Используйте следующий метод
Чтобы просмотреть вывод выберите следующий пункт из меню
Нужна помощь
Браузерный редактор вещь удобная, но вот если скрипт будет более объемным то в нем не совсем удобно работать.
По этой ссылке ниже описано как можно выгружать и загружать проект.
developers.google.com/apps-script/import-export
У меня появилась идея написать скрипт который на входе получал идентификатор проекта выгружал его на локальный диск в отдельные файлы. Эти файлы затем можно было бы по человечески отрефакторить и в конце опять через этот скрипт загрузить обратно.
Вроде все и описано, но вот что-то не выстрелило у меня ).
Может уже кто-то сделал такое или есть полезные ссылки? Буду благодарен.