How can I Export Pandas DataFrame to Google Sheets (specific cell) using Python?

Question

I can read data and write data to a google sheet.
I can also write a pandas dataframe to a google sheet cell(1,1).

I am now trying to write the same dataframe to a specific cell (like cell(20,20)) on a sheet.

resources:

this link is close, but only refers to the sheet and does not specify the cell: How can I Export Pandas DataFrame to Google Sheets using Python?
the docs are here, but the example seems to only refer to a sheet (so the result is again cell(1,1)): https://docs.gspread.org/en/latest/user-guide.html#using-gspread-with-pandas

what i have tried:

i have tried to modify the update_cell command with a dataframe.

# this works
sh.sheet1.update_cell(20,20,'hello world')

# but this fails

# example dataframe
d = {'col1': [1, 2], 'col2': [3, 4]}
df = pd.DataFrame(data=d)

# write to a specific cell
sh.sheet1.update_cell(20,20,df)

So my question is, how can i specify the cell that the pandas Dataframe is written to ?

The second answer to your linked question allows the top-left cell address to be specified; https://stackoverflow.com/a/68951536/53341 pyghseets manual; https://pygsheets.readthedocs.io/en/stable/worksheet.html#pygsheets.Worksheet.set_dataframe — MatBailie, Jan 31 '23 at 10:33
@Tasos, this was close, but not quite there. All the data of the dataframe went into one cell. So with `sh.update('b20', df.to_csv(index=False))` , the entire df went into cell `b20`... — D.L, Jan 31 '23 at 11:20
That's true. I haven't used it myself, but I was able to find this through github search. A python package called `gspread-dataframe` (https://github.com/robin900/gspread-dataframe) which has the functionality you need. With the `set_with_dataframe` you can specify the dataframe and the cell you want to start with. — Tasos, Jan 31 '23 at 12:05
@Tasos, it was a good starting point nevertheless, so appreciated. — D.L, Jan 31 '23 at 15:18

Tanaike · Accepted Answer · 2023-01-31T23:12:52.490

It seems that the data frame cannot be directly used to value of update_cell(row, col, value). Ref So, in your script, how about the following modification?

From:

sh.sheet1.update_cell(20,20,df)

To:

sh.sheet1.update("T20", [df.columns.values.tolist(), *df.values.tolist()], value_input_option="USER_ENTERED")

or, if you don't want to include the header row, please test the following modification.

sh.sheet1.update("T20", df.values.tolist(), value_input_option="USER_ENTERED")

I thought that 20,20 of sh.sheet1.update_cell(20,20,df) is a cell "T20".

Note:

From your showing script, I guessed that you are using gspread.

Reference:

update(range_name, values=None, **kwargs)

Added:

About your following additional question,

one question: is the user able to replace "t20" with a cell reference (like cell(10,20)) ?

If you want to use the coordinate like 20, 20 as "T20", how about the following modification?

sh.sheet1.update(gspread.utils.rowcol_to_a1(20, 20), [df.columns.values.tolist(), *df.values.tolist()], value_input_option="USER_ENTERED")

or

sh.sheet1.update(gspread.utils.rowcol_to_a1(20, 20), df.values.tolist(), value_input_option="USER_ENTERED")

In this case, gspread.utils.rowcol_to_a1(20, 20) is converted to "T20".

Reference:

gspread.utils.rowcol_to_a1(row, col)

this is correct, i have used gspread. if there is a better (or official) module please do say so. . . Also, i am marking this as the accepted answer as it resolves the. — D.L, Jan 31 '23 at 15:15
one question: is the user able to replace "t20" with a cell reference (like `cell(10,20)`) ? — D.L, Jan 31 '23 at 15:16
@D.L Thank you for replying. I'm glad your issue was resolved. About your additional question, I added one more modified script. Please confirm it. — Tanaike, Jan 31 '23 at 23:13

How can I Export Pandas DataFrame to Google Sheets (specific cell) using Python?

1 Answers1

From:

To:

Note:

Reference:

Added:

Reference: