将Google Sheets API(使用Python)中的列设置为数字格式 [英] Setting column in Google Sheets API (with Python) to be number-formatted

问题描述

我正在尝试使用API​​(特别是Sheets API v.4和Python 3.6.1)在Google表格中格式化数字列.我的部分非功能性代码如下.我知道它正在执行，因为设置了列的背景色，但是数字仍然显示为文本，而不是数字.

I'm trying to format a column of numbers in Google Sheets using the API (Sheets API v.4 and Python 3.6.1, specifically). A portion of my non-functional code is below. I know it's executing, as the background color of the column gets set, but the numbers still show as text, not numbers.

换一种说法，我试图获得等同的效果，即单击列标题(A，B，C或其他)，然后在GUI中选择格式"->数字"->数字"菜单项.

Put another way, I'm trying to get the equivalent of clicking on a column header (A, B, C, or whatever) then choosing the Format -> Number -> Number menu item in the GUI.

def sheets_batch_update(SHEET_ID,data):
    print ( ("Sheets: Batch update"))
    service.spreadsheets().batchUpdate(spreadsheetId=SHEET_ID,body=data).execute() #,valueInputOption='RAW'

data={
  "requests": [
    {
      "repeatCell": {
        "range": {
          "sheetId": all_sheets['Users'],
          "startColumnIndex": 19,
          "endColumnIndex": 20
        },
        "cell": {
          "userEnteredFormat": {
            "numberFormat": {
                "type": "NUMBER",
                "pattern": "#,##0",
            },
            "backgroundColor": {
              "red": 0.0,
              "green": 0.4,
              "blue": 0.4
            },
          }
        },
        "fields": "userEnteredFormat(numberFormat,backgroundColor)"
      }
    },   
  ]
}
sheets_batch_update(SHEET_ID, data)

推荐答案

问题很可能是您的数据当前存储为字符串，因此不受数字格式的影响.

The problem is likely that your data is currently stored as strings and therefore not affected by the number format.

"userEnteredValue": {
  "stringValue": "1000"
},
"formattedValue": "1000",
"userEnteredFormat": {
  "numberFormat": {
    "type": "NUMBER",
    "pattern": "#,##0"
  }
},

当您通过用户界面设置数字格式(格式>数字> ... )时，它实际上一次完成了两件事:

When you set a number format via the UI (Format > Number > ...) it's actually doing two things at once:

1. 设置数字格式.
2. 如果可能，将字符串值转换为数字值.

您的API调用仅执行#1，因此当前设置有字符串值的任何单元格都将保留为字符串值，因此不受数字格式的影响.一种解决方案是遍历受影响的值，如果单元格包含数字，则将stringValue移至numberValue.

Your API call is only doing #1, so any cells that are currently set with a string value will remain a string value and will therefore be unaffected by the number format. One solution would be to go through the affected values and move the stringValue to a numberValue if the cell contains a number.

这篇关于将Google Sheets API(使用Python)中的列设置为数字格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！