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