Creating a 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 week day (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)

Using Code

This example creates a year sparkline.

JavaScript	Copy Code
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 +')');

JavaScript

Copy Code

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 +')');