Honestly, I'm guessing this is more of a network or system issue than a code issue, but my IT guys are unable to help and I'm not sure what I can do to help them find the problem.
With that said, if there's a more efficient way to do this, I'm all ears.
My users have started complaining about this script timing out and I've narrowed it down to one specific line of code (the second one below) that sometimes takes 30 seconds, sometimes takes 3 minutes, and sometimes times out after 10 minutes. I have seen it execute n as little as 8 seconds.
var master = SpreadsheetApp.openByUrl("https://<URL redacted>").getSheetByName("Master");
var masterData = master.getRange(2,1,master.getLastRow(),13).getValues();
The data is 40,000 rows and 16 columns of fairly simple, mostly text data. There are three other worksheets in the workbook, but the one I need is by far the largest. The file is about 4MB and resides in a different folder on the same team drive in Google Drive as the Google Sheet where my script runs. The data changes infrequently, with maybe 100 rows getting added, removed or updated each Friday morning. I'm the only one who ever accesses the file.
My first guess was that there was some sort of caching at play, but I can sit at my desk, run the script 5 times in a row and get drastically different results every time.
When the script is misbehaving, I can make a copy of the spreadsheet where I'm running the script, kick off the script in that new spreadsheet and have it finish while the first one is still spinning.
Any thoughts on what might be causing this or how to get some better data to provide to the IT guys?
