Python gspread - loop through rows - update a cell with the sum or difference of other two cells in same row

Question

I would like to populate a google spreadsheet so that for each row of a certain column, the for loop inserts the sum or difference between two other cells of the same row and I can't figure it out for the life of me. Sorry, I'm new to programming.

My code looks like this (I've omitted some columns from the spreadsheet):

import gspread
from google.oauth2.service_account import Credentials

scopes = ["https://www.googleapis.com/auth/spreadsheets"]
creds = Credentials.from_service_account_file("credentials.json", scopes=scopes)
client = gspread.authorize(creds)

sheet_id = "url"
workbook = client.open_by_key(sheet_id)

worksheet_list = map(lambda x: x.title, workbook.worksheets())
new_worksheet_name = "template"

# check if new sheet exists already
if new_worksheet_name in worksheet_list:
    sheet = workbook.worksheet(new_worksheet_name)
else:
    sheet = workbook.add_worksheet(new_worksheet_name, rows=100, cols=30)

values = [
    ["Starting funds", "Bet", "Funds after bet"],
]

sheet.clear()

sheet.update(values, f"A1:Z{len(values)}")

sheet.update_acell("A2", 250)
sheet.update_acell("B2", 10)

I would like the cell C2 to display the sum (or difference) of A2 and B2, the cell C3 be the sum (or difference) of A3 and B3 and so on

For the difference between cells, I tried:

rows = sheet.get_all_values()
for row in rows[1:]:
    sheet.update_cell(row, 3, "=A:A-B:B")

But I get the following traceback:

  File "path", line 72, in <module>
    sheet.update_cell(row, 3, "=A:A-B:B")
  File "path\.venv\Lib\site-packages\gspread\worksheet.py", line 751, in update_cell
    range_name = absolute_range_name(self.title, rowcol_to_a1(row, col))
                                                 ^^^^^^^^^^^^^^^^^^^^^^
  File "path\.venv\Lib\site-packages\gspread\utils.py", line 306, in rowcol_to_a1
    if row < 1 or col < 1:
       ^^^^^^^
TypeError: '<' not supported between instances of 'list' and 'int'type here

Answer 1

Your error is happening because the update_cell method expects two integers as its first two arguments: the row number and the column number. However, you're passing a list (row, which is a row from your spreadsheet) and an integer (3 for the column number) to update_cell.

for i, row in enumerate(rows[1:], start=2):
    formula = f"=A{i}-B{i}"
    sheet.update_cell(i, 3, formula)