How would I optimise SpreadsheetApp.OpenByID?

[Josh Dalton]

Hey all,

Over the past week or so, my scripts have been experiencing issues concerning SpreadsheetApp.OpenByID taking far too long for a single spreadsheet to the point where it almost always times out with just this spreadsheet.

Is there any way to fetch just a single sheet out of the entire spreadsheet so I can avoid OpenById taking over 3-4 minutes to run? Or just any way to optimise OpenById?

[Josh Dalton]

Other than that, are there any known avenues to extend the time a single trigger (anyway I could get in touch with Google support basically) is run without restarting? Since I can't imagine a way you would be able to cache OpenById 

[Edward Ulle]

How do you know its openById() and not some other part of your code?

[CBMServices Web]

Hi Josh,

An open Byrd method should not be taking 3-4 mins. If it takes more than 3 seconds, then Google is experiencing heavy traffic.

If you publish your script  we may be able to give you some advise for optimization.

Typically, it is best to avoid the number of times you call on the spreadsheet for read or write. Best to do one read of the entire sheet, manipulate what you need with the data, then write back the changes in one write if you can. 

--
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/2453d59b-10ce-4d48-be39-f7fa175c1d83n%40googlegroups.com.

[Josh Dalton]

In order to narrow it down as to why my script went from taking 2-3 minutes to timing out at 6 minutes, I logged every time I did something major with the code such as opening a spreadsheet (just before the line for opening the spreadsheet ran, and immediately after it runs).

As you can see in the attachment, my fairly large spreadsheet is now taking over 5 minutes to open (forgive the duration being wrong in the screenshot, I'm not sure why that isn't matching up with the actual duration, but it's close enough)

The code is below

https://pastebin.com/Ya4TMfw0

For reference: This issue didn't start occurring until the 1st of July, before this it was taking a bit of time, but 90% of the time it went through fine (and the remaining 10% was server errors)

I didn't change anything on the spreadsheet for the past month, and my changes previous were insignificant (the spreadsheet is 1.1 MB, and my changes were less than 10 kB)

[นภาภรณ์ คําพรรณ]

ในวันที่ ศ. 8 ก.ค. 2022 00:45 น. CBMServices Web <cbmserv...@gmail.com> เขียนว่า:

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3UOdStcvzeRTyP6nDvj9W3CDUZW3%3D8FQha0v1jwVPHThA%40mail.gmail.com.

[cbmserv...@gmail.com]

Your script does multiple actions and is dependent on how many files you have in that folder.

 

It is grabbing all the files and running through every single one and trying to open them as a spreadsheet.

 

Are all files in that folder a spreadsheet?? You may want to add a check before you try to open a file as a spreadsheet to ensure it is of that type.

 

Before line 4 below, add a check whether the file is a spreadsheet first..

 

1.     while(files.hasNext())

2.     {

3.       Logger.log(Duration(startTime) + " Opening Spreadsheet")

4.       spreadsheets.push(SpreadsheetApp.openById(files.next().getId()))

5.       Logger.log(Duration(startTime) + " Spreadsheet Opened")

6.     }

--

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/570148f6-ad66-47a2-8f6d-e201dd7018c9n%40googlegroups.com.

[นภาภรณ์ คําพรรณ]

ในวันที่ ศ. 8 ก.ค. 2022 00:45 น. CBMServices Web <cbmserv...@gmail.com> เขียนว่า:

Hi Josh,

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3UOdStcvzeRTyP6nDvj9W3CDUZW3%3D8FQha0v1jwVPHThA%40mail.gmail.com.

[Josh Dalton]

All files in the folder are a spreadsheet and a check isn't necessary, the only thing that is causing this issue is the opening of the first large spreadsheet. Here's a screenshot of all that is in the folder I'm checking

[cbmserv...@gmail.com]

Split the getId from the open to see what is actually taking the extra time. But large files will certainly slow down operations. Is there a way for you to reduce its size?

 

From: google-apps-sc...@googlegroups.com <google-apps-sc...@googlegroups.com> On Behalf Of Josh Dalton

Sent: July 7, 2022 12:39 PM
To: Google Apps Script Community <google-apps-sc...@googlegroups.com>

Subject: Re: [Apps-Script] Re: How would I optimise SpreadsheetApp.OpenByID?

 

All files in the folder are a spreadsheet and a check isn't necessary, the only thing that is causing this issue is the opening of the first large spreadsheet. Here's a screenshot of all that is in the folder I'm checking

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/905d06f1-fe36-4b01-b6e7-e547ced4cff1n%40googlegroups.com.

[Josh Dalton]

There's no way for me to reduce the size of the spreadsheet unfortunately, here are the latest results along with the code change

  while(files.hasNext())

  {

    var file        = files.next()

    Logger.log(Duration(startTime) + " Fetching File ID")

    var fileID      = file.getId()

    Logger.log(Duration(startTime) + " File ID Fetched")

    Logger.log(Duration(startTime) + " Opening " + file.getName())

    spreadsheets.push(SpreadsheetApp.openById(fileID))

    Logger.log(Duration(startTime) + " " + file.getName() + " Opened")

  }

[cbmserv...@gmail.com]

Ok. So the get file ID is not the culprit, but opening definitely is. 6 minutes to open a file is most indicative of a corrupted file most likely. I would suggest you copy what content you need from it, delete it and create a new one. Regardless of the size of the file, it should not take that long to open. There is something wrong with the data for that file causing the problem.

 

From: google-apps-sc...@googlegroups.com <google-apps-sc...@googlegroups.com> On Behalf Of Josh Dalton
Sent: July 7, 2022 1:11 PM
To: Google Apps Script Community <google-apps-sc...@googlegroups.com>
Subject: Re: [Apps-Script] Re: How would I optimise SpreadsheetApp.OpenByID?

 

There's no way for me to reduce the size of the spreadsheet unfortunately, here are the latest results along with the code change

 

  while(files.hasNext())

  {

    var file        = files.next()

    Logger.log(Duration(startTime) + " Fetching File ID")

    var fileID      = file.getId()

    Logger.log(Duration(startTime) + " File ID Fetched")

 

    Logger.log(Duration(startTime) + " Opening " + file.getName())

    spreadsheets.push(SpreadsheetApp.openById(fileID))

    Logger.log(Duration(startTime) + " " + file.getName() + " Opened")

  }

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/6584d0b2-1b83-4189-88b9-cda52dd5ce59n%40googlegroups.com.