Displaying Umbraco Heartcore data in Excel using Office Script and GraphQL
Tags: umbraco, graphql, officescript, excel
Published: 2023-06-12 19:23:31

Recently I noticed that [Office Script](https://learn.microsoft.com/en-us/office/dev/scripts/overview/excel) is a thing. It enhances what you could previously do using VBA by making your enhancements Cloud-based and cross-platform. If you have ever used Google's [Apps Script](https://www.google.com/script/start/) you will find it quite similar. 

When I discovered Apps Script a few years ago I tested it by inserting Umbraco Heartcore data using the Content Delivery API.. this time around I wanted to test the GraphQL capabilities of Umbraco Heartcore instead of doing a copy-paste from my Google Drive.

> Please note that access to Office Script requires Office 365 Business or higher https://learn.microsoft.com/en-us/office/dev/scripts/overview/excel#requirements.

---

An Office Script can be created by going to **Automate** in the ribbon and clicking **New Script**, which will open a code editor in your Excel window. 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8g6el05vnu720dhnxbz5.png)

In this case the goal is to display products from an Umbraco Heartcore project where the stock property on the `Product` nodes is `<= 3`. As such, the first step is to add the necessary headers for the table. 

Add the following to the `main` function.

```js
  let sheet = workbook.getActiveWorksheet();

  let headers = [['SKU', 'Name', 'Price', 'Stock']];
  let headersRange = sheet.getRange('B2:E2');
  headersRange.setValues(headers);
  headersRange.getFormat().getFill().setColor('#4472C4');
  headersRange.getFormat().getFont().setColor('white');
```

Executing the code above will render the cells with the given header texts and colours.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l6yh5kx718odaywitgjm.png)

The next step is to create the interfaces needed for parsing the Umbraco Heartcore response.

```js
interface Response {
  data: Data
}

interface Data {
  allProduct: Connection
}

interface Connection {
  edges: Edge[]
}

interface Edge {
  node: Node
}

interface Node {
  sku: string,
  name: string,
  price: number,
  stock: number,
}
```

Create a function for getting the product data.

```js
async function getProductData() {
  const query = `
  {
    allProduct(
      where: {
        stock_lte: 3,
      },
      orderBy: name_ASC
    ) {
      edges {
        node {
          sku
          name
          price
          stock
        }
      }
    }
  }
  `;

  let result = await fetch('https://graphql.umbraco.io', {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'Umb-Project-Alias': 'umbraco-heartcore-alias-here'
    },
    body: JSON.stringify({
      query: query
    })
  });

  let data = [] = [];

  let response: Response = await result.json();
  response.data.allProduct.edges.forEach(e => {
    let node = e.node;

    data.push([
      node.sku,
      node.name,
      node.price,
      node.stock
    ])
  });

  return data;
}
```

Make the `main` function `async` and fetch the products.

```js
async function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getActiveWorksheet();

  let headers = [['SKU', 'Name', 'Price', 'Stock']];
  let headersRange = sheet.getRange('B2:E2');
  headersRange.setValues(headers);
  headersRange.getFormat().getFill().setColor('#4472C4');
  headersRange.getFormat().getFont().setColor('white');

  let data = await getProductData();
  let dataRangeLength = data.length + 2;

  sheet.getRange('B3:E' + dataRangeLength).setValues(data);

  let stockRange = sheet.getRange('E3:E' + dataRangeLength);
  let stockValues = stockRange.getValues() as number[][];

  for (let i = 0; i < stockValues.length; i++) {
    if (stockValues[i][0] == 0) {
      stockRange.getCell(i, 0).getFormat().getFill().setColor('red');
      }
    }
}
```

If a product's stuck is zero the cell is marked with red. [Microsoft recommends](https://learn.microsoft.com/en-us/office/dev/scripts/develop/web-client-performance#read-workbook-data-outside-of-a-loop) limiting requests to the worksheet in loops (sounds like a good idea), which is why all the values are fetched prior to the loop, significantly reducing the amount of requests.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yfsyridhmskkwirbi0mc.png)



Complete example.

```js
async function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getActiveWorksheet();

  let headers = [['SKU', 'Name', 'Price', 'Stock']];
  let headersRange = sheet.getRange('B2:E2');
  headersRange.setValues(headers);
  headersRange.getFormat().getFill().setColor('#4472C4');
  headersRange.getFormat().getFont().setColor('white');

  let data = await getProductData();
  let dataRangeLength = data.length + 2;

  sheet.getRange('B3:E' + dataRangeLength).setValues(data);

  let stockRange = sheet.getRange('E3:E' + dataRangeLength);
  let stockValues = stockRange.getValues() as number[][];

  for (let i = 0; i < stockValues.length; i++) {
    if (stockValues[i][0] == 0) {
      stockRange.getCell(i, 0).getFormat().getFill().setColor('red');
      }
    }
}

async function getProductData() {
  const query = `
  {
    allProduct(
      where: {
        stock_lte: 3,
      },
      orderBy: name_ASC
    ) {
      edges {
        node {
          sku
          name
          price
          stock
        }
      }
    }
  }
  `;

  let result = await fetch('https://graphql.umbraco.io', {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'Umb-Project-Alias': 'umbraco-heartcore-alias-here'
    },
    body: JSON.stringify({
      query: query
    })
  });

  let data = [] = [];

  let response: Response = await result.json();
  response.data.allProduct.edges.forEach(e => {
    let node = e.node;

    data.push([
      node.sku,
      node.name,
      node.price,
      node.stock
    ])
  });

  return data;
}


interface Response {
  data: Data
}

interface Data {
  allProduct: Connection
}

interface Connection {
  edges: Edge[]
}

interface Edge {
  node: Node
}

interface Node {
  sku: string,
  name: string,
  price: number,
  stock: number,
}
```