2

I managed to get this to happen with the simplest possible code:

function test() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  Logger.log("before");
  var st = ss.getSheetByName("STATUS");
  Logger.log("after");
}

Results in:

Head    test    Editor  Sep 21, 2020, 7:07:32 AM    3.087 s 
Running
Stackdriver logs
Sep 21, 2020, 7:07:32 AM    Info    before
Sep 21, 2020, 7:13:32 AM    Error   Exceeded maximum execution time

This all started when this function:

function updateSheets() {
  var ui = SpreadsheetApp.getUi();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var st = ss.getSheetByName("STATUS");
  var filelist = st.getDataRange().getValues();
  var dt;
  var folder = DriveApp.getFolderById(uploadID);
  for (i=0; i<filelist.length; i++) {
    var iter = folder.getFilesByName(filelist[i][0]);
    dt = new Date();
    if (iter.hasNext()) {
      var file = iter.next();
      st.getRange(i+1, 2).setValue(file.getLastUpdated());
      st.getRange(i+1, 3).setValue(dt);
      var name = getSheetName(file.getName());
      var data = Utilities.parseCsv(file.getBlob().getDataAsString()); data.shift();
      st.getRange(i+1, 5).setValue(data.length);
      var sht = ss.getSheetByName(name);
      if (!sht) {
        ss.getSheetByName("MASTER").copyTo(ss);
        sht = ss.getSheetByName("Copy of MASTER");
        sht.setName(name);
      }
      sht.getDataRange().clearContent();
      sht.getRange(2, 1, data.length, data[0].length).setValues(data);
      sht.getRange(1, 1).setValue(file.getLastUpdated());
      dt = new Date();
      st.getRange(i+1, 4).setValue(dt);
    }
    else {
      st.getRange(i+1, 2).setValue("File Not Fount");
      st.getRange(i+1, 3).setValue(dt);
      st.getRange(i+1, 4).clearContent();
      st.getRange(i+1, 5).clearContent();
    }
  }
}

with this the sheet STATUS

File    Last Updated    Start Time  Finish Time     Lines
Classes.csv 9/19/2020 5:00:09   9/21/2020 5:51:18   9/21/2020 5:51:20   1842
Customers.csv   9/21/2020 5:00:05   9/21/2020 5:51:20   9/21/2020 5:51:22   950
Items.csv   9/21/2020 5:00:08   9/21/2020 5:51:22   9/21/2020 5:51:36   5777
Routes-Detail.csv   9/21/2020 5:48:37   9/21/2020 5:51:38   9/18/2020 7:28:34   2252
Routes-other.csv    9/18/2020 6:24:52   9/18/2020 7:28:34   9/18/2020 7:28:37   90
Routes.csv  9/21/2020 5:48:36   9/21/2020 5:51:36   9/21/2020 5:51:38   279

As you can see, it failed with a time out on Routes-Detail.

After that, I could no longer get even the simple code in test to run through.

The only difference between Routes-Detail and the other csv's is that there is another page that uses query('Routes-Detail'!$A$3:$Z,"SELECT") and vlookups to format it out.

Any notion why these are timing out? If it is the other sheet recalculating - is there a way to disconnect those queries during the update?

(note - uploadID is a const declared up above with the id of the folder Google Backup & Sync backs up from my work computer, getSheetName() simply removes the .csv from the file name, I use a preformatted master sheet because the default color scheme kills my eyes)

(also, how do I format the data from the spreadsheet better?)

Thanks for any insight.

EDIT - This is definitely something in updateSheets() that's causing ongoing problems for a time after. The last time I ran it, it paused for 5 minutes on Routes-other before finishing (just under the 360 second timeout).

The .csv's that are being imported come from a legacy (AS400) system from a sql query running on a scheduled (or triggered by an email, depending on the query) powershell script.

None of them are overly large.

Is there a better way to automate the import? I've added the code to only update if the file is new (I intend to put this script on a time trigger).

Seank
  • 21
  • 2
  • Oh, I did catch that I started the loop up in the headers row and fixed it, but that only resulted in overwriting my headers with "file not found", and the other files ran up until the Routes-Detail file. I just fixed the headers back so it was clear. – Seank Sep 21 '20 at 14:32
  • Please help clarify for me by editing this question to a specific problem. Seems like you have an issue with getting the sheet by name, so the `updateSheets()` function is unnecessary to that first problem. Correct? – Diego Sep 21 '20 at 14:47
  • Try `SpreadsheetApp.flush()`. What is the table row x column size of STATUS sheet? – TheMaster Sep 21 '20 at 15:22
  • Since the issue with that the test function demonstrates occurred first in updateSheets, and only after updateSheets failed to run through, yes, it's relevant. The issue went away until I ran updateSheets again (at about 1.5hrs after the first post). This time updateSheets ran to the next file in the list - Routes-other before stopping. I'll try SpreadsheetApp.flush() at the end of each import as it doesn't seem to be anything to do with the Routes-Detail specifically, but something with the basic technique. – Seank Sep 21 '20 at 16:08
  • Would you consider providing a sanitized copy of the spreadsheet you're working on? – Iamblichus Sep 22 '20 at 09:57
  • Let's see if I can create a few datasets that I can cause the same issue with. – Seank Sep 22 '20 at 13:09

0 Answers0