Kition
sheetCharts

Market Share Doughnut

Unit sales of five coffee brands turned into shares, a change-in-points column and a labelled doughnut chart.

Market Share Doughnut — Sheet sample rendered by Kition

Shares are formulas over the unit totals, so the doughnut and the table always agree. The change column subtracts last year's share and shows it in percentage points with a custom format that adds a sign and a "pts" suffix; rules colour gains green and losses red. The brand names carry the colour of their slice, so the table doubles as the chart's legend.

Steps

40 commands
showcase.yaml51 lines
steps:
  - { command: range.setValues, params: { range: A1, values: [["Packaged Coffee Market Share"], ["Retail units sold in the region, this year against last year"]] } }
  - command: range.setValues
    params:
      range: A4
      values:
        - [Brand, Share, Units this year, Last year share, Change]
        - [Harbor Roast, "=C5/$C$10", 418000, 0.262, "=(B5-D5)*100"]
        - [Copper Kettle, "=C6/$C$10", 352000, 0.241, "=(B6-D6)*100"]
        - [Morning Ridge, "=C7/$C$10", 286000, 0.176, "=(B7-D7)*100"]
        - [Northbean, "=C8/$C$10", 195000, 0.142, "=(B8-D8)*100"]
        - [Other brands, "=C9/$C$10", 214000, 0.179, "=(B9-D9)*100"]
        - [Total, "=SUM(B5:B9)", "=SUM(C5:C9)", "=SUM(D5:D9)", null]
  - { command: home.fontSize, params: { range: A1, size: 20 } }
  - { command: home.bold, params: { range: A1 } }
  - { command: home.fontColor, params: { range: A1, color: "#1f2a37" } }
  - { command: home.italic, params: { range: A2 } }
  - { command: home.fontColor, params: { range: A2, color: "#6b7280" } }
  - { command: home.fillColor, params: { range: "A4:E4", color: "#1f2a37" } }
  - { command: home.fontColor, params: { range: "A4:E4", color: "#ffffff" } }
  - { command: home.bold, params: { range: "A4:E4" } }
  - { command: home.wrapText, params: { range: "A4:E4" } }
  - { command: home.alignRight, params: { range: "B4:E4" } }
  - { command: home.alignMiddle, params: { range: "A4:E10" } }
  - { command: home.numberFormat, params: { range: "C5:C10", format: "#,##0" } }
  - { command: home.numberFormat, params: { range: "B5:B10", format: "0.0%" } }
  - { command: home.numberFormat, params: { range: "D5:D10", format: "0.0%" } }
  - { command: home.numberFormat, params: { range: "E5:E9", format: "+0.0\" pts\";-0.0\" pts\";0.0\" pts\"" } }
  - { command: home.conditionalFormat, params: { range: "E5:E9", rule: { type: expression, formula: "=E5>0", preset: greenFill } } }
  - { command: home.conditionalFormat, params: { range: "E5:E9", rule: { type: expression, formula: "=E5<0", preset: lightRedFillDarkRedText } } }
  - { command: home.fillColor, params: { range: "A5", color: "#156082" } }
  - { command: home.fillColor, params: { range: "A6", color: "#e97132" } }
  - { command: home.fillColor, params: { range: "A7", color: "#196b24" } }
  - { command: home.fillColor, params: { range: "A8", color: "#0f9ed5" } }
  - { command: home.fillColor, params: { range: "A9", color: "#a02b93" } }
  - { command: home.fontColor, params: { range: "A5:A9", color: "#ffffff" } }
  - { command: home.bold, params: { range: "A5:A9" } }
  - { command: home.borders, params: { range: "A5:E9", preset: insideHorizontal, color: "#ffffff" } }
  - { command: home.bold, params: { range: "A10:E10" } }
  - { command: home.borders, params: { range: "A10:E10", preset: topDoubleBottom, color: "#1f2a37" } }
  - { command: home.columnWidth, params: { cols: "A:A", width: 104 } }
  - { command: home.columnWidth, params: { cols: "B:E", width: 74 } }
  - { command: home.columnWidth, params: { cols: "F:F", width: 20 } }
  - { command: home.rowHeight, params: { rows: "1:1", height: 28 } }
  - { command: home.rowHeight, params: { rows: "4:4", height: 32 } }
  - { command: home.rowHeight, params: { rows: "5:10", height: 22 } }
  - { command: insert.chart, params: { range: "A4:B9", type: doughnut, at: G1, width: 400, height: 300 }, as: chart }
  - { command: chart.set, params: { title: Share of units, legend: right, dataLabels: true } }
  - { command: drawing.describe, params: {}, as: drawing }
  - { command: sheet.read, params: { range: "B5:B10" }, as: shares }
  - { command: sheet.read, params: { range: "E5:E9", formatted: true }, as: change }

Checks

9 checks on every build
  • results.drawing.objects.0.chart.kind = "doughnut"
  • results.drawing.objects.0.chart.dataLabels = true
  • results.drawing.objects.0.chart.series.0.name = "Share"
  • results.drawing.objects.0.chart.series.0.values = {"count":5}
  • results.shares.values.0.0 = {"approx":0.285324,"tol":0.00001}
  • results.shares.values.5.0 = 1
  • results.change.values = [["+2.3 pts"],["-0.1 pts"],["+1.9 pts"],["-0.9 pts"],["-3.3 pts"]]
  • Diagnostics: none, the kit's own check finds nothing wrong
  • The saved document opens again, and its thumbnail matches thumb.png within the render tolerance

Related

More Sheet samples
sheetCharts

Employee Survey Results

Five-point agreement answers for eight survey statements, shown as a 100% stacked bar chart with a net-agreement score.

1 sheet; used range A1:H12
sheetCharts

Profit Bridge Waterfall

A waterfall chart that walks operating profit from last year to this year through its drivers.

1 sheet; used range A1:B16
sheetCharts

Revenue and Margin Combo

Monthly revenue and gross profit as columns with gross margin as a line on a secondary axis.

1 sheet; used range A1:D17
sheetCharts

Sign-up Trend Forecast

Twenty weeks of trial sign-ups as columns with a linear trendline, the fit worked out in cells and a week-24 forecast.

1 sheet; used range A1:F24