CODEWITHSUNDEEP
pythongoogle-sheetsgspread
pjk89
pjk89

Reputation: 11

Updating cells in a range with a for loop

I'm pretty new to coding so apologies if this is easy. I have two columns in google sheets and I want to add a formula into a third column that is something like this: =(E3*90)+(F3*10) - the values in the columns are grades and the 90 and 10 are weightings that are fixed.

I created a for loop to try and iterate through a range(3,90) as as it updates each cell in the column.

It prints the formula in every cell but it's only the last iteration '=(E89*90)+(F89*10)'

I managed to get this working by adding report.update_acell('E'+str(i),'=(E'+str(i)+'*90)+(F'+str(i)+'*10)') to the for loop but this create too many calls and causes problems.

sh = client.open("grading")
report = sh.worksheet("Report")
weighted = report.range('G3:G89')

for cell in weighted:
    for i in range(3,90):
        cell.value = '=(E'+str(i)+'*90)+(F'+str(i)+'*10)'

report.update_cells(weighted, value_input_option='USER_ENTERED')

What I'd like to see is every cell in the 'weighted' range be updated with a formula that looks at the two cells next to them and adds them into the formula so that a result is visible in weighted column. eg. row 3 should be =(E3*90)+(F3*10) row 4 should be =(E4*90)+(F4*10) and so on until the range is completed.

Upvotes: 0

Views: 576

Answers (1)

pjk89
pjk89

Reputation: 11

I fixed this after a lot of trial and error. For anyone who is trying to do the same here is my solution:

sh = client.open("grading")
report = sh.worksheet("Report")
weighted = report.range('G3:G89')

for i, cell in enumerate(weighted,3):
    cell.value = '=(E'+str(i)+'*90)+(F'+str(i)+'*10)'

report.update_cells(weighted, value_input_option='USER_ENTERED')

Upvotes: 1

Related Questions