使用gspread库向Google Sheet导入数据时货币格式不生效问题

问题描述 #

我有一组CSV数据，第一列为服务名称，其余列均为美元金额。使用Python的gspread库将CSV数据导入Google Sheet后，尝试为金额列单元格应用货币格式，代码能正常输出格式设置提示，但实际单元格的货币格式并未生效。

原因分析 #

默认情况下，append_row()方法使用value_input_option="RAW"参数，会将CSV中的金额以纯文本形式导入单元格。而Google Sheet的货币格式仅对数值型单元格生效，文本型单元格即使设置了格式也不会显示货币样式。

解决方案 #

将spreadsheet.sheet1.append_row(row)修改为spreadsheet.sheet1.append_row(row, value_input_option="USER_ENTERED")。该参数会让Google Sheet像用户手动输入内容一样解析数据，自动将文本形式的金额转换为数值类型，此时再应用货币格式即可生效。

相关CSV数据 #

"Service Name   ap-northeast-1","2023-03","2023-04","2023-05","2023-06","2023-07","2023-08","2023-09","2023-10","2023-11","2023-12","2024-01","2024-02"
"AWS Direct Connect","1575.24","1512.77","1629.79","1497.54","1504.33","1484.96","1543.41","1510.99","1404.84","1428.71","1424.15","1361.66"
"AWS ELB","76.29","75.02","80.48","79.06","71.27","72.74","74.61","77.1","72.42","78.28","73.62","73.42"
"AWS S3","51.77","53.71","52.56","53.83","55.7","53.57","54.26","55.99","55.97","56.97","56.75","56.94"
"AWS VPC","0","0","0","0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","48.73"
"EBS Volumes gp2","403.58","414.48","414.48","414.48","427.1","438.48","438.48","438.48","438.48","438.48","438.48","438.48"
"EBS Volumes gp3","206.21","206.21","206.21","206.21","206.21","206.21","206.21","206.21","207.42","208.13","208.13","208.13"
"EBS Volumes","491.52","491.52","491.52","491.52","491.52","491.52","491.52","491.52","491.52","491.52","491.52","491.52"
"EC2 - Other","1512.1","1502.28","1523.26","1513.06","1518.93","1523.01","1533.47","1525.14","1502.29","1503.11","1492.42","1485.31"
"EC2","1235.61","1159.1","1175.3","1151.3","1155.16","1141.6","1123.86","1160.83","1120.61","1481.42","1939.9","1320.95"
"PublicIPv4:InUseAddress","0","0","0","0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","48.73"
"Other          ap-northeast-1","0.0","0.0","-0.01","-0.01","0.01","0.0","-0.0","0.0","0.0","-0.0","0.0","-48.73"
"TOTAL:         ap-northeast-1","4451.01","4302.88","4461.38","4294.78","4305.4","4275.88","4329.61","4330.05","4156.13","4548.49","4986.84","4347.01"

修改前后代码对比 #

修改前（无效代码）

import gspread
import csv

... 全局变量等代码 ...

with open('your_csv_file.csv', 'r', encoding='utf-8') as csv_file:
    csv_reader = csv.reader(csv_file)
    for row in csv_reader:
        if not row:  # 检查是否为空行
            print(f"Adding blank row: {row}.")
            spreadsheet.sheet1.append_row(
                ['----', '----', '----', '----', '----', '----', '----', '----', '----', '----', '----', '----',
                 '----'])
            row_count += 1
        else:
            while True:
                try:
                    print(f"Adding row {row}")
                    # 默认使用RAW模式，金额以文本导入
                    added_row = spreadsheet.sheet1.append_row(row)
                    row_count += 1
                    if "Service Name" in row[0]:  # 检查首列是否包含表头标识
                        print(f"make HEADER BOLD row: {row}.")
                        spreadsheet.sheet1.format(f"A{row_count}:M{row_count}", service_name_row_format)
                    else:
                        print(f"set $$ format for row_count {row_count}")
                        spreadsheet.sheet1.format(f'B{row_count}:M{row_count}', currency_format)
                except Exception as e:
                    print(f"Error adding row: {e}, retrying...")
                    continue
                break

... 其他无关代码 ...

修改后（生效代码）

import gspread
import csv

... 全局变量等代码 ...

with open('your_csv_file.csv', 'r', encoding='utf-8') as csv_file:
    csv_reader = csv.reader(csv_file)
    for row in csv_reader:
        if not row:  # 检查是否为空行
            print(f"Adding blank row: {row}.")
            spreadsheet.sheet1.append_row(
                ['----', '----', '----', '----', '----', '----', '----', '----', '----', '----', '----', '----',
                 '----'], value_input_option="USER_ENTERED")
            row_count += 1
        else:
            while True:
                try:
                    print(f"Adding row {row}")
                    # 指定USER_ENTERED模式，自动解析数值
                    added_row = spreadsheet.sheet1.append_row(row, value_input_option="USER_ENTERED")
                    row_count += 1
                    if "Service Name" in row[0]:  # 检查首列是否包含表头标识
                        print(f"make HEADER BOLD row: {row}.")
                        spreadsheet.sheet1.format(f"A{row_count}:M{row_count}", service_name_row_format)
                    else:
                        print(f"set $$ format for row_count {row_count}")
                        spreadsheet.sheet1.format(f'B{row_count}:M{row_count}', currency_format)
                except Exception as e:
                    print(f"Error adding row: {e}, retrying...")
                    continue
                break

... 其他无关代码 ...

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