Search code examples
pythongoogle-sheets-apigspread

Updating cell values with formulas results in apostrophe prefixes with Sheets API

I'm using gspread and the Google Sheets API to update cell values, setting cell.value equal to a string of a specific formula.

Example code:

# Calculates sum of cells in current row from column B to H
G_SHEETS_ROW_SUM_COMMAND = '''=SUM(INDIRECT(CONCATENATE("B",ROW(),":H",ROW())))'''

for cell in cell_list:
    cell.value = G_SHEETS_ROW_SUM_COMMAND

When my spreadsheet is populated, however, my command becomes prefixed with an apostrophe ('). This is presumably to keep the cell from being interpreted as a formula, but that's exactly what I'd like it to do.

Here's an example from my spreadsheet:

enter image description here

Is there a way to remove this apostrophe automatically?

I've looked into value rendering options and input_value, though these options seem to be unavailable for writing to sheets.

Solution

  • The problem was that I didn't specify a value input option when I updated my cells. In my case, the solution looks like this:

    worksheet.update_cells(cell_list, value_input_option='USER_ENTERED')

    Note the value_input_option flag, it must be set to 'USER_ENTERED' so that cells are updated just as if they were entered in the Google Sheets UI.