As you grow build and grow your datasets using Cryptosheets custom function formulas you may encounter Timeout 15000 type errors.

😦 Most users are SHOCKED to learn what some of the most well known potential causes are according to official Microsoft & Google Sheets support documentation (read below and see official links)

πŸ‘‰ QUICK FACT

  • There are many potential causes varying from extremely simple to painfully complex

  • In ~95%+ of cases our users are able to easily adjust their existing/new workbooks and formulas to mitigate almost all of these types of errors

⚠️ The most common cause of most of these issues for users on a FREE subscription is simply that they are exceeding their allowed request quota(s) and request concurrency limits (aka simultaneous formula refreshed)


Please review the below in depth resources & specific examples of well known and documented causes that can be easily avoided.

❓ What's Happened?

  • When you combine API data with the power of spreadsheet formulas sometimes formulas can 'go wrong' or accidentally trigger.

  • Sometimes having multiple linked sheets, workbooks or tabs can set off multiple cascading triggers you may not realize

  • You may have lost track of your quota usage and simply surpassed your limit


Example causes that could trigger auto disable or Timeout Errors: ​

  1. You have multiple Cryptosheets functions that are directly or indirectly linked to cells with volatile functions like =NOW(), =TODAY() =BNOW(),

    πŸ‘‰ Using volatile functions including =NOW(), =TODAY(), =RAND(), =RANDBETWEEN(), =OFFSET(), =INDIRECT(), =INFO() and =CELL()` cause the entire workbook to refresh all cells on any* state change

    ⚠️ DO NOT use them with Cryptosheets functions as they will cause errors and rapidly consume your request quota ⚠️

    2) You are repeatedly sending incorrect symbol, or exchange asset pairs (which result in errors)
    3) You are repeatedly using incorrect parameters with custom functions (ie using time or exchange for =CS.EXRATE when those aren't valid parameters)
    4) You have existing VBA macros, custom Google App Scripts or similar programmatic triggers setup
    5) You are using a custom function that that your subscription tier doesn't have access to (ie =CS.TIME)


⚠️ Other Common Things that Can & Will Cause Unexpected Refresh Include:

The below specific examples of unintended data refresh are taken directly from the Microsoft Excel Support Documentation pages --> Most users are SHOCKED to learn these are well known potential causes!
  • Entering new data (if Excel is in Automatic recalculation mode, described later in this topic).

  • Explicitly instructing Excel to recalculate all or part of a workbook.

  • Deleting or inserting a row or column

  • Saving a workbook while the Recalculate before save option is set
    (πŸ‘‰ this is SO easy to overlook & not even consider)

  • Performing certain Autofilter actions.

  • Double-clicking a row or column divider (in Automatic calculation mode).

  • Adding, editing, or deleting a defined name

  • Renaming a worksheet tab

  • Changing the position of a worksheet in relation to other worksheets.

  • Hiding or unhiding rows, but not columns.

πŸ”— link: https://docs.microsoft.com/en-us/office/client-developer/excel/excel-recalculation?redirectedfrom=MSDN


πŸ‘‰ Quick VBA Example Using Volatile Functions + Solution

"I have a very complex workbook that contained multiple NOW() and TODAY() references and as it grew in size through the year the macro that ran transaction posting begin to take longer and longer."

  1. I replaced all references to NOW() and TODAY() with a pointer to a cell in my TBL worksheet named NVDate (Non-Volatile Date).

  2. Then I added the following code to the ThisWorkbook object:

    Private Sub Workbook_Open()
    β€˜Seed NVDate to eliminae volatile Today() and Now() functions
    TBL.Range(β€œNVDate”).Value = Date
    End Sub

"The gain in speed was astounding. A transaction that had been taking 9 seconds to post was now running in less than a second."


TAGS: disabled, enable, exhaust, error, subscription, quota, exhausted, error, disabled, excessive, usage, refresh, autorefresh, update, refresh data, save, autosave, backup, google drive, gsuite drive, sharepoint, onedrive, one drive, workbook calculation, excel manual refresh, vba, detected a large amount of error requests in your account, precautionary measure, have disabled your account, #NAME, subscription level, billing, account, queries, errors, deleted, destroyed, upgrade, downgrade, support@cryptosheets.com, help, wtf, error, timeout, support, #volatile, #volatile formulas, #volatile function, #=NOW, #=OFFSET, #=INDIRECT, =RAND, #=INFO, #=CELL, #=TODAY, #=BTODAY, #timeout, #unable, #Timeout Error, #15000, #30000, #NOW, #TODAY, #NOW(), #TODAY(), =NOW, =TODAY

=NOW

=TODAY

Did this answer your question?