Prerequisite: log in to io-base from the IndaSheet module.
The IndaSheet add-in provides access to several formulas in Excel :
IDB_AGGREGATED_VALUES: retrieves aggregated data for a metric between two dates.
IDB_AGGREGATED_VALUE: retrieves the aggregated value of a metric between two dates.
IDB_VALUES: retrieves data for a metric between two dates.
IDB_LATEST: retrieves the last recorded value for one or more metrics.
IDB_INSTANT: retrieves the value at a given date.
IDB_INSTANT_BEFORE: retrieves the last value recorded before a given date.
IDB_INSTANT_AFTER: retrieves the first value recorded after a given date.
IDB_RANGE: retrieves data for a metric between two dates.
IDB_DATE: returns a date in ISO format, which can be used in io-base formulas.
These formulas can be constructed using a graphical interface, or they can be entered directly into a cell.
IDB_RANGE formula
Signature of the formula
=iobase.IDB_RANGE(datasource;metric;start_date;end_date;aggregation;[interval];[displayInRow];[displayTimestamp];[displayCount];[displayMetric];[precision])
The last 6 parameters are optional. If they are not filled in, the default values are described below.
Details of the parameters
datasource : STRING : name of the database (in general, the production data is in the prod database)
metric : STRING : name of the metric
start_date : STRING : start date of the range. This date must be in "yyyy-MM-ddThh:mm:ssZ" format (iso format).
end_date : STRING : end date of the range. This date must be in "yyyy-MM-ddThh:mm:ssZ" format (iso format).
aggregation : STRING : can contain the following values :
MIN : minimum value
MAX : maximum value
SUM : sum
AVG : average
COUNT : number of values
none : no aggregation
[interval] : STRING : grouping to be applied to the aggregation. The possible values are in number of days (d), hours (h), minutes (m), seconds (s). Example 1d will return a value per day. This parameter is optional, it is only necessary if an aggregation is defined
[displayInRow]: BOOLEEN: Allows you to define whether the values will be displayed in rows or columns. The possible values are :
TRUE: display values in column
FALSE: display values in line
By default, the values will be displayed in column.
[displayTimestamp]: BOOLEEN: Allows you to define whether the timestamp should be displayed before the values. The possible values are :
TRUE: display the timestamp in the first row (or column as the case may be)
FALSE: only the values are displayed
By default the timestamp will be displayed.
[displayCount]: BOOLEEN: Allows you to define whether the number of results returned should be displayed before the values. The possible values are :
TRUE: display the number of results before the values
FALSE: the number of results is not displayed
By default, the number of results will not be displayed.
[displayMetric] : BOOLEEN : Allows to define if the name of the metric should be displayed at the top, before the values. The possible values are :
TRUE: display the metric name in the first line, before the results
FALSE: the metric name is not displayed
By default, the name of the metric is displayed
[precision] : STRING : precision which will be applied for the calculation of the aggregation. Possible values are in number of days (d), hours (h), minutes (m), seconds (s).
Example :
start date: 01/01/2023
end date: 31/03/2023
Aggregation: AVG
Frequency: 1d
Precision: 1h
The result of the query will be the average per day of the tag. By indicating a precision, we will force the query to make groups to improve the calculation precision. In this case, a first average calculation will be made every hour of the day. Then the average of all these averages will be done to obtain the value for the day. If in precision 1h had been entered, then the daily average would have been obtained by averaging all the hourly values.
If the parameter is not specified, the same value as the interval will be entered.
Notes
If an aggregation is selected, but no frequency is specified, then the calculation will be done on all the values in the range. For example, COUNT between two dates with no frequency will return the number of values in the base between the start and end dates
if an aggregation is selected, but no value on one of the frequency groupings is found, then the value returned is 0.
for the RANGE function, it is only possible to call one metric at a time
Examples
=iobase.IDB_RANGE("prod"; B6; A3; B3; "AVG"; "1d"; FALSE; TRUE; FALSE; FALSE)
With B6 = tag name
A3 = 2023-01-01T00:01:00Z
B3 = 2023-04-18T23:59:00Z
This formula will return the daily average value of the tag between 1 January 2023 and 18 April 2024. There will therefore be one value returned per day, displayed in columns. The first column will return the timestamp, and the second the value. The name of the tag and the number of results will not be displayed
To manage dynamic dates, it is possible to use Excel formulas. For example, a cell can contain the current date at 23:59, and refresh automatically with the following formula: =CONCAT(YEAR(NOW());"-";RIGHT(CONCATENER("0";MONTH(NOW()));2);"-";RIGHT(CONCATENER("0";DAY(NOW()));2); "T23:59:00Z")
The IDB_RANGE formula can then be based on this cell, which refreshes itself.
IDB_LATEST formula
Signature of the formula
=iobase.IDB_LATEST(datasource;metric;start_date;end_date;aggregation;[interval];[displayInRow];[displayTimestamp];[displayCount];[displayMetric];[precision])
The last 3 parameters are optional. If they are not filled in, the default values are described below.
Details of the parameters
datasource : STRING : name of the database (in general, the production data are in the prod database)
metric : STRING : name of the metric
[displayInRow] : BOOLEEN : Allows you to define whether the values will be displayed in rows or columns. The possible values are :
TRUE: display values in column
FALSE: display values in line
By default, the values will be displayed in column.
[displayTimestamp]: BOOLEEN: Allows you to define whether the timestamp should be displayed before the values. The possible values are :
TRUE: display the timestamp in the first row (or column as the case may be)
FALSE: only the values are displayed
By default the timestamp will be displayed.
[displayMetric] : BOOLEEN : Allows to define if the name of the metric should be displayed at the top, before the values. The possible values are :
TRUE: display the metric name in the first line, before the results
FALSE: the metric name is not displayed
By default, the metric name is displayed
Notes
for the LATEST function, it is possible to call several metrics at the same time. To do this, separate the names of the metrics with ",".
if several metrics are selected, they must all be part of the same Source (same database). Otherwise, separate queries should be made.
Examples
=iobase.IDB_LATEST("prod"; "tag1,tag2"; TRUE; TRUE)
This formula will return the last value in base for the tag1 and tag2 metrics. There will therefore be one value returned per metric, displayed in a row. The first line will return the timestamp, and the second the value. The name of the tag will be displayed.
Function IDB_INSTANT
This function retrieves the value of one or more metrics recorded at a specific timestamp.
Function Signature
=iobase.IDB_INSTANT(datasource;metric;date;[displayInRow];[displayTimestamp];[displayMetric])
The last 3 parameters are optional. If they are not provided, the default values described below are used.
Parameter Details :
datasource : STRING : database name (typically, production data is found in the 'prod' database)
metric : STRING : metric name
date : STRING : the reference timestamp in ISO format (e.g., "2025-04-14T08:00:38Z").
[displayInRow] : BOOLEAN : Defines whether values will be displayed in rows or columns. Possible values are:
TRUE : display values in columns
FALSE : display values in rows
By default, values will be displayed in columns.
[displayTimestamp] : BOOLEAN : Defines whether the timestamp should be displayed before the values. Possible values are:
TRUE : display the timestamp in the first row (or column, as applicable)
FALSE : only values are displayed
By default, the timestamp will be displayed.
[displayMetric] : BOOLEAN : Defines whether the metric name should be displayed at the top, before the values. Possible values are:
TRUE : display the metric name in the first line, before the results
FALSE : the metric name is not displayed
By default, the metric name is displayed.
[IsLocalDate] : BOOLEAN :
TRUE : display the timestamp in local format
FALSE : display the timestamp in ISO format
Notes :
For the INSTANT function, it is possible to call multiple metrics at once. To do this, separate the metric names with commas ",".
If multiple metrics are selected, they must all belong to the same Source (same database). Otherwise, separate queries will be necessary.
If no value exists at the exact selected timestamp, the query will return no value.
Example :
=iobase.IDB_INSTANT("main"; "nj26uv_temperature_maternelle,nj26uv_temperature_garderie"; "2025-04-14T08:00:38Z"; FAUX; VRAI; VRAI; FAUX)
This formula will return the value from the database at the selected timestamp 2025-04-14T08:00:38Z for the metrics nj26uv_temperature_maternelle and nj26uv_temperature_garderie. There will therefore be one value returned per metric :
Function IDB_INSTANT_BEFORE
This function retrieves the value of one or more metrics recorded just before a specific timestamp.
Function Signature:
=iobase.IDB_INSTANT_BEFORE(datasource;metric;date;[displayInRow];[displayTimestamp];[displayMetric])
The last 3 parameters are optional. If they are not provided, the default values are applied (display in columns, with timestamp and metric name).
Parameter Details
datasource : STRING : database name (e.g., "prod").
metric : STRING : metric name or names separated by commas (e.g., "tag1,tag2").
date : STRING : the reference timestamp in ISO format (e.g., "2023-04-11T14:45:43Z").
[rangeLimiter] : STRING : Allows defining a maximum time window for searching the previous value. The function will search for the last recorded value before the chosen date, but will stop its search if it needs to go back further than the duration specified in "rangeLimiter". The expected value is a duration (e.g., "2h", "30m", "10s" for hour, minute, second).
To ensure performance and security, the search is restricted to a 24-hour period prior to the specified date.
[displayInRow] : BOOLEAN :
TRUE : display values in columns (default).
FALSE : display values in rows.
[displayTimestamp] : BOOLEAN :
TRUE : display the exact timestamp of the found value (default).
FALSE : only values are displayed.
[displayMetric] : BOOLEAN :
TRUE : display the metric name (default).
FALSE : the metric name is not displayed.
[IsLocalDate] : BOOLEAN :
TRUE : display the timestamp in local format
FALSE : display the timestamp in ISO format
Notes :
It is possible to call multiple metrics simultaneously by separating them with commas.
If multiple metrics are selected, they must belong to the same
datasource. Otherwise, separate queries are necessary.If no value exists exactly at the specified
date, the function will return the last recorded value strictly before this timestamp, constrained by therangeLimiterif provided.
Example:
=iobase.IDB_INSTANT_BEFORE("main"; "nj26uv_temperature_maternelle,nj26uv_temperature_garderie"; "2025-04-14T08:00:10Z"; "1d"; FAUX; VRAI; VRAI; FAUX)
This formula will return the value recorded in the database just before the selected timestamp 2025-04-14T08:00:10Z for the metrics nj26uv_temperature_maternelle and nj26uv_temperature_garderie. There will therefore be one value returned per metric:
Function IDB_INSTANT_AFTER
This function retrieves the value of one or more metrics recorded just after a specific timestamp.
Function Signature
=iobase.IDB_INSTANT_AFTER(datasource;metric;date;[rangeLimiter];[displayInRow];[displayTimestamp];[displayMetric];[IsLocalDate])
The last 3 parameters are optional. If they are not provided, the default values are applied (display in columns, with timestamp and metric name).
Parameter Details :
datasource : STRING : database name (e.g., "prod").
metric : STRING : metric name or names separated by commas (e.g., "tagA,tagB").
date : STRING : the reference timestamp in ISO format (e.g., "2023-11-20T08:00:00Z").
[rangeLimiter] : STRING : Allows defining a maximum time window for searching the next value. The function will search for the first recorded value after the chosen date, but will stop its search if it needs to go further forward than the duration specified in "rangeLimiter". The expected value is a duration (e.g. "2h", "30m", "10s" for hour, minute, second).
To ensure performance and security, the search is restricted to a 24-hour period following the specified date.
[displayInRow] : BOOLEAN :
TRUE : display values in columns (default).
FALSE : display values in rows.
[displayTimestamp] : BOOLEAN :
TRUE : display the exact timestamp of the found value (default).
FALSE : only values are displayed.
[displayMetric] : BOOLEAN :
TRUE : display the metric name (default).
FALSE : the metric name is not displayed.
[IsLocalDate] : BOOLEAN :
TRUE : display the timestamp in local format
FALSE : display the timestamp in ISO format
Notes:
It is possible to call multiple metrics simultaneously by separating them with commas.
If multiple metrics are selected, they must belong to the same
datasource. Otherwise, separate queries are necessary.If no value exists exactly at the specified
date, the function will return the first recorded value strictly after this timestamp, constrained by therangeLimiterif provided.
Example:
=iobase.IDB_INSTANT_AFTER("main"; "nj26uv_temperature_maternelle,nj26uv_temperature_garderie"; "2025-04-14T08:00:10Z"; "1d"; FAUX; VRAI; VRAI; FAUX)
This formula will return the value recorded in the database just after the selected timestamp 2025-04-14T08:00:10Z for the metrics nj26uv_temperature_maternelle and nj26uv_temperature_garderie. There will therefore be one value returned per metric :
IDB_DATE Formula
The "IDB_DATE" formula allows you to convert a Date object into a date formatted to the ISO standard. This conversion takes the local time zone into account.
The purpose is to then allow you to use this function within other IO-base functions to retrieve data over specific periods.
Formula Signature : =iobase.IDB_DATE([date])
[date] parameter :
optional: If this parameter is omitted, the function returns the current date and time.
expected type: The [date] parameter must be a Date object, not a simple text string.
Examples :
=iobase.IDB_DATE(TODAY()+4) : Returns today's date + 4 days.
=iobase.IDB_DATE(NOW()) : Returns the current date.
Notes :
You can convert a text string into a proper Date object using the excel DATEVALUE and TIMEVALUE functions.
For example :
Aggregated_values Formula
Principle: Calculates and returns a series of aggregated values (average, sum, etc.) over a given period.
The function divides the selected period into regular, configurable intervals (frequency) and returns one value for each of these intervals.
Available aggregations are:
min
max
Avg (average)
Count
Sum
Median
StdDev (standard deviation)
First
Last
Integral
Syntax: =iobase.IDB_AGGREGATED_VALUE(datasource; metric; start_date; end_date; aggregation; [interval]; [interpolationType]; [integralTimeUnit]; [targetUnit]; [filter]; [isLocalDate]; [until_now]; [displayInRow]; [displayTimestamp]; [displayMetric])
Parameter Details:
datasource (STRING, mandatory): name of the database
metric (STRING, mandatory): name of the metric
start_date (STRING, mandatory): start date of the range. This date must be in the "yyyy-MM-ddThh:mm:ssZ" format (ISO format).
end_date (STRING, mandatory): end date of the range. This date must be in the "yyyy-MM-ddThh:mm:ssZ" format (ISO format).
aggregation (STRING, mandatory): The following aggregations are available: MIN: minimum value MAX: maximum value SUM: sum AVG: average COUNT: number of values Median: median StdDev: standard deviation First: first value of the selected period Last: last value of the selected period Integral: integral
[interval] (STRING, mandatory): grouping to apply to the aggregation. Possible values are in number of days (d), hours (h), minutes (m), seconds (s). Example: "1h" calculates one value every hour.
[interpolationType] (STRING, optional): Defines the calculation method for values located between recorded data points.
skip: Creates no value between data points, treating each measurement as an isolated event.
previous: Considers a value to remain constant and valid until the next measurement arrives (stair-step behavior).
linear: Calculates missing values by assuming a linear progression (a straight line) between each measurement point.
Note : If no interpolation type is specified, the "previous" type is applied.
Warning : This parameter must be left empty for aggregations of type "interval".
[integralTimeUnit] (STRING): time unit to use for the integral calculation (e.g., "s", "m", "h", "d").
Warning: This parameter should only be filled in for aggregations of type "interval". It must be left empty for other types of aggregations.
[targetUnit] (STRING, optional): target unit for a conversion.
[filter] (STRING, optional): allows to only consider values that match a criterion.
Example: “value>5”, to only take into account values greater than 5 in the calculation.
[isLocalDate] (BOOLEAN, optional): allows selecting whether to display the date in ISO or local format. TRUE: local format False: ISO format
Note: By default, the local format is applied.
[until_now] (BOOLEAN, optional): If TRUE, uses the current date and time as the end date, ignoring the end_date parameter.
[displayInRow] (BOOLEAN, optional): Allows defining whether the values will be written in a row or in a column. Possible values are: TRUE: display of values in a column FALSE: display of values in a row
Note: By default, values are displayed in a column.
[displayTimestamp] (BOOLEAN, optional): Allows defining whether the timestamp of the values should be displayed. Possible values are: TRUE: display of the timestamp FALSE: only the values are displayed
Note: By default, the timestamp is displayed.
[displayMetric] (BOOLEAN, optional): Allows defining whether the name of the metric should be displayed in the result. Possible values are: TRUE: display of the metric name FALSE: the metric name is not displayed
Note: By default, the metric name is not displayed.
Example :
Determine the average hourly electricity consumption over a day.
=iobase.IDB_AGGREGATED_VALUES("main";"nj26uv_nexobc"; "2025-06-17T04:00:00Z"; "2025-06-18T04:00:00Z"; "Avg"; "1h"; "Previous"; ""; ; ; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE)
Aggregated_value Formula
Principle: Calculates and returns a single aggregated value (average, sum…) over an entire given period.
The function treats the complete time period (from the start_date to the end_date) as a single block to extract one unique result.
Available aggregations are :
min
max
Avg (average)
Count
Sum
Median
StdDev (standard deviation) First Last Integral
Syntax :
=iobase.IDB_AGGREGATED_VALUE(datasource; metric; start_date; end_date; aggregation; [interval]; [interpolationType]; [integralTimeUnit]; [targetUnit]; [filter]; [isLocalDate]; [until_now]; [displayinRow]; [displayTimestamp]; [displayMetric])
Parameter Details :
datasource (STRING, mandatory): name of the database
metric (STRING, mandatory): name of the metric
start_date (STRING, mandatory): start date of the range. This date must be in the "yyyy-MM-ddThh:mm:ssZ" format (ISO format).
end_date (STRING, mandatory): end date of the range. This date must be in the "yyyy-MM-ddThh:mm:ssZ" format (ISO format).
aggregation (STRING, mandatory): The following aggregations are available:
MIN: minimum value
MAX: maximum value
SUM: sum
AVG: average
COUNT: number of values
Median: median
StdDev: standard deviation
First: first value of the selected period
Last: last value of the selected period
Integral: integral
[interval] (STRING, optional): grouping to apply to the aggregation. Possible values are in number of days (d), hours (h), minutes (m), seconds (s).
Example: "1h" calculates a value every hour.
[interpolationType] (STRING, optional): Defines the calculation method for values located between recorded data points.
skip: Creates no value between data points, treating each measurement as an isolated event.
previous: Considers a value to remain constant and valid until the next measurement arrives (stair-step behavior).
linear: Calculates missing values by assuming a linear progression (a straight line) between each measurement point.
Note: If no interpolation type is specified, the "previous" type is applied.
Warning: This parameter must be left empty for aggregations of type "integral".
[integralTimeUnit] (STRING): time unit to use for the integral calculation (e.g., "s", "m", "h", "d").
Warning: This parameter should only be filled in for aggregations of type "integral". It must be left empty for other types of aggregations.
[targetUnit] (STRING, optional): target unit for a conversion.
[filter] (STRING, optional): allows to only consider values that match a criterion.
Example: “value>5”, to only take into account values greater than 5 in the calculation.
[isLocalDate] (BOOLEAN, optional): allows selecting whether to display the date in ISO or local format TRUE: local format False: ISO format
Note: By default, the local format is applied.
[until_now] (BOOLEAN, optional): If TRUE, uses the current date and time as the end date, ignoring the end_date parameter.
[displayInRow] (BOOLEAN, optional): Allows defining whether the values will be written in a row or in a column. Possible values are: TRUE: display of values in a column FALSE: display of values in a row
Note: By default, values are displayed in a column.-
[displayTimestamp] (BOOLEAN, optional): Allows defining whether the timestamp of the values should be displayed. Possible values are: TRUE: display of the timestamp FALSE: only the values are displayed
Note : By default, the timestamp is displayed.
[displayMetric] (BOOLEAN, optional): Allows defining whether the name of the metric should be displayed in the result. Possible values are: TRUE: display of the metric name FALSE: the metric name is not displayed
Note : By default, the metric name is not displayed.
Example: To get the average electricity consumption for a full day.
=iobase.IDB_AGGREGATED_VALUE("main"; "nj26uv_nexobc"; "2025-06-17T06:00:00Z"; "2025-06-18T06:00:00Z"; "Avg"; "1h"; "Previous"; ""; ; ; FALSE; FALSE; TRUE)
This formula returns a single value corresponding to the average calculated over the entire day, taking into account the duration of each measurement for a fair calculation.
Values Formula
Principle : Retrieves and returns all raw data (timestamp and value) recorded for a metric over a given period.
This function does not perform any aggregation calculations (average, sum, etc.). It is used to extract data as it is stored in the database. The result is a two-column array that spreads into adjacent cells.
Syntax : =iobase.IDB_VALUES(datasource; metric; start_date; end_date; [filter]; [isLocalDate]; [until_now]; [displayInRow]; [displayTimestamp]; [displayCount]; [displayMetric])
Parameter Details:
datasource: STRING: name of the database (production data is usually in the prod database)
metric: STRING: name of the metric
start_date: STRING: start date of the range. This date must be in the "yyyy-MM-ddThh:mm:ssZ" format (ISO format).
end_date: STRING: end date of the range. This date must be in the "yyyy-MM-ddThh:mm:ssZ" format (ISO format).
[filter]: STRING: (Optional), allows filtering the raw data to be retrieved.
Example: “value>5”, to only retrieve points where the value is greater than 5.
[isLocalDate]: BOOLEAN: (Optional) Allows selecting whether to display the date in ISO or local format. TRUE: local format FALSE: ISO format (default)
[until_now]: BOOLEAN: (Optional) If TRUE, uses the current date and time as the end date, ignoring the end_date parameter.
[displayInRow]: BOOLEAN: (Optional) Allows defining whether the values will be written in a row or in a column. TRUE: display of values in a column FALSE: display of values in a row By default, values will be displayed in a column.
[displayTimestamp]: BOOLEAN: (Optional) Allows defining whether the timestamp should be displayed before the values. TRUE: display of the timestamp in the first row/column. FALSE: only the values are displayed. By default, the timestamp will be displayed.
[displayCount]: BOOLEAN: (Optional) Allows defining whether the number of retrieved results should be displayed before the values. TRUE: display of the result count before the values. FALSE: the result count is not displayed. By default, the result count is not displayed.
[displayMetric]: BOOLEAN: (Optional) Allows defining whether the name of the metric should be displayed at the top, before the values. TRUE: display of the metric name in the first row. FALSE: the metric name is not displayed. By default, the metric name is displayed.
Example: To extract all measurements recorded for a metric between 9:00 AM and 9:30 AM.
=iobase.IDB_VALUES("main"; "nj26uv_nexobc"; "2025-06-17T07:00:00Z"; "2025-06-17T07:30:00Z"; ; ; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE)