Prototyping OCDS data using spreadsheets. Part II

In this post for our technical series, we look at how to build and use a spreadsheet template. Check out the first blog on this issue that explained the need and tools for creating a pilot OCDS dataset using spreadsheets and part 3 on how to adapt the spreadsheet template for your needs.

Following on from our blog on motivations and tools for creating a pilot OCDS dataset using spreadsheets, in this entry we’ll be looking at how to create a template and how to use it to enter, check and convert data. 

Building a template

Before starting to enter or convert data, the first step is to create a spreadsheet template. We’ve created an example of a simple spreadsheet template with the following sheets:

A single input sheet where data can be entered for conversion to OCDS format.

For the sake of simplicity, this template assumes that there are no one-to-many relationships in the data (e.g. there is only be one award per tender, one supplier per award, etc.) to make it possible to enter data from all stages of the contracting processes into a single sheet. 

Flatten-tool’s configuration properties can be used to provide human readable field titles and descriptions in the input sheet along with other in-line documentation and to exclude this additional information from the conversion to JSON.

A schema reference sheet containing a flattened version of the OCDS schema.

This sheet provides the field titles and descriptions used in the input sheet.

A codelist reference sheet containing the possible values for each codelist in OCDS.

This sheet is used to validate the data entered into the input sheet and to make it possible to select values from drop down lists.

A template columns reference sheet containing an example column for each type of field in the OCDS schema, with appropriate validation and formatting set up.

The columns in this sheet can be used as templates when adding new fields to the input sheet, so it isn’t necessary to set up the same formatting and validation settings repeatedly.

A meta sheet setting flatten-tool’s configuration properties and providing the values for the package metadata in OCDS.

The settings in this sheet ensure that the data in the input sheet is interpreted correctly by flatten-tool and the Data Review Tool.

An instruction sheet, with guidance for users of the template on entering, checking, exploring and converting data; plus guidance for developers on setting configuration properties and adding fields and sheets.

Entering, checking and converting data

Data can be entered in several different ways:

• Import data from another file, such as a spreadsheet or CSV file, using either Google Sheets’ IMPORTDATA formula or import feature
• Copy-pasting data from exports or reports from other systems
• Entering data by hand

The columns in the imported data are likely to need reordering to match the structure of the input template and it may also be necessary to bring together data from different sources or reports, so it’s best to create new sheets for the imported data and to use formulas and look-ups to populate the OCDS input sheet. Some useful formulas for this type of work include VLOOKUP and, even better, INDEX MATCH.

Using the example template it is possible to send the data from the input sheet to the Data Review Tool for checking, conversion and exploration at the click of a single button.

The Data Review Tool provides feedback on areas where your data doesn’t conform to OCDS; this could include:

• Missing required fields, e.g. not providing identifiers where required.
• Incorrect formats, e.g. not using the ISO8601 date format
• Invalid codes, e.g. not mapping local procurement methods to the method codelist in OCDS

Understanding what steps are required to convert your data to OCDS is an important part of prototyping an OCDS dataset Issues reported by the Data Review Tool can be addressed using formulae in the Google Sheet in the prototype, effectively providing a blueprint for the functionality that would need to be implemented for automated publication of OCDS.

Once the errors reported by the Data Review Tool have been resolved, the prototype dataset can be converted to JSON format using the ‘Convert to JSON’ button in the DRT.

In part 3 of this series, we’ll look at how to adapt the example template to your needs and reflect on the pro’s and con’s of using spreadsheets to prototype OCDS data.