2

Scenario:

Using API, do the following:

  • Write a value on A1
  • Write another value on B1
  • A custom formula present on C1 computes the result
  • Get the value from C1

Sporadically the value for C1 will end up being Loading... which is a placeholder value until the custom formula returns a value. As I look on the spreadsheet UI, the value there is correct but API can return the Loading... value. I have tried adding some delay before making the call for getting the value from C1 but to no avail.

Am I missing something here? Should there be longer wait time?

Donnald Cucharo
  • 3,866
  • 1
  • 10
  • 17
Vikram Tiwari
  • 3,615
  • 1
  • 29
  • 39
  • 1
    About `I have tried adding some delay before making the call for getting the value from C1 but to no avail.`, in order to confirm your situation, can you provide the script? – Tanaike Jun 11 '21 at 00:12
  • Have you managed to solve the problem? I ran into the same issue in my project and still struggling to prevent this from happening when working with the Sheets API... – Fillyjonk Jan 25 '23 at 20:30
  • 1
    Hey! I looked at some other SO answers and realized that it's just not reliable. We got some respite by using formulas that are less complex. The more complex the formula is, the higher likelihood of it showing `Loading...`. – Vikram Tiwari Jan 26 '23 at 17:07
  • What helped me was following the suggested actions on this post. Mostly about reducing the data used in the formulas. https://thegeekpage.com/google-sheets-slow-and-stuck-on-loading/ – Vikram Tiwari Jan 26 '23 at 20:45

1 Answers1

0

In your spreadsheet formula, try to set absolute references for either columns $C1 or rows C$1 or both $C$1. For me, it worked just fine and instantly. Moreover, my problem doesn't seem to reemerge.

Just FYI, in my case, a custom app script formula =columnToJSON('Categ Lists'!H$4:H$103) was fetching 100 rows and compiling a JSON list out of array of non-empty values. The formula was repeated over some 50 columns. Setting columns as absolute references took an immediate effect and my Node.js app started reading the return values of GoogleSheets formulas with no sporadic interruptions.

Hope it helps you.

Fillyjonk
  • 178
  • 1
  • 1
  • 7