Office Scripts: Scripting Excel

JS

JavaScriptNinja

Posted: 2023-10-26 14:30 UTC

Hi everyone,

I'm trying to automate the creation of a specific type of chart in Excel using Office Scripts. I have a table of data and I want to generate a clustered column chart based on a selection of columns.

I've been looking at the Excel Script API, but I'm struggling with how to correctly define the range for the chart series and axes. My data looks something like this:


// Example Data Structure (Conceptual)
// Sheet1!A1:C5
// Month | Sales | Profit
// Jan   | 100   | 20
// Feb   | 120   | 25
// Mar   | 110   | 22
// Apr   | 130   | 28

I want the 'Month' column (A2:A5) to be my category (X) axis and 'Sales' (B2:B5) and 'Profit' (C2:C5) to be the value (Y) series.

Here's what I have so far, but it's not quite working:


function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let chart = selectedSheet.addChart(ExcelScript.ChartType.columnClustered);

    // How do I correctly specify these ranges?
    chart.setSeries([
        {
            categories: selectedSheet.getRange("A2:A5"), // This might be wrong
            values: selectedSheet.getRange("B2:B5")      // This might be wrong
        },
        {
            categories: selectedSheet.getRange("A2:A5"), // This might be wrong
            values: selectedSheet.getRange("C2:C5")      // This might be wrong
        }
    ]);

    // Other chart configurations like title, etc. will follow.
}

Any help on how to correctly define the chart series and categories using ranges in Office Scripts would be greatly appreciated!

Thanks!

MK

Mike_K

Posted: 2023-10-26 15:05 UTC

Hey JavaScriptNinja,

You're on the right track! The key is understanding how the `setSeries` method expects its arguments. Each series object needs to be configured with its `categories` and `values`. It's common to have all series share the same category range.

Here's how you can correctly define your chart series:


function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();

    // Define your data ranges
    let categoryRange = selectedSheet.getRange("A2:A5"); // Months
    let salesRange = selectedSheet.getRange("B2:B5");    // Sales data
    let profitRange = selectedSheet.getRange("C2:C5");   // Profit data

    // Add the clustered column chart
    let chart = selectedSheet.addChart(ExcelScript.ChartType.columnClustered);

    // Set the chart's data source
    // You can set the entire range first and then modify series if needed,
    // or build the series directly. Let's build directly for clarity.
    chart.setSeries([
        {
            categories: categoryRange,
            values: salesRange,
            name: "Sales" // Optional: Name for the series legend
        },
        {
            categories: categoryRange,
            values: profitRange,
            name: "Profit" // Optional: Name for the series legend
        }
    ]);

    // You might also want to set the chart title
    chart.setChartTitle("Monthly Performance");

    // You can also add data labels or other formatting here.
    // For example, to add data labels to the first series:
    // chart.getSeries()[0].setDataLabels(true);

    console.log("Chart created successfully!");
}

Notice how I explicitly defined `categoryRange`, `salesRange`, and `profitRange` variables for better readability. The `name` property for each series is also useful for the legend.

Give this a try and let me know if it works for you!

JS

JavaScriptNinja

Posted: 2023-10-26 15:35 UTC

Mike_K, that's perfect! Thank you so much.

The key was explicitly passing the `ExcelScript.Range` objects to the `categories` and `values` properties. I was trying to pass strings or arrays, which was the confusion.

I've incorporated your code, and the chart is generating exactly as I intended. I also added the `name` property for the legend, which is a nice touch.

Appreciate the quick and clear response!