исходный код гугл таблицы
Как работать с API Google Таблиц (Google Sheets API v4) на языке R с помощью нового пакета googlesheets4
Электронные таблицы по-прежнему остаются довольно популярным инструментом для работы с данными, а среди различных процессоров электронных таблиц наиболее популярными являются Google Таблицы. Во-первых, это бесплатный инструмент, во-вторых, функционал Google Таблиц достаточно широк, и они предоставляют вам возможность в онлайн режиме получить доступ к данным.
Для тех кому лень читать статью, вот ссылка на 10 минутный, русскоязычный видео урок на YouTube. Остальным добро пожаловать под кат.
Содержание
Если вы интересуетесь анализом данных, и в частности языком R, возможно вам будут интересны мои telegram и youtube каналы. Большая часть контента которых посвящена языку R.
Зачем переходить на работу с пакетом googlesheet4
На самом деле по функционалу они похожи, возможно даже googlesheets пока имеет больше возможностей, но он работает только с Google Sheets API V3. Google несколько месяцев назад сообщил, что поддержка 3 версии данного API будет прекращена 3 марта 2020 года.
Миграция с googlesheets на googlesheets4
Что бы максимально упростить миграцию с устаревшего пакета googlesheets на новый googlesheets4 я решил добавить таблицу соответвия новых функций старым.
Установка пакета googlesheets4
Установить его можно как из CRAN, так и из GitHub.
Установка из GitHub:
Авторизация
В пакете googlesheets4 есть по меньшей мере 3 способа авторизации:
Авторизация со стандартными значениями аргументов
Наиболее простой способ авторизации предоставляет функция gs4_auth() со значением аргументов принятых в ней по умолчанию.
Минус этого подхода заключается в том, что вы будете использовать приложение вшитое в пакет по умолчанию, как и 90% других его пользователей. Каждое приложение имеет квоты на количество отправляемых запросов, поэтому с ростом количества пользователей данного пакета возрастает и шанс выйти за выделенные лимиты.
Поэтому я рекомендую самостоятельно создать приложение в Gogle Cloud, и пройти авторизацию с его помощью.
Авторизация через собственное приложение
Как создать своё приложение?
Для создания приложения следуйте приведённым ниже инструкциям:
Скачиваем JSON
Копируем ID и секрет приложения
Если вы впервые создаёте приложение в Google Console, то также вам предварительно понадобиться создать «Окно запроса доступа OAuth», просто заполните в нём название и ваш email, насколько я помню больше там никаких обязательных полей нет.
Последний шаг, включаем Google Sheets API, для этого достаточно перейти по этой ссылке и нажать кнопку включить API.
Теперь возвращаемся в RStudio, подключаем пакет и проходим авторизацию через своё приложение.
Проходим авторизацию через JSON файл:
Проходим авторизацию указав в коде ID и Секрет приложения:
Авторизация через сервисный аккаунт
Для начала надо создать сервисный аккаунт, вот алгоритм действий который вам необходимо выполнить:
Теперь у вас появился сервисный аккаунт, откройте его и скопируйте почту.
При авторизации под сервисным аккаунтам вы можете работать только с теми Google Таблицами к которым вы предоставили доступ для созданного сервисного аккаунта по его почте. Доступ предоставляется также, как и обычному пользователю.
Проходим авторизацию через сервисный аккаунт:
При авторизации через сервисный аккаунт вам не понадобится подтверждать доступ к данным через браузер, но ещё раз напомню, что вам надо отдельно расшаривать доступ сервисному аккаунту к каждой Google Таблице, с которой вам необходимо работать по API.
Разница между авторизацией через приложение и через сервисный аккаунт
Основными отличиями между авторизацией через приложение и через серверный аккаунт являются:
По этим двум причинам наиболее предпочтительным способом автризации является авторизация через сервисный аккаунт. Но важно понимать, что если кто-то посторонний получит доступ к JSON файлу с ключём от сервисного аккаунта, он автоматически завладеет всеми правами и доступами которые вы предоставили этому сервисному аккаунту.
Основные функции пакета googlesheets4
Все функции пакета googlesheets4 разделены на 3 группы, каждая группа функций имеет свой префикс, который говорит об области действия этой функции:
Давайте рассмотрим основные функции пакета googlesheets4.
Пакет содержит также множество вспомогательных функций, но думаю перечисленных выше вам будет более чем достаточно для выполнения любых действий с Google Таблицами.
Пример работы с API Google Таблиц на языке R
Теперь я приведу примеры кода для выполнения основных операций с Google Таблицами.
Загрузка данных из существующих Google Таблиц
Считывание данных из Google Таблицы является наиболее часто используемой операцией.
Алгоритм действий достаточно прост, вам необходимо инициализировать подключение к нужной таблице, и считать данные с существующего в ней листа.
Пример кода для чтения данных из Google Таблицы
Процесс подключения и чтения данных можно записать более компактно используя пайплайны.
Создание Google Таблиц с помощью API
Для создания новой таблицы используйте функцию gs4_create() и следующие её аргументы.
Пример создания Google Таблицы
Приведённый выше код создаст новую Google Таблицу «my_new_dox», в которой будут 2 листа: mtcars, iris.
Создание нового листа в Google Таблице
Теперь мы можем проводить с созданной таблицей различные манипуляции, например создать в ней новый лист.
Пример кода для создания нового листа с данными
Функция sheet_write() имеет 3 основных аргумента:
Дописывание строк в существующий лист
Ещё одна достаточно важная операция — добавление данных на уже существующий лист.
При создании таблицы my_new_dox мы записали на лист iris только первые 6 строк с данными, давайте допишем оставшиеся.
Пример кода для добавления строк на существующий лист
Перемещение Google Таблиц между папаками Google Диска
Установка googledrive
Пример кода для перемещения Google Таблицы из одной папки Google Диска в другую
Полезные ссылки
В этом разделе приведу несколько полезных ссылок по теме статьи:
Заключение
Описанных в статье возможностей пакета googlesheets4 достаточно для решения подавляющего большинства задач, в которых необходимо использовать Google Sheets API.
На данный момент googlesheets4 находится в стадии активной разработки. Автор пакета планирует реализовать его функционал в полном объёме к марту 2020 года, в связи с чем в статье возможны корректировки и дополнения по мере изменения или расширения возможностей пакета.
Если вы дочитали до этого параграфа, то наверняка интересуетесь, и скорее всего уже используете язык R в работе. Если это так, то думаю вам будет интересен мой телеграм и youtube каналы, большая часть контента которых посвящена языку R.
Генерируем красивую Google-таблицу из своей программы (используя Google Sheets API v4)
Постановка задачи
Пусть нам нужно создать программой на языке Python вот такую таблицу:
Особенности этой таблицы:
Решение
Сразу отметаем неподходящие библиотеки. Например, gspread. Это обёртка над Google Sheets API v3, в котором нет методов для настройки оформления таблицы. Даже ширину столбца задать не получится.
Шаг 1. Создать сервисный аккаунт
Шаг 2. Установить необходимые библиотеки
А именно, google-api-python-client. Установить можно при помощи pip, например:
Эта библиотека притянет необходимые зависимости (такие, как oauth2client и прочие).
Шаг 3. Кодить
3.1. Service-объект
Создаём Service-объект, для работы с Google-таблицами:
3.2. Термины и id’шники
Теперь на секунду приостановимся и обсудим терминологию.
3.3. Новый spreadsheet
В ответ получаем снова объект Spreadsheet, только заполненных параметров больше:
Можно было задать многие из них в запросе, но для решения текущей задачи нас устраивают параметры по умолчанию.
Параметру locale было задано значение ru_RU не случайно, но об этом позже.
Что же делать? Ответ очевиден: выдать доступ к документу тоже с помощью API.
Можно создать документ вручную на своём Google-диске и дать доступ сервисному аккаунту (то есть вручную выдать разрешения тому e-mail наподобие account@test-proj-for-habr-article.iam.gserviceaccount.com). Затем работать с этим документом через API.
Меня этот вариант не устроил, потому что мне требовалось научить программу создавать много разных документов.
3.4. Доступ к новому документу
У нашего объекта service нет метода для настройки доступа к документу. Его просто нет в Google Sheets API. Зато он есть в Google Drive API v3. Пишем код.
Такой код даёт доступ всем на чтение по ссылке. Допустим, мы желаем вместо этого дать доступ на редактирование пользователю user@example.com. Для этого вместо
3.5. Ещё немного теории
Есть функция spreadsheets.batchUpdate. Она применяет сразу пачку изменений к документу. А точнее, сначала она проверяет всю пачку на корректность. Если всё OK, то атомарно применяет всё и возвращает соответствующую пачку результатов. Список изменений, которые можно применять этой функцией, находится здесь.
3.6. Ширина столбцов
Чтобы задать ширину столбцов нужно сделать UpdateDimensionPropertiesRequest.
Получилось весьма громоздко и много копипасты. На этом этапе я решил написать небольшой класс-обёртку над Sheets API, который даст мне в удобном виде необходимые методы.
3.7. Логика класса-обёртки
Пусть класс-обёртка (назовём его Spreadsheet) хранит список requests и в своём методе runPrepared передаст его функции spreadsheets.batchUpdate, а затем очистит. Добавлять элементы в этот список будут методы вида prepare_соответствующийЗапрос.
Теперь код для задания ширины столбцов выглядит так:
И вот код методов prepare_setColumnWidth и prepare_setColumnsWidth:
Код метода runPrepared я приведу немного далее, потому что он пополнится ещё кое-чем.
3.8. Заполнение ячеек данными
Для заполнения ячеек информацией в Google Sheets API v4 предусмотрена функция spreadsheets.values.batchUpdate, работающая по тому же принципу, что и spreadsheets.batchUpdate. Она принимает список прямоугольников и значений, которые нужно записать в каждый из них. Кроме этого, принимает параметр ValueInputOption:
Вот так можно заполнить данными пару прямоугольников на листе без использования нашего класса-обёртки:
Теперь сделаем, чтобы наш класс-обёртка предоставил удобные методы для достижения того же результата.
Пусть функция spreadsheets.values.batchUpdate вызывается в методе runPrepared, а метод prepare_setValues добавляет прямоугольник и данные в список valueRanges, который при вызове runPrepared будет передан в spreadsheets.values.batchUpdate.
Код методов prepare_setValues и runPrepared:
Заполним данными ту же пару прямоугольников, что и в примере выше, но уже с использованием нашего класса-обёртки:
3.9. Объединение ячеек, настройка жирности, формата отображения, цвета фона и прочего
Кому не терпится, можете сразу читать полный код класса Spreadsheet и пример его использования, который является решением задачи, поставленной в начале статьи.
Для более терпеливого читателя:
Некоторые тонкости
Q3: В запросах, передаваемых функции spreadsheets.batchUpdate, параметр range имеет формат GridRange:
А в прямоугольниках с данными для функции spreadsheets.values.batchUpdate параметр range — это строка, вида Название_листа!A5:E7 (A1 notation). Странно.
A3: Да. Возможно, в комментариях к статье кто-нибудь объяснит, почему так.
В классе-обёртке я сделал для удобства метод toGridRange.
Q4: Пикачу, который в таблице в начале статьи, посажен туда программно?
A4: Нет, Пикачу я разместил в таблице вручную. Не уверен, что Google Sheets API v4 позволяет сделать это программно, сходу нужную функцию не нашёл.
Q5: Есть ли какие-то ограничения использования Google Sheets API v4?
A5: Да, они называются квотами. За ними можно следить в Google Developers Console. Там же можно отправить запрос на увеличение квоты, если будет не хватать.
Заключение
Если Вы дочитали досюда, то, вероятно, освоили, как программно создать spreadsheet, и теперь горите желанием использовать Google-таблицы во всех своих проектах 🙂
Начинаем работу с Google Sheets на Python. От регистрации до чтения данных
Довольно долго я обходился выгрузкой данных в Excel, но мода меняется, пользователи хотят в облака.
Начав переводить ряд проектов на Python, решил, что самое время сменить (или дополнить) Excel чем-то более современным.
Когда я впервые столкнулся с необходимостью работы c таблицами Google из Python, то пребывал в иллюзии, что все это можно сделать в пару кликов. Реальность оказалась менее радужной, но другого глобуса у нас нет.
Мне очень помогли статьи:
Возможно, я просто шел длинным путем – буду рад, если вы меня поправите.
Все действия выполнялись на компьютере с Windows + Python 3.6.6, также использовался Jupyter Notebook.
Основные трудности у меня возникали на этапе предварительных настроек. Найти работоспособный код не представляет особого труда.
Код, использованный в статье, доступен в репозитории
Регистрация в сервисах Google и установка библиотек
Для работы с таблицами нужно зарегистрироваться на Google, настроить проект и установить необходимые библиотеки.
Сначала нужно зарегистрироваться на gmail.com (это вы можете сделать самостоятельно). Потом нужно создать проект (так Google предоставляет доступ к своим сервисам).
Это долгий и нудный процесс, который позволяет понять, почему интерфейсы от Google называют не самыми удобными и интуитивно понятными (некоторые считают, что социальная сеть Google+ не взлетела именно по этой причине).
Для этого зайдите на страницу console.developers.google.com/cloud-resource-manager и нажать «Создать проект»
Введите имя проекта и нажмите «Создать»
В обновленном списке проектов зайдите в меню «Права доступа» 
В открывшемся окне нажмите «Добавить», внесите свой email с домена gmail.com и выберите группу «Проект» — «Владелец»
Может показаться странным, что вы создали проект но вынуждены сами себе выдавать права. И это на самом деле странно, но именно такой путь пришлось пройти на момент написания этого курса, чтобы все начало работать как надо.
Выберите на своем проекте меню «Настройки»
В открывшемся окне выберите «Сервисные аккаунты», а затем «Создать сервисный аккаунт»
Введите название аккаунта и нажмите «Создать»
Выберите роль «Владелец» и нажмите «Продолжить»
В появившемся окне нажмите «Создать ключ»
Выберите тип ключа «json» и нажмите «Создать»
Будет создан и сразу же скачан файл с ключами. Сохраните его, именно благодаря ему мы сможем получать доступ к сервисам Google.
Нажмите на кнопку с тремя горизонтальными штрихами, слева от надписи «Google APIs», выберите пункт «API и сервисы», а в нем подпункт «Панель управления».
В открывшемся окне нажмите «Включить API и сервисы»
Введите в строку поиска «google drive» и кликните на сервисе «Google Drive API»
Сайт уведомит вас, что API включено и предупредит, что нужно создать учетные данные. Игнорируйте это предупреждение (ведь мы уже создали сервисный аккаунт).
Снова заходите в панель управления
В открывшемся окне нажмите «Включить API и сервисы»
Введите в строку поиска «sheet» и кликните на сервисе «Google Sheets API»
Убедитесь, что это API подключено. Оно должно включиться автоматически, при подключении Google Drive API. Если оно подключено, вы увидите кнопку «Управление API», если нет — кнопку «Включить». Включите его, при необходимости.
Выберите на своем проекте меню «Настройки»
В открывшемся окне выберите «Сервисные аккаунты», а затем скопируйте и сохраните email сервисного аккаунта. Он пригодится вам, чтобы выдавать доступ к таблицам.
Теперь переходим к установке библиотек. Выполните в консоли команду
Возможно, что при запуске второй команды вы получите сообщение, что библиотека oauth2client уже установлена.
Нажмите правую кнопку мышки и выберите «Сохранить как»
Сохраните файл под именем quickstart.py
и запустите его командой
Откроется новая страница в браузере (возможно, он скажет, что страница небезопасная, но смело идите вперед) и вам надо будет принять условия.
На этом наш путь завершен.
Заполнение и форматирование таблицы
Создадим первую таблицу
Если все прошло без ошибок — на экран будет выведена ссылка на таблицу.
В этой ссылки использован идентификатор файла, мы сохраняем его в переменной spreadsheetId и будем использовать в дальнейшем.
Переходите по ней. Google сообщит вам, что у вас нет доступа
Не запрашивайте разрешение! Вам придет уведомление, что невозможно доставить письмо с запросом на адрес, который сам Google назначил системному аккаунту. А изменить этот адрес нельзя. Возможно, это не работает только в бесплатном режиме.
Но мы можем выдать себе доступ через Google Drive. Вам нужно заменить адрес my_test_address@gmail.com на свой.
Теперь у вас есть доступ, не закрывайте таблицу, мы будем управлять ею и сразу же смотреть на изменения.
У каждого документа есть свой код — spreadsheetId — именно от отображается в адресной строке, когда мы открываем таблицу в браузере (в URL-е страницы с открытой таблицей он находится между «https://docs.google.com/spreadsheets/d/» и «/edit#gid=0»).
Мы сохранили его в переменной spreadsheetId и дальше будем с ним работать.
Сначала немного теории.
В каждом файле (spreadsheet) находятся листы-вкладки (sheet).
Каждый sheet имеет свой числовой код (sheetId). У первого созданного в документе листа этот Id равен 0. Остальные листы имеют сильно отличные от нуля Id (т.е. они не нумеруются подряд).
На экране появится нечто вроде:
0 Лист номер один
415832263 Еще один лист
Мы будем использовать лист с >
В самом деле, первый лист имеет Id равный нулю, а второй пронумерован иначе.
Еще один вопрос: как указывать диапазоны ячеек. Видимо, таблицы Google разрабатывали разные команды, под руководством разных менеджеров и при помощи разных архитекторов. Потому, что координаты ячеек задаются двумя разными способами.
Вариант 1: в формате текста «Лист номер один!B2:D5», т.е. имя листа, после него восклицательный знак, после — левая верхняя ячейка в формате «буква (колонка) + цифра (строка)» + правая нижняя ячейка в таком же формате.
Вариант 2: в json-формате, с указанием ID листа и координат левой верхней и правой нижней ячеек в числовом виде (номер строки и номер столбца)
Разные функции используют разные форматы.
Теперь мы знаем достаточно, чтобы заполнить ячейки данными, нарисовать рамку и выделить заголовки.
Заполняем несколько ячеек данными. Т.к. указан параметр USER_ENTERED, таблица воспринимает эти данные так, как восприняла бы ввод руками пользователя — преобразует числовые значения в числа, а значения, начинающиеся со знака «равно» в формулы.
Посмотрите в вашу таблицу, она заполнилась данными
Зададим ширину колонок. Функция batchUpdate может принимать несколько команд сразу, так что мы одним запросом установим ширину трех групп колонок. В первой и третьей группе одна колонка, а во второй — две.
Посмотрите на таблицу, ширины колонок изменились.
Нарисуем рамку вокруг таблицы
Объединим ячейки над таблицей и впишем в них заголовок
Установим формат у ячеек заголовка таблицы
Есть простой способ узнать, какую ширину или цвет нужно задать ячейке. Для этого достаточно вручную отформатировать одну из ячеек и прочитать ее свойства.
Значения и раскраска
[<'values': [<'userEnteredValue': <'stringValue': 'Ячейка C2'>, ‘effectiveValue’: <'stringValue': 'Ячейка C2'>, ‘formattedValue’: ‘Ячейка C2’, ‘userEnteredFormat’: <'backgroundColor': <'red': 1, 'green': 0.6>, ‘horizontalAlignment’: ‘CENTER’, ‘textFormat’: <'fontSize': 14, 'bold': True, 'italic': True>>, ‘effectiveFormat’: <'backgroundColor': <'red': 1, 'green': 0.6>, ‘padding’: <'top': 2, 'right': 3, 'bottom': 2, 'left': 3>, ‘horizontalAlignment’: ‘CENTER’, ‘verticalAlignment’: ‘BOTTOM’, ‘wrapStrategy’: ‘OVERFLOW_CELL’, ‘textFormat’: <'foregroundColor': <>, ‘fontFamily’: ‘Arial’, ‘fontSize’: 14, ‘bold’: True, ‘italic’: True, ‘strikethrough’: False, ‘underline’: False>, ‘hyperlinkDisplayType’: ‘PLAIN_TEXT’>>]>]
Этот код выведет свойства ячейки C2. Можно выбрать шрифт и цвет заливки вручную (в таблице), в потом увидеть, как они отражаются в json.
Чтение данных из таблицы
Чтобы особенности чтения данных проявились в полной мере, я вручную заполнил ячейки B4, C7 и D5 как показано на рисунке.
Код для чтения данных
Некоторые параметры функции: valueRenderOption — формат чтения числовых данных.