Blog

The script below allows you to access Google Spreadsheets files to extract data from the spreadsheet sheets and write new information.

In order for the script to work, the following conditions must be met:

• Create Service Account: https://console.cloud.google.com/home/dashboard
• Create a new project, install virtual environment and install the Google API software package (see video below)
• Create a file in the service account credentials.json
• Upload the credentials.json file to the project root
• Create a Google Spreadsheet file and give the bot access to this file (see video below)
• Copy files main.py and a1range.py and copy the code below

Code for main.py file:

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()

Code for a1range.py file:

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,
        )

You can find more information in this video (RU voice):