1

I am trying to find an item inside a worksheet using gspread but only for a specific column. For some reason, when I do

ws.find(item, in_column = 6)

it givens an

IndexError: list index out of range

but when I do it with 2, it works fine. I tested it, and for any column 5 and over, it does not work. Anyone know why this is? I can send the full error message and code if needed.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
Nebula
  • 23
  • 3
  • Can you provide your sample Spreadsheet as an image? – Tanaike Mar 30 '22 at 00:11
  • I am not sure how to send an image, but here is the link to it: https://docs.google.com/spreadsheets/d/10NWrdQURMkqQQR6odypuRpQvcysOMHMHiLlSiqdjg9c/edit#gid=1288556638 – Nebula Mar 30 '22 at 00:48
  • Thank you for replying. I could see your sample Spreadsheet. When I saw it, there are various sheets. In your question, what sheet did you test your script using? I would like to confirm the sheet for correctly replicating your issue of `IndexError: list index out of range`. – Tanaike Mar 30 '22 at 00:49
  • I iterated through all the sheets, here is my full code: `cell = None wslist = hoards.worksheets() for ws in wslist: cell = ws.find(knife, in_column = 6) if (cell != None): worksheet = ws print("found cell") break` – Nebula Mar 30 '22 at 00:53
  • Thank you for replying. What sheet did the error of `IndexError: list index out of range` occur at? – Tanaike Mar 30 '22 at 00:56
  • It should be at the second one, but I am not sure how to check. A quick search on the API reference doc didn't yield any results on how to get the worksheet name. – Nebula Mar 30 '22 at 01:01
  • Thank you for replying. From your replying, I checked your provided script using your sample Spreadsheet. By this, I proposed a modified script as an answer. Could you please confirm it? If that was not useful, I apologize. – Tanaike Mar 30 '22 at 01:08

1 Answers1

1

When I tested all sheets in your sample Spreadsheet using ws.find(item, in_column = 6), I confirmed that an error occurred on the 1st sheet ("Info" sheet). Also, I confirmed that in your 1st sheet of "Info", the merged cells are existing. In this case, it seems that such an error occurs.

If you are not required to use ws.find(item, in_column = 6) in the 1st sheet, as a simple modification, how about the following modification?

Modified script 1:

cell = None
wslist = hoards.worksheets()
for ws in wslist:
    if ws.title != "Info":
        cell = ws.find(knife, in_column=6)
        if (cell != None):
            worksheet = ws
            print("found cell")
            break
  • This script excludes the sheet using the sheet name "Info".

Modified script 2:

cell = None
wslist = hoards.worksheets()
for i, ws in enumerate(wslist):
    if i > 0:
        cell = ws.find(knife, in_column=6)
        if (cell != None):
            worksheet = ws
            print("found cell")
            break
  • This script excludes the sheet using the sheet index.

Note:

  • This modified script can be used for your sample Spreadsheet. If your Spreadsheet is changed, this script might not be able to be used. Please be careful about this.
Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • @Nebula Thank you for replying and testing it. I'm glad your issue was resolved. Thank you, too. – Tanaike Mar 30 '22 at 01:09