如何通过Google Sheets API和Python追加CSV数据避免覆盖原有内容

你代码的核心问题出在CSV粘贴逻辑的定位配置上：

• 每次粘贴CSV内容时rowIndex固定写死为0，永远从表格第一行开始覆盖写入
• sheetId参数错误使用了数组格式[sheetId]，不符合API参数要求
• 没有提前获取表格已有内容的行数，无法定位到正确的追加起始位置

首先需要新增逻辑获取当前工作表的已用行数，作为新内容的粘贴起始位置，再修正参数配置即可实现数据追加。
以下是修改后的完整代码：

def insertIntoSheets(self, email, owner):
    creds = service_account.Credentials.from_service_account_file(
    self.SERVICE_ACCOUNT_FILE, scopes=self.SCOPES)

    service = build('sheets', 'v4', credentials=creds)
    
    # Insert CSV information into Google Sheet
    sheet = service.spreadsheets()
                        

    csv_path = ""
    csv_files = os.listdir(self.DOWNLOADS_PATH)
    for csv_file in csv_files:
        if csv_file.startswith("lead_export"):
            print("CSV path found")
            csv_path = os.path.join(self.DOWNLOADS_PATH, csv_file)
            print(f'CSV_PATH: {csv_path}')

    if csv_path == "":
        print("No CSV file detected. Must not have downloaded. Please try again.")
    else:
        #add email and owner
        values = (
            (owner, email),
        )
        value_range_body = {
            'majorDimension': 'ROWS',
            'values': values
        }
        service.spreadsheets().values().append(spreadsheetId=self.DATA_SPREADSHEET_ID, 
            valueInputOption='USER_ENTERED',
            range="Sheet1!A1",
            body=value_range_body).execute()

        # 新增：获取当前表格已有行数，确定粘贴起始位置
        res = service.spreadsheets().values().get(
            spreadsheetId=self.DATA_SPREADSHEET_ID,
            range="Sheet1!A:A"
        ).execute()
        existing_rows = len(res.get('values', []))

        #add CSV to Google Sheets
        print(f'Attempting to upload CSV file for {email}')
        with open(csv_path, 'r') as csv_file:
            csvContents = csv_file.read()
        body = {
            'requests': [
                   
                {
                'pasteData': {
                    "coordinate": {
                        # 替换为你实际的Sheet1数字ID，从表格URL的gid参数获取
                        "sheetId": 替换为你的Sheet1数字ID,
                        "rowIndex": existing_rows,  # 改为动态获取的已有行数，从末尾追加
                        "columnIndex": 0,
                    },
                    "data": csvContents,
                    "type": 'PASTE_NORMAL',
                    "delimiter": ',',
                }
            }
            ]
        }
        request = sheet.batchUpdate(spreadsheetId=self.DATA_SPREADSHEET_ID, body=body)
        response = request.execute()

        print(f'Deleting CSV file for {email} account')
        deleted = self.removeCSV()
        if deleted == 0:
            print(f'No CSV files detected for {email}')
        else:
            print(f'{deleted} CSV files deleted for {email}.')

        return response

• 如果你的CSV文件包含表头，只需第一次运行时保留即可，后续运行可以在读取CSV内容后手动切掉第一行，避免重复插入表头
• 如果你需要从特定列开始粘贴，修改columnIndex的数值即可，列索引从0开始计数
• 工作表ID可以从Google Sheets的URL中获取，URL中gid=后面的数字就是对应工作表的ID

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