I have two google spreadsheets:
QC- many columns, I want to check if a value from column 4 appears in the second spreadsheet lastEdited_PEID; if it does, it would put 'Bingo!' in column 14 of the same row where the value was found
lastEdited- one column, long spreadsheets of values
I achieve that with the following code:
#acces the documents on Drive
QC = gc.open_by_key("FIRST KEY").sheet1
lastEdited = gc.open_by_key("SECOND KEY").sheet1
#get values from columns and convert to lists
QC_PEID = QC.col_values(4)
lastEdited_PEID = lastEdited.col_values(1)
#iterate by rows and check if value from each row appears in the second document
for value in QC_PEID:
ind = QC_PEID.index(value)
if value in lastEdited_PEID:
QC.update_cell(ind, 14, 'Bingo!')
So it does the job but does it very slowly (about 5 minutes). I am concerned about the speed because I have to perform the operation for about 50 spreadsheets (avg. 6000 rows each).
I tried to remove the element from the second list when found (it can only appear once) with the following code in the loop:
for value in QC_PEID:
ind = QC_PEID.index(value)
if value in lastEdited_PEID:
QC.update_cell(ind, 14, 'Bingo!')
**lastEdited_PEID.remove('value')**
I thought it would make it faster as the reference list would be shorter but surprisingly it takes even more.
What could I do to make the process quicker?