Using Script Lab to Create a PivotTable with JavaScript

"Microsoft Excel is not a spreadsheet application anymore, it’s a medium to store, present, and analyze data." - Puneet Gogia

The way Excel has evolved in the past few years, it’s almost impossible to work without it in this present world.

Apart from all the stuff you can learn to do in Excel, the thing which I love most is that I can code in it.

Once you know how to use VBA, you can create macros and automate all the activities which you do manually otherwise.

Today, I want to talk about something else. Something powerful! Let’s talk about using JavaScript (TYPESCRIPT) in Excel.

Before we get into this make sure to subscribe to Excelguru's Newsletter to get tips like this in your mailbox.

Script Lab

There’s a whole bunch of APIs which you can use and code with in your Office applications.

But, with Script Lab you can directly write and test your code into your applications. It’s an add-in which you can install from Microsoft’s App Store.

The Script Lab Ribbon

In Script Lab, there are three different sections where you can write JavaScript, HTML, and CSS code.

The Coding Window

Create a PivotTable in Excel using Javascript

The PivotTable is one of the most useful things which you can use in Excel to summarize and analyze your data.

Even though I like using VBA to create a PivotTable, writing code in JavaScript instead is real fun. In this post I’d like to share with you how to do this.

I’ve split it into a 5-steps process and you can download the files from here to follow along:

  1. Insert Worksheets and Data
  2. Enter a Blank PivotTable
  3. Insert a Row
  4. Insert a Column
  5. Add Values to the Pivot Table

1. Insert Worksheets and Data

The very first thing which you need to create a PivotTable is the source data. So, first of all, you need to write the code which adds source data to the worksheet.

Below is the code:

async function setup() {
await Excel.run(async (context) => {
context.workbook.worksheets.getItemOrNullObject("Data Sheet").delete();
const dataSheet = context.workbook.worksheets.add("Data Sheet");
context.workbook.worksheets.getItemOrNullObject("Pivot Table").delete();
const pivotSheet = context.workbook.worksheets.add("Pivot Table");
const data = [
["Farm", "Type", "Classification", "Crates Sold at Farm", "Crates Sold Wholesale"],
["A Farms", "Lime", "Organic", 300, 2000],
["A Farms", "Lemon", "Organic", 250, 1800],
["A Farms", "Orange", "Organic", 200, 2200],
["B Farms", "Lime", "Conventional", 80, 1000],
["B Farms", "Lemon", "Conventional", 75, 1230],
["B Farms", "Orange", "Conventional", 25, 800],
["B Farms", "Orange", "Organic", 20, 500],
["B Farms", "Lemon", "Organic", 10, 770],
["B Farms", "Kiwi", "Conventional", 30, 300],
["B Farms", "Lime", "Organic", 50, 400],
["C Farms", "Apple", "Organic", 275, 220],
["C Farms", "Kiwi", "Organic", 200, 120],
["D Farms", "Apple", "Conventional", 100, 3000],
["D Farms", "Apple", "Organic", 80, 2800],
["E Farms", "Lime", "Conventional", 160, 2700],
["E Farms", "Orange", "Conventional", 180, 2000],
["E Farms", "Apple", "Conventional", 245, 2200],
["E Farms", "Kiwi", "Conventional", 200, 1500],
["F Farms", "Kiwi", "Organic", 100, 150],
["F Farms", "Lemon", "Conventional", 150, 270]
];
const range = dataSheet.getRange("A1:E21");
range.values = data;
range.format.autofitColumns();
pivotSheet.activate();
await context.sync();
});
}

In this code, you have a function called “setup” which does the following things:

  • It will first check if “Data Sheet” and “Pivot Table” worksheets already exist, and if they do exist then it will delete them and insert new.
  • Next, in the “Data Sheet” worksheet it will add a set of data that will be the source data for the PivotTable.

When you run this code you’ll have something like this in your workbook:

Sample Data Set

2. Enter a Blank PivotTable

When you normally build a PivotTable in Excel, it first inserts a blank PivotTable and then you have to add a row, a column, and values manually.

So now you need a code that inserts a blank PivotTable in the “Pivot Table” worksheet. (You have already written the code in the previous section to insert the “Pivot Table” worksheet.)

Below is the code for this:

async function createWithNames() {
await Excel.run(async (context) => {
const rangeToAnalyze = context.workbook.worksheets.getItem("Data Sheet").getRange("A1:E21");
const rangeToPlacePivot = context.workbook.worksheets.getItem("Pivot Table").getRange("A2");
context.workbook.worksheets.getItem("Pivot Table").pivotTables.add("Farm Sales", rangeToAnalyze, rangeToPlacePivot);
await context.sync();
});
}

Now in this part of the code, we have a function called “createWithNames” which works as follows:

  • First of all, it takes range A1:E21 from the “Data Sheet” worksheet where you have the source data.
  • After that, it specifies the cell A2 in the “Pivot Table” worksheet for inserting the blank PivotTable.
  • Finally, it inserts a blank PivotTable.

After running this part of code you’ll have something like this in your “Pivot Table” worksheet:

Blank PivotTable

3. Insert Row Label

Once you insert a blank PivotTable, the next thing is to insert rows in it. Below is the code which can do this for you:

async function addRow() {
await Excel.run(async (context) => {
const pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
// check if the PivotTable already has rows
const farmRow = pivotTable.rowHierarchies.getItemOrNullObject("Farm");
const typeRow = pivotTable.rowHierarchies.getItemOrNullObject("Type");
const classificationRow = pivotTable.rowHierarchies.getItemOrNullObject("Classification");
pivotTable.rowHierarchies.load();
await context.sync();
if (typeRow.isNullObject) {
pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Type"));
} else if (farmRow.isNullObject) {
pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Farm"));
} else if (classificationRow.isNullObject) {
pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Classification"));
}
await context.sync();
});
}

Now in this part of the code, we have a function called “AddRows” which works as follows:

  • First of all, it checks if there are any rows already in the PivotTable (the first three columns of the source data would be matched one by one).
  • After that (as you are considering the first three columns), it will add them as rows one by one.

As we are using an HTML button here, you can use that button to add all the three rows one by one with it. At this point, you have you’ll have a PivotTable with rows like this:

PivotTable with rows added

4. Insert Column Labels

The next thing is to insert columns, and below is the code which you need to use:

async function toggleColumn() {
await Excel.run(async (context) => {
const pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
// check if the PivotTable already has a column
const column = pivotTable.columnHierarchies.getItemOrNullObject("Classification");
column.load("id");
await context.sync();
if (column.isNullObject) {
// adding the farm column to the column hierarchy automatically removes it from the row hierarchy
pivotTable.columnHierarchies.add(pivotTable.hierarchies.getItem("Classification"));
} else {
pivotTable.columnHierarchies.remove(column);
}
await context.sync();
});
}

In this code, you have referred only to the “Classification” column and it works in the following manner:

  • First of all, it checks if the column “Classification” is already there on not.
  • If the column is not already there, then it adds it to the column label.

As we are using an HTML button here, you can add and remove the column label with this code.

At this point, you’ll have a PivotTable with rows and columns like this:

PivotTable with rows and columns added

5. Add Data Values

The key thing in a PivotTable is the values, and with the below code you can add these in:

async function addValues() {
await Excel.run(async (context) => {
const pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
pivotTable.dataHierarchies.add(pivotTable.hierarchies.getItem("Crates Sold at Farm"));
await context.sync();
});
}
/** Default helper for invoking an action and handling errors. */
async function tryCatch(callback) {
try {
await callback();
} catch (error) {
// Note: In a production add-in, you'd want to notify the user through your add-in's UI.
console.error(error);
}
}

In the source data, you have two different columns where you have values:

  • Crates Sold at Farm
  • Crates Sold Wholesale

But here in this code, you have only one column to use as values: the "Crates Sold at Farm". However, you can change it or even use both of the columns as well.

And your completed PivotTable is here:

Completed PivotTable

How to Add this Code into Script Lab

As I have already mentioned, you can also use HTML and CSS in the Script Lab to create a side-pane where you can run and test your code.

Note: In the code, stored on GitHub, I have added a small bunch of code lines to handle errors, here is the download link.

Make sure to install the Script Lab add-on and then follow the below steps to enter this code into the Script Lab:

  • First of all, click on the Code button on the Script Lab tab to open the Code Editor.

Click on Code in the Script Lab tab to open the Code Editor

  • After that, click on the Menu button and select New Snippet.

Adding a New Snippet in the Script Lab Code Editor

  • Next, you need to add JavaScript, HTML, and CSS into the Script (1), HTML (2), and CSS (3) tabs respectively.

Tabs for adding your JavaScript, HTML, and CSS codes in the Code Editor

  • From here, you need to run the code. For this, you need to click on the Run button from the Script Lab tab.

Clicking the Run button in the Script Lab tab will run the code

Once you click on the Run button, the side pane appears, just like below:

The side pane shows the code being run

Because  you have built your code in five parts, you will have five different buttons that execute those codes and create a PivotTable.

You just need to click on the button to execute each step.

Isn’t it COOL?

Make sure to share this post with your friends, I am sure they will appreciate it.

Quick Thing: Apart from learning about and using JavaScript in Excel, there's one more thing which I'm curious to learn these days, and that's Power Query's M language. Don't forget to check out M is for Data Monkey.


About the Author: Puneet Gogia

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.

Power Query Challenge 6

Are you ready for Power Query Challenge 6?  In this challenge we'll look at splitting nested data sets into a nice table in First Normal Form.

Let's take a look at Power Query Challenge 6:

Where did the challenge come from?

The inspiration for this challenge came from last week's Self Service BI Boot Camp that we hosted in Vancouver, BC (you should have been there, it was awesome).  At the outset, we talked about how to identify if your data is in First Normal Form (the format that is ideal for a PivotTable), and I showed this data set:

Data table with nested data sets

Data table with multiple data points per cell

Notice how the invoice has multiple ItemID, multiple Quantity and multiple Price fields per cell?  That's not good at all.

What we Really Need - Data in First Normal Form

As I explained in the Boot Camp, it is essential that the data source be in First Normal Form in order for a PivotTable to consume it.  What does that mean?  It means that it needs to look like this:

Data in 1NF

Data shown in First Normal Form (1NF)

Notice that in this case the data is atomic - it only has one data point per cell.  In addition, there is now one complete record per row.  The InvoiceID shows on every row now, and each data point has been correctly split up and applied to them.

So what's the thrust of the Power Query Challenge 6?

Well, as it turns out this is a bit tricky.  There are a few issues at play here:

  • The data is separated by line feeds within the cells
  • There are a different number of line feeds in each row
  • At least the number of line feeds is consistent for each cell in the entire row though!

So the challenge is this: break the table apart so that the data is in First Normal Form like the second image.

You can download the data source file (and post your solutions) in our forum here.  I'll give you my version tomorrow.