Как сделать скрипт в гугл таблицах

Как с помощью 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: переносим расписание из таблицы в календарь

Для кого предназначена данная статья

Что понадобится

1. Создаем таблицу в Google Sheets

Это можно сделать, перейдя на страницу своего Google Drive.

Рис. 1. Правый клик мышью по рабочему пространству открывает контекстное меню, где нужно выбрать «Google Таблицы» — «Создать пустую таблицу»

В созданную таблицу нужно скопировать имеющееся у Вас расписание, в моем случае это расписание занятий в университете. Важно, чтобы в таблицы были следующие поля:

Вся остальная информация, в моем случае — преподаватель, тип занятия (лекция, семинар и т.д.), может быть отправлена в описание мероприятия.

Рис. 2. Таблица с расписанием

2. Создаем скрипт

2.1. Переходим в редактор скриптов

Нужно создать скрипт, который будет связан с только что созданной таблицей, будет иметь доступ к данным в ней и переносить их в календарь.

Рис. 3. Скрипт создается, через меню «Инструменты», в котором нужно выбрать пункт «Редактор скриптов»

Скрипты разрабатываются на языке JavaScript. Только что созданный скрипт содержит одну пустую функцию myFunction. Писать код можно в ней, но я предпочту дать ей более осмысленное имя SetCalendar.

Рис. 4. Созданный скрипт, дадим функции осмысленное имя — SetCalendar

Над редактором кода имеется выпадающий список, который позволяет выбрать основную функцию, которая будет являться точкой входа в запущенный скрипт. В нашем случае следуем выбрать функцию SetCalendar.

2.2. Разбираемся с отладкой

Часто для отладки скриптов на JavaScript мы выводим различные значения в консоль:

В Google Apps Script логировать значения следует немного иначе. Вместо объекта console следует использовать глобальный объект Logger:

После запуска скрипта, все выведенные в лог значения можно посмотреть из меню «Вид», выбрав пункт «Журналы» или же по сочетанию клавиш Ctrl+Enter:

Рис. 5. Модальное окно с логами последнего запуска скрипта

2.3. Извлекаем информацию о мероприятиях из таблицы

Сразу небольшой кусок кода

Рассмотрим строку 11.

Глобальный объект SpreadsheetApp — глобальный объект, содержащий методы, представляющие собой интерфейс для взаимодействия скрипта с приложением Google Sheets.

Первое, что нужно сделать — получить объект, представляющий активную (открытую в данный момент) страницу документа. Для этого необходимо вызвать метод getActiveSheet.

Метод возвращает объект sheet.

Далее, нужно получить объект range — он представляет собой диапазон ячеек таблицы, из которых мы будем извлекать данные. Это делается путем вызова метода getRange. В качестве параметров передаются:

Обратите внимание! Индексация строк и столбцов начинается с 1.

Строка 15: мы получаем из выбранного выше диапазона ячеек непосредственно данные путем вызова метода getDisplayValues.

Обратите внимание! Метод getDisplayValues() отдает данные в том виде, в каком он отображается в ячейках таблицы. Например, если ячейка содержит дату, то указанный метод вернет не какое-то внутреннее представление записанной в ячейку даты, а ее представление, которое видит пользователь в ячейке.

2.4. Извлекаем данные из ячеек

Прежде, чем работать с ячейками, удобно было бы дать номерам столбцов имена, дабы избежать ошибок при указании номеров столбцов с интересующими нас данными. Создадим для этого несколько констант:

Вы можете посмотреть какие столбцы стоят в таблице под данными номерами на рисунке 2.

Объект data представляет набор ячеек с данными, которые были получены. Теперь нужно получить данные из отдельных ячеек.

При использовании цикла for с переменной i по объекту data, переменная i на каждой итерации получает значение — целое число, которое является индексом очередной извлеченной из таблицы строки.

Строка 3: обращение к объекту data по индексу i возвращает объект row — текущую строку таблицы.

Строки 5-9: мы обращаемся к строке по индексу — номеру столбца с интересующими нас данными. Например, row[dateCol] — мы из текущей строки получаем дату мероприятия из столбца с индексом dateCol.

В строках 5-9 мы получили из текущей строки данные о дате занятия, времени начала и окончания, типе занятия, названии дисциплины и имени преподавателя.

В конце мы выводим полученные данные в лог, чтобы убедиться, что извлекли данные из таблицы правильно.

2.5. Работа с объединенными ячейками

В результате выполнения предыдущих шагов, мы получаем следующий скрипт

Выполним скрипт и посмотрим, что было выведено в лог:

Рис. 6. Не все даты были правильно извлечены из таблицы

Из лога видно, что из первой строки данные были извлечены, а в следующих строках, которые описывают мероприятия того же дня, поле с датой оказалось пустым. Почему так происходит?

Еще раз взглянем на таблицу:

Рис. 7. Даты вписаны в объединенные ячейки

При создании таблицы, дата вписывалась только в первую строчку расписания на конкретную дату. После чего, все ячейки из столбца, относящиеся к одной дате были объединены.

Когда мы двигаемся по строкам таблицы, мы получаем дату не из всех ячеек, которые были объединены в одну, а только из первой.

Немного изменим алгоритм, чтобы корректно извлекать дату из объединенных ячеек:

Мы вводим дополнительную переменную savedDate — в ней храним последнюю прочитанную дату.

При чтении даты из текущей строки. Если прочитанная дата — пустая строка, то берем дату из переменной savedDate, иначе обновляем значение переменной savedDate.

2.6. Парсим дату и время из строк

Как было отмечено выше — если мы используем метод getDisplayValues(), то значения из таблицы мы получаем в виде строк и ровно так, как они отображаются в ячейках, никаких внутренних представлений данных мы не получаем.

Как будет показано далее, чтобы создать событие в Google Calendar, нужно предварительно создать два объекта класса Date: дату-время начала события, дату-время окончания события.

В рассматриваемой таблице (см. рисунок 2), мы получаем дату занятия в формате dd.mm.yyyy, и временной промежуток, когда занятие длится, в формате hh:mm-hh.mm.

Из этих двух строк, нам нужно получить два объекта Date. Такая задача не касается Google Apps Script, с этим по идее должен справиться хоть сколько-нибудь опытный программист. Поэтому я не буду подробно останавливаться на алгоритме, а лишь приведу код двух JS-функций, которые парсят дату время и возвращают дату-время начала и дату-время окончания занятия.

Чтобы получить дату-время начала и окончания занятия, нужно вызывать функцию extractPeriod и передать две строки — дата и временной период, в который длится занятие.

2.7. Создание события в Google Calendar

Сразу небольшой листинг, пояснения будут дальше

Строки 1-6: получаем дату-время начала и окончания занятия, а также склеиваем в одну строку всю дополнительную информацию, которая будет записана в описание мероприятия в календаре.

Далее, к интерфейсу сервиса Google Calendar мы обращаемся через методы глобального объекта CalendarApp.

Посредством метода getCalendarsByName мы получаем массив календарей с указанным именем.

Получить объект-календарь можно также посредством метода getDefaultCalendar, тогда событие будет создано в основном календаре. Однако, я советую зайти в веб-версию Google Calendar и создать новый календарь, в который вы будете экспортировать мероприятия. Во-первых, вы получаете возможность скрывать события этого календаря, выделять события отдельным цветом.

Но, что более важно, в случае, если вы при написании скрипта где-то ошиблись, и экспортировали события как-то неправильно, то вместо того, чтобы удалять все многочисленные неправильные события вручную, вы можете просто удалить календарь, после чего создать новый, пустой, и попробовать запустить новый, исправленный скрипт.

В данном случае я создал календарь «Учеба», и собираюсь создавать события в нем. Поэтому в коде вызывается метод getCalendarsByName и в качестве параметра передается срока «Учеба».

Метод возвращает массив календарей с указанным названием, поэтому нужно взять из этого массива один элемент, в нашем случае нулевой:

У объекта-календаря необходимо вызывать метод createEvent. В качестве параметров нужно передать следующее:

Последняя строка в скрипте — задержка на 50 миллисекунд. Во время разработки скрипта и отладки я обнаружил, что иногда при выполнении кода выбрасывается исключение, суть которого в том, что Google Calendar не нравятся частые обращения к сервису, и в описании исключения рекомендуется вставить задержку между вызовами методов API календаря.

3. Итоговый скрипт

Ниже приведен полный скрипт, который получился

Обратите внимание! Данный скрипт заточен под структуру таблицы, которая приведена на рисунке 2. Если вы хотите использовать этот скрипт для себя как есть, убедитесь, что ваша таблица с расписанием имеет такую же структуру, или же измените скрипт под свою таблицу.

4. Немножко скриншотов

Что можно улучшить

Открыт к конструктивной критике, буду рад, если укажете на недостатки, посоветуете что можно улучшить, а что изменить в статье.

Источник

Скрипты для новичков. Часть 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 Scripts — часть 1

Доброго времени суток, дорогие читатели!

Как некоторые могут помнить, у нас была одна статья на тему программирования в Google таблицах. Она была больше как эксперимент, который показал, что в общем-то это направление довольно популярно (да-да, про эксель я не забыл, он тоже будет. рано или поздно, но будет).

Тем, кто уже знаком с такими понятиями, как: функция, переменная, оператор, массив и тому подобное; будет (по всей видимости) скучновато, так что в таком случае рекомендую просмотреть статью по диагонали, вдруг что интересное для себя найдете.

Итак, вы уже знаете где находится редактор скриптов (если кто забыл, смотрите предыдущую статью), открываете его и. идете заваривать чай (кофе, какао, глинтвейн), а я пока расскажу немножко теории ( которую заодно надо бы освежить в памяти ).

С чего начинается любой скрипт? Очевидно, что с какого-то кода, но с какого? Вполне может так быть, что у вас уже появляются такие мысли при виде этого, а точнее того, белого экрана. Но это нормально. Когда мало опыта и много непонятного, хочется все бросить и лечь спать. Но не отчаивайтесь! И отойдите от кровати! Если я не буду писать это в некотором ироничном виде, то от моей заунывной статьи вы уже на третьей строчке усне..

Так вот, с чего нам начать? Я сейчас не имею в виду алгоритм действий, который вы держите в голове, или блок-схемы на листочке, нет. Нам следует начать с. объявления функции.

Объявление функции имеет определенный формат вида:

Где вместо FUNCTIONNAME вы можете ввести любое название, кроме уже зарезервированных системой гуглом, а в скобочках вы можете указать параметры, которые бы хотели передать в эту функцию.

Часто можно обойтись одной функцией, особенно, если у вас небольшая задачка. Однако при достаточно большом куске кода лучше всего делить функцию на несколько меньших функций, где одна вызывается из другой. Но об этом в следующей серии.

Итак, вы определились с вашей первой функцией, теперь что? А теперь нужно объявить переменные. Для вашей позиции в торговом автомате переменными могут быть: номер ряда, номер колонки, картинка позиции, её цена.

Переменными в общем случае может быть любой набор символов любого типа. Объявляются они очень просто, достаточно сказать котелок вари

И ой, она уже объявлена. А для чего это нужно?

Когда компилятор (такая штука, которая превращает удобочитаемый код для нас в машинный набор нулей и единиц для компьютера) так вот, когда компилятор начинает последовательно читать ваш код (а делает это он либо при сохранении вами кода, либо при запуске) то встречая неизвестные ему названия он первым и последним делом ищет где вы объявили ему (рассказали что это такое и что с этим делать) эти названия.

Однако, если вы напишете:

Если же вы объявите переменную, но не напишете чему она равна, то компилятор будет считать, что значение не определено ( undefined ).

Давайте подробнее остановимся на операторах. Есть несколько категорий:

В чем же разница между первым и вторым оператором? (ответ в следующей строке, выделите мышкой)

falseВ первом случае мы сравниваем с типом «число»

truefalse (по значению)+false (по типу) = false

Итак, в общем-то с основами разобрались. Теперь немного практики. Задача:

У нас есть некоторый набор значений в ячейках (cкажем, три ряда по четыре колонки случайных чисел), нам надо отдельно вывести сумму каждого ряда и его среднее значение (учитывая что мы не знаем о встроенных функциях, а пишем сами).

Таблица для нашего примера:

Итак, с чего начать? Нет, не с написания кода, сначала надо прикинуть план действий (хотя бы очень грубо).

Путем нехитрого гугления мы достаточно быстро выясним как вытащить значение ячейки из таблицы. Но какой смысл копировать код, ничего в нем не понимая? Правильно, никакого. С точки зрения.. ммм.. гугла таблица представляется собой объект (класс), состоящий из множества подклассов (типа матрешки). Иерархия там примерно такая:

Таким образом, мы можем получить значение как одной ячейки, так и диапазона ячеек. Воспользуемся вторым вариантом. Для этого пишем соотв. код:

Создаем нашу функцию:

Почти всегда первая строчка функции начинается со ссылки на таблицу. И здесь мы ссылаемся именно на текущую таблицу (ведь мы можем ссылаться и на другой лист, на другой файл и тп.).

Теперь достанем наши значения из ячеек:

Да, не очень красиво, но работает. Кстати проверить результат можно несколькими способами:

У кого не получилось, код целиком ниже:

Итак, первый пункт выполнен. Теперь приступаем ко второму. Учитывая, что мы (теперь) знаем сумму ряда и знали с самого начала кол-во чисел в ряду, добавляем еще три переменные и проверяем (значком я обозначаю место, где можно поставить Breakpoint для быстрого просмотра результатов):

При этом, мы должны указать данные точно в таком же формате, как мы их получили. Присваивание значений массивам в общем-то достаточно простая задача. Допустим у нас есть значения 1, 2, 3, 4, 5 и нам нужен массив. Объявляем:

Выглядит криповато, но массив можно записать еще таким образом:

А это уже другое дело! Хотя тем, кто на этих массивах уже не первую собаку съел, первый способ может быть удобней, т.к. быстрее.

Теперь же осталось дело за малым. Записать сумму и среднее для каждого ряда:

Запускаем и смотрим что у нас происходит на листе:

Послесловие

Итак, дабы не перегружать вас (дорогих читателей) информацией, первая часть на этом радостном событии завершена. Небольшой итог:

Теперь вы имеете представление что такое функция, переменная, зачем они нужны, какие операторы бывают и что делают, а также усвоили (я надеюсь) базовые навыки по работе с таблицами.

В следующей серии поговорим о том, как можно улучшить код, дабы не плодить кучу переменных и иметь возможность работать с таблицей значений переменной длинны (т.е. когда мы заранее не знаем сколько рядов и строк нам надо). А также немножко разобьем все это дело на несколько функций. Stay Tunned!

Источник