Для определенных задач возникает необходимость из Пайтон (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
Благодарю за обратную связь. Рад, что статья помогла!