Updating cell values with formulas results in apos

问题:

Right now, 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 is prefixed with an apostrophe (presumably to keep the cell from being interpreted as a formula, though that's exactly what I'd like it to do).

Here's an example from my spreadsheet:

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.

回答1:

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.