如何用Streamlit Gsheets Connection向谷歌表格追加数据？

我正在用Streamlit和Gsheets Connection库加载谷歌表格到变量，想给表格添加一行数据。需求是获取员工输入，把数据存到雇主管理的谷歌表格里，理想状态是直接追加用户输入的内容。

我的代码如下：

import pandas as pd
import streamlit as st
from datetime import datetime
from streamlit_gsheets import GSheetsConnection


# title for the web page
st.title("Office Seat Booking System")

# Input fields for Employee ID, Floor, and Date
employee_id = st.text_input("Employee ID")
floor = st.selectbox("Floor", ["Floor 1", "Floor 2"])
date = st.date_input("Date")

# Establishing a Google Sheets connection
conn = st.experimental_connection("gsheets", type=GSheetsConnection)


#Existing Seats into a Datafram
st.write("Existing seats")
existing_entries = conn.read(worksheet="Bookings")

# creating seat entry
current_seat = {
     'Timestamp': [datetime.now()],
     'Employee_ID': [employee_id],
     'Floor': [floor],
    'Date': [date]
 }

st.write("New seats")
st.dataframe(current_seat)

# Submit button
if st.button("Submit"):
    timestamp = datetime.now()
    # updated_seats = pd.concat([existing_entries,current_seat]).drop_duplicates()
    #updated_seats = existing_entries.add_row(data=current_seat)
    #updated_seats = existing_entries.update(current_seat)
    updated_seats = existing_entries | current_seat    
    bookit = conn.update(worksheet="Bookings", data=current_seat)
    st.success(f"Your Seat has been booked 🎉 {timestamp}")

尝试合并现有数据和新行时出现以下错误：

File "C:\Users\user\AppData\Local\Programs\Python\Python311\Lib\site-packages\streamlit\runtime\scriptrunner\script_runner.py", line 541, in _run_script
    exec(code, module.__dict__)
File "C:\Users\user\Documents\Projects\office-seat-booking\connect-streamlit-with-google-sheets\bookseats.py", line 47, in <module>
    updated_seats = existing_entries | current_seat
                ~~~~~~~~~~~~~~~~~^~~~~~~~~~~~~~
File "C:\Users\user\AppData\Local\Programs\Python\Python311\Lib\site-packages\pandas\core\ops\common.py", line 72, in new_method
    return method(self, other)
           ^^^^^^^^^^^^^^^^^^^
File "C:\Users\user\AppData\Local\Programs\Python\Python311\Lib\site-packages\pandas\core\arraylike.py", line 80, in __or__
    return self._logical_method(other, operator.or_)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\user\AppData\Local\Programs\Python\Python311\Lib\site-packages\pandas\core\frame.py", line 7592, in _arith_method
    self, other = ops.align_method_FRAME(self, other, axis, flex=True, level=None)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\user\AppData\Local\Programs\Python\Python311\Lib\site-packages\pandas\core\ops\__init__.py", line 282, in align_method_FRAME
    right = to_series(right)
            ^^^^^^^^^^^^^^^^
File "C:\Users\user\AppData\Local\Programs\Python\Python311\Lib\site-packages\pandas\core\ops\__init__.py", line 239, in to_series
    raise ValueError(

问题在于：库提供的update函数会替换表格全部数据，我尝试把现有数据和新行合并后再更新，但没成功，现在需要实现追加新行的功能。

解决方法 #

你遇到的核心问题有两个：

• current_seat是字典格式，无法直接和existing_entries（DataFrame格式）合并
• 错误使用了|运算符，这不是DataFrame合并的正确方式

1. 正确的追加步骤 #

步骤1：将新数据转为DataFrame

把构造的字典转换成pandas DataFrame，确保和现有表格的列名、数据格式一致：

current_seat_df = pd.DataFrame(current_seat)

步骤2：合并现有数据与新数据

用pd.concat合并两个DataFrame，注意添加ignore_index=True重置索引，避免出现重复索引问题：

updated_seats = pd.concat([existing_entries, current_seat_df], ignore_index=True)

如果需要避免同一员工同一天重复预订，可以添加去重逻辑：

updated_seats = updated_seats.drop_duplicates(subset=['Employee_ID', 'Date'], keep='last')

步骤3：更新谷歌表格

将合并后的完整DataFrame传入conn.update，覆盖表格内容（相当于完成了新行追加）：

conn.update(worksheet="Bookings", data=updated_seats)

修改后的完整代码 #

import pandas as pd
import streamlit as st
from datetime import datetime
from streamlit_gsheets import GSheetsConnection


st.title("Office Seat Booking System")

# 输入字段
employee_id = st.text_input("Employee ID")
floor = st.selectbox("Floor", ["Floor 1", "Floor 2"])
date = st.date_input("Date")

# 建立谷歌表格连接
conn = st.experimental_connection("gsheets", type=GSheetsConnection)

# 读取现有预订数据
st.write("Existing seats")
existing_entries = conn.read(worksheet="Bookings")

# 构造新预订数据并转为DataFrame
current_seat = {
     'Timestamp': [datetime.now()],
     'Employee_ID': [employee_id],
     'Floor': [floor],
    'Date': [date]
 }
current_seat_df = pd.DataFrame(current_seat)

st.write("New seats")
st.dataframe(current_seat_df)

# 提交按钮逻辑
if st.button("Submit"):
    # 验证输入是否为空
    if not employee_id:
        st.error("请输入有效的员工ID")
    else:
        # 合并现有数据和新数据
        updated_seats = pd.concat([existing_entries, current_seat_df], ignore_index=True)
        # 可选：去重，防止同一员工同一天重复预订
        # updated_seats = updated_seats.drop_duplicates(subset=['Employee_ID', 'Date'], keep='last')
        # 更新谷歌表格
        conn.update(worksheet="Bookings", data=updated_seats)
        st.success(f"你的座位已预订成功 🎉 {datetime.now()}")

额外优化建议 #

如果你的谷歌表格数据量较大，每次读取全部数据再合并更新效率较低，可以尝试使用mode="append"参数（部分版本的Gsheets Connection支持），直接追加新行无需读取现有数据：

conn.update(worksheet="Bookings", data=current_seat_df, mode="append")

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