使用公式更新单元格值会在Sheets API中产生撇号前缀 [英] Updating cell values with formulas results in apostrophe prefixes with Sheets API

查看:117
本文介绍了使用公式更新单元格值会在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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆