Python脚本导入CSV至Google Sheets时空白行无法显示问题

我用Python的gspread库编写脚本，完成认证后创建Google Sheets文档，并将包含排版用空白行的CSV文件导入其中。但导入完成后，Google Sheets中看不到这些空白行。

相关代码 #

Python脚本 #

import gspread
import csv
import time

# Authenticate using gspread.oauth()
gc = gspread.oauth()

# Open or create a new Google Sheets document
spreadsheet = gc.create('Your Spreadsheet Name')

# Read CSV data and add it to the spreadsheet
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 blaknk row: {row}.")
            spreadsheet.sheet1.append_row(['    ', '   ', '    ', '    ', '    ', '    ', '    ', '    ', '    ', '    ', '    ', '    ', '    '])
        else:
            while True:
                try:
                    print(f"Adding row: {row}.")
                    spreadsheet.sheet1.append_row(row)
                    time.sleep(5)  # Add a sleep of 5 seconds between each append_row() call
                    break
                except gspread.exceptions.APIError as e:
                    if e.response.status_code == 503:
                        print("Service Unavailable. Retrying in 10 seconds ...")
                        time.sleep(10)  # Wait for 10 seconds before retrying
                    else:
                        raise e

CSV文件头部内容 #

(venv) [red@BP22006 aws_cost_explorer]$ head -30 your_csv_file.csv
"Service Name   af-south-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 VPC","0","0","0","0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","6.96"
"EBS Volumes gp2","19.63","19.64","19.63","19.64","19.63","19.63","19.64","19.63","19.64","19.63","19.63","19.64"
"EC2 - Other","27.59","27.79","27.14","27.26","27.64","26.4","26.55","27.79","29.6","28.91","33.82","25.15"
"EC2","94.14","76.91","59.53","57.61","59.53","59.53","57.61","59.53","57.61","64.88","60.07","56.52"
"PublicIPv4:IdleAddress","0","0","0","0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","3.48"
"PublicIPv4:InUseAddress","0","0","0","0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","3.48"
"Other          af-south-1","0.0","0.0","0.0","0.0","0.0","0.0","0.0","-0.0","-0.0","0.0","0.0","-6.96"
"TOTAL:         af-south-1","121.73","104.7","86.67","84.87","87.17","85.93","84.16","87.32","87.21","93.79","93.89","88.63"

"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"

"Service Name   ap-southeast-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 CloudWatch","0.74","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0"
"AWS ELB","21.05","19.76","21.62","20.51","20.87","21.52","20.37","21.11","20.32","20.19","20.8","22.89"
"AWS S3","36.04","36.25","36.22","36.54","36.99","36.86","36.93","36.44","36.31","36.58","35.21","35.51"
"AWS VPC","0","0","0","0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","27.85"
"EBS Volumes gp2","50.88","50.88","50.88","50.88","50.88","50.88","50.88","50.88","50.88","50.88","51.65","52.8"
...

导入结果截图 #

解决方法 #

1. 给空白行添加重试逻辑：当前脚本仅对非空白行的append_row调用做了API错误重试，空白行的添加没有重试机制，若遇到Google Sheets API的503服务不可用错误，空白行会添加失败。修改代码，将空白行的添加也放入重试循环：

if not row:  # Check if the row is empty
    print(f"Adding blank row: {row}.")
    while True:
        try:
            spreadsheet.sheet1.append_row([''] * 13)  # 用空字符串替代空格，匹配CSV列数
            time.sleep(5)
            break
        except gspread.exceptions.APIError as e:
            if e.response.status_code == 503:
                print("Service Unavailable. Retrying in 10 seconds ...")
                time.sleep(10)
            else:
                raise e

2. 使用空字符串而非空格：用['']*列数替代手动输入的空格，既简洁又能确保单元格被识别为有内容，避免Google Sheets忽略。
3. 验证CSV空白行识别：如果CSV中的空白行包含空格或其他不可见字符，可调整判断逻辑为all(cell.strip() == '' for cell in row)，确保准确检测无内容的行。

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