Google sheets: appending new data to an existing DataFrame

  1. Are you running your app locally or is it deployed?
  • Locally

I have a connection to Google Sheets where I am accessing a Data Frame. In streamlit I have a series of functions to generate values, for example category, amount and a timestamp of when the purchase was made. The problem is that when I try to add a new record it deletes the existing values in the spreadsheet and also if I enter a value first, and then try to enter another one, it gets overwritten. However, when I run the Python code lines 1 by 1, it does not cause this error, it is only when I do it from the app.

Here’s how my spreadsheet looks like:

image

This is how my app looks like and when I click on add:

These are the two main functions executing the code:

def submit_values():
    try:
        conn = st.experimental_connection("gsheets", type=GSheetsConnection)
        category, cat_spec = category_spending()
        amount = amount_money()
        time = time_done()
        if st.button("Añadir"):
            # Read the latest data from the spreadsheet
            df = conn.read(worksheet="datos", usecols=[0, 1, 2, 3])

            data = {"Rama": category, "Categoría": cat_spec, "Monto": amount, "Fecha": time}
            temp_df = pd.DataFrame([data])

            # Update the dataframe and the spreadsheet
            updated_df = update_dataframe(df, temp_df)
            conn.update(data=updated_df)
            st.success("¡Datos añadidos correctamente!")

    except Exception as e:
        st.error(f"Error: {e}")
def update_dataframe(existing_df, new_data):
    # Find the first empty index
    empty_indices = existing_df.index[existing_df.isnull().all(axis=1)].tolist()

    if empty_indices:
        # Use the first empty index
        first_empty_index = empty_indices[0]
        existing_df.loc[first_empty_index] = new_data.iloc[0]
    else:
        # If there are no empty indices, append the new data to the end
        existing_df = existing_df.append(new_data, ignore_index=True)

    existing_df["Fecha"] = pd.to_datetime(existing_df["Fecha"])
    existing_df.sort_values(by="Fecha", ascending=False, inplace=True)
    return existing_df
  1. Share the Streamlit and Python versions.

Python version: 3.9.18
Streamlit, version 1.26.0