Блог

Скрипт, представленный ниже, позовляет осуществлять достуа к файлам Google Spreadsheets для извлечения данных с листов электронной таблицы и записи новой информации.

Для того, чтобы скримт мог работать необхомо выполнить следующие условия:

• Создать сервисный аккаунт: https://console.cloud.google.com/home/dashboard
• Создать новый проект, установить виртуальное окружение и установить программый пакет Google API (см. видео)
• В сервисном аккаунте создать файл credentials.json
• Выгрузить файл credentials.json в корневой каталог проекта
• Создать файл Google Spreadsheet и дать доступ боту к этому файлу (см. видео)
• Скопировать файлы main.py и a1range.py и скопировать в них код, представленный ниже

Код для файла main.py:

from a1range import A1Range
import os.path
from googleapiclient.discovery import build
from google.oauth2 import service_account

SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
SERVICE_ACCOUNT_FILE = os.path.join(BASE_DIR, 'credentials.json')

credentials = service_account.Credentials.from_service_account_file(
        SERVICE_ACCOUNT_FILE, scopes=SCOPES)

# The ID and range of a sample spreadsheet.
SAMPLE_SPREADSHEET_ID = '1KKOM9tpdCKtQmxyKkP_mcEMj1zreHyZ72cLL0ENXSHw'
SAMPLE_RANGE_NAME = 'Sheet1'


service = build('sheets', 'v4', credentials=credentials).spreadsheets().values()

# Call the Sheets API
# result = service.get(spreadsheetId=SAMPLE_SPREADSHEET_ID,
#                      range=SAMPLE_RANGE_NAME).execute()

# data_from_sheet = result.get('values', [])

array = {'values': [[5, 6, None, 100], ['=SUM(A1:A4)', '=SUM(B1:B4)']]}
range_ = A1Range.create_a1range_from_list('Sheet1', 4, 1, array['values']).format()
response = service.update(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                          range=range_,
                          valueInputOption='USER_ENTERED',
                          body=array).execute()

Код для файла a1range.py:

from dataclasses import dataclass


@dataclass
class A1Range:
    sheet_name: str
    start_row: int
    start_col: int
    end_row: int
    end_col: int

    def format(self) -> str:
        start = f"{self.col_number_to_letter(self.start_col)}{self.start_row}"
        end = f"{self.col_number_to_letter(self.end_col)}{self.end_row}"
        return f"{self.sheet_name}!{start}:{end}"

    def iter_rows(self):
        return range(self.start_row, self.end_row + 1)

    def iter_cols(self):
        return range(self.start_col, self.end_col + 1)

    @staticmethod
    def col_number_to_letter(j: int) -> str:
        if 1 <= j <= 26:
            return chr(ord('A') + j - 1)
        elif 27 <= j <= 26 * 26:
            first_letter = chr(ord('A') - 1 + (j - 1) // 26)
            second_letter = chr(ord('A') + (j - 1) % 26)
            return first_letter + second_letter
        else:
            raise ValueError(f"Col number is out of range: {j!r}")

    @staticmethod
    def col_letter_to_number(letters: str) -> int:
        letters = letters.upper()
        if len(letters) == 1 and (ord(letters) < ord('A') or ord(letters) > ord('Z')):
            raise ValueError(f"Col letter is out of range: {letters!r}")

        if len(letters) == 2 and (ord(letters[1]) < ord('A') or ord(letters[1]) > ord('Z')):
            raise ValueError(f"The second Col letter is out of range: {letters!r}")

        if len(letters) == 1:
            return ord(letters) - ord('A') + 1
        elif len(letters) == 2:
            return (ord(letters[0]) - ord('A') + 1) * 26 + ord(letters[1]) - ord('A') + 1

    @staticmethod
    def extract_letters(text) -> str:
        only_letters = ''
        for t in text:
            if t.isalpha():
                only_letters += t
        return only_letters

    @staticmethod
    def extract_digits(text) -> str:
        only_digits = ''
        for t in text:
            if t.isdigit():
                only_digits += t
        return only_digits

    @classmethod
    def parse_a1_range(cls, a1: str):
        if "!" in a1 and ":" in a1:
            sheet_name, cell_range = a1.split('!')
            range_start, range_end = cell_range.split(':')
            start_col, start_row = cls.extract_letters(range_start), cls.extract_digits(range_start)
            end_col, end_row = cls.extract_letters(range_end), cls.extract_digits(range_end)
            return cls(
                sheet_name=sheet_name,
                start_col=cls.col_letter_to_number(start_col),
                start_row=int(start_row),
                end_col=cls.col_letter_to_number(end_col),
                end_row=int(end_row),
            )
        else:
            raise ValueError(f'Error! For method "parse_a1_range()" must be full address!')

    @classmethod
    def create_a1range_from_list(cls, sheet_name, from_row, from_col, array):
        """
        The method find coordinates in format A1Notation for a list with data for inserting into a Google Sheet
        :param sheet_name: sheet name in Google Sheet
        :param from_col: the column coordinate of the upper left corner
        :param from_row: the row coordinate of the upper left corner
        :param array: a list with data for inserting in Google Sheet
        :return: coordinates for Google Sheet in format A1Notation
        """
        count_rows = len(array)
        count_cols = 0
        for row in array:
            if len(row) > count_cols:
                count_cols = len(row)
        return cls(
            sheet_name=sheet_name,
            start_col=from_col,
            start_row=from_row,
            end_col=from_col+count_cols-1,
            end_row=from_row+count_rows-1,
        )

Более подробно о работе скрипта рассказано в этом видео: