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:


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():
        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 ="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)
            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]
        # 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

hey hi ! it will be awesome if you share more detail or source code even deployment link for now you can check out this docs and blog

this reference blog describes it all…

Hi @Manswi_More thanks for your advice, I replace the code with streamlit forms function and that solved it for me. Thanks!

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.