Global Parameters
All official Cryptosheets functions support a common way for querying, sorting, limiting, transforming and selecting specific data from integrated sources and providers called global parameters. They include _limit, _fields, _path, _count, _formatDates, _orderBy, _output, _distinct, _paths, _renameHeaders and _showHeaders.
TIP 👉 The most successful Cryptosheets users quickly learn to master the global parameters to save time and create the most relevant, useful & efficient datasets for their respective use cases
Examples
- Using the global parameters section that can be found inside the API console and UniConsole is the best way to learn and leverage the power of Cryptosheets global parameters.
- Find click + copy + paste examples below for Excel & Google Sheets and browse our YouTube channel playlist for global parameter examples.
YouTube Video Examples
TIP 👉 By default all global parameters follow their physical, sequential order of operation as they are written & positioned within a given formula
- DOES WORK:
=CS.SYMBOLS("_fields","symbol,name","_renameHeaders","symbol:ticker")- DOES NOT WORK:
=CS.SYMBOLS("_renameHeaders","symbol:ticker","_fields","symbol,name")- Combining or chaining multiple global parameters together is extremely powerful and can instantly transform complex nested JSON arrays into concise single cell data points. Keep in mind certain combinations and types of global parameters in tandem with larger data sets can create additional latency in your requests & formulas
Excel
_limit
_limit will return only the number of results you specify.
Usage
=CS.QUERYA("Cryptunit", "Earnings Auto", "_limit", "10")
_path
_path will query specific fields in a response using JMESPath query syntax.
Usage
=CS.QUERYA("Cryptunit", "Earnings Auto", "_path", "[].coins")
_paths
_paths will pull all possible paths in a JSON response, to be subsequently used in _path.
Usage
=CS.QUERYA("Cryptunit", "Earnings Auto", "_paths", "TRUE")
_fields
_fields will return only the fields (or column names) you specify.
Usage
=CS.QUERYA("Cryptunit", "Earnings Auto", "_fields", "hashrate,hashrate_auto")
TIP 👉 The fields you specify must match the fields (column headers) from the provider exactly
- You can easily rename any field or multiple fields using the
_renameHeadersglobal parameter in the formula- Example:
"_renameHeaders","crypto_symbol:ticker,crypto_name:name"
_formatDates
_formatDates will attempt to format a date series based on momentjs formatting.
Usage
=CS.QUERYA("Cryptunit", "Earnings Auto", "_formatDates", "MM DD YYYY")
TIP 👉 For providers returning datetime columns without names or named as
tor0etc
- You can easily rename them using the
_renameHeadersglobal parameter before the_formatDatesglobal parameter in the formula- Example:
"_renameHeaders","t:time","_formatDates","MM-DD-YYYY"
_count
_count will return the count of rows per the specified column header.
Usage
=CS.QUERYA("Cryptunit", "Earnings Auto", "_count", "MM DD YYYY")
_orderBy
_orderBy will order a column ascending or descending (prefixed with a '-')
Usage
=CS.QUERYA("Cryptunit", "Earnings Auto", "_orderBy", "coin_name,coin_algo")
TIP 👉 When using relative cell references in Excel & Google Sheets you'll most likely need to use a
'in front of the field your sorting by when sorting indescendingorder
- Example:
"_orderBy","-date"vs"_orderBy","A1"(where cell A1 is-datewill need to be typed into cell A1 as'-dateso the sheet doesn't think it's a formula)
_showHeaders
_showHeaders is a toggle to show or hide property values in a data response. In table formats, this typically involves hiding the first row.
Usage
=CS.QUERYA("Cryptunit", "Earnings Auto", "_showHeaders", "true")
_renameHeaders
_renameHeaders will rename headers based on specified native to alias mappings.
Usage
=CS.QUERYA("Cryptunit", "Earnings Auto", "_renameHeaders", "coin_name:name")
_distinct
Creates a duplicate-free version of the data, using SameValueZero for equality comparisons, in which only the first occurrence of each element is kept. The order of result values is determined by the order they occur in the initial data.
Usage
=CS.QUERYA("Cryptunit", "Earnings Auto", "_distinct", "algo_name")
Google Sheets
_limit
_limit will return only the number of results you specify.
Usage
=CSQUERYA("Cryptunit", "Earnings Auto", "_limit", "10")
TIP 👉 For endpoints that have their own native limits and/or limit parameters, in most cases the global
_limitparameter will not override the native limit parameterization
_path
_path will query specific fields in a response using JMESPath query syntax.
Usage
=CSQUERYA("Cryptunit", "Earnings Auto", "_path", "[].coins")
_paths
_paths will pull all possible paths in a JSON response, to be subsequently used in _path.
Usage
=CSQUERYA("Cryptunit", "Earnings Auto", "_paths", "TRUE")
_fields
_fields will return only the fields (or column names) you specify.
Usage
=CSQUERYA("Cryptunit", "Earnings Auto", "_fields", "hashrate,hashrate_auto")
_formatDates
_formatDates will attempt to format a date series based on momentjs formatting.
Usage
=CSQUERYA("Cryptunit", "Earnings Auto", "_formatDates", "MM DD YYYY")
_count
_count will return the count of rows per the specified column header.
Usage
=CSQUERYA("Cryptunit", "Earnings Auto", "_count", "MM DD YYYY")
_orderBy
_orderBy will order a column ascending or descending (prefixed with a '-')
Usage
=CSQUERYA("Cryptunit", "Earnings Auto", "_orderBy", "coin_name,coin_algo")
_showHeaders
_showHeaders is a toggle to show or hide property values in a data response. In table formats, this typically involves hiding the first row.
Usage
=CSQUERYA("Cryptunit", "Earnings Auto", "_showHeaders", "true")
_renameHeaders
_renameHeaders will rename headers based on specified native to alias mappings.
Usage
=CSQUERYA("Cryptunit", "Earnings Auto", "_renameHeaders", "coin_name:name")
_distinct
Creates a duplicate-free version of the data, using SameValueZero for equality comparisons, in which only the first occurrence of each element is kept. The order of result values is determined by the order they occur in the initial data.
Usage
=CSQUERYA("Cryptunit", "Earnings Auto", "_distinct", "algo_name")
Date & Timestamp Formats for _formatDates
- While you can technically specify any custom date time format (as long as your syntax is exactly correct) there are over 65 existing formats with abbreviated codes including many that match
ISOstandard and are incredibly easy to use. - Remember you can always use the
_formatDatesparameter with ANY API endpoint or UniConsole function inside the global parameters section of the API side panel consoles [IMG] - See a list of examples below & make sure to check out both the dedicated blog post and free template specifically for using the
_formatDatesglobal parameter.
| parameter | example | type | unit | description |
|---|---|---|---|---|
| MM-DD-YYYY hh:mm:ss.000 | datetime | |||
| YYYY-MM-DD | date | |||
| MM/DD/YYYY | date | |||
| MM-DD-YYYY | date | |||
| x | 1.36001E+12 | datetime | number | Unix Millisecond Timestamp |
| X | 1360013296 | datetime | number | Unix Timestamp |
| ZZ | -0700 -0600 ... +0600 +0700 | timezone | ||
| Z | -07:00 -06:00 ... +06:00 +07:00 | timezone | ||
| z or zz | EST CST ... MST PST | timezone | Time Zone (Note: as of 1.6.0, the z/zz format tokens have been deprecated from plain moment objects.) | |
| SSSS ... SSSSSSSSS | 000[0..] 001[0..] ... 998[0..] 999[0..] | time | second | |
| SSS | 000 001 ... 998 999 | time | second | |
| SS | 00 01 ... 98 99 | time | second | |
| S | 0 1 ... 8 9 | time | second | Fractional Second |
| ss | 00 01 ... 58 59 | time | second | |
| s | 0 1 ... 58 59 | time | second | Second |
| mm | 00 01 ... 58 59 | time | minute | |
| m | 0 1 ... 58 59 | time | minute | Minute |
| kk | 01 02 ... 23 24 | time | hour | |
| k | 1 2 ... 23 24 | time | hour | |
| hh | 01 02 ... 11 12 | time | hour | |
| h | 1 2 ... 11 12 | time | hour | |
| HH | 00 01 ... 22 23 | time | hour | |
| H | 0 1 ... 22 23 | time | hour | Hour |
| a | am pm | time | ||
| A | AM PM | time | AM/PM | |
| GGGG | 1970 1971 ... 2029 2030 | date | year | |
| GG | 70 71 ... 29 30 | date | year | Week Year (ISO) |
| gggg | 1970 1971 ... 2029 2030 | date | year | |
| gg | 70 71 ... 29 30 | date | year | Week Year |
| Y | 1970 1971 ... 9999 +10000 +10001 | date | year | |
| YYYY | 1970 1971 ... 2029 2030 | date | year | |
| YY | 70 71 ... 29 30 | date | year | Year |
| WW | 01 02 ... 52 53 | date | week | |
| Wo | 1st 2nd ... 52nd 53rd | date | week | |
| W | 1 2 ... 52 53 | date | week | Week of Year (ISO) |
| ww | 01 02 ... 52 53 | date | week | |
| wo | 1st 2nd ... 52nd 53rd | date | week | |
| w | 1 2 ... 52 53 | date | week | Week of Year |
| E | 1 2 ... 6 7 | date | day | Day of Week (ISO) |
| e | 0 1 ... 5 6 | date | day | Day of Week (Locale) |
| dddd | Sunday Monday ... Friday Saturday | date | day | |
| ddd | Sun Mon ... Fri Sat | date | day | |
| dd | Su Mo ... Fr Sa | date | day | |
| do | 0th 1st ... 5th 6th | date | day | |
| d | 0 1 ... 5 6 | date | day | Day of Week |
| DDDD | 001 002 ... 364 365 | date | day | |
| DDDo | 1st 2nd ... 364th 365th | date | day | |
| DDD | 1 2 ... 364 365 | date | day | Day of Year |
| DD | 01 02 ... 30 31 | date | day | |
| Do | 1st 2nd ... 30th 31st | date | day | |
| D | 1 2 ... 30 31 | date | day | Day of Month |
| Qo | 1st 2nd 3rd 4th | date | quarter | |
| Q | 1 2 3 4 | date | quarter | Quarter |
| MMMM | January February ... November December | date | month | |
| MMM | Jan Feb ... Nov Dec | date | month | |
| MM | 01 02 ... 11 12 | date | month | |
| Mo | 1st 2nd ... 11th 12th | date | month | |
| M | 1 2 ... 11 12 | date | month | Month |
| llll | Thu, Sep 4, 1986 8:30 PM | datetime | ||
| LLLL | Thursday, September 4, 1986 8:30 PM | datetime | Month name, day of month, day of week, year, time | |
| lll | 31659.85417 | datetime | ||
| LLL | 31659.85417 | datetime | Month name, day of month, year, time | |
| ll | 31659 | date | ||
| LL | 31659 | date | Month name, day of month, year | |
| l | 31659 | date | ||
| L | 31659 | date | Month numeral, day of month, year | |
| LTS | 0.854456019 | datetime | Time with seconds | |
| LT | 8:30:00 PM | datetime | Time |
Advanced Users
The 4th primary module of the Cryptosheets API console is called the Output Inspector. The Inspector interface uses a language called JMESPath that provides powerful tools allowing any Cryptosheets user to easily search, filter, inspect, slice, merge, extract, flatten, create nested functions, visualize the transformed output and much more -- with ANY Cryptosheets data response! The Output Inspector is also a critical part of working with and using the _path global parameter.
For additional guidance, tutorials, reference and other resources on using the JMESPath Query builder Data Wizard, please see the links below.
*Private training sessions and group teach ins are available for premium tier subscriptions and corporate or enterprise accounts, please contacts us through the Intercom support chat with any related inquiry.