1
  • 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:

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 ?

Improve this question
5
  • 1
    Try with this sh.update(cell, df.to_csv(index=False)) Commented Jan 31, 2023 at 10:31
  • The second answer to your linked question allows the top-left cell address to be specified; stackoverflow.com/a/68951536/53341 pyghseets manual; pygsheets.readthedocs.io/en/stable/… Commented Jan 31, 2023 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... Commented Jan 31, 2023 at 11:20
  • 1
    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 (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. Commented Jan 31, 2023 at 12:05
  • @Tasos, it was a good starting point nevertheless, so appreciated. Commented Jan 31, 2023 at 15:18

1 Answer 1

Reset to default
2

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:

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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.
one question: is the user able to replace "t20" with a cell reference (like cell(10,20)) ?
@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.
this was the final part and completes the question in full.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.