You can create a GaugeKPI sparkline using the GAUGEKPISPARKLINE function in a formula: =GAUGEKPISPARKLINE(targetValue,currentValue,minValue,maxValue,showLabel?,targetValueLabel?,currentValueLabel?,minValueLabel?,maxValueLabel?,fontArray?,minAngle?,maxAngle?,radiusRatio?,gaugeType?,colorRange?).
The function has the following parameters:
targetValue: (Required) [Number] The target value in the gaugeKPI sparkline, the target value is between minValue and maxValue.
currentValue: (Required) [Number] The current value in the gaugeKPI sparkline, the current value is between minValue and maxValue.
minValue: (Required) [Number] The min value in the gaugeKPI sparkline, the minValue is less than maxValue.
maxValue: (Required) [Number] The max value in the KPI sparkline. the maxValue is bigger than minValue.
showLabel: (optional) [Boolean] Whether should show the label of targetValue, currentValue, minValue and maxValue. If the showLabel is false, will never show the label. If the showLabel is true, when the cell width is not wide enough or the cell height is not high enough to show one label, will show the graph and other label, until the cell has enough width and height, will show both graph and label. The default value is true.
targetValueLabel: (optional) [String] The displayed label of target value. The default value is the same as the targetValue.
currentValueLabel: (optional) [String] The displayed label of current value. The default value is the same as the currentValue.
minValueLabel: (optional) [String] The displayed label of min value. The default value is the same as the minValue.
maxValueLabel: (optional) [String] The displayed label of max value. The default value is the same as the maxValue.
fontArray: (optional) [CalcArray] The fontArray have four font string items that follow the CSS font format, each font string matches font of target value label(default value is "16px Calibri"), current value label(default value is "bold 22px Calibri"), min value label(default value is "12px Calibri") and max value label(default value is "12px Calibri") label correspondingly. Will only work while the showLabel is true.
minAngle: (optional) [Number] The min angle value of circle type, the minAngle should be less than maxAngle. (0 is the 12 o'clock position, -90 is the 9 o'clock position, 90 is the 3 o'clock position, -180 and 180 is the 6 o'clock position). The default value is -90. Will only work while the gaugeType is 0(The circle type). The default value is -90.
maxAngle: (optional) [Number] The max angle value, the maxAngle should be bigger than minAngle. (0 is the 12 o'clock position, -90 is the 9 o'clock position, 90 is the 3 o'clock position, -180 and 180 is the 6 o'clock position). The default value is 90. Will only work while the gaugeType is 0(The circle type). The default value is 90.
radiusRatio: (optional) [Number] The inner circle radius divided by the outer circle radius is the radiusRatio(between 0 and 1), the outer circle radius value is decided by the cell size.Will only work while the gaugeType is 0(The circle type). The default value of radiusRatio is 0.
gaugeType: (optional) [Number] The KPI sprakline type, 0 stands the circle type, 1 stands the verticalBar and 2 stands the horizontalBar.The default type is circle type.
colorRange: (optional Repeatable) [CalcArray] The special color range. The first item is the start value of the range. The second item is the end value of the range. The third item is the color of the range between startValue and endValue. The start value should be less than the end value and both are between minValue and maxValue.The default color range is from minValue to maxValue filling with light grey color.
var spreadNS = GC.Spread.Sheets, spread;
window.onload = () => {
spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
initSpread(spread);
}
function initSpread(spread) {
spread.options.newTabVisible = false;
spread.setSheetCount(3);
spread.sheets[0].name("Circle");
spread.sheets[1].name("Vertical");
spread.sheets[2].name("Horizontal");
initGaugeKPISparklineCircle(spread.sheets[0]);
initGaugeKPISparklineVertical(spread.sheets[1]);
initGaugeKPISparklineHorizontal(spread.sheets[2]);
document.getElementById("minValue").addEventListener('change',applyChanges);
document.getElementById("maxValue").addEventListener('change',applyChanges);
document.getElementById("endValue1").addEventListener('change',applyChanges);
document.getElementById("endValue2").addEventListener('change',applyChanges);
document.getElementById("color1").addEventListener('change',applyChanges);
document.getElementById("color2").addEventListener('change',applyChanges);
document.getElementById("color3").addEventListener('change',applyChanges);
}
function applyChanges() {
var sheet = spread.getActiveSheet();
document.getElementById("startValue1").value = document.getElementById("minValue").value;
document.getElementById("startValue2").value = document.getElementById("endValue1").value;
document.getElementById("startValue3").value = document.getElementById("endValue2").value;
document.getElementById("endValue3").value = document.getElementById("maxValue").value;
var gaugeType = spread.getActiveSheetIndex(); // same as sheet index
var start = 1,end=7;
if(gaugeType == 2)
{
start = 2;
end = 8;
}
for (var i = start; i < end; i++) {
var goalcolumn = String.fromCharCode(65 + i)+"3";
var actualcolumn = String.fromCharCode(65 + i)+"4";
var row = 4; var column = i;
if(gaugeType == 2)//for horizontal gauge
{
goalcolumn = "B"+(i+1);
actualcolumn = "C"+(i+1);
row=i;
column=3;
}
sheet.setFormula(row,column,'=GAUGEKPISPARKLINE('+goalcolumn+','+actualcolumn+','+document.getElementById("minValue").value
+','+document.getElementById("maxValue").value
+',TRUE,TEXT('+goalcolumn+',"$0,K"),TEXT('+actualcolumn+',"$0,K"),TEXT('+document.getElementById("minValue").value
+',"$0,K"),TEXT('+document.getElementById("maxValue").value
+',"$0,K"),,-90,90,0.5,'+gaugeType+',{'
+ document.getElementById("startValue1").value +','+document.getElementById("endValue1").value +',"'+document.getElementById("color1").value +'"},{'
+ document.getElementById("startValue2").value +','+document.getElementById("endValue2").value +',"'+document.getElementById("color2").value +'"},{'
+ document.getElementById("startValue3").value +','+document.getElementById("endValue3").value +',"'+document.getElementById("color3").value+'"})');
}
}
function initGaugeKPISparklineCircle(sheet) {
sheet.suspendPaint();
sheet.setArray(1, 0, [
["Teams","Team A","Team B","Team C","Team D","Team E","Team F"],
["Goal",25000,22000,45000,39000,49000,16000],
["Amount Raised",24000,23000,45500,29000,49500,25000],
["Diagram"]
]);
//styling
for(var i = 0; i<7; i++)
{
sheet.setColumnWidth(i, 150);
}
sheet.setRowHeight(4, 160);
sheet.setRowHeight(0, 35);
sheet.addSpan(0, 0, 1, 7);
sheet.getCell(0, 0).value("Fundraising Teams KPI")
.font("17px Arial")
.vAlign(GC.Spread.Sheets.VerticalAlign.center)
.backColor("gray")
.foreColor("white");
sheet.getRange(1, 0, 4, 1)
.font("bold 13px Arial")
.setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { right: true });
sheet.getRange(1,1,3,6).hAlign(GC.Spread.Sheets.HorizontalAlign.center).formatter("$0,K");
sheet.setFormula(4,1,'=GAUGEKPISPARKLINE(B3,B4,0,50000,TRUE,TEXT(B3,"$0,K"),TEXT(B4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,-90,90,0.5,0,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})');
sheet.setFormula(4,2,'=GAUGEKPISPARKLINE(C3,C4,0,50000,TRUE,TEXT(C3,"$0,K"),TEXT(C4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,-90,90,0.5,0,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})');
sheet.setFormula(4,3,'=GAUGEKPISPARKLINE(D3,D4,0,50000,TRUE,TEXT(D3,"$0,K"),TEXT(D4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,-90,90,0.5,0,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})');
sheet.setFormula(4,4,'=GAUGEKPISPARKLINE(E3,E4,0,50000,TRUE,TEXT(E3,"$0,K"),TEXT(E4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,-90,90,0.5,0,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})');
sheet.setFormula(4,5,'=GAUGEKPISPARKLINE(F3,F4,0,50000,TRUE,TEXT(F3,"$0,K"),TEXT(F4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,-90,90,0.5,0,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})');
sheet.setFormula(4,6,'=GAUGEKPISPARKLINE(G3,G4,0,50000,TRUE,TEXT(G3,"$0,K"),TEXT(G4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,-90,90,0.5,0,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})');
sheet.resumePaint();
}
function initGaugeKPISparklineVertical(sheet) {
sheet.suspendPaint();
sheet.setArray(1, 0, [
["Teams","Team A","Team B","Team C","Team D","Team E","Team F"],
["Goal",25000,22000,45000,39000,49000,16000],
["Amount Raised",24000,23000,45500,29000,49500,25000],
["Diagram"]
]);
//styling
for(var i = 0; i<7; i++)
{
sheet.setColumnWidth(i, 150);
}
sheet.setRowHeight(4, 160);
sheet.setRowHeight(0, 35);
sheet.addSpan(0, 0, 1, 7);
sheet.getCell(0, 0).value("Fundraising Teams KPI")
.font("17px Arial")
.vAlign(GC.Spread.Sheets.VerticalAlign.center)
.backColor("gray")
.foreColor("white");
sheet.getRange(1, 0, 4, 1)
.font("bold 13px Arial")
.setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { right: true });
sheet.getRange(1,1,3,6).hAlign(GC.Spread.Sheets.HorizontalAlign.center).formatter("$0,K");
sheet.setFormula(4,1,'=GAUGEKPISPARKLINE(B3,B4,0,50000,TRUE,TEXT(B3,"$0,K"),TEXT(B4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,1,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})');
sheet.setFormula(4,2,'=GAUGEKPISPARKLINE(C3,C4,0,50000,TRUE,TEXT(C3,"$0,K"),TEXT(C4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,1,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})');
sheet.setFormula(4,3,'=GAUGEKPISPARKLINE(D3,D4,0,50000,TRUE,TEXT(D3,"$0,K"),TEXT(D4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,1,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})');
sheet.setFormula(4,4,'=GAUGEKPISPARKLINE(E3,E4,0,50000,TRUE,TEXT(E3,"$0,K"),TEXT(E4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,1,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})');
sheet.setFormula(4,5,'=GAUGEKPISPARKLINE(F3,F4,0,50000,TRUE,TEXT(F3,"$0,K"),TEXT(F4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,1,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})');
sheet.setFormula(4,6,'=GAUGEKPISPARKLINE(G3,G4,0,50000,TRUE,TEXT(G3,"$0,K"),TEXT(G4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,1,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})');
sheet.resumePaint();
}
function initGaugeKPISparklineHorizontal(sheet) {
sheet.suspendPaint();
sheet.setArray(1, 0, [
["Teams","Goal","Amount Raised","Diagram"],
["Team A",25000,24000],["Team B",22000,23000],["Team C",45000,45500],
["Team D",39000,29000],["Team E",49000,49500],["Team F",16000,25000]
]);
//styling
for(var i = 0; i<3; i++)
{
sheet.setColumnWidth(i, 150);
}
sheet.setRowHeight(0, 35);
sheet.setRowHeight(1, 30);
for(var i = 2; i<8; i++)
{
sheet.setRowHeight(i, 80);
}
sheet.setColumnWidth(3, 260);
sheet.addSpan(0, 0, 1, 4);
sheet.getCell(0, 0).value("Fundraising Teams KPI")
.font("17px Arial")
.vAlign(GC.Spread.Sheets.VerticalAlign.center)
.backColor("gray")
.foreColor("white");
sheet.getRange(1, 0, 1, 4)
.font("bold 13px Arial")
.setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { bottom: true });
sheet.getRange(2,1,6,3).hAlign(GC.Spread.Sheets.HorizontalAlign.center).formatter("$0,K");
sheet.getRange(1,0,7,4).vAlign(GC.Spread.Sheets.VerticalAlign.center);
sheet.setFormula(2,3,'=GAUGEKPISPARKLINE(B3,C3,0,50000,TRUE,TEXT(B3,"$0,K"),TEXT(C3,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,2,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})');
sheet.setFormula(3,3,'=GAUGEKPISPARKLINE(B4,C4,0,50000,TRUE,TEXT(B4,"$0,K"),TEXT(C4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,2,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})');
sheet.setFormula(4,3,'=GAUGEKPISPARKLINE(B5,C5,0,50000,TRUE,TEXT(B5,"$0,K"),TEXT(C5,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,2,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})');
sheet.setFormula(5,3,'=GAUGEKPISPARKLINE(B6,C6,0,50000,TRUE,TEXT(B6,"$0,K"),TEXT(C6,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,2,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})');
sheet.setFormula(6,3,'=GAUGEKPISPARKLINE(B7,C7,0,50000,TRUE,TEXT(B7,"$0,K"),TEXT(C7,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,2,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})');
sheet.setFormula(7,3,'=GAUGEKPISPARKLINE(B8,C8,0,50000,TRUE,TEXT(B8,"$0,K"),TEXT(C8,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,2,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})');
sheet.resumePaint();
}
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<div class="option-row">
<label for="color1">Gauge KPI Settings</label>
</div>
<hr>
<div class="option-row">
<label for="barSize"><u>Min Value:</u></label>
<input type="text" style="margin: 0 20px 0 6px;width:100px;" id="minValue" value="0"/>
</div>
<div class="option-row">
<label for="barSize"><u>Max Value:</u></label>
<input type="text" style="margin: 0 20px 0 6px;width:100px;" id="maxValue" value="50000"/>
</div>
<hr>
<div class="option-row">
<label for="range1"><u>Range 1</u></label>
</div>
<div class="option-row">
<label for="colorScheme">Color: </label>
<select id="color1" style="margin: 0 20px 0 6px">
<option value="#FFFFFF">White</option>
<option value="#000000">Black</option>
<option value="#F7A711" selected>Orange</option>
<option value="#DDDDDD">LightGrey</option>
<option value="#BBBBBB">Grey</option>
<option value="#999999">DarkGrey</option>
<option value="#82BC00">Green</option>
</select>
</div>
<div class="option-row">
<label for="startValue1">Start Value:</label>
<input type="text" style="margin: 0 20px 0 6px;width:100px;" id="startValue1" value="0" disabled/>
</div>
<div class="option-row">
<label for="barSize">End Value:</label>
<input type="text" style="margin: 0 20px 0 6px;width:100px;" id="endValue1" value="10000"/>
</div>
<hr>
<div class="option-row">
<label for="range1"><u>Range 2</u></label>
</div>
<div class="option-row">
<label for="colorScheme">Color: </label>
<select id="color2" style="margin: 0 20px 0 6px">
<option value="#FFFFFF">White</option>
<option value="#000000">Black</option>
<option value="#F7A711">Orange</option>
<option value="#DDDDDD">LightGrey</option>
<option value="#BBBBBB" selected>Grey</option>
<option value="#999999">DarkGrey</option>
<option value="#82BC00">Green</option>
</select>
</div>
<div class="option-row">
<label for="startValue2">Start Value:</label>
<input type="text" style="margin: 0 20px 0 6px;width:100px;" id="startValue2" value="10000" disabled/>
</div>
<div class="option-row">
<label for="barSize">End Value:</label>
<input type="text" style="margin: 0 20px 0 6px;width:100px;" id="endValue2" value="25000"/>
</div>
<hr>
<div class="option-row">
<label for="range3"><u>Range 3</u></label>
</div>
<div class="option-row">
<label for="color3">Color: </label>
<select id="color3" style="margin: 0 20px 0 6px">
<option value="#FFFFFF">White</option>
<option value="#000000">Black</option>
<option value="#F7A711">Orange</option>
<option value="#DDDDDD">LightGrey</option>
<option value="#BBBBBB">Grey</option>
<option value="#999999">DarkGrey</option>
<option value="#82BC00" selected>Green</option>
</select>
</div>
<div class="option-row">
<label for="startValue1">Start Value:</label>
<input type="text" style="margin: 0 20px 0 6px;width:100px;" id="startValue3" value="25000" disabled/>
</div>
<div class="option-row">
<label for="barSize">End Value:</label>
<input type="text" style="margin: 0 20px 0 6px;width:100px;" id="endValue3" value="50000" disabled/>
</div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
label {
margin-bottom: 6px;
}
input {
padding: 4px 6px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
