Чтение гугл таблиц python

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

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

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

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

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

В меню слева выберите “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.

Кликаем на только что созданный аккаунт.

Переходим во вкладку KEYS. Жмем на ADD KEY. В появившемся меню выбираем Create new key.

Выбираем JSON и жмем CREATE.

Скачиваем json файл на свой компьютер.

Переходим во вкладку Details, копируем Email.

Переходим в таблицу, к которой у вас будет доступ. Жмем “Настройки доступа”, вводим скопированный Email и жмем “Готово”.

После этого вам будет предложено выбрать роль, выберите “Редактор”.

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

Подключение gspread в Python

Сначала вам нужно установить 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_list = sh.worksheets()

Создание рабочего листа

worksheet = sh.add_worksheet(title="Январь", rows="100", cols="20")
Code language: JavaScript (javascript)

Удаление рабочего листа

sh.del_worksheet(worksheet)
Code language: CSS (css)

Получение значения ячейки

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

Обновление ячеек

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

Источник

How do I access (read, write) Google Sheets spreadsheets with Python?

I am wondering if you can point me to an example of reading/writing to/from a google doc/spreadsheet using python. I did look at google docs API here https://developers.google.com/google-apps/spreadsheets/ but not sure if I hit the right link. Also an example will be of great help. What I am trying to do is query spreadsheets based on the different columns more like a SQL query and then do some downstream parsing with the data and put it into another spreadsheet or doc at google docs. Best, -Abhi

8 Answers 8

(Jun-Dec 2016) Most answers here are now out-of-date as: 1) GData APIs are the previous generation of Google APIs, and that’s why it was hard for @Josh Brown to find that old GData Docs API documentation. While not all GData APIs have been deprecated, all newer Google APIs do not use the Google Data protocol; and 2) Google released a new Google Sheets API (not GData). In order to use the new API, you need to get the Google APIs Client Library for Python (it’s as easy as pip install -U google-api-python-client [or pip3 for Python 3]) and use the latest Sheets API v4+, which is much more powerful & flexible than older API releases.

Here’s one code sample from the official docs to help get you kickstarted. However, here are slightly longer, more «real-world» examples of using the API you can learn from (videos plus blog posts):

• Migrating SQL data to a Sheet plus code deep dive post
• Formatting text using the Sheets API plus code deep dive post
• Generating slides from spreadsheet data plus code deep dive post
• Those and others in the Sheets API video library

The latest Sheets API provides features not available in older releases, namely giving developers programmatic access to a Sheet as if you were using the user interface (create frozen rows, perform cell formatting, resizing rows/columns, adding pivot tables, creating charts, etc.), but NOT as if it was some database that you could perform searches on and get selected rows from. You’d basically have to build a querying layer on top of the API that does this. One alternative is to use the Google Charts Visualization API query language, which does support SQL-like querying. You can also query from within the Sheet itself. Be aware that this functionality existed before the v4 API, and that the security model was updated in Aug 2016.

Also note that the Sheets API is primarily for programmatically accessing spreadsheet operations & functionality as described above, but to perform file-level access such as imports/exports, copy, move, rename, etc., use the Google Drive API instead. Examples of using the Drive API:

• Listing your files in Google Drive and code deep dive post
• Google Drive: Uploading & Downloading Files plus «Poor man’s plain text to PDF converter» code deep dive post (*)
• Exporting a Google Sheet as CSV blog post only

(*) — TL;DR: upload plain text file to Drive, import/convert to Google Docs format, then export that Doc as PDF. Post above uses Drive API v2; this follow-up post describes migrating it to Drive API v3, and here’s a developer video combining both «poor man’s converter» posts.

To learn more about how to use Google APIs with Python in general, check out my blog as well as a variety of Google developer videos (series 1 and series 2) I’m producing.

ps. As far as Google Docs goes, there isn’t a REST API available at this time, so the only way to programmatically access a Doc is by using Google Apps Script (which like Node.js is JavaScript outside of the browser, but instead of running on a Node server, these apps run in Google’s cloud; also check out my intro video.) With Apps Script, you can build a Docs app or an add-on for Docs (and other things like Sheets & Forms).

UPDATE Jul 2018: The above «ps.» is no longer true. The G Suite developer team pre-announced a new Google Docs REST API at Google Cloud NEXT ’18. Developers interested in getting into the early access program for the new API should register at https://developers.google.com/docs.

UPDATE Feb 2019: The Docs API launched to preview last July is now available generally to all. read the launch post for more details.

UPDATE Nov 2019: In an effort to bring G Suite and GCP APIs more inline with each other, earlier this year, all G Suite code samples were partially integrated with GCP’s newer (lower-level not product) Python client libraries. The way auth is done is similar but (currently) requires a tiny bit more code to manage token storage, meaning rather than our libraries manage storage.json , you’ll store them using pickle ( token.pickle or whatever name you prefer) instead, or choose your own form of persistent storage. For you readers here, take a look at the updated Python quickstart example.

Источник