В этом руководстве мы будем использовать пакет gspread из Python для чтения, записи и удаления данных из электронной таблицы Google с помощью всего нескольких строк кода.
Содержание
- 1 Настройка подключения в Google Api Console.
- 2 Подключение gspread в Python
- 3 Методы работы с google таблицами в Python с использованием gspread
- 3.1 Открытие электронной таблицы
- 3.2 Создание электронной таблицы
- 3.3 Совместное использование электронной таблицы
- 3.4 Выбор рабочего листа
- 3.5 Создание рабочего листа
- 3.6 Удаление рабочего листа
- 3.7 Получение значения ячейки
- 3.8 Получение всех значений из строки или столбца
- 3.9 Получение всех значений из рабочего листа в виде списка списков
- 3.10 Получение всех значений из рабочего листа в виде списка словарей
- 3.11 Поиск ячейки
- 3.12 Поиск всех совпадающих ячеек
- 3.13 Объект ячейки
- 3.14 Обновление ячеек
- 3.15 Форматирование
Настройка подключения в Google Api Console.
Если вы уже сделали это, можете пролистать. Код на Python будет сразу после инструкции по подключению.
- Зайдите в Google API Console.
2. Создайте новый проект.
Введите имя проекта.
Если у вас уже есть проекты, выберите только что созданный.
В меню слева выберите “Marketplace”
В поле поиска введите “Google Drive api” и нажмите на Enter.
Кликните на Google Drive API
На открывшейся странице нажмите “Enable”.
Повторите эти же шаги (начиная с момента, когда вы заходите в marketplace) но в поиске введите Google Sheets API, перейдите в него и нажмите Enable.
Затем зайдите в пункт меню “APIs & Services”.
Слева в меню перейдите в “Credentials”. Нажмите на “Create Credentials”, в открывшемся меню выберите пункт Service account.
Откроется страница создания аккаунта. Введите имя и нажмите “Create”
В поле “Select Role” выберите “Editor”. Затем нажмите Continue.
Нажмите Done.
Кликаем на только что созданный аккаунт.
Переходим во вкладку KEYS. Жмем на ADD KEY. В появившемся меню выбираем Create new key.
Выбираем JSON и жмем CREATE.
Скачиваем json файл на свой компьютер.
Переходим во вкладку Details, копируем Email.
Переходим в таблицу, к которой у вас будет доступ. Жмем “Настройки доступа”, вводим скопированный Email и жмем “Готово”.
После этого вам будет предложено выбрать роль, выберите “Редактор”.
Файл json вы можете загрузить в любую папку, доступ к нему можно будет прописать в коде.
Подключение gspread в Python
Сначала вам нужно установить gspread. Это можно сделать командой:
pip install gspread
Импортируем библиотеку, получим и выведем ячейку из “Тестовой таблицы”.
import gspread
# Указываем путь к JSON
gc = gspread.service_account(filename='my-test-project-314413-dd942cf9cb7a.json')
#Открываем тестовую таблицу
sh = gc.open("Тестовая таблица")
#Выводим значение ячейки A1
print(sh.sheet1.get('A1'))
Code language: PHP (php)
Результат:
[['1']]
Code language: JSON / JSON with Comments (json)
Далее рассмотрим методы для работы с таблицами.
Методы работы с google таблицами в Python с использованием gspread
Открытие электронной таблицы
Вы можете открыть электронную таблицу по ее названию, как она отображается в Документах Google:
sh = gc.open('Моя таблица')
Code language: JavaScript (javascript)
Если вы хотите точно определить, используйте ключ (который можно извлечь из url электронной таблицы):
sht1 = gc.open_by_key('1KJkl7crYR1Xmmdha7kj3aUE6oDG4XXADK-Sl4QO2DGI')
Code language: JavaScript (javascript)
Или, если вам лень извлекать этот ключ, вставьте url всей электронной таблицы
sht2 = gc.open_by_url('https://docs.google.com/spreadsheet/ccc?key=1KJ...O2DGI')
Code language: JavaScript (javascript)
Создание электронной таблицы
Используйте create() для создания новой пустой таблицы:
sh = gc.create('Новая таблица')
Code language: JavaScript (javascript)
Если вы используете служебный аккаунт, новая электронная таблица будет видна только этому аккаунту. Чтобы получить доступ к только что созданной электронной таблице из Google Sheets с помощью собственного аккаунта Google, вы должны поделиться ею со своей электронной почтой.
Совместное использование электронной таблицы
Если ваша электронная почта ivan@site.com, вы можете поделиться созданной электронной таблицей с самим собой:
sh.share('ivan@site.com', perm_type='user', role='writer')
Code language: JavaScript (javascript)
Выбор рабочего листа
Выбор рабочего листа по индексу. Индексы рабочих листов начинаются с нуля:
worksheet = sh.get_worksheet(0)
Или по названию:
worksheet = sh.worksheet("Январь")
Code language: JavaScript (javascript)
Или самый распространенный случай: Sheet1:
worksheet = sh.sheet1
Чтобы получить список всех рабочих листов:
worksheet_list = sh.worksheets()
Создание рабочего листа
worksheet = sh.add_worksheet(title="Январь", rows="100", cols="20")
Code language: JavaScript (javascript)
Удаление рабочего листа
sh.del_worksheet(worksheet)
Code language: CSS (css)
Получение значения ячейки
Используя формат A1:
val = worksheet.acell('C3').value
Code language: JavaScript (javascript)
Или координаты строк и столбцов:
val = worksheet.cell(1, 2).value
Если вы хотите получить формулу ячейки:
cell = worksheet.acell('B1', value_render_option='FORMULA').value
# или
cell = worksheet.cell(1, 2, value_render_option='FORMULA').value
Code language: PHP (php)
Получение всех значений из строки или столбца
Получить все значения из первой строки:
values_list = worksheet.row_values(1)
Получить все значения из первого столбца:
values_list = worksheet.col_values(1)
Получение всех значений из рабочего листа в виде списка списков
list_of_lists = worksheet.get_all_values()
Получение всех значений из рабочего листа в виде списка словарей
list_of_dicts = worksheet.get_all_records()
Поиск ячейки
Найти ячейку, соответствующую строке:
cell = worksheet.find("Картошка")
print("Найдено в ячейке R%sC%s" % (cell.row, cell.col))
Code language: PHP (php)
Найти ячейку, соответствующую регулярному выражению
amount_re = re.compile(r'(Красная|Белая) картошка')
cell = worksheet.find(amount_re)
Code language: JavaScript (javascript)
Поиск всех совпадающих ячеек
Найти все ячейки, соответствующие строке:
cell_list = worksheet.findall("Красная картошка")
Code language: JavaScript (javascript)
Найдите все я
Найти все ячейки, соответствующие регулярному выражению:
criteria_re = re.compile(r'(Красная|Белая) картошка')
cell_list = worksheet.findall(criteria_re)
Code language: JavaScript (javascript)
Объект ячейки
Каждая ячейка имеет значение и свойства координат:
value = cell.value row_number = cell.row column_number = cell.col
Обновление ячеек
Используя формат A1:
worksheet.update('B1', 'Свекла')
Code language: JavaScript (javascript)
Или координаты строк и столбцов:
worksheet.update_cell(1, 2, 'Свекла')
Code language: JavaScript (javascript)
Обновить диапазон
worksheet.update('A1:B2', [[1, 2], [3, 4]])
Code language: JavaScript (javascript)
Форматирование
Вот пример базового форматирования.
Установим для текста A1:B1 полужирный формат:
worksheet.format('A1:B1', {'textFormat': {'bold': True}})
Code language: PHP (php)
Окрасим фон диапазона ячеек A2:B2 в черный цвет, изменим горизонтальное выравнивание, цвет текста и размер шрифта:
worksheet.format("A2:B2", {
"backgroundColor": {
"red": 0.0,
"green": 0.0,
"blue": 0.0
},
"horizontalAlignment": "CENTER",
"textFormat": {
"foregroundColor": {
"red": 1.0,
"green": 1.0,
"blue": 1.0
},
"fontSize": 12,
"bold": True
}
})
Code language: PHP (php)
Второй аргумент format() – это словарь, содержащий поля для обновления.