Google таблица api: Как работать с API Google Таблиц (Google Sheets API v4) на языке R с помощью нового пакета googlesheets4 / Хабр |

Posted:

Авг 3, 2021

Comments:

Google таблица api: Как работать с API Google Таблиц (Google Sheets API v4) на языке R с помощью нового пакета googlesheets4 / Хабр

Содержание

Как работать с API Google Таблиц (Google Sheets API v4) на языке R с помощью нового пакета googlesheets4 / Хабр

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

В этой статье мы разберёмся с тем, как на языке программирования R работать с Google Sheets API v4 с помощью пакета googlesheets4, а точнее:

Как пройти авторизацию для работы с Google Таблицами по API;

Рассмотрим основные функции пакета;

Разберём примеры кода для чтения данных, создания новых таблиц и выполнения других манипуляций с Google Таблицами по API.

Также эта статья поможет пользователям устаревшего пакета googlesheets мигрировать на новый googlesheets4.

Для тех кому лень читать статью, вот ссылка на 10 минутный, русскоязычный видео урок на YouTube. Остальным добро пожаловать под кат.

Если вы интересуетесь анализом данных, и в частности языком R, возможно вам будут интересны мои telegram и youtube каналы. Большая часть контента которых посвящена языку R.

Зачем переходить на работу с пакетом googlesheet4

Миграция с googlesheets на googlesheets4

Установка пакета googlesheets4

Авторизация

4.1. Авторизация со стандартными параметрами

4.2. Авторизация через собственное приложение

4.3. Авторизация через сервисный аккаунт

4.4. Разница между авторизацией через приложение и через сервисный аккаунт

Основные функции пакета googlesheets4

Пример работы с API Google Таблиц на языке R

6.1. Загрузка данных из существующих Google Таблиц

6.2. Создание Google Таблиц с помощью API

6. 3. Создание нового листа в Google Таблице

6.4. Дописываем строки в существующий лист

6.5. Перемещение Google Таблиц между папаками Google Диска

Полезные ссылки по теме статьи

Заключение

Если вы уже являетесь активным пользователем языка R, и на практике вам встречались задачи по работе с Google Sheets API наверняка вы уже знакомы с пакетом googlesheet. И вполне резонно, что первый вопрос который возник у вас в голове — «А чем же googlesheets4 лучше?».

На самом деле по функционалу они похожи, возможно даже googlesheets пока имеет больше возможностей, но он работает только с Google Sheets API V3. Google несколько месяцев назад сообщил, что поддержка 3 версии данного API будет прекращена 3 марта 2020 года.

Именно поэтому, даже если вас вполне устраивает пакет googlesheets, вам в любом случае до 3 марта необходимо мигрировать на googlesheets4, надеюсь данная статья вам в этом поможет.

Что бы максимально упростить миграцию с устаревшего пакета googlesheets на новый googlesheets4 я решил добавить таблицу соответвия новых функций старым.

Для работы с API Google Таблиц, как я уже писал выше, мы будем использовать пакет googlesheets4, написанный Дженни Брайан из RStudio.

Установить его можно как из CRAN, так и из GitHub.

Устновка из CRAN:

install.packages('googlesheets4')

Установка из GitHub:

# установка пакета devtools
if ( !require(devtools) ) install.packages("devtools")
# установка пакета googlesheets4
devtools::install_github("tidyverse/googlesheets4")
# подключаем пакет
library(googlesheets4)

В пакете googlesheets4 есть по меньшей мере 3 способа авторизации:

Использовать стандартные параметры функции gs4_auth().

Авторизоваться через собственное приложение.

Авторизоваться через сервисный аккаунт.

Авторизация со стандартными значениями аргументов

Наиболее простой способ авторизации предоставляет функция gs4_auth() со значением аргументов принятых в ней по умолчанию.

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

Поэтому я рекомендую самостоятельно создать приложение в Gogle Cloud, и пройти авторизацию с его помощью.

Авторизация через собственное приложение

Как создать своё приложение?

Для создания приложения следуйте приведённым ниже инструкциям:

Переходим в Google Cloud Console и создаём там проект, если у вас ещё нет созданного. Также для создания проекта можно воспользоваться ссылкой.

Переходим в «Основное меню» > «API и сервисы» > «Учетные данные».

«Создать учетные данные» > «Идентификатор клиента OAuth».

Вводим любое произвольное название для вашего приложения.

Далее будет сгенерирован id и secret вашего приложения.

Теперь вам необходимо либо скачать JSON файл с созданными учётными данными, либо скопировать id и секрет созданного вами приложения.

Скачиваем JSON

Копируем ID и секрет приложения

Если вы впервые создаёте приложение в Google Console, то также вам предварительно понадобиться создать «Окно запроса доступа OAuth», просто заполните в нём название и ваш email, насколько я помню больше там никаких обязательных полей нет.

Последний шаг, включаем Google Sheets API, для этого достаточно перейти по этой ссылке и нажать кнопку включить API.

Теперь возвращаемся в RStudio, подключаем пакет и проходим авторизацию через своё приложение.

Проходим авторизацию через JSON файл:

# подключаем пакет
library(googlesheets4)
# указываем путь к JSON файлу, который ранее скачали
gs4_auth_configure(path = "C:/path/to/app_credential. json")
# проходим авторизацию
gs4_auth(email = "[email protected]")

Проходим авторизацию указав в коде ID и Секрет приложения:

# подключаем пакет
library(googlesheets4)
# указываем путь к JSON файлу, который ранее скачали
httr::oauth_app(appname = "app_name", 
                  key    = "00000000-abcdefghk.apps.googleusercontent.com", 
                  secret = "qwertyuiopasdf")
# проходим авторизацию
gs4_auth(email = "[email protected]")

После запуска функции gs4_auth(), в приведённых выше примерах кода, вы будете перенаправлены в браузер. Там вы подтверждаете разрешение на доступ к данным и возвращаетесь в RStudio для продолжения работы.

Авторизация через сервисный аккаунт

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

Создать проект в Google Cloud, если он ещё не создан.

Включить Google Sheets API, для этого необходимо перейти по этой ссылке.

Для создания сервисного аккаунта перейдите по этой ссылке.

Заполните его название и описание (при необходимости) и нажмите «Создать».

Роль устанавливать не надо, поэтому жмём «Продолжить».

В следующем окне жмём «Создать ключ» > Тип ключа выбираем JSON > Создать.

Запоминаем расположение и название JSON файла, при сохранении вы можете дать ему любое название.

Жмём «Готово».

Теперь у вас появился сервисный аккаунт, откройте его и скопируйте почту.

При авторизации под сервисным аккаунтам вы можете работать только с теми Google Таблицами к которым вы предоставили доступ для созданного сервисного аккаунта по его почте. Доступ предоставляется также, как и обычному пользователю.

Открываем Google Таблицу.

Жмём «Настройка доступа».

Открываем доступ на почту созданного сервисного аккаунта.

Проходим авторизацию через сервисный аккаунт:

# подключаем пакет
library(googlesheets4)
# проходим авторизацию через сервисный аккаунт
gs4_auth(path = "C:/path/to/service_credential. json")

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

Разница между авторизацией через приложение и через сервисный аккаунт

Основными отличиями между авторизацией через приложение и через серверный аккаунт являются:

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

Сервисный аккаунт имеет доступ только к тем Google таблицам к которым вы сами ему предоставили доступ на почту. При авторизации через приложение вы подтверждаете доступ ко всей доступной вашему Google аккаунту информации.

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

Все функции пакета googlesheets4 разделены на 3 группы, каждая группа функций имеет свой префикс, который говорит об области действия этой функции:

gs4_ — объединяет функции реализующие операции над книгой GoogleSheets

sheet_ — операции над рабочими листами

range_ — операции над отдельными диапазонами ячеек

cell_ — операции над отдельными ячейками

Давайте рассмотрим основные функции пакета googlesheets4.

gs4_auth() — Авторизация;

gs4_create() — Создаёт новую Google Таблицу;

gs4_browse() — Открывает Google Таблицу в браузере;

as_sheets_id() — Инициализирует подключение к Google Таблице, в качестве единственного аргумента принимает URL или ключ нужной Google Таблицы;

range_read() — Считывает данные из указанного листа Google Таблицы;

sheet_write() — Записывает данные в Google Таблицу, при необходимости создаёт новый лист. Если вы пытаетесь записать данные на существующий лист то все данные будут перезаписаны;

sheet_append() — Дописывает данные на уже существующий лист;

sheet_add() — Создаёт новые листы в существующей Google Таблице;

sheet_delete() — Удаляет существующие листы из Google Таблицы;

sheet_names() — Выводит вектор содержащий имена листов Google Таблицы.

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

Теперь я приведу примеры кода для выполнения основных операций с Google Таблицами.

Для примера нам потребуются какие-нибудь тестовые наборы данных. Что бы не изобретать велосипед мы будем использовать созданную мной Google Таблицу и встроенные в R датасеты iris, mtcars и ToothGrowth.

Загрузка данных из существующих Google Таблиц

Считывание данных из Google Таблицы является наиболее часто используемой операцией.

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

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

URL: https://docs.google.com/spreadsheets/d/1hP1OwJuSHfvrTZvZUgEHDwcPPVlDMwPwCqCQPs64OSE/
Ключ: 1hP1OwJuSHfvrTZvZUgEHDwcPPVlDMwPwCqCQPs64OSE

Как вы поняли из приведённого выше примера ключ это часть URL адреса таблицы, которая находится между /d/ и следующие слешем .

Для чтения данных в googlesheets4 предназначена функция range_read().

Пример кода для чтения данных из Google Таблицы

# подключаем пакет
library(googlesheets4)
# проходим авторизацию одним из описанных способов
gs4_auth(email = "[email protected]")
# подключаемся к тестовому доксу
my_dox <- as_sheets_id("1hP1OwJuSHfvrTZvZUgEHDwcPPVlDMwPwCqCQPs64OSE")
# считываем данные с листа test_data
data   <- range_read(my_dox, sheet = "test_data")

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

data <- as_sheets_id("1hP1OwJuSHfvrTZvZUgEHDwcPPVlDMwPwCqCQPs64OSE") %>%
        range_read("test_data")

Создание Google Таблиц с помощью API

Для создания новой таблицы используйте функцию gs4_create() и следующие её аргументы.

name — Имя Google Таблицы;

locale — Региональные настройки;

timeZone — Часовой пояс;

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

Пример создания Google Таблицы

# подключаем пакет
library(googlesheets4)
# проходим авторизацию одним из описанных способов
gs4_auth(email = "[email protected]")
# создаём Google Таблицу
ss <- gs4_create(name = "my_new_dox", 
                    locale = "ru_RU",
                    sheets = list(mtcars = mtcars,
                                  iris   = head(iris)))
# открываем созданную таблицу в браузере
gs4_browse(ss)

Приведённый выше код создаст новую Google Таблицу «my_new_dox», в которой будут 2 листа: mtcars, iris.

Создание нового листа в Google Таблице

Итак, только что мы создали новую Google Таблицу, и при этом инициировали объект подключения к ней, который назвали ss.

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

Пример кода для создания нового листа с данными

# создаём новый лист с данными
sheet_write(ToothGrowth, ss, 
             sheet = "tooth_growth")

Writing to 'my_new_dox'
Writing to sheet "tooth_growth"

Функция sheet_write() имеет 3 основных аргумента:

data — Дата фрейм с данными которые вы хотите записать на новый лист, или перезаписать данные на существующем листе.

ss — Объект подключения к Google Таблице, который вы можете получить с помощью функций as_sheets_id() или gs4_create().

sheet — Название листа который будет создан в Google Таблице, или на котором будут перезаписаны данные.

Дописывание строк в существующий лист

Ещё одна достаточно важная операция — добавление данных на уже существующий лист.

Осуществляется эта операция функцией sheet_append(), которая имеет 3, уже знакомых вам аргумента.

data — Дата фрейм с данными которые вы хотите дописать на существующий лист.

ss — Объект подключения к Google Таблице, который вы можете получить с помощью функций as_sheets_id() или gs4_create().

sheet — Название листа на который требуется дописать строки.

При создании таблицы my_new_dox мы записали на лист iris только первые 6 строк с данными, давайте допишем оставшиеся.

Пример кода для добавления строк на существующий лист

# дописываем строки на лист iris
sheet_append(data = iris[7:150,], ss, 
              sheet = "iris")

Writing to 'my_new_dox'
Appending 144 row(s) to 'iris'

Перемещение Google Таблиц между папаками Google Диска

Ещё одна операция которая может вам пригодиться, но в данном случае помимо googlesheets4 вам понадобится пакет googledrive.

Установка googledrive

install.packages("googledrive")

Переместить созданную ранее Google Таблицу можно с помощью функции drive_mv(). Но предварительно необходимо пройти автооризацию с помощью функции drive_auth().

Авторизация в пакете googledrive ничем не отличается от описанной в начале этой статьи, т.к. оба рассматриваемых пакета для авторизации используют вспомогательный пакет gargle. Авторизовавшись с попощью функции drive_auth() вы можете передать полученный токен в пакет googlesheets4 для совместного использования: gs4_auth(token = drive_token()).

Далее открываем на Google Диске нужную папку и копируем её URL или ключ. Если нужная папка ещё не создана её можно создать с помощью функции drive_mkdir().

Для инициализации подключения к папке используем функцию as_id().

Пример кода для перемещения Google Таблицы из одной папки Google Диска в другую

# Подключаем библиотеки
library(googlesheets4)
library(googledrive)
# авторизация
## можно либо дважды пройти авторизацию, отдельно под каждым пакетом
## gs4_auth(email = "your_email@gmail. com")
## drive_auth(email = "[email protected]")
## либо пройти авторизацию с помощью google drive, и передать полученный токен для дальнейшего использования в google sheets
drive_auth(email = "[email protected]")
gs4_auth(token = drive_token())
# Инициируем подключение к таблице и папке
## Подключаемся к таблице которую требуется переместить
ss     <- as_sheets_id("1BNrYUajVSR3wuGySY0ybXrqh4-Jjq-eIS5_f_a6kt_c")
## Подключаемся к папке в которую надо перенести Google таблицу
folder <- as_id("1x94xChfZwSCPFzHvWqwk6HyF85_ASDFW")
# Либо создаём новую папку
## folder <- drive_mkdir("my_folder")
# Переносим Google Таблицу в нужную директорию
drive_mv(file = ss, 
         path = folder)

File moved:
  * my_new_dox -> my_folder/my_new_dox

В этом разделе приведу несколько полезных ссылок по теме статьи:

Видео урок на YouTube по работе с пакетом googlesheets4 (русский)

Официальная документация к пакету googlesheets4 (english)

Страница пакета на GitHub (english)

Страница пакета на CRAN (english)

Описанных в статье возможностей пакета googlesheets4 достаточно для решения подавляющего большинства задач, в которых необходимо использовать Google Sheets API.

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

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

Как работать с API Google Таблиц (Google Sheets API v4) на языке R с помощью нового пакета googlesheets4 / Хабр

Как пройти авторизацию для работы с Google Таблицами по API;

Рассмотрим основные функции пакета;

Разберём примеры кода для чтения данных, создания новых таблиц и выполнения других манипуляций с Google Таблицами по API.

Также эта статья поможет пользователям устаревшего пакета googlesheets мигрировать на новый googlesheets4.

Зачем переходить на работу с пакетом googlesheet4

Миграция с googlesheets на googlesheets4

Установка пакета googlesheets4

Авторизация

4. 1. Авторизация со стандартными параметрами

4.2. Авторизация через собственное приложение

4.3. Авторизация через сервисный аккаунт

4.4. Разница между авторизацией через приложение и через сервисный аккаунт

Основные функции пакета googlesheets4

Пример работы с API Google Таблиц на языке R

6.1. Загрузка данных из существующих Google Таблиц

6.2. Создание Google Таблиц с помощью API

6.3. Создание нового листа в Google Таблице

6.4. Дописываем строки в существующий лист

6.5. Перемещение Google Таблиц между папаками Google Диска

Полезные ссылки по теме статьи

Заключение

Установить его можно как из CRAN, так и из GitHub.

Устновка из CRAN:

install.packages('googlesheets4')

Установка из GitHub:

# установка пакета devtools
if ( !require(devtools) ) install. packages("devtools")
# установка пакета googlesheets4
devtools::install_github("tidyverse/googlesheets4")
# подключаем пакет
library(googlesheets4)

В пакете googlesheets4 есть по меньшей мере 3 способа авторизации:

Использовать стандартные параметры функции gs4_auth().

Авторизоваться через собственное приложение.

Авторизоваться через сервисный аккаунт.

Авторизация со стандартными значениями аргументов

Поэтому я рекомендую самостоятельно создать приложение в Gogle Cloud, и пройти авторизацию с его помощью.

Авторизация через собственное приложение

Как создать своё приложение?

Для создания приложения следуйте приведённым ниже инструкциям:

Переходим в Google Cloud Console и создаём там проект, если у вас ещё нет созданного. Также для создания проекта можно воспользоваться ссылкой.

Переходим в «Основное меню» > «API и сервисы» > «Учетные данные».

«Создать учетные данные» > «Идентификатор клиента OAuth».

Вводим любое произвольное название для вашего приложения.

Далее будет сгенерирован id и secret вашего приложения.

Теперь вам необходимо либо скачать JSON файл с созданными учётными данными, либо скопировать id и секрет созданного вами приложения.

Скачиваем JSON

Копируем ID и секрет приложения

Последний шаг, включаем Google Sheets API, для этого достаточно перейти по этой ссылке и нажать кнопку включить API.

Теперь возвращаемся в RStudio, подключаем пакет и проходим авторизацию через своё приложение.

Проходим авторизацию через JSON файл:

# подключаем пакет
library(googlesheets4)
# указываем путь к JSON файлу, который ранее скачали
gs4_auth_configure(path = "C:/path/to/app_credential.json")
# проходим авторизацию
gs4_auth(email = "[email protected]")

Проходим авторизацию указав в коде ID и Секрет приложения:

# подключаем пакет
library(googlesheets4)
# указываем путь к JSON файлу, который ранее скачали
httr::oauth_app(appname = "app_name", 
                  key    = "00000000-abcdefghk.apps.googleusercontent.com", 
                  secret = "qwertyuiopasdf")
# проходим авторизацию
gs4_auth(email = "[email protected]")

Авторизация через сервисный аккаунт

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

Создать проект в Google Cloud, если он ещё не создан.

Включить Google Sheets API, для этого необходимо перейти по этой ссылке.

Для создания сервисного аккаунта перейдите по этой ссылке.

Заполните его название и описание (при необходимости) и нажмите «Создать».

Роль устанавливать не надо, поэтому жмём «Продолжить».

В следующем окне жмём «Создать ключ» > Тип ключа выбираем JSON > Создать.

Запоминаем расположение и название JSON файла, при сохранении вы можете дать ему любое название.

Жмём «Готово».

Теперь у вас появился сервисный аккаунт, откройте его и скопируйте почту.

Открываем Google Таблицу.

Жмём «Настройка доступа».

Открываем доступ на почту созданного сервисного аккаунта.

Проходим авторизацию через сервисный аккаунт:

# подключаем пакет
library(googlesheets4)
# проходим авторизацию через сервисный аккаунт
gs4_auth(path = "C:/path/to/service_credential.json")

Разница между авторизацией через приложение и через сервисный аккаунт

Основными отличиями между авторизацией через приложение и через серверный аккаунт являются:

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

Сервисный аккаунт имеет доступ только к тем Google таблицам к которым вы сами ему предоставили доступ на почту. При авторизации через приложение вы подтверждаете доступ ко всей доступной вашему Google аккаунту информации.

gs4_ — объединяет функции реализующие операции над книгой GoogleSheets

sheet_ — операции над рабочими листами

range_ — операции над отдельными диапазонами ячеек

cell_ — операции над отдельными ячейками

Давайте рассмотрим основные функции пакета googlesheets4.

gs4_auth() — Авторизация;

gs4_create() — Создаёт новую Google Таблицу;

gs4_browse() — Открывает Google Таблицу в браузере;

as_sheets_id() — Инициализирует подключение к Google Таблице, в качестве единственного аргумента принимает URL или ключ нужной Google Таблицы;

range_read() — Считывает данные из указанного листа Google Таблицы;

sheet_write() — Записывает данные в Google Таблицу, при необходимости создаёт новый лист. Если вы пытаетесь записать данные на существующий лист то все данные будут перезаписаны;

sheet_append() — Дописывает данные на уже существующий лист;

sheet_add() — Создаёт новые листы в существующей Google Таблице;

sheet_delete() — Удаляет существующие листы из Google Таблицы;

sheet_names() — Выводит вектор содержащий имена листов Google Таблицы.

Теперь я приведу примеры кода для выполнения основных операций с Google Таблицами.

Загрузка данных из существующих Google Таблиц

Считывание данных из Google Таблицы является наиболее часто используемой операцией.

URL: https://docs. google.com/spreadsheets/d/1hP1OwJuSHfvrTZvZUgEHDwcPPVlDMwPwCqCQPs64OSE/
Ключ: 1hP1OwJuSHfvrTZvZUgEHDwcPPVlDMwPwCqCQPs64OSE

Для чтения данных в googlesheets4 предназначена функция range_read().

Пример кода для чтения данных из Google Таблицы

# подключаем пакет
library(googlesheets4)
# проходим авторизацию одним из описанных способов
gs4_auth(email = "[email protected]")
# подключаемся к тестовому доксу
my_dox <- as_sheets_id("1hP1OwJuSHfvrTZvZUgEHDwcPPVlDMwPwCqCQPs64OSE")
# считываем данные с листа test_data
data   <- range_read(my_dox, sheet = "test_data")

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

data <- as_sheets_id("1hP1OwJuSHfvrTZvZUgEHDwcPPVlDMwPwCqCQPs64OSE") %>%
        range_read("test_data")

Создание Google Таблиц с помощью API

Для создания новой таблицы используйте функцию gs4_create() и следующие её аргументы.

name — Имя Google Таблицы;

locale — Региональные настройки;

timeZone — Часовой пояс;

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

Пример создания Google Таблицы

# подключаем пакет
library(googlesheets4)
# проходим авторизацию одним из описанных способов
gs4_auth(email = "[email protected]")
# создаём Google Таблицу
ss <- gs4_create(name = "my_new_dox", 
                    locale = "ru_RU",
                    sheets = list(mtcars = mtcars,
                                  iris   = head(iris)))
# открываем созданную таблицу в браузере
gs4_browse(ss)

Приведённый выше код создаст новую Google Таблицу «my_new_dox», в которой будут 2 листа: mtcars, iris.

Создание нового листа в Google Таблице

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

Пример кода для создания нового листа с данными

# создаём новый лист с данными
sheet_write(ToothGrowth, ss, 
             sheet = "tooth_growth")

Writing to 'my_new_dox'
Writing to sheet "tooth_growth"

Функция sheet_write() имеет 3 основных аргумента:

data — Дата фрейм с данными которые вы хотите записать на новый лист, или перезаписать данные на существующем листе.

ss — Объект подключения к Google Таблице, который вы можете получить с помощью функций as_sheets_id() или gs4_create().

sheet — Название листа который будет создан в Google Таблице, или на котором будут перезаписаны данные.

Дописывание строк в существующий лист

Ещё одна достаточно важная операция — добавление данных на уже существующий лист.

Осуществляется эта операция функцией sheet_append(), которая имеет 3, уже знакомых вам аргумента.

data — Дата фрейм с данными которые вы хотите дописать на существующий лист.

ss — Объект подключения к Google Таблице, который вы можете получить с помощью функций as_sheets_id() или gs4_create().

sheet — Название листа на который требуется дописать строки.

При создании таблицы my_new_dox мы записали на лист iris только первые 6 строк с данными, давайте допишем оставшиеся.

Пример кода для добавления строк на существующий лист

# дописываем строки на лист iris
sheet_append(data = iris[7:150,], ss, 
              sheet = "iris")

Writing to 'my_new_dox'
Appending 144 row(s) to 'iris'

Перемещение Google Таблиц между папаками Google Диска

Установка googledrive

install. packages("googledrive")

Для инициализации подключения к папке используем функцию as_id().

Пример кода для перемещения Google Таблицы из одной папки Google Диска в другую

# Подключаем библиотеки
library(googlesheets4)
library(googledrive)
# авторизация
## можно либо дважды пройти авторизацию, отдельно под каждым пакетом
## gs4_auth(email = "your_email@gmail. com")
## drive_auth(email = "[email protected]")
## либо пройти авторизацию с помощью google drive, и передать полученный токен для дальнейшего использования в google sheets
drive_auth(email = "[email protected]")
gs4_auth(token = drive_token())
# Инициируем подключение к таблице и папке
## Подключаемся к таблице которую требуется переместить
ss     <- as_sheets_id("1BNrYUajVSR3wuGySY0ybXrqh4-Jjq-eIS5_f_a6kt_c")
## Подключаемся к папке в которую надо перенести Google таблицу
folder <- as_id("1x94xChfZwSCPFzHvWqwk6HyF85_ASDFW")
# Либо создаём новую папку
## folder <- drive_mkdir("my_folder")
# Переносим Google Таблицу в нужную директорию
drive_mv(file = ss, 
         path = folder)

File moved:
  * my_new_dox -> my_folder/my_new_dox

В этом разделе приведу несколько полезных ссылок по теме статьи:

Видео урок на YouTube по работе с пакетом googlesheets4 (русский)

Официальная документация к пакету googlesheets4 (english)

Страница пакета на GitHub (english)

Страница пакета на CRAN (english)

Учебное пособие по API Google Sheets с Javascript | by Ignacio Nicolas Aguirre

Краткое подробное руководство.

https://cdn.pixabay.com/photo/2016/10/26/12/48/excel-1771393_1280.jpg

Игнасио Н. Агирре

Пару месяцев назад я пытался сделать CRUD операции с электронной таблицей Google с помощью Sheets API V4 для моего приложения Javascript. Я использовал React.js для интерфейса и Node.js для бэкэнда: это было очень больно. У меня был свой токен Google на бэкенде, я отправил его на фронтенд, но когда я попытался получить доступ к API с помощью React/Javascript, он не работал! Наконец, я понял, что вам нужно, чтобы заставить его работать, и как расширить использование API. Я сразу перейду к тому, как это сделать, и объясню более подробно позже.

Я использовал приложение create-реагировать. Но он должен работать на любом Javascript-приложении, использующем выборку. Вы можете увидеть возможности того, что этот API может делать с этой игрой:

www.snowballfinances.com/sheets

Это бюджетная игра, созданная с помощью React и электронных таблиц. Наслаждаться!

Я также пишу электронную книгу, чтобы поделиться всем, что я узнал об API Google с нуля! Пожалуйста, дайте мне знать, если вы заинтересованы 🙂

React и API Google

Научитесь манипулировать API Google с нуля: аутентификация, управление электронными таблицами, Календарь Google, токен…

react-ultimate-guide-googleapis. vercel.app

Я не рекомендую использовать онлайн-среды IDE, вы будете работать в проблемы CORS . Есть много туториалов по create-react-app (то, чем я пользуюсь), его настройка займет не более 5 минут. Вот быстрая настройка:

npm i -g create-react-app

create-react-app my-app

cd my-app

Откройте его в своем любимом редакторе кода.

Вот что вам нужно для работы:

Электронная таблица и ее идентификатор
Токен для отправки в Sheets API с правильными областями действия
fetch() запрос с правильной информацией (тело , заголовки аутентификации)

Давайте пошагово. Мы начнем с чего-то простого, обновив значение 10 в ячейке A1.

Это простая часть. Перейдите на Sheets.google.com, войдите в свою учетную запись Google и создайте электронную таблицу. Когда вы увидите электронную таблицу, идентификатор электронной таблицы будет находиться в URL-адресе , как показано ниже.

Таким образом, наш идентификатор в этом случае: 1nIrrHDVn0Jx3AbyWowfvLoBkvHg7jqvtP5Lh2yRI2Ks

Используйте свой собственный идентификатор электронной таблицы. Это только для пояснений!

Чтобы получить токен от Google, вам понадобится система Google OAuth. Мы получим токен с Google Auth3 Playground:

OAuth 2.0 Playground

OAuth 2.0 Playground позволяет вам играть с OAuth 2.0 и API, которые его поддерживают.

Developers.google.com

Перейдите на веб-сайт, указанный выше.

Теперь в левой колонке найдите Google Sheets API V4:

Нажмите на него, а затем нажмите scope : h ttps://www.googleapis.com/auth/ электронные таблицы

Слева от него должен появиться check

8 9.

Наконец, нажмите Authorize APIs. Это синяя кнопка выше.

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

После входа в систему и авторизации вы получите Код авторизации, , как показано ниже . Щелкните Код авторизации Exchange для токенов.

Предупреждение: Убедитесь, что вы находитесь на Шаг 2, вам НЕ нужен шаг 3.

Вот и все! Вы только что получили «токен доступа». Скопируйте куда-нибудь! Технически мы готовы обновить нашу таблицу. Итак, давайте сделаем это!

Теперь давайте отправим правильный запрос fetch(). Чтобы увидеть правильные параметры запроса на выборку, мы перейдем к документации Sheets API V4. Вы можете не переходить на сайт, ответ ниже в любом случае! Но если вы хотите расширить функциональность, вы захотите проверить это позже.

Метод: электронные таблицы.batchUpdate | API листов | Google Developers

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

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate

Я нахожу полезным, когда сначала вижу полный код. Я использую React.js create-реагировать-приложение. Итак, вот код для обновления ячейки A1 до 10.

Важно: Это будет работать в любой локальной среде, где вы можете использовать fetch(). Вы НЕ ограничены React!

Этот код обновит вашу электронную таблицу числом 10 в ячейке A1.

Предупреждение: Вам необходимо ЗАМЕНИТЬ SHEET_ID и ACCESS_TOKEN своей информацией. Моя не пойдет!

Когда я запускаю свое приложение React.js, я нажимаю кнопку, и лист обновляется!

Сообщите мне, если это не сработает в комментариях, и я буду рад помочь. Посмотрим, как я построю объект более подробно ниже.

Остальная часть руководства содержит более подробное объяснение.

Запрос на выборку принимает строковый аргумент: fetch('https://sheets.googleapis.com/v4/spreadsheets/SHEET_ID_HERE/batchUpdate')

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

 fetch('https://sheets.googleapis.com/v4/spreadsheets/SHEET_ID_HERE/batchUpdate',{ 9Метод 0203: «POST», заголовки 
: { 
  Авторизация: «Bearer TOKEN_GOES_HERE», 
 «тип контента»: «application/json», 
  }})

Отлично!

Теперь самое главное: кузов. Мы используем запрос пакетного обновления Google . Посмотрите на конец изображения ниже.

URL-адрес запроса (/batchUpdate) определяет, что будет обновляться на вашем листе, где и как. Существует десятков, повторяю, десятков способов обновления электронной таблицы Google. Я выбрал пакетное обновление, потому что у вас есть большой контроль над значением ячейки (ячеек), форматом, выравниванием и т. Д. Другой пример будет позже, но сначала давайте закончим объяснение этого.

Тело

Ну вот! Вы создали свой первый объект для обновления электронной таблицы!

У вас есть POST-запрос с вашим идентификатором листа для Google URL-адрес batchUpdate , который содержит токен , заголовок аутентификации и тело . Этот запрос использует Google Sheets API v4 для обновления вашей конкретной электронной таблицы.

Все это можно использовать в приложении! Допустим, вы хотите, чтобы пользователи заполнили форму, которая обновляет вашу таблицу (/batchUpdate), или функцию, извлекающую информацию из электронной таблицы (/batchGet), или обновляли форматы ячеек, или динамически создавали таблицы, все это возможно (я все сделал!). Чтобы научиться создавать правильные запросы на выборку с правильными параметрами, у Google есть способ протестировать запросы API и создать объекты для вас. Вы можете перейти в раздел «Обзор» каждого запроса.

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

Метод: Spreadsheets.batchUpdate | API листов | Google Developers

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

Я использовал Passport.js в бэкенде Node.js для аутентификации своих пользователей в Google. Область , которую я использовал, , которая очень важна для манипулирования листами, — это область drive.file , потому что она предоставляет приложению доступ только к созданным листам, и не более того. В этом руководстве мы использовали область электронных таблиц для доступа к API из любого приложения.

После аутентификации пользователя я отправил токен , который вы должны получить обратно после аутентификации с помощью Passport, клиенту. Теперь у клиента React.js есть доступ к API, и с клиента я отправляю запросы к Google Sheets API v4. Я часто использую пакетное обновление . Другие методы, которые я использовал, это / values/batchUpdate (отличается от batchUpdate), / batchClear, и / значений.

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

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

https://www.youtube.com/watch?v=zq0TuNqV0Ew&t=195s

Немного настроек, но это работает! Вы получите токен, который сможете использовать.

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

Метод: электронные таблицы.значения.получить | API листов | Google Developers

Изменить описание

Developers.google.com

Допустим, я хочу получить следующие значения столбца:

В этом случае я хочу получить доступ ко всем значениям в диапазоне A1:B5. Что ж, у Google есть метод, который делает именно это!

Для этого я буду использовать запрос /values/{range}. См. ниже структуру URL-адреса запроса.

Я создам другой вид выборки, который будет проще (потому что это GET-запрос), и что самое приятное: в намного меньше кода. Я использую React из своей локальной среды разработки.

В этом приложении есть кнопка, которая получает диапазон значений A1:B5. Теперь у вас есть доступ к этой информации!

Круто, правда?

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

Мне пришлось научиться использовать этот API для стартапа, который я создаю. Перейдите на сайт www.snowballfinances.com (пока не для мобильных устройств), чтобы увидеть все приложения API Google Sheets! Я обучаю людей личным финансам с помощью реальных инструментов, а затем превращаю их в игры. Дайте мне знать, если вы хотите услышать больше!

Кроме того, если вы хотите узнать больше о функциях Javascript Async/Await, посмотрите мой другой учебник:

Объединение вызовов API с Javascript Async/Await:
https://medium.com/@inaguirre/ комбинирование вызовов API-с-javascript-try-catch-ba1b7b9303a5

Вот репозиторий, если вы хотите, что мы сделали сегодня:

Ignacio1996/sheetsAPI-V4-tutorial

Участие в разработке Ignacio1996/sheetsAPI-V4-tutorial создав учетную запись на GitHub.

github.com

Как читать и писать листы Google с помощью Python | Прафулла Далви | Analytics Vidhya

Photo by Mika Baumeister on Unsplash

Imp Примечание: если вы ищете чтение и запись gsheet с помощью python, то этот блог решит вашу проблему. это мой 100% гарантия но для этого просто нужно идти медленно и внимательно читать.

H ello Scholars,

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

Звучит круто? Давайте начнем

Во-первых, вам нужно импортировать следующие пакеты с помощью Pip.

 pip install google_spreadsheet 
 pip install google-auth-oauthlib 
 pip install pandas

Затем возьмите идентификатор листа Google, из которого вы хотите прочитать данные.

Получить идентификатор листа.

Перейдите к электронной таблице (в моем случае я хочу прочитать данные из таблицы спортсмена) и нажмите «Поделиться».

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

3. Вы получите ссылку в этом формате.

https://docs.google.com/spreadsheets/d/ 1cvZswLiDo3LfhnA7RcS8vFqacx73RGor-OZ_FtvyLE8 /edit?usp=sharing

Жирным шрифтом обозначен ваш идентификатор листа Google. (если вы читаете данные с чужого листа, то этот лист должен быть предоставлен совместно с вашим идентификатором Google)

Включите API листов Google.

Перейдите на страницу https://developers.google.com/sheets/api/quickstart/python.

Нажмите кнопку API «Включить лист Google», загрузите файл JSON и сохраните его в той же папке, где будет сохранен наш код.

(если вы впервые используете Google Cloud, активируйте GCP и создайте новый проект)

Запустите следующий код в среде Python.

В этом коде необходимо внести следующие изменения.

изменить идентификатор листа Google.
изменить файл JSON.
изменить диапазон листа в соответствии с вашими данными. (при необходимости)

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

Этот код будет считывать данные с вашего листа Google и сохранять их в кадре данных pandas.

Давайте быстро разберемся в коде.

Импортированы необходимые библиотеки.
Определил область листа. Объем может быть изменен в соответствии с необходимостью.

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

3. Ввел ID и диапазон листа. с указанного ID и данные из выбранного диапазона будут считаны по коду.

4. Аутентификация Google API с помощью загруженного файла JSON. Используя этот файл, мы создали файл «token.pickle», который будет храниться на нашем компьютере для будущего использования, и всякий раз, когда срок действия этого файла pickle истечет, наш код будет обновлять файл.

5. Создан сервис, который будет вызывать API листа и получать данные. затем, наконец, сохранение данных в кадре данных pandas.

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

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

Я прочитал данные листа «athlete_events» и сохранил их в кадре данных. Теперь мне нужны данные только о спортсменах, завоевавших золотую медаль по гимнастике в таблице «gold_medal».

круто…. давай сделаем это.

Получите идентификатор листа, на котором вы хотите сохранить выходные данные.
Определить диапазон моего вывода.

Этот код создаст файл pickle из файла JSON, построит сервис и, наконец, обновит записи на листе «gold_medal».

This entry was posted in Популярное

Posted:

Comments: