Displaying Umbraco Heartcore data in Excel using Office Script and GraphQL

Recently I noticed that Office Script 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 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.

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.

  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.

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

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.

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.

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 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.

Complete example.

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,
}

Blog

Displaying Umbraco Heartcore data in Excel using Office Script and GraphQL

Morten Hartvig

Join Our Newsletter. No Spam, Only the good stuff.

Related