This Office Scripts function creates and formats a chart - for exploration and learning

create-format-chart.ts

/*
 * Create and format a chart from some AdventureWorksDW2019 data
 * The table has these columns: Year, Month, SalesAmount
 * The worksheet must include a named cell called 'referenceLineValue'. This value is used to draw the line on the chart.
 */
function main(workbook: ExcelScript.Workbook) {

  let selectedSheet = workbook.getActiveWorksheet();

  /* Get the table, 
  the rows containing data, 
  the value column, 
  and the category columns */

  // clear charts from sheet (while testing)
  selectedSheet.getCharts().forEach(c => c.delete())
  let dataTable = selectedSheet.getTables()[0]
  dataTable = deleteColumnsAfterIndex(dataTable, 2)
  dataTable = addFormulaColumn(dataTable, "YearMonth", "=DATE([@[Year]], [@[Month]], 1)", "yyyy-mm")

  const dataRange = dataTable.getRangeBetweenHeaderAndTotal()
  const valueRange = dataRange.getColumn(2)
  const categoryRange = dataRange.getColumn(3)
  const referenceValue = selectedSheet.getRange("referenceLineValue").getValue()

  /* Create a new clustered column chart using 
  the valueRange as the data */
  const chart = selectedSheet.addChart(
    ExcelScript.ChartType.columnClustered,
    valueRange
  )

  /* Assign the category data to the category axis, 
  Make the category axis *not* multi-level
  Use center alignment and horizontal text for the axis labels */
  const categoryAxis = chart.getAxes().getCategoryAxis()
  categoryAxis.setCategoryNames(categoryRange)
  categoryAxis.setAlignment(
    ExcelScript.ChartTickLabelAlignment.center
    )
  categoryAxis.setTextOrientation(0)
  categoryAxis.setCategoryType(ExcelScript.ChartAxisCategoryType.dateAxis)
  
  // Remove the horizontal major gridlines
  const valueAxis = chart.getAxes().getValueAxis()
  // valueAxis.getMajorGridlines().setVisible(false)
  const gridLine = valueAxis.getMajorGridlines().getFormat().getLine()
  // gridLine.setWeight(1)
  gridLine.setColor("lightGrey")
  gridLine.setLineStyle(ExcelScript.ChartLineStyle.grey50)
  

  // Give the chart a sensible title
  chart.getTitle().setText("Sales by Month against Target")

  // Reduce the distance between the columns
  const columnSeries = chart.getSeries()[0]
  columnSeries.setGapWidth(12)
  
  // Set all bars to silver, except those that are taller than the reference line
  columnSeries.getFormat().getFill().setSolidColor("silver")
  columnSeries.getPoints().forEach(
    p => {if (p.getValue() >= referenceValue) {p.getFormat().getFill().setSolidColor("darkBlue")}}
    )
  
  // Create the data for the reference line
  dataTable = addFormulaColumn(dataTable, "Reference", "=referenceLineValue", "#")

  // Add the line as a new series
  const referenceSeries = chart.addChartSeries("Target")
  referenceSeries.setValues(valueRange.getOffsetRange(0, 2))
  referenceSeries.setChartType(ExcelScript.ChartType.line)
  referenceSeries.getFormat().getLine().setColor("darkGrey")

  // Size and position the chart on the worksheet
  chart.setHeight(500)
  chart.setWidth(800)
  chart.setPosition("F1")
}


/**
 * Adds a formula column to an Excel table.
 *
 * @param table The ExcelScript.Table to which the column will be added.
 * @param columnName The name of the new column.
 * @param formula The formula to assign to the data range of the column.
 * @param numberFormat (Optional) The number format for the column data.
 * @returns The modified ExcelScript.Table.
 */
function addFormulaColumn(
  table: ExcelScript.Table,
  columnName: string,
  formula: string,
  numberFormat?: string): ExcelScript.Table {

    // Create a new column with the given name
    const column = table.addColumn(-1, null, columnName)

    // Get the range of the data (not the header or total rows)
    const range = column.getRangeBetweenHeaderAndTotal()

    // Assign the formula to the data range
    range.setFormula(formula)
  
    // Set the number format if provided
    if (numberFormat) {range.setNumberFormat(numberFormat)}
  
    // Return the modified table
    return table
}


/**
 * Delete all table columns after a specific column index
 *
 * @param table The ExcelScript.Table from which columns will be deleted.
 * @param deleteAfterIndex All columns with index greater than this will be deleted.
 * @returns The modified ExcelScript.Table.
 */
function deleteColumnsAfterIndex(
  table: ExcelScript.Table, 
  deleteAfterIndex: number
  ): ExcelScript.Table {

  // An array of TableColumns to hold the columns to delete
  const columnsToDelete: ExcelScript.TableColumn[] = []

  /* Iterate and push the columns to delete into the array
  We don't delete them while iterating, as this changes the indices of the
  collection we're iterating over, which can cause problems */
  table.getColumns().forEach(
     c => {if (c.getIndex() > deleteAfterIndex) {columnsToDelete.push(c)}}
     )
  
  /* Now we have a fixed array of the column objects to delete
  We can delete them one by one without problem */
  columnsToDelete.forEach((columnToDelete) => {
    columnToDelete.delete()
  });

  return table

}

sales-data.txt

// Convert this array into a table in your worksheet, then run the code in the ts file in this gist
{"Year","Month","SalesAmount","YearMonth","Reference";2010,12,43421.0364,40513,1500000;2011,1,469823.9148,40544,1500000;2011,2,466334.903,40575,1500000;2011,3,485198.6594,40603,1500000;2011,4,502073.8458,40634,1500000;2011,5,561681.4758,40664,1500000;2011,6,737839.8214,40695,1500000;2011,7,596746.5568,40725,1500000;2011,8,614557.935,40756,1500000;2011,9,603083.4976,40787,1500000;2011,10,708208.0032,40817,1500000;2011,11,660545.8132,40848,1500000;2011,12,669431.5031,40878,1500000;2012,1,495364.1261,40909,1500000;2012,2,506994.1876,40940,1500000;2012,3,373483.0054,40969,1500000;2012,4,400335.6145,41000,1500000;2012,5,358877.8907,41030,1500000;2012,6,555160.1428,41061,1500000;2012,7,444558.2281,41091,1500000;2012,8,523917.3815,41122,1500000;2012,9,486177.4502,41153,1500000;2012,10,535159.4846,41183,1500000;2012,11,537955.517,41214,1500000;2012,12,624502.1667,41244,1500000;2013,1,857689.91,41275,1500000;2013,2,771348.74,41306,1500000;2013,3,1049907.39,41334,1500000;2013,4,1046022.77,41365,1500000;2013,5,1284592.93,41395,1500000;2013,6,1643177.78,41426,1500000;2013,7,1371675.81,41456,1500000;2013,8,1551065.56,41487,1500000;2013,9,1447495.69,41518,1500000;2013,10,1673293.41,41548,1500000;2013,11,1780920.06,41579,1500000;2013,12,1874360.29,41609,1500000;2014,1,45694.72,41640,1500000}