JavaScript Excel Spreadsheet Use Cases: Government Agencies

The United States government is a wide-reaching entity with many facets and departments that focus on a wide range of problems to solve and things to keep track of. Since SpreadJS can be used in many different scenarios, we can use it to create a simple government application that can be expanded upon. In the case of this application, we will create a tracker for government bids, a compliance checklist, and a contract management dashboard.

Download the sample for this blog and follow along.

Ready to check out more from SpreadJS? Download Our 30-Day Free Trial Today!

Creating the Template

To get started, we can put together a template workbook in the Desktop SpreadJS Designer. This workbook will have three sheets:

• Bid Tracker
• Compliance Checklist
• Contract Management

We can create these three sheets and then add the content to them. For the bid tracker sheet, all we need is a title and a simple table, which we can name “BidTable”:

The Bid Tracker sheet in the SpreadJS Designer

Our table can have any number of rows, as we will add the data in the code.

The next sheet is the Compliance Checklist, a simple form to which users can add data. As such, we will need to make sure we lock down specific cells to prevent users from changing them. We can do this by simply selecting the cells we want users to be able to add data to and navigating to HOME>Cells>Format>Unlock Cells:

Unlocking cells in the SpreadJS Designer

When you’ve done that for all of the cells you want to edit, navigate to HOME>Cells>Format>Protect Sheet… and select only the “Select unlocked cells” option. In addition, we can set a password to unprotect the sheet in this same dialog:

Protecting the sheet in the SpreadJS Designer

The last sheet we are putting together is the Contract Management sheet, which has a simple table on it. We will add the rest in the code:

The Contract Management sheet in the SpreadJS Designer

Bid Tracker

The bid tracker sheet is just a simple table with some conditional formatting, so we can go ahead and load the data from “data.js” into the table:

var bidSheet = spread.sheets[0];
var bidColumns = [],
    bidNames = ['bidNumber', 'item', 'bidDate', 'amount', 'deadline'],
    bidLabels = ['Bid #', 'Item', 'Date of Bid', 'Amount', 'Deadline'];
var bidTable = bidSheet.tables.findByName("BidTable");
bidTable.autoGenerateColumns(false);
bidNames.forEach(function(name, index) {
    var tableColumn = new GC.Spread.Sheets.Tables.TableColumn();
    tableColumn.name(bidLabels[index]);
    tableColumn.dataField(name);
    bidColumns.push(tableColumn);
});
bidTable.bindColumns(bidColumns);
bidTable.bindingPath('bids');
bidSheet.setDataSource(source);

We can also add currency formatting to the Amount column:

var bidSheet = spread.sheets[0];
bidSheet.getRange("E5:E14").formatter("$0,0");

Finally, we can add conditional formatting to the Amount column, in this case, a data bar:

function addConditionalFormatting(spread) {
    var bidSheet = spread.sheets[0];
    var r = 3;
    var c = 4;
    var w = 1;
    var h = 11
    bidSheet.getRange(r, c, h, w).setBorder(new GC.Spread.Sheets.LineBorder("Black", GC.Spread.Sheets.LineStyle.dashed), { all: true });
    var dataBarRule = bidSheet.conditionalFormats.addDataBarRule(GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.lowestValue, null,
            GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.highestValue, null,
            "orange", [new GC.Spread.Sheets.Range(r + 1, c, h - 1, w)]);
    dataBarRule.gradient(true);
    dataBarRule.showBorder(false);
    dataBarRule.showBarOnly(false);
}

The final Bid Tracker sheet

Compliance Checklist

Regarding the Compliance Checklist, the work was finished in the SpreadJS Designer. We were able to add the text, format the cells, unlock specific cells, and protect the sheet, so no further work is needed:

The finished Compliance Checklist sheet

You’ll notice that due to the options we changed in the SpreadJS Designer, we can only select and change specific cells, particularly the cells where data needs to be entered.

Contract Management

Contracts are a big part of the US government, especially with the amount of products and services that the government buys. Keeping track of all the contracts can be immensely useful so that we can create a sheet in our application showing the contracts and their amounts.

As with the other sheets, we have data in a separate “data.js” file so that we can load that data into the table we created:

var contractSheet = spread.sheets[2];
var contractColumns = [],
    contractNames = ['contract', 'vendor', 'date', 'originalAmount', 'forecastedAmount', 'invoicedAmount', 'status'],
    contractLabels = ['Contract', 'Vendor', 'Date', 'Original Amount', 'Forecasted Amount', 'Invoiced Amount', 'Status'];
var contractTable = contractSheet.tables.findByName("ContractTable");
contractTable.autoGenerateColumns(false);
contractNames.forEach(function(name, index) {
    var tableColumn = new GC.Spread.Sheets.Tables.TableColumn();
    tableColumn.name(contractLabels[index]);
    tableColumn.dataField(name);
    contractColumns.push(tableColumn);
});
contractTable.bindColumns(contractColumns);
contractTable.bindingPath('contracts');
contractSheet.setDataSource(source);

We also need to add formatting for the Original, Forecasted, and Invoiced Amount columns to format them as currency, so we can just set a formatter:

var contractSheet = spread.sheets[2];
contractSheet.getRange("E16:G36").formatter("$0,0");

The last things we need to add to the Contract Management sheet are the charts, such as a doughnut chart for the Invoice Amount column and a column chart for all the contract amounts. We can start with the doughnut chart. We need to create the chart based on the Invoice Amount column and set the Contract column as the labels for the chart:

var contractSheet = spread.sheets[2];
var doughnutChart = contractSheet.charts.add('doughnut', GC.Spread.Sheets.Charts.ChartType.doughnut, 25, 25, 475, 225, 'G15:G29');
var doughnutSeriesItem = doughnutChart.series().get(0);
doughnutSeriesItem.xValues = "B16:B29";
doughnutSeriesItem.doughnutHoleSize = 0.4;
doughnutSeriesItem.dataPoints = {
    0: {
        backColor: "rgb(91, 155, 213)"
    },
    1: {
        backColor: "rgb(237, 125, 49)"
    },
    2: {
        backColor: "rgb(165, 165, 165)"
    },
    3: {
        backColor: "rgb(255, 192, 0)"
    },
    4: {
        backColor: "rgb(68, 114, 196)"
    },
    5: {
        backColor: "rgb(112, 173, 71)"
    },
    6: {
        backColor: "rgb(255,20,128)"
    },
    7: {
        backColor: "rgb(91, 155, 213)"
    },
    8: {
        backColor: "rgb(237, 125, 49)"
    },
    9: {
        backColor: "rgb(165, 165, 165)"
    },
    10: {
        backColor: "rgb(255, 192, 0)"
    },
    11: {
        backColor: "rgb(68, 114, 196)"
    },
    12: {
        backColor: "rgb(112, 173, 71)"
    },
    13: {
        backColor: "rgb(255,20,128)"
    },
    14: {
        backColor: "rgb(91, 155, 213)"
    }
}
doughnutChart.series().set(0, doughnutSeriesItem);

doughnutChart.legend({position:GC.Spread.Sheets.Charts.LegendPosition.right});
doughnutChart.title({text: "Invoiced Amount"});

For the column chart, we want to have three different series, one for each amount. So we can create the column chart based on the whole table and then just set the xValues and yValues for each series in the chart:

var columnChart = contractSheet.charts.add('column', GC.Spread.Sheets.Charts.ChartType.columnStacked100, 550, 25, 475, 225, 'B15:G29');
var columnSeriesItem = columnChart.series().get(0);
columnSeriesItem.name = "Original Amount";
columnSeriesItem.xValues = "B16:B29";
columnSeriesItem.yValues = "E16:E29";
columnChart.series().set(0, columnSeriesItem);
columnSeriesItem = columnChart.series().get(1);
columnSeriesItem.name = "Forecasted Amount";
columnSeriesItem.xValues = "B16:B29";
columnSeriesItem.yValues = "F16:F29";
columnChart.series().set(1, columnSeriesItem);
columnSeriesItem = columnChart.series().get(2);
columnSeriesItem.name = "Invoiced Amount";
columnSeriesItem.xValues = "B16:B29";
columnSeriesItem.yValues = "G16:G29";
columnChart.series().set(2, columnSeriesItem);
columnChart.legend({position:GC.Spread.Sheets.Charts.LegendPosition.right});
columnChart.title({text: "Contract Amounts"});

The final Contract Management sheet with charts

With that, we have created a simple government application. This can be expanded upon with even more features and security, and this can serve as a simple starting point.

If you want to try these features and more out for yourself, be sure to download a trial of SpreadJS today: Download JavaScript Spreadsheet | SpreadJS