Search code examples
pythongoogle-sheets-apigspread

Found cell name isn't the same as the on in Google sheets

I'm using gspread to edit my google sheet but I'm having some trouble with the cell name call out.

I use this code to find and show what cell the word 'LOCATE' is in: 

cell_list = worksheet.findall('LOCATE')
print(cell_list)

getting the result:

[<Cell R12C1 'LOCATE'>]

I'm using cell 'A12' for testing so I see 'R12C1' stands for Row 12 Column 1

I've have success writing to that cells using the code:

worksheet.update_acell('A12', "TEST")

however if I try to test editing the cell 'R12C1' with this code it doesn't work:

worksheet.update_acell('R12C1', "TEST")

Is there a way I can instead have the 'findall' function return and 'A12' style cell name call out?

I'm using this for now but it feels a bit sloppy and was wondering if anyone has any tips?

alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
deconstruct = str(cell_list[0:1])
row_value = deconstruct[8:10]
col_value = alphabet[(int(deconstruct[11:13])-1)]
cell = col_value + row_value
worksheet.update_acell(cell, "TEST")
Solution

  • How about this answer?

    When I saw the script of gspread, findall retrieves the values of the sheet using the method of spreadsheets.values.get of Sheets API and the value is searched from the retrieved values which is an 2 dimensional array. So in this case, the coordinates of the cells are returned as the R1C1 type. If you want to retrieve the A1Notation, I think that your script can be useful.

    In order to use the values retrieved by findall, if you want to make the script be more simple, how about using update_cell or update_cells instead of update_acell? In this case, the sample script is as follows.

    Sample script:

    At the following sample scripts, it supposes that the worksheet has a cell including the value of LOCATE.

    Pattern 1:

    In this pattern, the cell coordinate is retrieved from cell_list.

    cell_list = worksheet.findall('LOCATE')

value = cell_list[0]
worksheet.update_cell(value.row, value.col, 'TEST')

    Pattern 2:

    In this pattern, the cell retrieved by findall is updated.

    cell_list = worksheet.findall('LOCATE')

cell_list[0].value = 'TEST'
worksheet.update_cells(cell_list)

    Note:

    • These sample scripts supposes that you have already been able to put and get values for Spreadsheet using gspread.

    References:

    If I misunderstood your question and this was not the direction you want, I apologize.