# Updating Google Sheets table efficiently

Program description:

A program accepts a list (uTable). The list consists of at least 4 different lists (rows), each with 7 elements (cells). Check whether the value in [7] is zero and assign a new random variable to it on the basis of what [3] is: 1, 2, 3, 4 or 5. A chance should be used to calculate the chance of the assigning operation. A cell should be updated using sheet_store.update_cell(i, k, value). A check for admin permissions should be included ( if adminId in admins:).

My solution:

 if adminId in admins:
uTable = sheet_store.get_all_values()
for i in range(len(uTable)):
row = uTable[i]
if (row[7] == '0'):
chance = random.uniform(0, 1)
if (row[3] == '5'):
if chance <= 0.05:
print('5 EXECUTED!')
sheet_store.update_cell(i+1, 8, 1)
elif (row[3] == '4'):
random_1 = randint(1, 3)
if chance <= 0.1:
print('4 EXECUTED!')
sheet_store.update_cell(i+1, 8, random_1)
elif (row[3] == '3'):
random_2 = randint(1, 5)
if chance <= 0.4:
print('3 EXECUTED!')
sheet_store.update_cell(i+1, 8, random_2)
elif (row[3] == '2'):
random_3 = randint(1, 10)
if chance <= 0.6:
print('2 EXECUTED!')
sheet_store.update_cell(i+1, 8, random_3)
elif (row[3] == '1'):
random_4 = randint(1, 20)
if chance <= 1:
print('1 EXECUTED!')
sheet_store.update_cell(i+1, 8, random_4)
return True
else:
return False


Input: (TABLE IS TRANSFERRED AS LIST) (Row and column with index 0 are not included)

0    1              2   3       4          5           6          7
1 артифак           0   5   незвестен   a_1.jpg 07/08/20/15/30    0
2 Съедобный предмет 1   3   бафф        a_2.jpg                   0
3 описание          2   4   неизвестен  a_3.jpg время обновления  1
4 Отличный компан   3   2   компаньон                             7


Runtime:

1 EXECUTED!
2 EXECUTED!

success
0.5s


Is there any way to improve the code and make the program run faster? Thank you in advance.

• Please choose a better title for your question. The convention on this site is to describe the program's purpose in the title. Without this convention, every question here would be "can this program be made faster, cleaner", which would be confusing. Dec 2, 2020 at 6:53
• The current question title, which states your concerns about the code, applies to too many questions on this site to be useful. The site standard is for the title to simply state the task accomplished by the code. Please see How do I ask a good question?. Dec 2, 2020 at 8:40
• I've changed the title to make it more specific. Next time please follow the indications provided by RolandIllig and BCdotWEB in the comments.
– Marc
Dec 3, 2020 at 6:28

These are my suggestions regarding performances:

## Generate random numbers only if needed

The new value for the column 7 can be generated after you are sure that needs to be updated. From:

elif (row[3] == '4'):
random_1 = randint(1, 3)
if chance <= 0.1:
print('4 EXECUTED!')
sheet_store.update_cell(i+1, 8, random_1)


To:

elif (row[3] == '4'):
if chance <= 0.1:
print('4 EXECUTED!')
sheet_store.update_cell(i+1, 8, randint(1, 3))


In this way, the new random value is generated only if chance<=0.1. Same for the other cases.

## API call cost

I assume that you are using gspread. The doc says:

Under the hood, gspread uses Google Sheets API v4. Most of the time when you call a gspread method to fetch or update a sheet gspread produces one HTTP API call.

Given this statement, your code sends 1 API call with get_all_values() and 1 APIs call for each row to update with update_cell(). In the worst case, there are 5 API calls.

One suggestion is to generate the list of all new values for the column 7 and send a single update call with update(), so that the total number of API calls will be reduced to 2.