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

Google Apps Script: полезные функции и фишки для SEO (часть первая)

Всем привет. Я SEO-специалист Netpeak в отделе по работе с крупными проектами. Масштабные проекты — это всегда большие объемы данных, много анализа и исследований, которые отнимают много времени, поэтому без автоматизации тут не обойтись. Вообще в работе очень сильно помогает софт ребят из Netpeak Software , за это им отдельное большое спасибо.

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

Так как это первый пост, думаю, нужно написать о языке и ограничениях.

Скрипт работает в сервисах:

1. Google Docs.
2. Google Sheets.
3. Google Slides.
4. Google Forms.

Какой уровень владения языками программирования должен быть, чтобы понимать и самостоятельно писать скрипт? Ответ простой: любой, главное желание, терпение и смекалка =)

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

Сразу скажу: я не программист и отдельно какие-то курсы не проходил. Возможно, где-то в коде откровенные «велосипеды». Буду рад, если в комментариях вы мне укажете на них.

Как создать скрипт Google Apps Script для Google Sheets

По традиции, выводим надпись «Hello world»:

1. Заходим в таблицу.

2. Переходим во вкладку «Инструменты» — «Редактор скриптов».

3. Мы видим рабочую область, где и нужно писать скрипты.

4. Вставляем код:

5. Запускаем нашу функцию:

6. Сохраняем проект и проходим этап авторизации.

Шаг 1:

Шаг 2:

Шаг 3:

Шаг 4:

Шаг 5:

Разбор кода скрипта в Google Apps Script

В этой строке мы создаем объект класса SpreadsheetApp, чтобы в дальнейшем мы могли использовать различные методы этого класса.

В этой строке грубо говоря мы присваиваем переменной sheetOne так называемый адрес вкладки, которая называется Пост 1. Это упрощает работу с таблицами.

В этой строке мы выводим надпись «Hello world» в ячейку A1 на вкладке «Пост 1». Тут нужно разобраться подробно:

1. sheetOne — как мы описали выше, это «ссылка» на нашу вкладку «Пост 1».
2. .getRange(1,1) — это метод, который указывает, что мы будем работать с ячейкой, которая имеет адрес: колонка 1 (A) и столбец 1.
3. .setValue(«Hello world») — это метод, который записывает то, что в скобках. В нашем случае это «Hello world».
• Структура самой функции:

Бонус: как спарсить тег Title и метатег Description

Скрипт можно получить по ссылке. Обязательно делаем копию.

1. В колонке А Вставляем наши URL.



2. Запускаем скрипт.



3. Забираем результаты.



Какие сайты скрипт не сможет спарсить:

Вывод

Google Apps Script помогает бесплатно автоматизировать много ручной работы, например:

1. Автоматически выгружать различные показатели в Google Analytics и настроить оповещение через электронную почту, если данный показатель изменился на какой-то процент.
2. Выгружать данные по количеству кликов и показов из сервиса Google Search Console и также оповещать об изменениях.
3. Мониторить изменения на сайте (наличие новых страниц, изменения в метатегах, коды ответа сервера и так далее).

Конечно, в скриптовом языке Google Apps Script есть много других полезных проверок и расчетов. Их я продемонстрирую в следующих статьях.

Если хотите поделиться своими фишками или задать вопросы, добро пожаловать в комментарии.

С SEO познакомился в 2015 году.
Работал и обучался в компании Palmira Studio.
C августа 2015 года работаю SEO-специалистом в агентстве интернет-маркетинга Netpeak.
Сертификация: Google Analytics, Google AdWords по направлениям «поисковая и мобильная реклама».

Google Docs, Google Drive, Google Scripts: как писать скрипты, макросы и код — часть 0

Доброго времени суток, дорогие читатели, вредины, злодеи, доброжелатели и прочие личности. Сегодня мы про Google Scripts , точнее скрипты в таблицах как таковые.



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

Те, кто пользуется диском Google (Google Drive ), наверное уже использовали Таблицы (Spreadsheets ) и заметили, что по функционалу они немного уступают Экселю, но тем не менее это всё ещё мощный инструмент.

Так вот, в Экселе были макросы (этакие команды, упрощающие и автоматизирующие вычисления) , написанные на небезызвестном языке VBA (Visual Basic for Applications) . В Таблицах Google также есть макросы, которые именуются скриптами и пишутся уже на языке Javascript . С ними мы сегодня и познакомимся.

Я заранее Вас предупреждаю о возможной сложности дальнейшего примера, т.к. он не столько обучающий, сколько.. Мм.. Так сказать, конечный факт, которым Вы можете пользоваться и.. И развивать, если это Вам знакомо.

Соберитесь в комочек мозга.. И приступим 🙂

• Создание таблицы Google Drive / Scripts и наполнение её контентом
• шКоддинг
• Скрипты и макросы таблиц Google, дополнение
• Послесловие

Создание таблицы Google Drive / Scripts и наполнение её контентом

Рассмотрим такую простенькую задачку:
У нас есть две колонки, в первой мы пишем названия фруктов, а во второй цвет, который соответствует этому фрукту. И мы хотим, чтобы при вводе цвета в колонке цветов автоматически менялся бы цвет названия фрукта.

Если Вы забыли как вообще пользоваться документами Google , то милости просим почитать соответствующую и уже упомянутую выше статью. Если Вам это не нужно совсем, то читать наверное и дальше даже нет смысла. Хотя, конечно, кому что 🙂

Так вот, создаем новую таблицу Google , именуем её, например, » Фрукты «. Ну, как, например.. Учитывая, что пример про фрукты, то.. Ну Вы поняли 🙂

Теперь добавляем на первый лист наши фрукты и цвета:



Примечание! Для того, чтобы считались фрукты, введите в ячейку А1 формулу:

=»фрукт («&COUNTA(A2:A)&»)»

Теперь создадим макрос. Для этого идем в меню » Инструменты » и выбираем » Управление скриптами «. Появится всплывающее меню, где мы жмем на кнопку » Создать «.



В появившемся окошке выбираем » Пустой проект «.



Откроется редактор, который на первый взгляд (да и на второй) может вызвать ступор.



Собственно, что дальше? А дальше мы начинаем писать наш собственный макрос ручками (да, всё самостоятельно). Как будет выглядеть наш макрос? Нужно составить схемку сего процесса (иначе этот процесс займет у Вас очень много времени).

1. Достать значения цветов из второй колонки;
2. В соответствии с этими значениями задавать цвета для первой колонки.

Итак.. Вроде бы всё просто.. Если знать, как это делать, конечно 🙂

шКоддинг

Перейдем к самому коду:

Теперь я постараюсь Вам его объяснить. Функция onOpen добавляет меню » Скрипты » к таблице при открытии оной. И выглядит это дело так:



Эта строчка добавляет в переменную sheet идентификатор открытого нами документа, чтобы потом по нему обращаться к документу.

Эта переменная-массив содержит список названий менюшек и функций, которые выполняются при клике на эти менюшки.

Этот метод добавляет к нашему документу меню » Скрипты «.

Функция MakeMeHappy, собственно, и будет нашей главной функцией, которая красит фрукты.
Сначала я объявляю переменные:

Соответственно, в переменной sheet находится идентификатор нашего документа. В переменной range находится выделенная нами область (например, ячейки B2:B6 ), в переменной data находятся значения этих ячеек в виде массива.

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

В этом цикле мы проходимся по каждой ячейке из диапазона B2:B

Эти три свойства убирают форматирование ячеек A[i] (например, A1 , A2 , A3 и т.п., т.к. мы внутри цикла), а также центрируют значения в ячейке по вертикали и горизонтали.

Тут следует иметь в виду, что т.к. наш диапазон соответствует второй колонке ( В2:В ), а нам надо убрать форматирование и отцентровать первую колонку, то для этого используется метод offset (номер ряда диапазона, номер колонки, кол-во рядов, кол-во колонок). Например, метод range.offset(0 ,1,4,3) для ячейки B2 (т.е. range соответствует B2:B2 ) будет означать, что мы будем воздействовать не на ячейку B2:B2 , а на диапазон [ B + 1 ][ 2 + 0 ]:[ В + 3 ][ 2 + ( 4 -1) ] = C2 : E5 . Более подробно сморите в документации.

Функция switch является так называемым переключателем. Она смотрит значение переменной и в соответствии с тем, что в ней хранится, выполняет определенное условие » case «. Можно её переписать в стандартном виде if else . Но получится очень неудобно. Например:

..Будет эквивалентно функции:

Т.к. можно ввести цвет как с большой, так и с маленькой буквы, то нам надо по два условия, что соответствует записи case «зеленый»: case «Зеленый»: действие; break; (у меня это записано блочной структурой) . Нужно иметь в виду, что после каждого действия надо писать функцию break ; т.к. иначе мы будем выполнять все условия по порядку, а не то, которое нам надо. Условие default используется в том случае, если для нашей переменной нет подходящего условия.

Методы setFontColor и setBackgroundColor задают цвета текста и фона в виде #rrggbb (r-red, g-green, b-blue, диапазоны цветов) соответственно.

Теперь проверим функцию. Выделяем диапазон B2:B9 , заходим в меню » Скрипты » и выбираем опцию » Покрасить «. Смотрим, как наши фрукты обрели жизнь цвета 🙂

В общем-то на этом всё. Но не совсем.

Скрипты и макросы таблиц Google, дополнение

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

Для этого зайдите в редакторе скриптов в меню » Ресурсы » и выберите там » Триггеры текущего проекта «. Откроется менюшка, в которой уже будет наша функция onLoad . Добавляем новую функцию (1 ) и задаем название функции (2 ) и тип активации оной (3 ). Также можно нажать на » Уведомления » и добавить/убрать свой почтовый адрес из списка уведомлений.

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



Конечный результат действа:



Послесловие

Поздравляю с первым скриптом. Это лишь малая доля того, что можно сделать при помощи такого мощного устройства, как Google Scripts . Понятно, что наверное большинство читателей такая штука пугает, тем более, что другие статьи не так суровы и «ругаются» на Вас кодом, да и прочими ужасами жизни.. Но что уж делать.

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

Продолжения раз-два-готовим и три. Ну и комментарии конечно содержат много вкусного.

P.S. За существование оной статьи отдельное спасибо другу проекта и члену нашей команды под ником “barn4k“.

Белов Андрей (Sonikelf) alt=»Sonikelf» /> alt=»Sonikelf» />Заметки Сис.Админа [Sonikelf’s Project’s] Космодамианская наб., 32-34 Россия, Москва (916) 174-8226

Скрипты 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. В этом файле сценария вы можете писать код. В одном файле сценария может быть несколько сценариев, а также несколько файлов сценариев.

Если вы щелкните маленькую направленную вниз стрелку справа от имени файла сценария, отобразятся варианты переименования, удаления и создания копии файла сценария.



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

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



Панель инструментов редактора скриптов

Панель инструментов редактора скриптов имеет следующие параметры:

• Кнопка «Вернуть / Отменить» : для возврата / отмены изменений, которые вы сделали в скрипте.
• Кнопка отступа: это кнопка-переключатель, и вы можете включить или отключить отступ, нажав на нее. Когда отступы включены, они автоматически делают отступы для некоторых частей вашего скрипта, чтобы сделать его более читабельным. Это может иметь место, когда вы используете циклы или операторы IF. Он будет автоматически делать отступы для наборов кодов внутри цикла, чтобы повысить удобочитаемость (если отступы включены). Этот параметр включен по умолчанию, и я рекомендую вам оставить его в таком же виде.
• Кнопка «Сохранить» : вы можете использовать эту кнопку, чтобы сохранить любые изменения в вашем скрипте. Вы также можете использовать сочетание клавиш Control + S. Обратите внимание, что, в отличие от Google Таблиц, вам необходимо сохранить свой проект, чтобы убедиться, что изменения не потеряны.
• Кнопка текущего триггера проекта : при нажатии на эту кнопку откроется панель управления триггерами, на которой перечислены все триггеры, которые у вас есть. Триггер — это все, что запускает выполнение кода. Например, если вы хотите, чтобы код запускался и вводил текущую дату и время в ячейку A1 всякий раз, когда кто-то открывает Google Таблицы, вы будете использовать для этого триггер.
• Кнопка «Выполнить» : используйте ее для запуска сценария. Если у вас несколько функций, выберите любую строку в той, которую вы хотите запустить, а затем нажмите кнопку «Выполнить».
• Кнопка отладки : отладка помогает находить ошибки в коде, а также дает некоторую полезную информацию. Когда вы нажимаете кнопку «Отладка», на панели инструментов отображаются некоторые дополнительные параметры, связанные с отладкой.
• Выберите функцию : это раскрывающийся список, в котором перечислены все ваши функции в файле сценария. Это полезно, когда у вас много функций в скрипте и вы хотите запустить конкретную. Вы можете просто выбрать имя здесь, а затем нажать кнопку запуска (или отладить его, если хотите).

Параметры меню редактора скриптов

Помимо панели инструментов, есть много других опций, доступных в Google Apps Script в Google Таблицах.

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

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

• ФАЙЛ : из меню «Файл» вы можете добавить новый проект или файл сценария. Проект будет полностью новым проектом в отдельном окне, где вы можете создать дополнительные файлы сценариев. Когда вы добавляете новый файл сценария, он просто добавляет его в тот же проект (вы увидите его на левой панели под текущими файлами сценария). Вы также можете переименовывать и удалять проекты отсюда. Еще одна полезная опция, которую вы можете найти в меню «Файл», — это возможность управлять версиями проектов. Когда вы сохраняете проект, сохраняется его версия, и вы можете вернуться и вернуться к этой версии, если хотите.
• РЕДАКТИРОВАТЬ : Edit имеет несколько полезных опций, которые могут помочь при написании или редактировании кода. Например, есть возможность найти и заменить текст в вашем коде. Есть также такие опции, как Завершение слов, Помощник по содержанию и Переключить комментарии.
• ПРОСМОТР : у этого есть параметры, которые могут быть полезны, когда вы хотите получить дополнительную информацию о скрипте, когда он выполняется, или хотите добавить журналы, чтобы помочь в отладке в будущем. Например, вы можете получить стенограмму выполнения, в которой подробно описаны все действия, выполняемые вашим скриптом.
• ВЫПОЛНИТЬ : Есть варианты для запуска различных функций или их отладки. Поскольку эти параметры также доступны на панели инструментов, их реже можно использовать из меню.
• ПУБЛИКАЦИЯ : здесь есть более продвинутые функции, такие как публикация ваших скриптов в виде веб-приложений.
• РЕСУРСЫ: это дает вам доступ к расширенным параметрам, таким как библиотеки и расширенные службы Google. Вы можете использовать эти параметры для подключения к другим ресурсам Google, таким как Google Forms или Docs.
• СПРАВКА : Здесь есть учебные пособия и ресурсы, которые могут помочь вам, когда вы начинаете / работаете со скриптами Google Apps. Одним из наиболее полезных вариантов здесь является ссылка на страницу документации, где вы можете найти множество руководств и ссылок для изучения скриптов Google Apps.

В этой статье я рассмотрел основы скрипта Google Apps и общую анатомию интерфейса.

Как с помощью js и google sheets стать соседом Билла Гейтса по гольф клубу



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

Вот и у меня появилось свободное время, которое я посвятил анализу своих сделок в Тинькофф Инвестициях. Есть 2 типа людей: одни прекрасно строят многомерные массивы у себя в голове, пробегаясь по ним for-циклом в IPython Notebook, другим же нравится «щупать» цифры, раскладывая их по полочкам в Excel. Себя я отношу ко второй категории, поэтому все свои сделки аккуратно заносил в Google Sheets.

Под катом я расскажу, как автоматизировал свою рутину при помощи Google Apps Script и API от Тинькофф Инвестиций.

Перед тем как мы перейдём к сути, маленький словарик терминов, которыми я пользуюсь в статье:

• ТИ — Тинькофф Инвестиции
• Инструмент — любая ценная бумага, такая как акция, облигация или ETF.
• Ticker — короткий ID инструмента на бирже. Как правило, участники биржи знают тикеры тех инструментов, которые покупают или продают. По крайней мере я исхожу из этого в своём коде
• Figi — Financial Instrument Global Identifier (Финансовый Глобальный Идентификатор инструмента). Большая часть API-запросов ТИ принимает на вход именно figi.
• Стакан — таблица заявок на покупку и продажу конкретного инструмента.

Задача

У каждого инвестора или трейдера есть свой особый способ вести аналитику сделок. Кому-то достаточно тех инструментов, которые предоставляет брокер — дэшборд в личном кабинете или еженедельные отчёты. Мой способ такой: я веду отдельную таблицу по каждому инструменту, которым торгую. В этих таблицах я рассчитываю прибыль/убыток, определяю стратегию будущих сделок и всячески учусь на своих ошибках.

Какое-то время я заносил сделки вручную, перепечатывая их из мобильного приложения ТИ. Мне захотелось оптимизировать этот процесс. В поисках решения я наткнулся на статью хабраюзера OvkHabr. Из неё я узнал, что брокер предоставляет API, который полностью покрывает мои нужды и принялся за разработку.

Google Apps Script

Всё, что нужно, чтобы расширить возможности документа Google Sheets, это перейти в Tools -> Script editor , задать название проекта и начать писать код на JavaScript.



OpenAPI

Методы взаимодействия с ТИ реализованы с помощью OpenAPI, а сама документация представлена через swagger-ui

Авторизация представляет собой http-заголовок с токеном, который нужно посылать при каждом запросе. Процедура получение токена описана в ещё одной документации.

Для начала нужно набросать простенький клиент для http походов в ТИ.
Какие методы нам понадобятся?

1. Получать описание инструмента, из которого будем брать figi
2. Получать стакан инструмента, из которого будем брать актуальную цену
3. Получать список сделок, отфильтровывая его по времени

Сделаем клиент классом, чтобы потом создать его единственный экземпляр и обращаться во всём верхнеуровневом коде:

Получение цены инструмента

Протестируем получившийся клиент на чём-нибудь простом, чтобы узнать что у нас всё работает. Например, получим цену акции yandex с тикером YNDX .

Custom Functions

Google Sheets предлагает большой выбор встроенных формул, таких как AVERAGE , SUM , или VLOOKUP . Но, когда этого недостаточно, мы всегда можем сделать свою. Всё, что для этого нужно, — обозначить функцию в .gs файле. То, что будет возвращать такая функция, будет вставляться в ячейку, которая вызвала функцию. Причём, если функция возвращает двумерный массив, то данные заполнят область справа и снизу, при условии, что там не будет занятых клеток.

Давайте сделаем функцию getPriceByTicker, которая будет возвращать текущую цену инструмента. Её мы будем использовать в качестве формулы в любой ячейке ( =getPriceByTicker(«YNDX») ).

Для этого нам сначала нужно получить figi инструмента, а потом получить его стакан, из которого мы и вытащим цену:

Здорово! Теперь в нашем распоряжении есть отличный тул, при помощи которого мы можем получать текущую цену акции, и использовать её в расчётах.

Автообновление формулы

Для того, чтобы данные в таблице всегда были актуальными, хочется сделать эту формулу автообновляемой. Прямого способа сделать это нет, но GAS комьюнити придумало вот такой хак:

• Мы резервируем ячейку, в которую при каждом обновлении листа будет складываться случайное число
• Эту ячейку мы будем указывать в качестве аргумента у тех функций, которым необходим периодический пересчёт, например getPriceByTicker

Cache Service

Как мы видим, получая цену мы делаем аж 2 API-вызова. И если в случае похода за стаканом это оправдано, так как цена постоянно меняется, то figi у инструмента является константой. Чтобы сделать нашу формулу чуть быстрее и надёжней, воспользуемся Apps Script Cache Service. Это простое key-value хранилище, которое отлично справится с нашей задачей:

Получение списка сделок

Теперь получим список проведённых сделок по инструменту. Сделаем функцию getTrades , которая будет ходить за операциями по API, формировать двумерный массив с данными и возвращать его.

На вход будем получать тикер, а также временной интервал, по которому нас интересуют сделки. Для интервала сделаем дефолтные значения, чтобы каждый раз не бодаться с форматом ISO 8601, который требует на вход API.
Figi будем получать при помощи _getFigiByTicker , который мы уже реализовали выше.

Взвешенное среднее

Исходя из документации, объект Operation возвращается нам в виде:

Некоторые операции купли-продажи являются составными. Это обусловлено законами, по которым работает биржа: продавая 100 акций YNDX по 2500₽, в моменте может быть всего 40 предложений по цене 2500₽, и 60 предложений по цене 2499₽. Поэтому, как и было описано в статье у OvkHabr, часть данных о сделках лежит в биржевых операциях — подмассиве trades .

Меня же интересует сводная информация по конкретной сделке, поэтому для определения цены, по которой она произошла, мы будем пользоваться взвешенным средним.

Для нашего примера с яндексом, взвешенное среднее считается так

А в коде это будет выглядеть так

Работа с таблицей

Как было описано выше, если custom функция возвращает двумерный массив, данные займут всё необходимое свободное пространство под ячейкой с формулой. Соответственно, нам необходимо сформировать такой массив.

Мы будем итерироваться по операциям и биржевым сделкам, чтобы этот массив заполнить. Нас не интересуют отменённые операции, а также операции списания комиссии, но интересует само значение комиссии. Помимо этого, мы «на лету» будем присваивать минус операциям покупки (символизируя списание с нашего брокерского счёта) и плюс продажам. Таким образом, нам будет проще понимать текущую стоимость позиции (просто просуммировав столбец)

Остаётся только вернуть массив values. Итоговый код функции выглядит так:

Проверяем работу в бою:

Заключение

В рамках статьи мы познакомились с API Тинькофф Инвестиций, возможностями, которые предлагает Google Apps Script, а также решили задачу автоматизации заполнения Google Sheets реальными сделками с брокерского счёта. Надеюсь, вам было интересно)

Весь код и короткий how-to выложен на github

Для тех читателей, кто хочет вступить на дорогу инвестирования, но не знает с чего начать — могу посоветовать бесплатный курс от Тинькофф Журнала https://journal.tinkoff.ru/pro/invest/ — он короткий, информативный и доходчивый.

А при открытии брокерского счёта в ТИ по моей ссылке вы получите акцию стоимостью до 20000 рублей в подарок.

Читать:
Как откатить обновления windows 10 если система не загружается