Google-sheets – Run a custom script more than 20 times in Google Spreadsheet

google sheetsgoogle-apps-script

I need a way of running a custom script more than 20 times in my spreadsheet. I'm creating a class assessment spreadsheet that will be used as a master across my school, and it needs to retrieve the names of the students in each class. Each student's name is a sheet name in the spreadsheet. These are retrieved by numerical reference to the sheet they are on (i.e. =getsheetname(2) returns 'John Smith' for the spreadsheet for class 2B, but 'Sarah Jones' for class 5A's spreadsheet).

However I keep getting:

error: too many simultaneous invocations: Spreadsheets (line20, file "Code")

This is the code:

function getSheetName1(index) { 
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var allSheets = ss.getSheets(); 
  var allSheetNames = new Array(); 
  var SheetNumber = allSheets.length; 
  if (arguments.length == 0) { 
    return SpreadsheetApp.getActiveSheet().getName();
  } else { 
    for (var i = 0; i < SheetNumber; i++) { 
      allSheetNames[i] = allSheets[i].getName();
    } 
    if (index == 0) { 
      return allSheetNames; 
    } else { 
      return allSheetNames[index-1];
    }
  }
}

I understand this is because I can only run the custom script 20 times, but there are 30 students in each class. Is there a way to circumvent this? I have only a basic understanding of code so your patience would be appreciated!

Best Answer

A couple of days of looking at your code and I think I may have a solution, but it's a little bit of a kludge.

You can create a function that returns a 2D array which will fill the cells below and to the right of the original cell. So I would try something like this: (sample sheet here)

= getSheetNames(2)

where your student sheets are after the second sheet, and use this custom function:

function getSheetNames(after) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  if (arguments.length === 0) {
    after = 0;
  }
  var allSheets = ss.getSheets();
  var allSheetNames = [];
  var sheetCount = allSheets.length;

  for (var i = after; i < sheetCount; i++) {
    //as you're putting them all in one row, you don't use array wrappers
    allSheetNames.push(allSheets[i].getName());
    //because it's merged need to add extra cell
    allSheetNames.push('');
  }
  return allSheetNames;
}

Edit - as you are using one row and inserting into merged cells - I have made some changes

Related Topic