Чтение и запись Google Sheets с помощью Python

Python на первый взгляд может показаться непривычным, но язык прост в освоении и широко используется: от игр и алгоритмов машинного обучения до серверной логики и автоматизации. В этой статье вы научитесь читать и записывать Google Sheets через Python — от настройки проекта в Google Cloud до готовых функций, тестов и чек‑листов для продакшн‑использования.

Что понадобится

• Учётная запись Google с доступом к Google Cloud Console.
• Лист Google Sheets, который вы хотите читать/изменять.
• Локальная среда Python (виртуальное окружение рекомендовано).
• Пакеты: gspread и oauth2client (или альтернативы: google-auth, google-auth-oauthlib, gspread_dataframe).

Важно: не храните JSON‑ключ сервисного аккаунта в публичных репозиториях. Используйте переменные окружения или секреты CI/CD.

Подготовка Google

1. Создайте новый Google Sheet или используйте существующий. Для примера мы используем таблицу со списком раллийных автомобилей:

2. Откройте Google Developers Console (Google Cloud Console). Нажмите кнопку «CREATE PROJECT», задайте имя и выберите проект.

3. Перейдите в меню «APIs and services» → «Dashboard», затем нажмите «ENABLE APIS AND SERVICES» и включите Google Sheets API.

4. Перейдите в раздел «Credentials» и создайте учётные данные: выберите «Create credentials» → «Service account».

5. Задайте имя сервисного аккаунта и завершите создание. Затем в списке сервисных аккаунтов откройте созданный аккаунт и добавьте ключ: «Add Key» → «Create new key» → формат JSON. Скачайте JSON в локальную папку проекта.

6. Откройте скачанный JSON и найдите поле client_email (например, id@project.iam.gserviceaccount.com). Поделитесь вашим Google Sheet с этим email (кнопка «Share» в правом верхнем углу). Дайте доступ на запись, если планируете обновлять таблицу.

Важно: предоставляйте минимально необходимые привилегии (см. раздел «Безопасность»).

Подготовка Python

1. Создайте виртуальное окружение и активируйте его:

python -m venv .venv
# Windows
.\.venv\Scripts\activate
# macOS / Linux
source .venv/bin/activate

2. Установите зависимости:

pip install gspread oauth2client
# по желанию для удобной работы с таблицами и DataFrame
pip install pandas gspread-dataframe

Если у вас возникают проблемы с SSL на некоторых системах Windows, дополнительно установите PyOpenSSL:

pip install PyOpenSSL

3. Создайте файл проекта, например sheet_example.py, и поместите туда JSON‑ключ в ту же папку (или читайте из переменной окружения — безопаснее).

Пример кода: подключение и аутентификация

Ниже — минимальный пример подключения и открытия таблицы с помощью gspread + oauth2client:

from oauth2client.service_account import ServiceAccountCredentials
import gspread

scopes = [
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive'
]

credentials = ServiceAccountCredentials.from_json_keyfile_name('your-service-account.json', scopes)
gc = gspread.authorize(credentials)

# Открыть таблицу по названию
sh = gc.open('Python_MUO_Google_Sheet')
# Открыть лист внутри таблицы по имени
sheet = sh.worksheet('Sheet1')

Замените ‘your-service-account.json’ и имена на свои. При корректной авторизации ошибок не должно быть.

Чтение данных

Примеры чтения:

• Получить диапазон ячеек:

all_cells = sheet.range('A1:C6')
for cell in all_cells:
    print(cell.value)

• Получить одно значение по адресу ячейки:

val = sheet.acell('A2').value
print(val)  # например, 'Ford'

• Получить значение по координатам (строка, столбец):

val = sheet.cell(5, 3).value

• Получить всю строку или столбец:

row = sheet.row_values(1)
col = sheet.col_values(2)

Отформатированный вывод примера в консоли:

Запись данных

Запись по адресу или координатам:

sheet.update_acell('C2', 'Blue')
# или
sheet.update_cell(2, 3, 'Blue')

Обновление диапазона:

sheet.update('A2:B3', [['Not Ford', 'Not Lancia'], ['Nothing', 'Not']])

Форматирование ячеек (пример — сделать заголовки жирными):

sheet.format('A1:C1', {'textFormat': {'bold': True}})

Добавление новой строки (append):

sheet.append_row(['Новое', 'Значение', 123])

Практика безопасности при записи: контролируйте «safety»‑ячейку перед массовой записью — храните специальное значение в ячейке и проверяйте его, чтобы избежать незапланированных перезаписей.

if sheet.acell('B3').value != 'SAFETY':
    print('Sheet already updated. Aborting write.')
else:
    sheet.update_acell('C2', 'Blue')

Полный пример: агент для синхронизации столбца

Ниже — пример небольшой функции, которая читает столбец A, применяет простую трансформацию и записывает результат в столбец B:

def transform_and_write(sheet):
    col = sheet.col_values(1)  # читаем столбец A
    out = []
    for v in col:
        if not v:
            out.append('')
        else:
            out.append(v.strip().title())
    # Запишем результат начиная со второй строки в столбец B
    cell_list = sheet.range(1, 2, len(out), 2)
    for i, cell in enumerate(cell_list):
        cell.value = out[i]
    sheet.update_cells(cell_list)

Этот шаблон полезен для пакетных обновлений и минимизирует число сетевых запросов.

Автоматизация и структура проекта

Рекомендации по организации:

• Разделите логику: auth.py (аутентификация), sheets.py (обёртки вызовов API), tasks.py (ETL/триггеры).
• Логиируйте операции записи и ошибки в файл/сервис (например, Sentry, если используете).
• Используйте cron/Cloud Scheduler + Cloud Function/Cloud Run для запуска задач по расписанию.
• Добавьте тесты, описанные в разделе «Критерии приёмки».

Альтернативные подходы

• oauth2client устарел — для новых проектов рассматривайте google-auth и google-auth-oauthlib в связке с gspread: они лучше поддерживаются.
• Используйте gspread-dataframe для конвертации между pandas.DataFrame и листом таблицы.
• Для корпоративных интеграций рассмотрите прямой доступ через Google Sheets API v4 (googleapiclient.discovery) вместо gspread.

Хорошие практики и безопасность

• Least privilege: назначайте сервисному аккаунту роль только на чтение/запись нужных таблиц, избегайте broad roles/editor.
• Хранение ключа: не в репозитории. Используйте секреты CI/CD, Secret Manager или переменные окружения.
• Ротация ключей: периодически создавайте новые ключи и отключайте старые.
• Конфиденциальность данных: если храните персональные данные, применяйте шифрование, минимизацию полей и маскирование.
• Журналирование: фиксируйте, кто и когда запускал автоматизацию, и какие изменения были внесены.

Безопасная конфигурация сервисного аккаунта:

• Минимум прав: role/drive.file или кастомные права вместо project/editor.
• Ограничьте доступ на уровне самого документа — делайте Share только для конкретного email сервисного аккаунта.

Конфиденциальность и соответствие (GDPR и другие)

• Если в Google Sheets содержатся персональные данные EU‑граждан, соблюдайте принципы: минимизация данных, ограничение хранения, контроль доступа.
• Документируйте основания обработки данных и сроки хранения. При необходимости предоставьте пользователям возможность удалить свои данные из таблицы.
• Для логов и бэкапов учитывайте требования локальных регуляторов: не храните логи с персональными данными дольше, чем нужно.

Отладка и распространённые ошибки

• Ошибка: No module named ‘gspread’ — активируйте виртуальное окружение и выполните pip install gspread.
• Ошибка с JSON ключом: проверьте путь и права доступа к файлу, убедитесь, что client_email соответствует тому, кому предоставлен доступ к таблице.
• Permission denied: проверьте, что вы поделились документом с client_email сервисного аккаунта.
• Rate limit / Quota: при массовых операциях используйте batch‑запросы (update_cells, update) и добавляйте задержки/ретраи.

Тесты и критерии приёмки

Критерии приёмки:

• Скрипт корректно авторизуется с использованием предоставленного JSON.
• Удачное чтение: чтение первой строки возвращает ожидаемую структуру (количество колонок, заголовки).
• Удачная запись: запись тестовой строки не ломает структуру и возвращается корректный статус (при необходимости восстановление).
• Проверка safety: при изменённой safety‑ячейке запись блокируется.

Примеры тестов (unit/integration):

• Unit: мок gspread.Client и проверить, что функция вызвала update_cells с ожидаемыми значениями.
• Integration (локально): запуск скрипта против тестовой таблицы и сверка результата.

Шаблоны, чек‑лист и роль‑ориентированные задачи

Чек‑лист перед запуском скрипта в продакшн:

• JSON‑ключ хранится в защищённом хранилище
• Таблица имеет резервную копию
• Есть проверка safety‑ячейки
• Логи записываются в централизованный сервис
• Наличие тестовых кейсов и среда тестирования

Роль‑ориентированные задачи:

• Dev: реализовать модульную структуру, покрыть unit‑тестами
• Data engineer: настроить расписание задач и мониторинг квот
• Analyst: подготовить шаблон таблицы и описать DDL (колонки/типы)
• Admin: управлять сервисными аккаунтами и ротацией ключей

Шаблон таблицы (пример):

Сохраняйте дату обработки в отдельной колонке processed_at для аудита.

Примеры дополнительных сниппетов

• Использование pandas для загрузки листа в DataFrame:

from gspread_dataframe import get_as_dataframe, set_with_dataframe
import pandas as pd

df = get_as_dataframe(sheet, evaluate_formulas=True, header=0)
# обработка через pandas
set_with_dataframe(sheet, df)

• Загрузка JSON‑ключа из переменной окружения (без записи в файл):

import os
import json
from oauth2client.service_account import ServiceAccountCredentials
from oauth2client.service_account import ServiceAccountCredentials

info = json.loads(os.environ['GCP_SERVICE_ACCOUNT_JSON'])
credentials = ServiceAccountCredentials.from_json_keyfile_dict(info, scopes)

Когда этот подход не подходит (ограничения)

• Большие объёмы данных: Google Sheets не предназначен для хранения больших наборов данных (>100k строк), используйте BigQuery, Cloud SQL или S3 + Parquet.
• Требования к транзакциям: Sheets не обеспечивает ACID‑транзакции.
• Чувствительные данные с жёсткими регуляторными ограничениями: лучше хранить в специализированных сертифицированных хранилищах.

Миграция и совместимость

• При переносе с oauth2client на google-auth: замените методы авторизации на google.oauth2.service_account.Credentials и используйте gspread.authorize с таким объектом.
• Если планируете перенести в GCP среду выполнения (Cloud Functions / Cloud Run), рассмотрите использование Service Account через IAM и Secret Manager.

Глоссарий

• Service account — автоматизированный аккаунт для серверного доступа к API.
• Scope — область доступа OAuth, определяет разрешения (чтение/запись).
• gspread — Python‑библиотека-обёртка для Google Sheets API.
• JSON‑ключ — файл с приватными ключами и идентификаторами сервисного аккаунта.

Краткое резюме

Работа с Google Sheets из Python проста: настройте сервисный аккаунт, поделитесь таблицей, используйте gspread для чтения и записи. Для продакшн‑использования добавьте проверку safety, логирование, ограничьте права сервисного аккаунта и храните ключи в безопасном хранилище.

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