Setting column in Google Sheets API (with Python)

问题:

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.

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)

回答1:

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. Setting the number format.
2. Converting string values to number values, if possible.

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.

回答2:

To flesh out the answer from Eric Koleda a bit more, I ended up solving this two ways, depending on how I was getting the data for the Sheet:

First, if I was appending cells to the sheet, I used a function:

def set_cell_type(cell_contents):
    current_cell_contents=str(cell_contents).replace(',', '')
    float_cell=re.compile("^\d+\.\d+$")
    int_cell=re.compile("^\d+$")

    if int_cell.search(current_cell_contents):
        data = {"userEnteredValue": {"numberValue": int(current_cell_contents)}}
    elif float_cell.search(current_cell_contents):      
        data =  {"userEnteredValue": {"numberValue": float(current_cell_contents)}}
    else:
        data =  {"userEnteredValue": {"stringValue": str(cell_contents)}}

    return data

To format the cells properly. Here's the call that actually did the appending:

rows = [{"values": [set_cell_type(cell) for cell in row]} for row in daily_data_output]
data = { "requests": [ { "appendCells": { "sheetId": all_sheets['Daily record'], "rows": rows, "fields": "*", } } ], }
sheets_batch_update(SHEET_ID,data)

Second, if I was replacing a whole sheet, I did:

#convert the ints to ints and floats to floats
float_cell=re.compile("^\d+\.\d+$")
int_cell=re.compile("^\d+$")
row_list=error_message.split("\t")
i=0
while i < len(row_list):
    current_cell=row_list[i].replace(',', '') #remove the commas from any numbers
    if int_cell.search(current_cell):
        row_list[i]=int(current_cell)
    elif float_cell.search(current_cell):       
        row_list[i]=float(current_cell)
    i+=1
error_output.append(row_list)

then the following to actually save error_output to the sheet:

data = {'values': [row for row in error_output]}
sheets_update(SHEET_ID,data,'Errors!A1')

those two techniques, coupled with the formatting calls I had already figured out in my initial question, did the trick.