Differences

This shows you the differences between two versions of the page.

--- excel:formulas [2019/01/15 11:55] – su
+++ excel:formulas [2022/08/17 15:35] (current) – su
@@ Line 127: / Line 127: @@
 Some tags return JSON objects when queried. In order to make these more useful in Excel the add-in provide the "ParseJson(...)" formula this can be used to retrieve individual properties from JSON objects.
+  * Select a cell
   * Enter "=ParseJson(<json>, <property name>)" in the formula bar
-This takes 4 parameters:
+This takes 2 parameters:
-  - The fully qualified name of the data source you would like to query (you can get this using the "GetDataSources()" formula)
+  - The JSON object
-  - The name of the tag you want to query
+  - The name of the property you want to extract (optional, if unspecified the whole object will be returned)
-  - Whether a time stamps column should be included
-  - Whether a header row should be included
+This will return the value of the specified property in the provided JSON. If the JSON is invalid then an error will be returned.
+To get nested properties apply the ParseJson(...) formula multiple times. If the value of a property is a nested object then the ParseJson formula returns the JSON from the inner object, this can then be passed to the parse JSON object again to get the properties of the object.
+If the property being selected is an array then the formula will return the result as an Excel array and the formula acts like an array formula (i.e. you must select an area the size of the array horizotally and then press Ctrl + Shift + Enter to enter it). Therefore, if the root object is an arra you can parse it by not providing a property name parameter.
 ====== Tips and Tricks ======