I need a number to be converted to a name. In B1:B1000 I have numbers. In C1:C1000 I has function name. On writing C30 got an error: invoked too many times per second ... how do I do function on range C?

enter image description here

function name(choosen) {

var x = "x" ;

switch (choosen) {

case 55837649021 :
 x="Anton Hylderburg";
break;
case 55837109810 :
 x="John Strong";
break;
case 55837109410 : 
x="Elisabeth Joseph"
;break;    

}
  return x;
}

Google offers a limited amount of computing power to Sheets users. This restriction is particularly noticeable for scripts: they take a long time to run, and trying to invoke a script too often results in errors of the type you encounter.

The task you are trying to do here could be done with a single script invocation. It may still take a while to process the sheet, but at least you won't get the error about trying to invoke a script 1000 times at once.

function names() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var numberRange = sheet.getRange("B2:B1001");
  var nameRange = sheet.getRange("C2:C1001");
  var values = numberRange.getValues();
  var names = []; 
  for (var i=0; i<values.length; i++) {
    switch (values[i][0]) {
      case 55837649021 : 
        names.push(["Anton Hylderburg"]); break;
      case 55837109810 : 
        names.push(["John Strong"]); break;
      case 55837109410 : 
        names.push(["Elisabeth Joseph"]); break;
      default: 
        names.push([""]);
    }
  }
  nameRange.setValues(names);
}

This script can be invoked manually, from the editor window. Or you could add an item to the main menu if you use this often. Like this:

function onOpen() {
 var menu = [{name: "Insert Names", functionName: "names"}];
 SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
 jumpToDate();
}

This function will execute any time you open the spreadsheet, and will add an item Custom > Insert Names to the menu. Using this function, you can invoke the script any time you want.

It's also possible to run the script automatically, e.g., every hour. In the script editor, go to Resources > Current project's trigger, and add a trigger for function names.

  • wauw thank you pardon i am beginer in script .....or i need call fuction minimum 8x/day because number add across google form .... how i add in main menu often? – Centrum Volneho času Jul 10 '15 at 22:20
  • I added this to my answer. – user79865 Jul 10 '15 at 22:50

As an alternative to the use of a custom function, consider the use of built-in functions like vlookup(). From the previous linked help article:

Example

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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