使用gspread库时货币格式未应用于Google Sheets

Question

我有一些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"

除了第一列，其他的都是美元。我想给这些单元格加上货币格式，所以我写了这段Python代码：

import gspread
import csv


... global vars and stuff ...

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:  # Check if the row is empty
            print(f"Adding blank row: {row}.")
            spreadsheet.sheet1.append_row(
                ['----', '----', '----', '----', '----', '----', '----', '----', '----', '----', '----', '----',
                 '----'])
            row_count += 1
        else:
            while True:
                try:
                    print(f"Adding row {row}")
                    added_row = spreadsheet.sheet1.append_row(row)
                    row_count += 1
                    if "Service Name" in row[0]:  # Check if "Service Name" is in the first column
                        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)

... more unrelated code ...

我确实看到了输出 ...

set $$ format for row_count 2

... 但是那些钱的单元格格式始终没有改变。这里有一张截图：

更新：

结果我只需要把这一行改成 ...

spreadsheet.sheet1.append_row(row)

... 改成这个 ...

spreadsheet.sheet1.append_row(row, value_input_option="USER_ENTERED")

Answer 1

我觉得你的目标是这样的。

• 除了第一列，其他列都是美元。我要给这些单元格应用货币格式，你想把CSV数据添加进来，并把表头设置为粗体，同时把"B"到"M"列的数字格式改成加上$符号。
• 你想用gspread来实现这个。

在你展示的脚本中，下面的修改怎么样？

修改后的脚本：

spreadsheetId = "###" # Please set your Spreadsheet ID.

spreadsheet = client.open_by_key(spreadsheetId)
worksheet = spreadsheet.sheet1

offset = len(worksheet.get_all_values())
values = []
formats1 = []
formats2 = []
with open('your_csv_file.csv', 'r', encoding='utf-8') as csv_file:
    csv_reader = csv.reader(csv_file)
    for i, row in enumerate(csv_reader):
        if not row:  # Check if the row is empty
            print(f"Adding blank row: {row}.")
            values(['----', '----', '----', '----', '----', '----', '----', '----', '----', '----', '----', '----', '----'])
        else:
            values.append(row)
            if "Service Name" in row[0]:
                print(f"make HEADER BOLD row: {row}.")
                formats1.append(f'{gspread.utils.rowcol_to_a1(offset + i + 1, 1)}:{gspread.utils.rowcol_to_a1(offset + i + 1, len(row))}')
            else:
                print(f"set $$ format for row_count {i + 1}")
                formats2.append(f'{gspread.utils.rowcol_to_a1(offset + i + 1, 2)}:{gspread.utils.rowcol_to_a1(offset + i + 1, len(row))}')

worksheet.append_rows(values, value_input_option="USER_ENTERED")
if formats1 != []:
    worksheet.format(formats1, {"textFormat": {"bold": True}})
if formats2 != []:
    worksheet.format(formats2, {"numberFormat": {"type": "CURRENCY", "pattern": "\"$\"#,##0.00"}})

• 在这个修改中，所有的值都是通过append_rows来添加的。

• 如果值不是从"A"列开始添加的，请按以下方式修改上面的脚本。

  • 从

    worksheet.append_rows(values, value_input_option="USER_ENTERED")
    

  • 到

    worksheet.update(gspread.utils.rowcol_to_a1(offset + 1, 1), values, value_input_option="USER_ENTERED")
    

测试：

当你提供的CSV数据用于这个修改后的脚本时，会得到以下结果。在这个例子中，你的CSV值是从第6行开始添加的。