使用公式更新单元格值会在Sheets API中产生撇号前缀 [英] Updating cell values with formulas results in apostrophe prefixes with Sheets API
问题描述
现在,我正在使用gspread和Google Sheets API更新单元格值,将cell.value
设置为等于特定公式的字符串.
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.
示例代码:
# 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?
我研究了值呈现选项和input_value,尽管这些选项似乎不适用于写入工作表.
I've looked into value rendering options and input_value, though these options seem to be unavailable for writing to sheets.
推荐答案
问题是更新单元格时没有指定值输入选项.就我而言,解决方案如下所示:worksheet.update_cells(cell_list, value_input_option='USER_ENTERED')
注意value_input_option标志,必须将其设置为'USER_ENTERED'
,以便像在Google Sheets UI中输入单元格一样对其进行更新.
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.
这篇关于使用公式更新单元格值会在Sheets API中产生撇号前缀的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!