Setting Column In Google Sheets API (with Python) To Be Number-formatted

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 e

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

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