Язык программирования Python

Google Таблицы и Python – подробное руководство с примерами.

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

Настройка подключения в Google Api Console.

Если вы уже сделали это, можете пролистать. Код на Python будет сразу после инструкции по подключению.

  1. Зайдите в Google API Console.

2. Создайте новый проект.

Нажмите на список проектов, затем NEW PROJECT

Введите имя проекта.

После ввода имени нажмите “Create”

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

Для выбор кликните на названия проектов и из списка выберите нужный.

В меню слева выберите “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() – это словарь, содержащий поля для обновления.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *