Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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!

share|improve this question
    
The error message indicates that the error originates from line 20 in your code! If you want us to have a look, then please share the code. Welcome to Web Applications !! –  Jacob Jan Tuinstra Oct 7 '13 at 5:05
    
Thanks @Jacob, please find below: 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]; } } } –  KK82HK Oct 7 '13 at 8:22
    
That's a complete mess sorry, I can share the link to the google spreadsheet if that's more helpful? –  KK82HK Oct 7 '13 at 8:25
    
If the info can bare the daylight, then yes, that would be helpful. –  Jacob Jan Tuinstra Oct 7 '13 at 8:51
add comment

1 Answer 1

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

share|improve this answer
    
Many thanks @Tom Horwood, apologies for late response, have been out of the country. I will try this code and get back to you. Please click here if you'd like to see the spreadsheet: docs.google.com/spreadsheet/… –  KK82HK Oct 30 '13 at 6:15
    
Also thanks to @Jacob, would only let me put one person in at a time! –  KK82HK Oct 30 '13 at 6:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.