[]
DEMOS DOC DESIGNER API
DOWNLOAD
(Showing Draft Content)

Year Sparkline

You can create a year sparkline using the YEARSPARKLINE formula and cell values.


A year sparkline has 54*7 squares. The horizontal direction is the year week (from left to right, from 1st to 54th). The vertical direction is the weekday (from top to bottom, from Sunday to Saturday). The color of the days in the year depends on the value (from minimum to maximum, from startColor to middleColor to endColor).




The year sparkline formula has the following options:

Option

Description

year

A full year number, such as 2017.

dataRange

A reference that represents a range where the first column is a date and the second column is a number, such as "A1:B400".

emptyColor

A color string that represents days with no value or zero value, such as "lightgray".

startColor

A color string that represents the minimum day value, such as "lightgreen".

middleColor

A color string that represents the day with the average minimum and maximum value, such as "green".

endColor

A color string that represents the day with the maximum value, such as "darkgreen".

colorRange

A reference that represents a range where the data is a color string.

The year sparkline formula has the following formats:


=YEARSPARKLINE(year, dataRange, emptyColor, startColor, middleColor, endColor)

=YEARSPARKLINE(year, dataRange, colorRange)


The following code sample creates a year sparkline.

activeSheet.name("ColorRange");
activeSheet.setFormatter(-1, 0, "MM/DD/YYYY");
activeSheet.setRowHeight(0, 100);
activeSheet.setColumnWidth(0, 75);
activeSheet.setColumnWidth(1, 25);
activeSheet.setColumnWidth(2, 25);
activeSheet.setColumnWidth(3, 600);
activeSheet.setRowCount(500);
var rowCount = activeSheet.getRowCount();
activeSheet.setValue(0, 1, "sales");
activeSheet.setValue(0, 2, "color");
for (var rowIndex = 1; rowIndex < rowCount; rowIndex++) {
    activeSheet.setValue(rowIndex, 0, new Date(2017, 4, rowIndex));
    var sale = Math.round(Math.random() * 50000);
    activeSheet.setValue(rowIndex, 1, sale);
    activeSheet.setValue(rowIndex, 2, sale > 25000 ? "Green" : "YellowGreen");
}
activeSheet.setFormula(0, 3, '=YearSparkline(2017, A2:B' + rowCount + ', C2:C' + rowCount +')');