在谷歌表格中添加空行
我有一个简单的Python脚本，它可以根据一个CSV文件为我创建一个Google表格。
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"
...
但是当我打开Google表格时，我发现表格里没有这个空行：
1 个回答
1
建议：使用 batch_update 方法
append_row 方法会把数据插入到电子表格中第一个不为空的行，这样会覆盖掉脚本添加的空行。我建议使用 batch_update 方法来插入整个 CSV 文件，包括那些空行。这样做的好处是只需要一次 API 调用就能插入整个 CSV 文件。
你可以试试这个稍微修改过的脚本版本：
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')
data = []
# 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:
data.append(row)
#Gets the total rows of the CSV file
rowCount = len(data)
#Gets the total columns of the CSV file, based on the first row.
columnCount = len(data[0])
try:
#Calls the batch_update method and inserts the data from the CSV file to Google Sheets.
spreadsheet.sheet1.batch_update([{
'range': f'A1:{chr(columnCount+65)}{rowCount}',
'values': data
}])
print("Data successfully uploaded to Google Sheets.")
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
这是我在通过 batch_update 插入 CSV 文件后，数据在我这边显示的样子：
编辑：
在数据插入到 Google Sheets 后，可以修改背景颜色。比如，这个脚本会修改所有包含 AWS VPC 的单元格的背景颜色：
service_name = spreadsheet.sheet1.findall('AWS VPC')
for cell in service_name:
spreadsheet.sheet1.format(
f'A{cell.row}', {
"backgroundColor": {
"red": 0.0,
"green": 0.0,
"blue": 50.0
},
})