Skip to content

Instantly share code, notes, and snippets.

@potykion
Created Aug 4, 2021
Embed
What would you like to do?
Работа с Google таблицами с помощью Sheets API https://potyk.io/dev/python/gsheets
"""
Работа с Google таблицами с помощью Sheets API
https://potyk.io/dev/python/gsheets
"""
import os.path
from typing import List
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from openpyxl.utils.cell import column_index_from_string
# https://docs.google.com/spreadsheets/d/e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b/edit#gid=1340597691
TABLE = 'e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b'
SHEET = "Лист 1"
SHEET_ID = 1340597691
# Строка вида "Лист 1!D5:GJ396"
START_COLUMN, START_ROW = "D", 5
END_COL, END_ROW = "GJ", 396
RANGE_STR = F'{SHEET}!{START_COLUMN}{START_ROW}:{END_COL}{END_ROW}'
# Объект GridRange
# Индексы колонок и строк начинаются с 0,
# то есть для ячейки A1 будут индексы 0 и 0, для ячейки D5 будут индексы 3 и 4
START_COL_INDEX = column_index_from_string(START_COLUMN) - 1
START_ROW_INDEX = START_ROW - 1
END_COL_INDEX = column_index_from_string(END_COL) - 1
END_ROW_INDEX = END_ROW - 1
# GridRange наполовину инклюзивный - [startIndex, endIndex),
# т.е. включает начальный индекс диапазона и не включает конечный индекс,
# т. е. чтобы включить последний индекс нужно плюсануть 1
RANGE = {
"sheetId": SHEET_ID,
"startRowIndex": START_ROW_INDEX,
"startColumnIndex": START_COL_INDEX,
"endRowIndex": END_ROW_INDEX + 1,
"endColumnIndex": END_COL_INDEX + 1,
}
def value_by_path(cell, path: str):
"""
>>> value_by_path({"a": [1]}, "a.0")
1
"""
res = cell
path_parts = path.split(".")
for part in path_parts:
try:
part = int(part)
except ValueError:
...
try:
res = res[part]
except (IndexError, TypeError, KeyError):
return None
return res
def get_creds(secrets_file: str, scopes: List[str]) -> Credentials:
"""
Генерирует токен, необходимый для работы с апи
Токен читается из файла
Если этого файла нет, то используются доступы из {secrets_file}
После чтения доступов из файла в консоли появится ссылке, перейдя по которой надо будет авторизоваться
Если возникает надпись This app isn't verified, то надо прожать Advanced > Go to {Project Name} (unsafe)
https://developers.google.com/sheets/api/quickstart/python#this_app_isnt_verified
После авторизации токен сохранится на диск
:param secrets_file: путь к файлу с доступами, напр. client_secret_406798162311-63us552o41nrs0ashvt87h58gbgivjsh.apps.googleusercontent.com.json
:param scopes: права доступа, необходимые для работы с апи;
в данном случае будет достаточно [`https://www.googleapis.com/auth/spreadsheets`] - это права и на чтение и на запись данных в таблицу
"""
creds = None
if os.path.exists('token.json'):
creds = Credentials.from_authorized_user_file('token.json', scopes)
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file(secrets_file, scopes)
creds = flow.run_local_server(port=0)
with open('token.json', 'w') as token:
token.write(creds.to_json())
return creds
def main():
# Получение токена и создание апи-клиента
creds = get_creds(
secrets_file='client_secret_406798162311-63us552o41nrs0ashvt87h58gbgivjsh.apps.googleusercontent.com.json',
scopes=['https://www.googleapis.com/auth/spreadsheets']
)
service = build('sheets', 'v4', credentials=creds)
# Получение ячеек таблицы
resp = service.spreadsheets().get(
spreadsheetId=TABLE,
ranges=[RANGE_STR],
includeGridData=True,
).execute()
rows = resp["sheets"][0]["data"][0]["rowData"]
# Алгоритм замены ссылок
old_url = 'https://google.com/'
new_url = "https://yandex.ru/"
update_rows = []
for row in rows:
update_cells = []
for cell in row["values"]:
link = (
cell.get("hyperlink") or
value_by_path(cell, "textFormatRuns.0.format.link.uri") or
value_by_path(cell, "textFormatRuns.1.format.link.uri")
)
update_link = (
{"userEnteredFormat": {"textFormat": {"link": {"uri": new_url + link[len(old_url):]}}}}
if link and link.startswith(old_url)
else {}
)
update_cells.append(update_link)
update_rows.append({"values": update_cells})
# Вызов метода обновления ячеек
service.spreadsheets().batchUpdate(
spreadsheetId=TABLE,
body={
"requests": [
{
"updateCells": {
"rows": update_rows,
"fields": "userEnteredFormat.textFormat.link.uri",
"range": RANGE
}
}
]
}
).execute()
if __name__ == '__main__':
main()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment