How to create an Excel Sheet using the add matrix endpoint

From Tygron Support wiki
Jump to navigation Jump to search

The API endpoint Api session event editor excelsheet add matrix can be used to create an Excel Sheet using text input arguments that represent a matrix of cell content, a matrix of named cells and the name of created excel sheet.

Content format

The endpoint requires four input parameters:

  1. The name of the created excel sheet; Must be lower-case underscored.
  2. A row-major matrix with the content for each cell; The content can be number, or text.
  3. A row-major matrix with names put on particular cells. Empty names are ignored.
  4. The uploader name.

Named Cells

Names can be assigned to cells in Excel. Generally these names are used to reference the content of a cell inside an excel sheet. These references can be used directly in excel formulas. Additionally, named cells are used by the Tygron Platform to put data into and obtain data out of an instance of an Excel Sheet.

The following names can be used for input and output:

  • TQL
  • Indicator specific, for example: EXPLANATION and SCORE
  • Panel specific, for example: EXPLANATION

Example

Consider the situation where various Neighborhoods are present in a project. Several Neighborhoods have the attribute NBH_SCORE assigned. An indicator can be made that counts the number of neighborhoods that have an attribute value larger than a given threshold and divides that by the total number of neighborhoods. In the following example, the threshold is set to the value 5: 

curl 'https://engine.tygron.com/api/session/event/editorexcelsheet/add_matrix/' -H 'Content-Type: application/json' -H 'Authorization: Bearer API_TOKEN' -d '[ "my_indicator", [ [ "", "", "" ], [ "", "=COUNTIF(A2:A51,\">5\")/MAX(1,COUNT(A2:A51))", "=\"Neighborhoods above threshold: \"&COUNTIF(A2:A51,\">5\")&\" of \"&COUNT(A2:A51)" ] ], [ [ "SELECT_ATTRIBUTE_WHERE_NAME_IS_NBH_SCORE_AND_NEIGHBORHOOD_IS_X", "", "" ], [ "", "SCORE", "EXPLANATION" ] ], "indicatoragent" ]'

If a project contains 4 Neighborhoods, and their attribute values are 4, 7 , 3 and 9 respectively, the Indicator output will be:

  • Score: 0.5
  • Explanation: 
    Neighborhoods above threshold: 2 of 4


Notes

  • Formula cells are provided as text, starting with a "="-sign.
  • Numbers cells can also be provided as text using a formula; For example: "=5.14";
  • Use empty strings for empty cells; ""
  • An existing Excel Sheet can be updated with the set matrix endpoint.

How-to's

See also

API Endpoints

Retrieved from "https://support.tygron.com/w/index.php?title=How_to_create_an_Excel_Sheet_using_the_add_matrix_endpoint&oldid=96412"