getSheetByName() timeout

[Ross Crawford]

Hi, I have a script that has been working fine by suddenly is getting timeouts. I've narrowed it down to the following simple code:

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var doc = ss.getSheetByName("Sheet name");

When I debug, the first line works fine, but the 2nd line never completes, and times out after 5 minutes. Nothing in the sheet has changed as far as I can see, what would cause this?

Thanks in advance.

[Ross Crawford]

Even var ns = ss.getNumSheets(); times out. Could this be a corruption in my sheet? It opens fine in google docs, and all the data looks right.

[Clark Lind]

Make sure the script is authorized. Add this simple function, then debug it:

function authorized() {

  console.log("I am authorized")

[Moises Chavez]

Hey, having the same issue with one of my "bulky" reports. For some reason, any script times out, even the smallest of things. I don't have issues on other reports. 

Did you manage to find out the issue in your end? 

[Bayu Anandavi]

I have the same issue with "getSheetByName" function. It always timed out. Did you managed to resolve it?

[Ross Crawford]

Hi, yes I resolved it by moving all my calculations to a separate spreadsheet and copying data to it using IMPORTRANGE(). Apparently calling  getSheetByName seems to cause the sheet to refresh all the calculated cells.

[Andrew Brown]

HI,

I use:

const sn = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();

Regards,

Andrew J Brown

+44 (0)7703 367642

--
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 on the web visit https://groups.google.com/d/msgid/google-apps-script-community/84fbb6c2-dda0-46d7-ad45-f56be4762ed1n%40googlegroups.com.

[Ross Crawford]

Thanks, but that is doing the opposite of what this discussion is about. We are returning a spreadsheet reference given it's name.

[Dmitry Kresin]

I have the same issue with the same two lines of code. Everything worked fine for weeks and now it just stops working without any errors.  getSheetByName simply times out, it never ends. Did anybody find the solution? Do you need to buy a google suite account to avoid this kind of issues?

[Robin Kowalski]

Hi,

Like Dmitry, I have the same issue. Everything worked fine until this morning. Does anyone have a solution ?

[cbmserv...@gmail.com]

This may be a corrupted spreadsheet. I would try to create a new spreadsheet and copy over any data to it and then link the script to the new sheet.

 

I have seen this before and the corruption I had seen was more how the scripts were linked to the sheets.

--

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 on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a24fce44-98ad-403d-9bd8-aba5c602de67n%40googlegroups.com.

[Zach Davis]

Seeing this same issue. Did anyone find a solution? I already offloaded my calculations and still no luck.

[Valeriu]

Same issue here with a spreadsheet with many calculations. When I remove the sheets with calculations, the script seems to be working fine.

[cwl...@gmail.com]

Has anyone tried adding a "spreadsheet.flush()" so it doesn't get bogged down updating the calculated content? If you have a bunch of calculations, prior to trying to grab a sheet (e.g., getSheetByName() ), insert a flush statement right before you attempt to get the sheet. I haven't tested it, but that is what I would try.

[Valeriu]

I just tried using Spreadsheet.flush() before the getSheetByName and it doesn't help.

[Todd Wolford]

Maybe try 

var ss = SpreadsheetApp.getActive()

ss.setIterativeCalculationEnabled(false)

//do your thing

ss.setIterativeCalculationEnabled(true)

Not tested- just a suggestion of something to try...

[Valeriu]

I tried using 

ss.setIterativeCalculationEnabled(false)

but it freezes at this line.