Create Custom Names

Custom user-defined names are identifiers that represent information in the sheet. A custom name can refer to a range of cells or a formula.

You can define a custom name and then use the name in formulas. When the formula is evaluated, the custom name's value is referenced and evaluated. Use the addCustomName method to create a custom name.

The following code sample creates a custom name.

activeSheet.setValue(0, 0, 1);
activeSheet.setValue(0, 1, 2);
activeSheet.setValue(0, 2, 3);

// Add custom name by setting the isReadOnly parameter to true
activeSheet.addCustomName("customName1","=12", 0, 0, 'readonly', true);

activeSheet.addCustomName("customName2","Average(20,45)", 0, 0);
activeSheet.addCustomName("customName3", "=$B$1:$C$1", 0, 0);

activeSheet.setFormula(1, 0, "customName1");
activeSheet.setFormula(1, 1, "customName2");
activeSheet.setFormula(1, 2, "sum(customName3)");

SpreadJS does not allow you to change or remove read-only custom names from the Name Manager Dialog in SpreadJS Designer. If you select a read-only custom name, the Edit and Delete buttons are disabled, and double-clicking on the read-only custom name will be unresponsive. The read-only status of a custom name can only be set or changed using the API.

Update Custom Names

You can update the formulas or any other information, such as changing the name or the comment for an existing custom name using the set method of the NameInfo class.

The following code sample enables you to update the custom name information:

// On renaming custom names, all referenced formulas are recalculated.
activeSheet.getCustomName("customName2").set("updatedName");

Custom name formulas without sheet names are updated to use the spread instance's custom name when there is no custom name in the sheet.

The following code sample enables you to use spread’s custom name:

spread.sheets[0].setFormula(0, 0, "=customName1+1");
spread.sheets[0].setFormula(1, 0, "=Sheet1!customName1+1");
spread.addCustomName("customName1", "111"); // A1 shows 111, A2 keeps #NAME!
spread.getCustomName("customName1").set("customUpdateName1"); // A1:=bbb+1 A2:=Sheet1!customName1+1

However, if the updated custom name already exists, then an ‘Invalid custom name’ error is thrown. For example:

activeSheet.addCustomName("updatedName", "222");
activeSheet.getCustomName("customName2").set("updatedName");

Various other scenarios supported by SpreadJS for updating custom name information are as follows:

1. When the old name is not used in the formula and the new name is referenced in the formula, the cell formula referenced in the new name is recalculated after the set name. For example:

   activeSheet.setFormula(2, 0, "updatedName+1"); // the formula result is #NAME!
   activeSheet.getCustomName("customName1").set("updatedName"); // The formula result is 13

2. When both the new name and old name are referenced in the formula. For example:

   // Exception 'Both the current name and new name are referenced in cell formulas' is thrown.
   activeSheet.setFormula(1, 0, "customName1");
   activeSheet.setFormula(1, 1, "updatedName");
   activeSheet.getCustomName("customName1").set("updatedName");

3. Update the formula and rename the custom name. For example:

   activeSheet.getCustomName("customName2").set("updatedName","SUM(20,45)");

4. Update the custom name formula with the base row and base column and update the comment. For example:

   activeSheet.getCustomName("customName2").set(null,"=A1", 0,1, "the cell adjacent to the upper left corner");

5. Update the custom name comment. For example:

   activeSheet.addCustomName("customName4", "=$A$1");
   activeSheet.getCustomName("customName4").set(null, null, null, null, "the A1 cell in current sheet");

6. Update the custom name which is used in another custom name. For example:

   activeSheet.addCustomName("customName4", "=$A$1");
   activeSheet.addCustomName("customName5", "=customName4+1");
   activeSheet.getCustomName("customName4").set("customName6", "=$C$2", null, null); // the custom name customName5 will be "=customName6+1"

7. Update the custom name which is used in another custom name where the old name is not used but new name is in use. For example:

   activeSheet.addCustomName("customName4", "=$A$1");
   activeSheet.addCustomName("customName5", "=customName6+1");
   activeSheet.getCustomName("customName4").set("customName6", "=$D$2", null, null); //customName4 will be changed to customName6

Note: SpreadJS does not modify the formula as per workbook’s custom names.