Usage PivotAPI to Beautify Data On Pivot
Usage PivotAPI to Beautify Data On Pivot
In this article, we will review the capabilities of PivotAPI to customize cells. Additionally, we will review important properties, as well as some tricks, we can use to visualize data in a more beautiful way.
NOTE: Usage of these technics requires minimal knowledge of JavaScript
We will review data formatting for two use cases:
- Changing styles of the cell depending on the value in the cell;
- Converting seconds to HH:MM:SS format.
Changing styles of the cell depending on the value in the cell
Code:
const myTarget = {
type: ['value'],
values: [
{
title: 'formulaTitle' // put here desired column
}
]
};
widget.transformPivot(myTarget, (metadata, cell) => {
//Exclude 0 and empty rows
const isPositive = cell.value > 0;
const isNegative = cell.value < 0;
cell.style = cell.style || {};
let additionalStyle;
if (isPositive) {
cell.content = `${cell.content} ↑`;
additionalStyle = {
color: 'green',
fontWeight: 'bold'
};
} else if (isNegative) {
cell.content = `(${cell.content.replace('-', '')}) ↓`;
additionalStyle = {
color: 'red',
fontSize: '18px'
};
}
if (additionalStyle) {
cell.style = Object.assign(cell.style, additionalStyle);
}
});
Explanation:
We need to define columns where the script will be applied. It's done in the variable [myTarget]. This variable has two properties:
- type - refers to the type of cells, which will be modified by the script;
- values - an array of the values, which will be modified by the script.
So, our script is targeted to update cells, which shows values computed by the formula 'formulaTitle'. Once we have defined the target of the script, we will need to initiate widget.transformPivot(). As the first argument we are passing the target and as the second argument we send a callback function, that will be executed for the cells. The callback function receives information about the cell as the second argument.
It contains several important properties:
- value - this is a numeric value before formatting (for formulas only);
- content - this is formatted value, which is shown in a frame;
- style - additional styles of the cell.
In terms of our script, we did the following:
- Check the value in the cell to understand its sign;
- After this, depending on the sign we update the styles of the cell and change content by adding an arrow up or arrow down.
| Before | After |
 |
 |
Converting seconds to HH:MM:SS format.
Code:
const formulaTitle = 'Formatted'; //Name of the formula, which store integer;
const time_separator = ":"; //Delimiter
const hourSign = ''; //Symbol for hours if needed
const minuteSign = ''; //Symbol for minutes if needed
const secondSign = ''; //Symbol for seconds if needed
widget.transformPivot(
{
type: ['value'] //We are going to process values (formulas) only
},
processCell
);
function processCell(metadata, cell) {
if (metadata.measure.title === formulaTitle) { //Find formula with the name defined in a variable [formulaTitle]
try {
cell.content = computeContent(cell); //Convert value into desired format
} catch(err) {
console.warn('Unable to process cell');
}
};
}
function computeContent(cell) {
if (!cell.value || isNaN(parseInt(cell.value))) {
return cell.content;
} else {
const value = parseInt(cell.value);
const sign = value < 0 ? "-" : "";
const hours = parseInt(value / 3600);
const minutes = parseInt((value - (hours * 3600)) / 60);
const seconds = value - (hours * 3600) - (minutes * 60);
const hoursText = `${hours < 10 ? "0" + hours : hours}${hourSign}`;
const minutesText = `${minutes < 10 ? "0" + minutes : minutes}${minuteSign}`;
const secondsText = `${seconds < 10 ? "0" + seconds : seconds}${secondSign}`;
return `${sign}${hoursText}${time_separator}${minutesText}${time_separator}${secondsText}`;
}
}
Explanation:
This script processes all the cells, which are produced by the formulas. If you have multiple formulas in your pivot, then the time of execution can be quite long. If you want to process some particular cells, then check the first example - it shows how to limit columns, which are processed by the script.
We are going to process only values defined by the formula with the title 'Formatted', so we add this condition to the function callback. If this condition is not met (another formula computed the value), we will not execute the further logic. The further logic converts an integer from the cell to the format HH:MM:SS. The computed value is returned and set as [cell.content].
Result of execution (original value is stored in the column [Original], the formatted one in the column [Formatted]):
I hope you find this article useful and leverage the knowledge shared about PivotAPI capabilities and their usage.
Please share your experience in the comments!
