使用gspread API实现Google Sheets数值取整及解决DataFrame上传后精度异常问题

I’ve run into this exact floating-point precision quirk before—even when your DataFrame looks like it has clean two-decimal values, hidden extra digits can pop up once uploaded to Google Sheets. Here are two solid fixes to resolve this:

1. Explicitly Round Your DataFrame Before Upload #

First, make sure the underlying values in your DataFrame are actually limited to two decimal places. Pandas might display two decimals but store more precision under the hood. Add a rounding step before uploading:

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

# Round all numeric columns to 2 decimal places explicitly
rounded_data = data.round(2)

# Upload the cleaned DataFrame
d2g.upload(rounded_data, sheet.id, 'test_name', clean=True, credentials=creds, col_names=True, row_names=False)

This ensures the values sent to Google Sheets are exactly two decimal places, cutting off any hidden precision that might cause display issues.

2. Force Google Sheets Cell Formatting After Upload #

If rounding alone doesn’t lock in the display (or you want to guarantee consistent formatting), use gspread_formatting to set the number format directly in the sheet:

# After uploading your DataFrame, grab the target worksheet
worksheet = sheet.worksheet('test_name')

# Define a number format that shows exactly 2 decimal places
two_decimal_fmt = cellFormat(
    numberFormat=numberFormat(
        type='NUMBER',
        pattern='#,##0.00'
    )
)

# Calculate the range to format (adjust based on your data size)
# Assumes column names are in row 1, data starts at row 2
last_col = chr(ord('A') + len(rounded_data.columns) - 1)
last_row = len(rounded_data) + 1
target_range = f"A2:{last_col}{last_row}"

# Apply the format to the entire data range
format_cell_range(worksheet, target_range, two_decimal_fmt)

This will override how Google Sheets displays the values, ensuring every cell in your data range shows exactly two decimal places, no matter the underlying value.

Quick Note on the Root Cause #

Floating-point numbers in pandas often store more precision than what’s visible in your printout. When uploaded, Google Sheets parses this raw value instead of the truncated displayed version. Combining explicit rounding with cell formatting covers both the data being sent and how it’s presented in Sheets.

内容的提问来源于stack exchange，提问作者Jonas Palačionis