4

I am writing a pandas dataframe to google sheets using gspread:

from gspread_formatting import *
import gspread
from df2gspread import df2gspread as d2g
import pandas as pd

d2g.upload(data, sheet.id, 'test_name', clean=True, credentials=creds, col_names=True, row_names=False)

While the pandas dataframe is rounded to 2 decimal points the value in google sheets sometimes have more than that.

df

a               b
100.56          600.79

Result in google sheets:

aa                b
100.5616          600.79

And I can't find any information how I can round a value using python gspread API.

Jonas Palačionis
  • 4,591
  • 4
  • 22
  • 55
  • you cannot round the values using python gspread API, but you can do it beforehand. Actually, I think the safe way to achieve what you need is to convert your data to formatted string with two decimal places, and send string values to gspread. – GoranK Aug 30 '21 at 10:12
  • I am rounding every value to 2 decimal points in my df, before pushing it to google sheets, but I had to modify the `d2g` library to be able to push `int` values rather than `str` – Jonas Palačionis Aug 30 '21 at 10:19
  • can you set the ValueInputOption to USER_ENTERED somewhere? https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption – GoranK Aug 30 '21 at 11:35

1 Answers1

5

If you get the worksheet element you can use format to achieve what you want.

sh = gc.open("sheet_name")
worksheet = sh.get_worksheet(0) # your sheet number
worksheet.format('A', {'numberFormat': {'type' : 'NUMBER', 'pattern': '0.0#'}})
alparslan mimaroğlu
  • 1,450
  • 1
  • 10
  • 20