Для определенных задач возникает необходимость из Пайтон (Python) подключиться к Гугл Таблице. Например для того, чтобы получить данные из Google sheet или работать с Google Sheets API на Python. Тому, как это сделать и будет посвящен этот пост.
Данная статья появилась благодаря моему племяннику, которому для решения своих задач необходимо получать и обновлять данные в гугл таблице.
Я работаю с гугл таблицами и скриптами для автоматизации различных процессов, но не пишу на Python.
В ходе написания статьи попробуем вместе, используя минимум знаний о Python, разобраться с тем, каким образом подключиться к существующей гугл таблице и работать с данными.
Небольшое замечание. Если картинка на скриншоте нечеткая, попробуйте открыть ее в новой вкладке (правый клик мышки – “Открыть картинку в новой вкладке”)
Итак, вначале нам необходимо создать и настроить гугл проект.
Для этого переходим по ссылке
https://console.cloud.google.com/
В открывшемся окошке находим выпадающий список “Select a project” (Выбрать проект)
Кликаем и выбираем “NEW PROJECT ” (Новый проект)
Вводим (придумываем) имя нашему проекту и кликаем на “Create” (Создать)
Видим сообщение, что наш проект создан и кликаем на “Select project” (выбрать проект)
В следующем окне переходим на страницу с API
Включаем использование API нашим проектом
Далее нам необходимо найти API для работы с гугл таблицей. Для этого в поисковой строке набираем ключевое слово “drive” и кликаем на Enter
В результатах поиска находим и выбираем нужное нам API (Google Drive API)
Когда API выбрано, его необходимо включить для нашего проекта (сделать доступным)
Следующий этап – подключение интерфейса для работы непосредственно с гугл таблицами. Для этого в строке для поиска вбиваем слово “sheets”.
В результатах поиска находим “Google sheets API” и кликаем на ней
Делаем найденный интерфейс доступным для нашего проекта
После того, как мы сделали интерфейс доступным для нашего проекта, переходим в раздел “APIs & Services” и находим в нем раздел “Credentials”
В данном разделе нас интересует пункт “Create credentials”
После клика на “Create credentials” в выпадающем списке выбираем “Service account”
Выбираем имя учетной записи, описание и кликаем на “Create and continue” (Создать и продолжить)
На следующем этапе выбираем роль только-что созданной учетной записи. Нас интересует “Project -> Editor”
Делаем нужный выбор и жмем на “Continue” (Продолжить)
Теперь, когда мы создали учетную запись и определились с ролью, нам нужно создать ключ, который мы будем использовать для подключения к нашей таблице из нашего python-проекта.
Находим в окне нашего проекта пункт меню “Service Accounts” и кликаем по нему. В открывшемся списке находим нашу (ранее созданную) учетную запись и кликаем на ней
Переходим в меню “Keys”
И выбираем “Add key” (добавить ключ)
Создаем новый ключ с помощью пункта меню из выпадающего списка (“Create new key”)
Выбираем тип ключа (JSON) и кликаем на “Create”
Требуемый ключ создан и загружен на ваш компьютер
По сути, процес создания и настройки гугл проекта завершен. Переходим ко второму этапу – собственно созданию проекта на Python для работы с нашей таблицей.
Для нашего проекта я использовал IDE PyCharm, вы можете использовать среду разработки по своему вкусу.
Итак, создаем новый проект. Находим ранее загруженный ключ, переименовываем его (для удобства) и добавляем в наш проект
Гугл таблицу для тестирования я создал заранее. Нам необходимо дать разрешение на ее редактирование из нашего проекта.
Для этого находим email, под которой зарегистрировалась наша ранее созданная учетная запись – в подключенном к проекту файле с ключем копируем значение параметра “client_email”
И добавляем разрешение в нашей гугл таблице
Теперь, когда доступ к таблице из проекта разрешен, переходим непосредственно в сам код Python
Для работы с гугл таблицами нам необходимо импортировать в наш проект специальную библиотеку (gspread)
Это можно сделать с помощью директивы “import”
Пишем “import gspread”, наводим мышку на слово, подчеркнутое красным.
Немного ждем появления всплывающего меню и выбираем “install package gspread”.
В открывшемся окне выбираем “Install Anyway” (установить в любом случае)
Дожидаемся окончания установки
Теперь, когда библиотека установлена, можем подключиться к нашей таблице и работать с ней.
import gspread # импортируем библиотеку gs = gspread.service_account(filename='credits.json') # подключаем файл с ключами и пр. sh = gs.open_by_key('1sQWERTYvS3bCEEaJjQfwjDw5kXMMHcL6gb-qWe8rty') # подключаем таблицу по ID worksheet = sh.sheet1 # получаем первый лист
Чтобы получить все записи листа, используем метод “get_all_records”. Данный метод возвращает объект, который содержит записи указанного листа в виде “ключ-значение”
При помощи метода “get_all_values” можно получить все записи с заданного листа
Для примера рассмотрим еще парочку методов работы с гугл таблицей.
Получим первую строчку листа
Получим первую колонку листа
Получим заданную ячейку
Получим заданный диапазон
Вставить новую строку с данными
Добавить новую строку
Обновить ячейку
Удалить заданную строку
Это несколько основных методов работы с данными гугл таблицы, которые я использовал в качестве примера, чтобы подключиться к гугл таблице.
Код, который я использовал при этом, добавляю
# This is a Python script. import gspread # импортируем библиотеку gs = gspread.service_account(filename='credits.json') # подключаем файл с ключами и пр. sh = gs.open_by_key('1sQWERTYvS3bCEEaJjQfwjDw5kXMMHcL6gb-qWe8rty') # подключаем таблицу по ID worksheet = sh.sheet1 # получаем первый лист # res = worksheet.get_all_records() # считываем все записи (массив: ключ-значение) # res = worksheet.get_all_values() # считываем все значения # res = worksheet.row_values(1) # получаем первую строчку таблицы # res = worksheet.col_values(1) # получаем первую колонку таблицы # res = worksheet.get('A2') # получаем заданную ячейку # res = worksheet.get('A2:C2') # получаем заданный диапазон # print(res) # выводим в консоль # newRec = ["Антонов", "Самолет", 2500] # worksheet.insert_row(newRec, 2) # добавляем новые данные в строку 2 # worksheet.append_row(["Онищенко", "Велосипед", 220]) # добавить новую строку с данными # worksheet.update_cell(6, 3, 180) # обновить ячейку worksheet.delete_rows(3) # удалить строку номер 3
Надеюсь, информация будет вам полезной.
Пожелания и вопросы пишите в комментариях к данной статье.
Рекомендую почитать:
- “Готовые шаблоны CRM на базе Google таблиц”
- “10 бирж фриланса и удаленной работы для новичков и профи: список и рекомендации по заработку”
- “Скачать футажи, видео, видеошаблоны, презентации, графику и т.д.: цифровые ресурсы без ограничений”
Сергей 19th Жовтень 2021 , 18:57
Добавил в закладки). Спасибо!
Игорь Билецкий 19th Жовтень 2021 , 19:29
Спасибо за обратную связь.
Рад, что статья пригодилась.
Михаил 1st Січень 2022 , 20:56
1 вопрос- как указать несколько строк в res = worksheet.row_values(1)
Игорь Билецкий 2nd Січень 2022 , 13:09
Целью данной статьи было рассмотреть, как из python подключиться к гугл таблице.
Как работать с таблицей после подключения – рассмотрено несколько методов просто для примера.
Я пока не пишу на python, поэтому не могу что-то советовать.
В принципе, в интернете есть справочник по библиотеке gspread в целом и методу .row_values() в частности.
Навскидку, как вариант, можно получить заранее известный диапазон данных с помощью worksheet.get(‘A2:F22’). Думаю, что есть также варианты и в .row_values(), нужно изучать документацию.
Владимир 18th Січень 2022 , 19:23
Добрый вечер, выходит ошибка:
Traceback (most recent call last):
File “C:\Python\report\for_excel.py”, line 1, in
import gspread
File “C:\Users\PycharmProjects\pythonProject1\venv2\lib\site-packages\gspread\__init__.py”, line 16, in
from .auth import oauth, oauth_from_dict, service_account, service_account_from_dict
File “C:\Users\PycharmProjects\pythonProject1\venv2\lib\site-packages\gspread\auth.py”, line 14, in
from google_auth_oauthlib.flow import InstalledAppFlow
File “C:\Users\PycharmProjects\pythonProject1\venv2\lib\site-packages\google_auth_oauthlib\__init__.py”, line 21, in
from .interactive import get_user_credentials
File “C:\Users\PycharmProjects\pythonProject1\venv2\lib\site-packages\google_auth_oauthlib\interactive.py”, line 24, in
import google_auth_oauthlib.flow
File “C:\Users\PycharmProjects\pythonProject1\venv2\lib\site-packages\google_auth_oauthlib\flow.py”, line 68, in
import google.auth.transport.requests
File “C:\Users\PycharmProjects\pythonProject1\venv2\lib\site-packages\google\auth\transport\requests.py”, line 39, in
from requests.packages.urllib3.util.ssl_ import (
ModuleNotFoundError: No module named ‘requests.packages’
подскажите куда копать?
Игорь Билецкий 18th Січень 2022 , 21:15
Добрый вечер.
Я в самом начале статьи писал, что не программирую на python, а просто попытался разобраться, как из python подключиться к гугл таблице.
Заодно нарыл в интернете несколько методов, чтобы на практике проверить работу подключения.
Поэтому не могу вам что-то посоветовать.
Если будут вопросы по работе с гугл таблицами и написанием скриптов для них, прошу сюда:
https://forbiz-online.org/rabota-s-gugl-tablicami-raznye-voprosy-skripty-gotovye-resheniya/
Елена 7th Червень 2022 , 11:24
Спасибо, очень помогла ваша статья 🙂
Игорь Билецкий 9th Червень 2022 , 03:52
Я рад, что статья принесла пользу. Спасибо за обратную связь 🙂
Екатерина 20th Грудень 2022 , 10:51
Здравствуйте. Очень полезная статья, очень помогла. Не могли бы Вы подсказать,как работать со вторым листом таблицы?
Ігор Білецький 20th Грудень 2022 , 18:06
Добрый день, Екатерина.
Статья была написана исключительно, чтобы разобраться со способом подключения к гугл-таблице из Python.
Я собственно не пишу на этом языке.
В принципе, если логично подумать, то если мы подключаемся к первому листу с помощью
“worksheet = sh.sheet1 # получаем первый лист”, то подключиться ко второму листу:
“worksheet2 = sh.sheet2”.
По идее в переменной worksheet2 будет лежать второй лист.
Попробуйте так.
Екатерина 21st Грудень 2022 , 14:26
Спасибо за ответ!
«worksheet2 = sh.sheet2» не сработало, поэтому задавала вопрос. На всякий случай оставляю решение:
import gspread
gs = gspread.service_account(filename=’название_файла.json’) # подключаем файл с ключами и пр.
sh = gs.open_by_key(‘здесь ID таблицы’) # подключаем таблицу по ID
worksheet = sh.get_worksheet(0) # получаем первый лист
worksheet2 = sh.get_worksheet(1) # получаем второй лист
res = worksheet.get_all_records()
res2 = worksheet2.get_all_records()
# res = worksheet.row_values(1) #получить первую строку таблицы
new_rec = [‘Антон’, 797800000, 9111111, ‘ул.Кирова’, ‘Кафе’, ‘1 чашка в год’, ‘Комментарий’, ‘username’] #создаем список, который заполнит 2ю строку в таблице
worksheet.insert_row(new_rec, 2) #добавляем строку в таблицу, пишем название списка для записи и указываем номер строки, в которую вставляем
print(res)
print(res2)
Ігор Білецький 21st Грудень 2022 , 19:52
Спасибо за полезную информацию. Думаю, что она обязательно пригодиться нашим читателям. Возможно, я также начну писать на Python.
Михаил 19th Березень 2023 , 22:00
Самая толковая и понятная статья на тему телеграм-Бота+ГуглТаблица. Спасибо!
Ігор Білецький 20th Березень 2023 , 07:23
Благодарю за обратную связь. Рад, что статья помогла!