Extremely inconsistent performance with Google Apps Script - slow search, but seemingly instant sheet activation with unexpected data

[Wolf Knipfer (Segelwolf)]

I'm experiencing very inconsistent performance with a Google Apps Script project that involves searching data in one sheet ("Buchungen") and displaying/editing it in another ("Buchungsmaske").

Here's the workflow:

1. A modal dialog (Suchmaske.html) allows users to search bookings by various fields using the searchBookings(searchTerm) function in BuchungsMasken.gs.

2. Clicking on a search result triggers the loadBookingForEdit(bookingId) function in BuchungsMasken.gs, which is intended to:

   • Read the data for the selected bookingId from "Buchungen".

   • Clear any existing data below the header row in "Buchungsmaske".

   • Write the data to the first data row of "Buchungsmaske".

   • Activate the "Buchungsmaske" sheet.

The Problem:

The performance is highly erratic:

• Search Speed: Initially, the search (searchBookings) took around 15-20 seconds for a very small dataset (around 14 rows). After some adjustments to the searchBookings function, it temporarily improved to around 2 seconds. However, it has now regressed to around 20 seconds again for the same dataset.

• Loading/Sheet Activation: This is the most perplexing part. When loadBookingForEdit is triggered:

  • The time taken to switch to the "Buchungsmaske" sheet varies greatly, sometimes taking an unexpectedly long time (around 19 seconds).

  • Even when the loadBookingForEdit function was simplified to only activate the "Buchungsmaske" sheet (without reading or writing any data), it still sometimes took a significant amount of time (around 19 seconds).

  • Most strangely, even with the simplified loadBookingForEdit function that should not write any data, the "Buchungsmaske" sheet appears almost instantly and contains the Buchungs-ID of the selected record in the first data cell (A2). This suggests that data is being written to the sheet even when the code explicitly only calls setActiveSheet().

What I've Tried:

• Clearing browser cache.

• Restarting the Google Sheet.

• Simplifying the loadBookingForEdit function to only activate the target sheet.

• Checking for conditional formatting and data validation rules on "Buchungsmaske" (only minimal rules on the header row).

• Checking for project triggers (none are active).

• Ensuring the correct version of the script is being run.

My Environment:

• I am using a personal Google account (not a Google Workspace account)

• Relatively fast local machine and internet connection.

• The dataset in "Buchungen" is very small (currently < 20 rows, expected to stay in the low hundreds per year).

• The "Buchungsmaske" sheet is very basic, with just a header row copied from "Buchungen".

I am at a loss to explain this inconsistent and seemingly contradictory behavior, especially the fact that data appears on the target sheet even when the loading function is simplified to only activate the sheet.

Has anyone experienced similar issues or have any insights into what might be causing this? Any suggestions for further debugging or potential solutions would be greatly appreciated.

Thank you in advance for your help!

[George Ghanem]

Hi Wolf,

There are several ways to improve performance that you can make to speed it up. However the fact it is writing to the Target sheet when it is not implies there is a bug in your code somewhere. We can not help you unless you share the code for us to take a look.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/8bc07b49-ea3d-47d3-b90b-0e4284e4fe68n%40googlegroups.com.

[Keith Andersen]

Can you provide a copy to examine and test? Make it view only and we can make a copy to gain editor privileges. Seeing your code and sheet is the best way we can help problem shoot.

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAA4K68bgPRgkD2Sw4O0Qdgp193PtXuFqXr0hn35eP8Y%2BmLVs5w%40mail.gmail.com.