How to Create a Chart in Excel using SCRIPT Lab

In Microsoft Excel, you can use VBA for Programming, but last year Microsoft had introduced ScriptLab

ScriptLab is a ribbon add-in which can help you to work with Office JavaScript API within the Excel. You can create and edit code snippets (JavaScript, HTML, CSS, and references to libraries). 

You can download it from here.

And, in this post, we will see how we can insert a line chart in Excel by using JavaScript in ScriptLab.

1. Code to Add Data Table

The first thing we need to do here is to insert a table to use to create the line chart. So following is the code that you can use.

async function setup() {
  await Excel.run(async (context) => {
    context.workbook.worksheets.getItemOrNullObject("Sample").delete();
    const sheet = context.workbook.worksheets.add("Sample");

    let expensesTable = sheet.tables.add("A1:B1", true);
    expensesTable.name = "SalesTable";
    expensesTable.getHeaderRowRange().values = [["Date", "Quantity"]];
    expensesTable.rows.add(null, [
      ["01-Jan-2020", 1897],
      ["02-Jan-2020", 1010],
      ["03-Jan-2020", 1359],
      ["04-Jan-2020", 1695],
      ["05-Jan-2020", 1374],
      ["06-Jan-2020", 1132],
      ["07-Jan-2020", 1302],
      ["08-Jan-2020", 1738],
      ["09-Jan-2020", 1835],
      ["10-Jan-2020", 1759],
    ]);

    sheet.getUsedRange().format.autofitColumns();
    sheet.getUsedRange().format.autofitRows();
    sheet.activate();
    await context.sync();
  });
}

This code adds a table with dates and quantities in a new worksheet and names it “Sample”.

2. Code to Insert the Line Chart

Now the next thing is to insert the line chart and for this, we need to use the below code snippet which takes the data from the cell range where you have the data. 

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let salesTable = sheet.tables.getItem("SalesTable");

    let dataRange = sheet.getRange("A1:E7");
    let chart = sheet.charts.add("Line", dataRange, "Auto");

    chart.setPosition("A10", "F20");
    chart.legend.position = "Right";
    chart.legend.format.fill.setSolidColor("white");
    chart.dataLabels.format.font.size = 15;
    chart.dataLabels.format.font.color = "black";
    chart.title.text = "Bicycle Parts Quarterly Sales";

    await context.sync();
  });
}

In this code snippet, I have defined some of the properties of the chart that you are going to create, and you can make changes to these properties.

3. HTML and CSS

You also need to use HTML and so that you can add buttons to the run pane and then use them to insert the chart whenever you want.

HTML

section.samples .ms-Button, section.setup .ms-Button {
    display: block;
    margin-bottom: 2px;
}

CSS

section.samples .ms-Button, section.setup .ms-Button {
    display: block;
    margin-bottom: 2px;
}

With this HTML and CSS, you can have two buttons to insert data and create a chart.

How to Add these codes to Script Lab

Once you install the Script lab, you will have a new tab on the Excel ribbon, like below.

Click on the code button to open the code window and then click on the menu button and then “New Snippet”.

When you click on the “New Snippet” it shows you three tabs to add JavaScript, HTML, and CSS, respectively.

And once you do that, click on the RUN button to run open the run pane.

About the Author

Puneet is the Co-Founder at ExcelChamps and has been using Excel since his college days. He’s helped thousands of people to understand the power of spreadsheets and learn Excel and VBA. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.