Друзья, привет. После последней публикации многие из вас выразили желание получить от меня пошаговые инструкции по работе с Google Таблицами (Google Sheets) через Python. Как вы догадались, вот инструкция.

Существует достаточно много методов взаимодействия с Google Sheets при помощи Python. Сегодня я рассмотрю, как мне кажется, самый простой и самый доступный модуль - Gspread.

Подготовка

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

1. Создать аккаунт в Google (если у вас его еще нет).

2. Получить авторизационные данные для программного подключения к гугл таблицам.

Получаем авторизационные данные

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

• Авторизуемся в Google.

• Переходим на Google Cloud Platform и проверяем, что выполнена авторизация в аккаунт Google: Google Cloud Platform API Dashboard

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

  Для этого переходим по ссылке: Создание проекта. На стартовом экране указываем имя проекта и нажимаем на "CREATE":

• Выпускаем токен для доступа к таблицам.

  Переходим в "Credentials" по ссылке: Google Cloud Credentials. Обратите внимание, после перехода по вашей ссылке у вас автоматически должен сформироваться путь, содержащий название вашего проекта. Если это не так, убедитесь, что выбран корректный проект.

  У меня путь к данному разделу выглядит так:

  https://console.cloud.google.com/apis/credentials?project=yakvenalex-habr-project

  

  Наша задача на этом этапе – создать и загрузить credentials. Нас интересует "Service Accounts". Жмем на "CREATE SERVICE ACCOUNT".

  В открывшемся окне указываем имя своего аккаунта и нажимаем на "DONE".

Зачем нужен Service Account?

Service Account (сервисный аккаунт) – это специальный тип аккаунта, который используется для автоматизированного доступа к сервисам Google. Он позволяет вашему приложению или скрипту взаимодействовать с Google Sheets от имени этого аккаунта, без необходимости использования ваших личных учетных данных. Это обеспечивает безопасность и удобство при интеграции и автоматизации работы с Google Таблицами.

• Создаем ключ.

  Для этого нажимаем на три вертикальные точки возле созданного аккаунта и выбираем "Manage keys".

  

  Затем нажимаем "Add key" и выбираем "Create new key".

  

  На следующем экране выбираем JSON и нажимаем "CREATE".

  

  Начнется загрузка JSON файла. Этот JSON файл мы будем использовать с библиотекой Gspread для авторизации.

• Копируем email аккаунта.

  Его можно взять из JSON файла (значение ключа client_email) или с главной страницы аккаунта: Service Accounts.

  

  Далее вы будете давать доступ к таблицам Google не от своего имени, а от имени этого созданного аккаунта (email).

• Активируем Google Sheets API. Для этого переходим по ссылке Google Sheets API и нажимаем на "ENABLE". Обратите внимание. Под каждый сервисный аккаунт необходимо выполнять активацию плагина Google Sheets API

• Создаем первую таблицу и даем доступ для Service Account email.

  Таблицу я назову "Habr table".

  Переходим в Google Sheets: Google Sheets.

  Нажимаем на "+" для создания новой таблицы:

  

  Даем имя таблице (двойной клик на "Untitled spreadsheet") и вводим новое название таблицы:

  

  Открываем доступ к таблице созданному аккаунту. Для этого жмем на "Share", вставляем email созданного аккаунта и нажимаем "Send".

  

  Проверяем доступы, повторно нажав на "Share".

  

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

  Далее я покажу, как подключиться к Google Sheets и выполнить основные операции: чтение, запись, удаление и обновление данных.

Gspread: Коротко о главном

Gspread – это популярная Python-библиотека для работы с Google Таблицами, выпущенная сторонними разработчиками (не Google). Она упрощает взаимодействие с Google Sheets, предоставляя удобные методы для чтения, записи и обновления данных. С Gspread можно легко автоматизировать задачи, связанные с таблицами, например, собирать данные, создавать отчеты или интегрировать Google Sheets с другими сервисами. Библиотека поддерживает авторизацию через сервисные аккаунты, что делает её безопасным и мощным инструментом для разработчиков.

Начинаем писать код.

Я создам отдельный проект в Pycharm под написание кода и предлагаю вам сделать так же.

Назову проект GspreadTech. Во внутрь проекта помещу скачанный JSON и создам файл main.py, в котором и буду вести всю разработку. Страница проекта на моем GitHub: GspreadTech.

Установка:

pip install -r req.txt

Выполним импорты:

from gspread import Client, Spreadsheet, Worksheet, service_account

• Client:

  • Это класс, представляющий клиентскую сессию для взаимодействия с Google Sheets. Используется для аутентификации и управления доступом к таблицам.

• Spreadsheet:

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

• Worksheet:

  • Это класс, представляющий конкретный рабочий лист внутри таблицы. Используется для выполнения операций с данными, таких как чтение, запись и обновление.

• service_account:

  • Это функция для аутентификации с использованием сервисного аккаунта. С её помощью создается клиентская сессия (Client) для взаимодействия с Google Sheets от имени сервисного аккаунта.

Напишем функцию, которая позволит выполнить инициализацию клиента для работы с Google Sheets через загруженный JSON:

def client_init_json() -> Client:
    """Создание клиента для работы с Google Sheets."""
    return service_account(filename='yakvenalex-habr-project.json')

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

Если мы просто вызовем эту функцию и распечатаем результат, то увидим следующее:

<gspread.client.Client object at 0x00000166052A0380>

Такой ответ говорит нам о том, что авторизация прошла корректно.

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

Теперь подключимся к нашей таблице и получим с нее данные. Далее я рассмотрю 2 способа подключения: через id таблицы и через ссылку.

Для получения ссылки на таблицу (а из нее ещё и ID) просто копируем ссылку с браузера на таблицу. В моем случае вид такой:

Обратите внимание. В ссылке на таблицу уже указывется ee ID.

Пишем код для доступа к таблице:

def get_table_by_url(client: Client, table_url):
    """Получение таблицы из Google Sheets по ссылке."""
    return client.open_by_url(table_url)


def get_table_by_id(client: Client, table_url):
    """Получение таблицы из Google Sheets по ID таблицы."""
    return client.open_by_key(table_url)

Тестируем:

def test_get_table(table_url: str, table_key: str):
    """Тестирование получения таблицы из Google Sheets."""
    client = client_init_json()
    table = get_table_by_url(client, table_url)
    print('Инфо по таблице по ссылке: ', table)
    table = get_table_by_id(client, table_key)
    print('Инфо по таблице по id: ', table)


if __name__ == '__main__':
    test_get_table(table_link, table_id)

Результат выполнения кода:

Инфо по таблице по ссылке:  <Spreadsheet 'Habr table' id:1lzQ78nxKShICHQaVW2ZuKw5QBRR1q4gAzPPbVOHsd4Q>
Инфо по таблице по id:  <Spreadsheet 'Habr table' id:1lzQ78nxKShICHQaVW2ZuKw5QBRR1q4gAzPPbVOHsd4Q>

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

Для начала давайте узнаем количество листов в таблице и их название.

def get_worksheet_info(table: Spreadsheet) -> dict:
    """Возвращает количество листов в таблице и их названия."""
    worksheets = table.worksheets()
    worksheet_info = {
        "count": len(worksheets),
        "names": [worksheet.title for worksheet in worksheets]
    }
    return worksheet_info


def main():
    # Создаем клиента и открываем таблицу
    client = client_init_json()
    table = get_table_by_id(client, table_id)

    # Получаем информацию о листах
    info = get_worksheet_info(table)
    print(f"Количество листов: {info['count']}")
    print("Названия листов:")
    for name in info['names']:
        print(name)


if __name__ == '__main__':
    main()

Смотрим на результат:

Количество листов: 1
Названия листов:
Sheet1

Если я добавлю новые листы или изменю название, то при повторном вызове получу актуальную информацию. Дело в том, что каждое обращение клиента к таблице происходит в live-режиме.

Очень важно после изменения имени листа указать это в коде. У меня из практики были случаи, когда клиенты переименовывали лист, а после возвращались ко мне с претензиями, мол не работает. Внимательно.

Количество листов: 2
Названия листов:
РабочийЛист1
РабочийЛист2

Для того чтоб клиент понимал с каким листом работать, в дальнейшем, необходимо будет явно указывать лист. Речь про такие операции, как: добавление записи, удаление записи, удаление листа и прочее (рассмотрим далее). А сейчас я покажу как создать новый лист через нашего клиента:

def create_worksheet(table: Spreadsheet, title: str, rows: int, cols: int):
    """Создание листа в таблице."""
    return table.add_worksheet(title, rows, cols)

Тестируем

def test_create_worksheet():
    client = client_init_json()
    table = get_table_by_id(client, table_id)
    rez = create_worksheet(table, 'НовыйРабочийЛист', rows=15, cols=10)
    print(rez)


test_create_worksheet()

Результат:

<Worksheet 'НовыйРабочийЛист' id:2135109655>

Обратите внимание. Листу присвоеy id. Смотрим в таблицу:

Для удаления листа можно использовать такую функцию:

def delete_worksheet(table: Spreadsheet, title: str):
    """Удаление листа из таблицы."""
    table.del_worksheet(table.worksheet(title))

Пример использования:

def test_dell_worksheet():
    client = client_init_json()
    table = get_table_by_id(client, table_id)
    delete_worksheet(table, title='НовыйРабочийЛист')


test_dell_worksheet()

На этом примере вы могли заметить конструкцию table.worksheet(title). Благодаря такому простому обращению мы указываем клиенту текущий рабочий лист. Далее, когда мы будем добавлять данные и удалять их мы будем использовать похожее указание на текущий рабочий лист.

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

def insert_one(table: Spreadsheet, title: str, data: list, index: int = 1):
    """Вставка данных в лист."""
    worksheet = table.worksheet(title)
    worksheet.insert_row(data, index=index)

Обратите внимание. Мы бы могли в эту функцию передавать worksheet, но, с опыта скажу что удобнее явно передавать имя листа. Меньше проблем потом.

Индексы в таблицах начинаются не с 0, как мы привыкли, а с 1. По умолчанию запись будет добавлена в первую строку (индекс 1), но я его решил явно передать чтоб путанницы не было. Если укажем 2, то запись пойдет во вторую строку и так далее.

def test_add_data():
    """Тестирование добавления данных в таблицу."""
    client = client_init_json()
    table = get_table_by_id(client, table_id)
    worksheet_info = get_worksheet_info(table)
    print('Инфо по таблице: ', worksheet_info)
    insert_one(table=table,
               title=worksheet_info['names'][0],
               data=['name', 'address', 'email', 'phone_number', 'birth_date', 'company', 'job'])


test_add_data()

А вот тут давайте остановимся подробнее. Для получения имени листа я воспользовался функцией worksheet_info и достал данные по нулевому индексу. Сделал это просто для демонстрации, а для работы было бы просто достаточно указания названия листа.

data - это список и в данной функции при его помощи мы создаем некий header для нашей таблицы. Выполним код и посмотрим что получилось:

Мы видим что данные добавлены. Теперь было бы не плохо заполнить их информацией и понять, а как вообще добавлять данные массово, не по 1 строке.

Напишем код для создания фейковых пользователей при помощи библиотеки faker.

from faker import Faker


def generate_fake_user():
    fake = Faker('ru_RU')

    return {
        'name': fake.name(),
        'address': fake.address(),
        'email': fake.email(),
        'phone_number': fake.phone_number(),
        'birth_date': fake.date_of_birth().strftime("%d.%m.%Y"),
        'company': fake.company(),
        'job': fake.job()
    }


def get_fake_users(count: int):
    return [generate_fake_user() for _ in range(count)]

Смысл данного кода в генерации фейковых данных по русским пользователям. На выходе укажем число пользователей и получим список из питоновских словарей с данными.

Сам код поместил в отдельный файл, чтоб не перегружать main.py.

Первый вариант функции (простой)

def add_data_to_worksheet_var_1(table: Spreadsheet, title: str, data: List[Dict], start_row: int = 2) -> None:
    """
    Добавляет данные на рабочий лист в Google Sheets.

    :param table: Объект таблицы (Spreadsheet).
    :param title: Название рабочего листа.
    :param data: Список словарей с данными.
    :param start_row: Номер строки, с которой начнется добавление данных.
    """
    try:
        worksheet = table.worksheet(title)
    except exceptions.WorksheetNotFound:
        worksheet = create_worksheet(table, title, rows=100, cols=20)

    # Преобразуем список словарей в список списков для добавления через insert_rows
    headers = list(data[0].keys())
    rows = [[row[header] for header in headers] for row in data]

    # Вставляем строки с данными в рабочий лист
    worksheet.insert_rows(rows, row=start_row)

В данном коде я прописал условие, при котором, при отсутствии листа, он будет создан. Для этого используется функция, которую мы уже расмотрели.

Далее мы преобразуем список словарей в список списков (rows), подходящий для метода insert_rows, а после уже идет сам метод insert_rows. Тестируем:

def test_add_many_data():
    """Тестирование добавления данных на рабочий лист."""
    client = client_init_json()
    table = get_table_by_id(client, table_id)
    users_data = get_fake_users(10)
    add_data_to_worksheet_var_1(table=table,
                                title='РабочийЛист1',
                                data=users_data)


test_add_many_data()

При помощи функции get_fake_users мы создали 10 пользователей, а после, без особых проблем, добавили их на рабочий лист с именем 'РабочийЛист1'.

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

def add_data_to_worksheet_var_2(table: Spreadsheet, title: str, data: List[Dict], start_row: int = 2) -> None:
    """
    Добавляет данные на рабочий лист в Google Sheets.

    :param table: Объект таблицы (Spreadsheet).
    :param title: Название рабочего листа.
    :param data: Список словарей с данными.
    :param start_row: Номер строки, с которой начнется добавление данных.
    """
    try:
        worksheet = table.worksheet(title)
    except exceptions.WorksheetNotFound:
        worksheet = create_worksheet(table, title, rows=100, cols=20)

    headers = data[0].keys()
    end_row = start_row + len(data) - 1
    end_col = chr(ord('A') + len(headers) - 1)

    cell_range = f'A{start_row}:{end_col}{end_row}'
    cell_list = worksheet.range(cell_range)

    flat_data = []
    for row in data:
        for header in headers:
            flat_data.append(row[header])

    for i, cell in enumerate(cell_list):
        cell.value = flat_data[i]

    worksheet.update_cells(cell_list)

Новый функционал:

• Определяет диапазон ячеек, которые будут обновлены, на основе размера данных.

• Преобразует список словарей в плоский список значений для обновления ячеек.

• Обновляет ячейки в указанном диапазоне значениями из данных (используя метод update_cells).

Как вы видите, данный метод немного сложнее в синтаксисе, но, в то же время, он более гибкий. Кроме того, на примере данного метода вы увидели как просиходит обновление данных в ячейках (метод update_cells).

Пишем функцию для получения данных:

def extract_data_from_sheet(table: Spreadsheet, sheet_name: str) -> List[Dict]:
    """
    Извлекает данные из указанного листа таблицы Google Sheets и возвращает список словарей.

    :param table: Объект таблицы Google Sheets (Spreadsheet).
    :param sheet_name: Название листа в таблице.
    :return: Список словарей, представляющих данные из таблицы.
    """
    worksheet = table.worksheet(sheet_name)
    rows = worksheet.get_all_records()
    return rows

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

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

Тестируем:

def test_get_data():
    """Тестирование извлечения данных из таблицы Google Sheets."""
    client = client_init_json()
    table = get_table_by_id(client, table_id)
    data = extract_data_from_sheet(table, 'РабочийЛист1')
    for i in data:
        print(i)


test_get_data()

Результат:

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

Для получения данных из таблицы Google Sheets без использования метода get_all_records(), можно воспользоваться более низкоуровневыми методами библиотеки gspread, такими как get для получения диапазона ячеек. Это позволяет более гибко управлять процессом извлечения данных. Вот пример функции, которая извлекает данные из таблицы, используя метод get:

def extract_data_from_sheet_var_2(table: Spreadsheet, sheet_name: str) -> List[Dict]:
    """
    Извлекает данные из указанного листа таблицы Google Sheets и возвращает список словарей.

    :param table: Объект таблицы Google Sheets (Spreadsheet).
    :param sheet_name: Название листа в таблице.
    :return: Список словарей, представляющих данные из таблицы.
    """
    worksheet = table.worksheet(sheet_name)
    headers = worksheet.row_values(1)  # Первая строка считается заголовками

    data = []
    rows = worksheet.get_all_values()[1:]  # Начинаем считывать с второй строки

    for row in rows:
        row_dict = {headers[i]: value for i, value in enumerate(row)}
        data.append(row_dict)

    return data

Давайте рассмотрим тему с удалением данных из таблицы. Тут будет 2 варианта:

• Удаление данных из заданного диапазона ячеек на указанном рабочем листе таблицы Google Sheets.

• Удаление всех данных из указанного рабочего листа таблицы Google Sheets.

def clear_range(table: Spreadsheet, sheet_name: str, start_cell: str, end_cell: str) -> None:
    """
    Удаляет данные из заданного диапазона ячеек на указанном рабочем листе таблицы Google Sheets.

    :param table: Объект таблицы Google Sheets (Spreadsheet).
    :param sheet_name: Название листа в таблице.
    :param start_cell: Начальная ячейка диапазона (например, 'A1').
    :param end_cell: Конечная ячейка диапазона (например, 'B10').
    """
    worksheet = table.worksheet(sheet_name)
    cell_list = worksheet.range(f"{start_cell}:{end_cell}")
    for cell in cell_list:
        cell.value = ''
    worksheet.update_cells(cell_list)
    print(f"Данные в диапазоне {start_cell}:{end_cell} на листе '{sheet_name}' были успешно удалены.")

Давайте удалим данные в диапазоне A10:G11

Тестируем:

def test_dell_data():
    """Тестирование удаления данных по указанному диапазону."""
    client = client_init_json()
    table = get_table_by_id(client, table_id)
    clear_range(table=table, sheet_name="РабочийЛист1", start_cell="A10", end_cell="G11")


test_dell_data()

Смотрим:

Данные в диапазоне A10:G11 на листе 'РабочийЛист1' были успешно удалены.

Теперь удалим все данные с указанного листа:

def clear_sheet(table: Spreadsheet, sheet_name: str) -> None:
    """
    Удаляет все данные из указанного рабочего листа таблицы Google Sheets.

    :param table: Объект таблицы Google Sheets (Spreadsheet).
    :param sheet_name: Название листа в таблице.
    """
    worksheet = table.worksheet(sheet_name)
    worksheet.clear()
    print(f"Все данные на листе '{sheet_name}' были успешно удалены.")

Тестируем удаление всех данных:

def test_dell_all_data():
    """Тестирование удаления данных по указанному диапазону."""
    client = client_init_json()
    table = get_table_by_id(client, table_id)
    clear_sheet(table=table, sheet_name="РабочийЛист1")


test_dell_all_data()

Смотрим:

Все данные на листе 'РабочийЛист1' были успешно удалены.

Ссылка на полный код: GspreadTeach

Заключение

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

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

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

До скорого!

P.S. Дорогие друзья! Я хочу выразить искреннюю благодарность каждому из вас за комментарии, лайки, подписки и особенно за подержку рублем. Все это, действительно, бесценно. В связи с тем, что я практически вошел в топ-100 авторов (на данный момент я на 101 месте) и достиг первой сотни подписчиков, я обещаю продолжать делиться своими знаниями и опытом, совершенствуя свой контент. Спасибо вам!