Начинаем работать с Google Sheets API v4

Статья будет полезна тем, кто хочет создавать Гугл-таблицы, вносить или забирать данные через API.

Может пригодиться для аналитики или автоматизации рутины: например, создания типовых документов. Продвинутым тру-программистам лучше не читать — эта запись своего рода конспект собственных экспериментов, чтобы ничего не забыть. Надеюсь, новичкам вроде меня с её помощью будет легче разобраться.

Понадобится anaconda, jupyter notebook и python 3.

Создаём аккаунт и авторизируемся

Нужна почта на gmail.
Заходим на Google Developers Console и создаём новый проект.
Включаем Drive API и Sheets API.
Сохраняем учётные данные и закрытый ключ:

После всех манипуляций вы скачаете json-файл. Он понадобится вот тут:

!pip install --upgrade google-api-python-client

from datetime import datetime
import calendar
import locale
locale.setlocale(locale.LC_TIME, 'ru_RU.UTF-8') 
from pprint import pprint
import httplib2
import apiclient.discovery
from oauth2client.service_account import ServiceAccountCredentials
CREDENTIALS_FILE = 'XXX.json'  #  ← имя скаченного файла с закрытым ключом
credentials = ServiceAccountCredentials.from_json_keyfile_name(CREDENTIALS_FILE, ['https://www.googleapis.com/auth/spreadsheets',                                                                               'https://www.googleapis.com/auth/drive'])
httpAuth = credentials.authorize(httplib2.Http())
service = apiclient.discovery.build('sheets', 'v4', http = httpAuth)

Достаём данные

Создайте новый Googlesheet, дайте разрешение на редактирование всем у кого есть ссылка:

Скопируйте id из адресной строки:

Заполните ячейку A1 чем-нибудь неожиданным:

И попробуйте что-то вроде:

spreadsheetId = '1vR7sByPDE10wZSyz7I8hqBS8j5DgTNerFH2X_1sbVu8'
range_name = 'Лист1!A:G'
table = service.spreadsheets().values().get(spreadsheetId=spreadsheetId, range=range_name).execute()
table

Ура, забрали данные:

Пусть в исходной табличке будет табличка вида:

Ставим pandas, делаем dataframe:

import pandas as pd

campaign_list = []
visits_list = []
cost_list= []

for i in table['values'][1:]:

    campaign_list.append( i[0] )
    visits_list.append( i[1] )
    cost_list.append( i[2] )

right = pd.DataFrame( list(zip(campaign_list, visits_list, cost_list) ),columns=[ 
                                                table['values'][0][0],
                                                table['values'][0][1],
                                                table['values'][0][2]])
right

Вуаля ;-)

Отправляем данные

Создаём новый spreadsheet:

title = 'Hello, world'
list_title = '1'

spreadsheet = service.spreadsheets().create(body = {
    'properties': {'title': title, 'locale': 'ru_RU'},
    'sheets': [{'properties': {'sheetType': 'GRID',
                               'sheetId': 0,
                               'title': list_title,
                               'gridProperties': {'hideGridlines': True, 'rowCount': 5, 'columnCount': 5}}}]
}).execute()

Файл создан — смотрим, что получилось:

Если перейти по ссылке, увидим «Доступ закрыт» — всё верно, файл принадлежит сервисному аккаунту. Даём себе разрешение:

email = 'xxx@gmail.com' # ← ваша почта

driveService = apiclient.discovery.build('drive', 'v3', http = httpAuth)
shareRes = driveService.permissions().create(
    fileId = spreadsheet['spreadsheetId'],
    #body = {'type': 'anyone', 'role': 'writer'},  # доступ на редактирование кому угодно
    body = {'type': 'user', 'role': 'writer', 'emailAddress': email},
    fields = 'id'
).execute()

Теперь ссылка открывается:

Вставляем значение в выбранную ячейку:

cell = 'A1'
value = '1,618'

service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheet['spreadsheetId'], 
body = {
"valueInputOption": "USER_ENTERED",
"data": [
    {"range": list_title + "!" + cell,
     "majorDimension": "ROWS",
     "values": [[value]]}

]
}).execute()

Жирный шрифт:

DATA = {'requests': [{'repeatCell':
        {'range': 
            { 
              'startRowIndex': 0, # номер строки (нумерация с 0) с которой включительно будет применено форматирование
              'endRowIndex': 1, # номер строки до которой будет применено форматирование, не включительно
                
              'startColumnIndex': 0, # номер столбца (нумерация с 0)…
              'endColumnIndex': 1
            },
        
        'cell':  {'userEnteredFormat': {'textFormat': {'bold': True}}},

        'fields': 'userEnteredFormat', # другие параметры форматирования ячейки будут сброшены
    }}
]}

service.spreadsheets().batchUpdate(spreadsheetId = spreadsheet['spreadsheetId'], body=DATA).execute()

Число с тремя знаками после запятой, горизонтальное выравнивание влево, вертикальное по верху, 9-й шрифт, отменить жирность, перенос по словам:

DATA = {'requests': [{'repeatCell':
        {'range': 
            { 
              'startRowIndex': 0, # номер строки (нумерация с 0) с которой включительно будет применено форматирование
              'endRowIndex': 1, # номер строки до которой будет применено форматирование, не включительно
                
              'startColumnIndex': 0, # номер столбца (нумерация с 0)…
              'endColumnIndex': 1
            },
        
        'cell':  {'userEnteredFormat': {"numberFormat": { "type": "NUMBER", "pattern": "#,##0.000"}, 
                                        "verticalAlignment": "TOP", 
                                        "horizontalAlignment": "LEFT", 
                                        "wrapStrategy": "WRAP", 
                                        "textFormat": {"fontSize": 9,'bold': False}
                                              }},

        'fields': 'userEnteredFormat', # другие параметры форматирования ячейки будут сброшены
    }}
]}

service.spreadsheets().batchUpdate(spreadsheetId = spreadsheet['spreadsheetId'], body=DATA).execute()

Другие возможности — в описании метода:

В правой можно потренироваться в составлении запроса

Задаём ширину столбца или высоту строки:

service.spreadsheets().batchUpdate(spreadsheetId = spreadsheet['spreadsheetId'], body = {
  "requests": [


    {
      "updateDimensionProperties": {
        "range": {
          "sheetId": 0,
          "dimension": "COLUMNS",  # COLUMNS — столбцы, ROWS — строки
          "startIndex": 0,         # Нумеруются с нуля
          "endIndex": 1            # startIndex берётся включительно, endIndex — не включительно
        },
        "properties": {
          "pixelSize": 200     # Например, 200 пикселей
        },
        "fields": "pixelSize" 
      }
    }
  ]
}).execute()

That’s it!

Ещё по теме:
Эта статья на 50% — упрощенный копипаст вот этого поста на Хабре
Описание метода batchUpdate на сайте Google

Поделиться
Отправить
Запинить
Популярное