0

I have a report file get the content from other files in a Google drive to generate a report. The script do the following

  • Get list of files in folder (and their IDs)
  • Create new sheets using predetermined form
  • Get content from files in folder into the sheets (via IMPORTRANGE)
  • Get summary of files' content in each sheet (progress of projects)
  • Get the summary in sheets into one single sheet (via INDIRECT)

However, it seems that the result could behave wildly. Sometimes IMPORTRANGE will not get the correct file content even all the results lead to it are all correct. Sometimes INDIRECT will not get error even when enter the formula exactly the same manually will get the correct result. Sometimes the function getSheets() will be error but most of the time it will not.

When the result is not correct, I just delete the newly created sheets and run the code all over again. After awhile, they all will be correct.

So my question is what's the problem/limitation with google sheet or script that they will have results varies wildly between run?

Tam Le
  • 360
  • 3
  • 17
  • 1
    You're mixing formulas with scripts. If you want reliability, use your script to replicate the function provided by IMPORTRANGE/INDIRECT. Use code or formulas. The mixing might be the cause of unreliability. – TheMaster Sep 19 '18 at 11:16
  • 1
    If you're setting & getting formulas in the same script run, make sure you use [`flush`](https://stackoverflow.com/questions/41175326/why-do-we-use-spreadsheetapp-flush) – tehhowch Sep 19 '18 at 12:53
  • @tehhowch When do I use `flush`? In my script, I only have one at the end of the script. – Tam Le Sep 20 '18 at 02:03
  • @tam read the linked question and learn about it. Then use your knowledge to analyze your code. You haven't shared an [MCVE](https://stackoverflow.com/help/mcve) here so we can't possibly help you figure out where to use it better than you can. – tehhowch Sep 20 '18 at 02:07

1 Answers1

0

After a lot of trying and searching, it ALL comes down to IMPORTRANGE. It has a specific cycle of updating value of 30 mins. To force the function into update more regularly, just put in a variable that will. The solution is something like this =IF(MINUTES(NOW())=MINUTES(NOW()),IMPORTRANGE(.....)

After I implement this, everything goes smoothly.

Tam Le
  • 360
  • 3
  • 17