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

Question

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.

Answer 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.

Answer 2

I had a similar issue, but was looking to just update a single cell using a formula. I found a couple of ways to do it:

# 'A1' notation
worksheet.update('A1', 7)
# raw=False is what does the trick.
worksheet.update('B1', "=A1 * 3", raw=False)

# Or if you prefer (row, col) notation
worksheet.update_cell(2, 1, 7)
worksheet.update_cell(2, 2, "=A2 * 3")

Will result in the following