How can I create a function that takes any number of tables as arguments?

General
1

I have this function in Excel Script Lab:

Libraries

https://appsforoffice.microsoft.com/lib/1/hosted/office.js
@types/office-js

office-ui-fabric-js@1.4.0/dist/css/fabric.min.css
office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css

core-js@2.4.1/client/core.min.js
@types/core-js

jquery@3.1.1
@types/jquery@3.3.1

Script:

/**
 * Create a list.
 * @customfunction
 * @param name string
 * @param args {string[]}
 */
function datatablex(display: string, ...args: string[]): any {
  const entity = {
    type: "Entity",
    text: display,
    properties: {
      Nombre: {
        type: "String",
        basicValue: display,
        propertyMetadata: {
          excludeFrom: {
            cardView: true
          }
        }
      }
    }
  };

  for (let i = 0; i < args[0].length; i += 2) {
    const name = args[0][i + 0];
    const value = args[0][i + 1];

    entity.properties[name] = {
      type: "String",
      basicValue: value
    };
  }
  return entity;
}

This function allows me to create a DataType: Entity with the parameters given in the function as arguments. Currently, this function takes a string which will be the displayed text in the cell, and then any number of arguments. These extra arguments always come in couples. The first is the name of the value and the second is the value itself. This repeats many times if you add many more parameters. Below is an image showing an example:

Current Example

My problem is that the extra arguments are currently strings and instead I want them to be tables (two dimensions each). I tried making changes in the function:

  • Replace the string with string and even string
  • Replace the property “String” type to “Array” and basicValue to elements.
  • Replace the args[0][i + 0] stuff with more . However, I couldn’t make it work. Sometimes it gives #Value! and others "Calc! depending on what I change. Below is an image explaining the imputs I want:

[Example imputs]

Below is an example of the output I want (I created it manually, not using the function cause obviously it doesn’t work)

[Example output]
Example when clicking one of the tables.

Example ouput inside

2

Goal:

You want to pass tables (2D arrays) instead of flat string arguments to your Excel custom function, and have your function dynamically convert them into properties in a DataType Entity.

Problem with current code:

Your function is designed to accept a list of strings, not 2D arrays. Also, it incorrectly tries to iterate over args[0] assuming it’s an array of key/value pairs, but if args is an array of arrays (tables), this logic breaks.

Correct approach:

Here is a revised version of your function that supports multiple 2D table inputs, where each table is expected to be 2 columns: [ [key1, value1], [key2, value2], ... ].

/**
 * Create a list from 2D arrays.
 * @customfunction
 * @param display Display text
 * @param tables Array of key/value pairs in table format (multiple 2D arrays)
 * @returns A custom entity
 */
function datatablex(display: string, ...tables: string[][][]): any {
  const entity: any = {
    type: "Entity",
    text: display,
    properties: {
      Nombre: {
        type: "String",
        basicValue: display,
        propertyMetadata: {
          excludeFrom: {
            cardView: true
          }
        }
      }
    }
  };

  for (const table of tables) {
    for (const [key, value] of table) {
      entity.properties[key] = {
        type: "String",
        basicValue: value
      };
    }
  }

  return entity;
}

How to use it in Excel:

Pass multiple 2D arrays (tables), each with 2 columns (first column = key, second = value).

Example usage in Excel custom function cell:

=datatablex("Person Info", A1:B3, D1:E2)
  • A1:B3 might contain:
Name   John
Age    30
City   Paris
  • D1:E2 might contain:

nginx

CopyEdit

Country  France
Email    john@example.com

This will generate an Entity with all those key/value pairs.

Notes:

  • Each input table must be 2 columns.
  • Excel custom functions don’t support true object types directly — Entities are a workaround.
3

Goal:

Change this function:

function datatablex(display: string, ...args: string[]): any

To this:

function datatablex(display: string, ...tables: string[][][]): any

Each table is a 2D array (e.g. from a range like A1:B3), where:

  • First column = property name
  • Second column = property value

Updated Working Code:

/**
 * Create an entity from one or more tables.
 * @customfunction
 * @param display Displayed text in the cell
 * @param tables  One or more 2D arrays with key/value pairs
 * @returns An Entity object
 */
function datatablex(display: string, ...tables: string[][][]): any {
  const entity: any = {
    type: "Entity",
    text: display,
    properties: {
      Nombre: {
        type: "String",
        basicValue: display,
        propertyMetadata: {
          excludeFrom: {
            cardView: true
          }
        }
      }
    }
  };

  for (const table of tables) {
    for (const row of table) {
      const [key, value] = row;
      if (key && value !== undefined) {
        entity.properties[key] = {
          type: "String",
          basicValue: value
        };
      }
    }
  }

  return entity;
}

How to Use in Excel:

In a cell, call the function like this:

=datatablex("Profile", A1:B3, D1:E2)

Where:

  • A1:B3 contains:
Name      John
Age       30
City      Paris
  • D1:E2 contains:
Country   France
Email     john@example.com

This will return a DataType Entity with all those properties, and display "Profile" in the cell.

Supports:

  • Any number of tables
  • Dynamic key/value pairs
  • Clean fallback for in-cell display