CODEWITHSUNDEEP
pythonpandasdataframegoogle-sheetsgoogle-sheets-api
darren
darren

Reputation: 5694

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

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

resources:

what i have tried:

# 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 ?

Upvotes: 1

Views: 2837

Answers (1)

Tanaike
Tanaike

Reputation: 201408

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:

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:

Upvotes: 2

Related Questions