使用gspread_formatting无法将Google Sheets单元格转为百分比格式

Key Issues & Fixes #

1. Ensure Cell Value is Stored as a Number
   If you set the cell value as a string (e.g., '0.1'), Google Sheets won't apply percentage formatting correctly. Update the value as a numeric type with the right input option:

   worksheet.update('A1', 0.1, value_input_option='USER_ENTERED')
   

   The USER_ENTERED option tells Google Sheets to parse the value as if you typed it manually, ensuring it's stored as a number.

2. Correct gspread_formatting Syntax
   Verify your NumberFormat definition. Both of these variants should work:

   • Using PERCENT type with explicit pattern:

     from gspread_formatting import format_cell_range, CellFormat, NumberFormat
     
     fmt = CellFormat(numberFormat=NumberFormat(type='PERCENT', pattern='0.00%'))
     format_cell_range(worksheet, 'A1', fmt)
     

   • Using CUSTOM type with percentage pattern (useful for edge cases):

     fmt = CellFormat(numberFormat=NumberFormat(type='CUSTOM', pattern='0.00%'))
     format_cell_range(worksheet, 'A1', fmt)
     

3. Fix Batch Update Range
   Your original batch update code targeted rows 2-3 and columns B-C (since Google Sheets uses 0-based indices). To target cell A1, adjust the range parameters:

   spreadsheet = client.open_by_key(spreadsheet_id)
   requests = [
       {
           'repeatCell': {
               'range': {
                   'sheetId': worksheet.id,
                   'startRowIndex': 0,  # Row 1 (0-based)
                   'endRowIndex': 1,
                   'startColumnIndex': 0,  # Column A (0-based)
                   'endColumnIndex': 1
               },
               'cell': {
                   'userEnteredFormat': {
                       'numberFormat': {
                           'type': 'PERCENT',
                           'pattern': '0.00%'
                       },
                       'textFormat': {
                           'bold': True
                       }
                   }
               },
               'fields': 'userEnteredFormat.numberFormat,userEnteredFormat.textFormat'
           }
       }
   ]
   spreadsheet.batch_update({'requests': requests})
   

Why This Works #

The primary issue is almost always a non-numeric cell value—Google Sheets only applies numeric formatting to cells containing numbers. The batch update failure was due to targeting the wrong range, while the gspread_formatting issue likely stemmed from either a string value or incorrect format type/pattern combination.

内容的提问来源于stack exchange，提问作者glitchwizard