I'm writing a Python script that uploads a Polars DataFrame to Google Sheets and formats it accordingly. One of my goals is to create a summary row at the bottom of the table that sums the numerical values for each column.
Currently, I have the following code snippet that successfully constructs a summary row:
# Add a summary row at the end of the data
num_rows = len(data)
total_row = ['Grand Total', ""]
for col in range(2, len(header)):
total_formula = f'=SUM({chr(65 + col)}2:{chr(65 + col)}{num_rows})'
total_row.append(total_formula)
new_sheet.append_row(total_row)
The problem is, I've encountered an issue where this code creates a string in the grand total row that includes a leading single quote, like this:
'=SUM(F2:F47)
This prevents Google Sheets from executing it as a formula.
My Question: How can I modify this code to ensure that it writes an executable formula to Google Sheets without the leading single quote?
Any insights or suggestions would be greatly appreciated!
Additional Context: Packages use include
Data Structure: I’m using a Polars DataFrame to manage my data before uploading it to Google Sheets. Thank you!
The default value_input_option is set to RAW in gspread's append_row method. The input data won't be parsed, if set to RAW according to sheets API. Set it to USER_ENTERED:
new_sheet.append_row(total_row, value_input_option='USER_ENTERED')
Or
new_sheet.append_row(total_row, value_input_option=gspread.utils.ValueInputOption.user_entered)