Google App Script does not enter the function

Question

Google App Script doesnt seem to enter into a function. The logs show that the variables before the function were all initialized, which means the custom function is working. I also tested it an hour ago, and my custom function worked.

I also have other script files with functions but the only global varialbe is from a Spreadsheet.getActiveSpreadsheet();

Logs:

7:40:47 PM  Notice  Execution started
7:40:48 PM  Info    Mapped named ranges.
7:40:51 PM  Info    Initialized SyncData variables...
7:46:47 PM  Error   Exceeded maximum execution time

My code is pretty simple as I was just testing a custom function. My goal is to TEST a function that does a batch retrieve of all named ranges instead of doing it one by one.

/****   retrieve settings   ******/
//const DataSourceRange       = ranges.get("DataSourceRange").getValue();
let ranges = mapNamedRanges(thisSpreadSheet.getNamedRanges());
const TrackerHeaderRow      = ranges.get('TrackerHeaderRow').getRange().getValue();
const firstRowTrackerData   = TrackerHeaderRow + 1;

const FirstColHeaderName    = ranges.get("FirstColHeaderName").getRange().getValue();

/****   setting up sheets    ******/
const TrackerSheetName      = ranges.get("TrackerSheetName").getRange().getValue();
const QuerySheetName        = ranges.get("QuerySheetNameSetting").getRange().getValue();

const trackerSheet          = thisSpreadSheet.getSheetByName(TrackerSheetName);
const querySheet            = thisSpreadSheet.getSheetByName(QuerySheetName);

Logger.log('Initialized SyncData variables...')

function testRange(){
  Logger.log('Testing ranges mapping');
  let ranges1 = mapNamedRanges(thisSpreadSheet.getNamedRanges());
  Logger.log('ranges len = ' + ranges.size);
  //Logger.log(ranges1);

  const trackerSheetNameRange = ranges1.get('TrackerSheetName').getRange();

  Logger.log(trackerSheetNameRange.getValue());

  //testing for sheets
  
  Logger.log('Testing sheets mapping');
  const sheets = thisSpreadSheet.getSheets();
  const sheet = mapSheetNames(sheets);
  Logger.log('sheet sample: ' + sheet.get("SLI Tracker"));

  for( var i in sheets ) {
   Logger.log('sheet: '+ sheets[i]);
  } 

}

Custom functions:

/**
 * @param {Array} namedRangesArray - requires a multidimensional array consisting the properties and content of the namedRange
 * @return {Map} returns a keyed/mapped array
 */
function mapNamedRanges(namedRangesArray) {
  let map = new Map();

  //Logger.log('namedRanges length: ' + namedRangesArray.length);

  for (i=0; i < namedRangesArray.length; i++){
    map.set(namedRangesArray[i].getName(),namedRangesArray[i]); //key, value
  }
  
  Logger.log('Mapped named ranges.');

  return map;
}

/**
 * @param {Array} sheetsArray - an array of sheets
 * @return {Map} returns a keyed/mapped array
 */
function mapSheetNames(sheetsArray) {
  let map = new Map();

  for (i=0; i < sheetsArray.length; i++){
    map.set(sheetsArray[i].getName(),sheetsArray[i]); //key, value
  }

  return map;
}

To add:

onOpen() function seems to be running forever too...

Code below for onOpen trigger function:

function onAppOpen() { 
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('⚙️ SLI Tools')
    .addSubMenu (ui.createMenu('Tracker')
      .addItem('↺ Refresh data', 'refreshDataToTrackerAlert')
      .addItem('⇧ Upload changes', 'uploadChangesAlert')
      .addItem('⇧ Upload ALL rows', 'uploadAllChangesAlert')       
      .addSeparator()       
      .addItem('✕ Clear data', 'clearDataAlert')          
    )
    .addSubMenu(ui.createMenu('Billing')
      .addItem('⇩ Export to PDF', 'printToPdf')       
      .addSeparator()       
      .addItem('🖌 Format tables', 'formatDataTable') 
    )
    .addSubMenu(ui.createMenu('Uploader')
      .addItem('⇧ Upload new JO', 'uploadDataToSourceAlert')       
      .addSeparator()       
      .addItem('✕ Clear data', 'clearUploaderDataAlert')
    )       
    .addSeparator() 
    .addSubMenu(ui.createMenu('Data')
      .addItem('↺ Refresh background query', 'refreshQueryAlert')
    )  
    .addToUi();  
}

The sheet itself also seems to reload forever.

This shows that the file is connected to the internet and not disconnected.

Update:

It seems to be working again. Could it be the GAS servers malfunctioning?

Update 2:

I haven't changed the code for the function in this test. The last 2 runs had an average of 19 seconds runtime.

After some minutes, the script gets stuck again.

Chrome also isnt at full CPU although it is expected to hog the memory with 4 tabs running.

Answer 1

Regarding Update 2

One of the things that might be making your script to have an unreliable performance it that it's using the global scope to call SpreadsheetApp methods and having them across multiple files.

Try moving out from the global scope those methods to check if that helps on improving the reliability of your script. One way simple way to do this is by creating an function responsible to initialize the global variables .

Related

• Calling variables at global scope and comparing it in Google apps script
• TypeError: Method "getRange" of null when using multiple scripts (Libraries) in Google scripts

---

From the question;

Update:

It seems to be working again. Could it be the GAS servers malfunctioning?

When there is a mayor failure on the Google side it is reported on https://www.google.com/appsstatus/dashboard/. It's worthy to note that there are other factors that might cause scripts to not work sporadically:

• The computer is busy. I have seen the same problem when some Windows system process are consuming a lot of resources (CPU / Disk Drive usage above 50%)
• The web browser is busy. If you have multiple tabs opened or have installed extensions it might be take a long time for Google Sheets to fully load all the stuff that it requires to run.
• The network (your device connection, ISP, etc.) might be busy making that the communication between your computer and the Google data centers fail.