ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Data collection spreadsheet
2
3
Version1
4
5
Use this spreadsheet to collect data in OCDS format. The OCDS Data Review Tool can be used to check, explore and convert data directly from the spreadsheet. Try it using the link below!
6
7
 Key
8
The following colour coding is used throughout this template
9
10
 Manual data entry
11
 Choose value from list
12
 Calculated value
13
 Guidance and notes
14
 Header column
15
16
User guidance
17
18
Entering data
19
Use the Input: Contracting processes sheet to enter data. Each row represents a single contracting process and each column represents a field of data relating to that contracting process.
20
21
Use the Input: Tender Items, Input: Award Items, Input:Tenderers and Input: Implementation Transactions sheets to enter additional data about your contracting processes. Data entered in these sheets is related to the data in the Input: Contracting processes sheet using the `ocid` column. Use the drop down to select the contracting process you are entering data for.
22
23
24
Checking, exploring and converting data
25
Send your data to the OCDS Data Review Tool to check its structure and format, explore it in a visualization tool and convert it to JSON:
26
27
Check, explore and convert my data
28
29
Developer guidance
30
31
Adding a field with an OCDS mapping to the template
32
33
1. Insert a new column in the relevant input sheet.
34
2. Copy and paste the appropriate column from the # Reference: Template columns sheet to the new column you added, based on the desired format for the new column, e.g. text, date, number etc.
35
3. Enter the path of the OCDS mapping in the # OCDS field path row of the new column, refer to the # Reference: schema sheet for a list of OCDS fields and paths.
36
4. Enter any additional information for users in the Guidance row of the new column, e.g. the form or report from which the data should be entered.
37
5. If the field uses an OCDS codelist, set the data validation rules for the Data rows of the new column to "List from range" and set the range to the relevant codelist in the # Reference: codelists sheet. See https://support.google.com/docs/answer/186103 for more information on creating drop down lists in Google Sheets.
38
6. If the field is a date field, add '#' before the OCDS field path, copy the 'date conversion helper column' from the # Reference: Template columns sheet to your input sheet, enter the OCDS field path without the '#' and update the formulae in the green cells to reference the input column you added.
39
40
Adding a field without an OCDS mapping to the template
41
42
1. Insert a new column in the relevant input sheet.
43
2. Copy and paste the appropriate column from the # Reference: Template columns sheet to the new column you added, based on the desired format for the new column, e.g. text, date, number etc.
44
3. Enter '#' in the # OCDS field path row of the new column, this tells the OCDS Data Review Tool to skip this column when converting data
45
4. Enter any additional information for users in the Guidance row of the new column, e.g. the form or report from which the data should be entered.
46
5. Contact the OCDS helpdesk to agree an appropriate value to use in the # OCDS field path row for the new column.
47
48
One to many relationships
49
One to many relationships, for example one tender which results in multiple contracts, are modelled using arrays in OCDS and can be represented in two different ways in the template.
50
51
Single sheet, multiple column representation
52
In this approach, new columns are added for each item in the array. This approach is suitable where the maximum number of items in the array is known and is small.
53
54
Contracting processes input sheet
55
OCDS field pathocidtender/titlecontracts/0/titlecontracts/1/titlecontracts/2/title
56
Dataocds-123456Purchase of office furnitureContract for supply of 30 desksContract for supply of 10 chairsContract for supply of 40 laptop stands
57
58
Multi sheet, single column representation
59
In this approach, a separate sheet is used for the items in the array and entries on this sheet are linked to tenders using the contracting process identifier. This approach is suitable when the maximum number of items in an array is unknown or large.
60
61
Tenders input sheet
62
OCDS field pathocidtender/title
63
Dataocds-123456Purchase of office furniture
64
65
Contracts input sheet
66
OCDS field pathocidcontracts/0/title
67
Dataocds-123456Contract for supply of 30 desks
68
ocds-123456Contract for supply of 10 chairs
69
ocds-123456Contract for supply of 40 laptop stands
70
71
Refer to the flatten-tool documentation for more details: https://flatten-tool.readthedocs.io/en/latest/unflatten/
72
73
Adding new sheets
74
To add a sheet which will be used to enter data, duplicate an existing input sheet and add/remove columns as required.
75
76
To add a sheet which will not be used to enter data (e.g. containing guidance or reference information) create a new sheet and prefix the sheet name with '#', this tells the OCDS Data Review Tool to skip this sheet.
77
78
Changing the structure of the input sheet(s)
79
The Meta sheet defines the structure of the Input: Contracting processes sheet. The following parameters are set in the first row of the sheet:
80
81
ParameterDescription
82
skiprowsThis parameter defines how many rows are above the OCDS field path row in the input sheets, these rows will be skipped by the OCDS Data Review Tool
83
headerrowsThis parameter defines how many rows are below the OCDS field path row in the input sheets before the data, these rows will be skipped by the OCDS Data Review Tool
84
hashcommentsThis parameter means that any sheets or columns beginning with '#' will be skipped by the OCDS Data Review Tool
85
86
Refer to the flatten tool documentation for more details: https://flatten-tool.readthedocs.io/en/latest/unflatten/#configuration-properties-skip-and-header-rows
87
88
Updating the publisher metadata
89
The Meta sheet also sets the metadata about the publication. This should be updated with the details of the publisher.
90
91
Refer to the flatten tool documentation: https://flatten-tool.readthedocs.io/en/latest/unflatten/#metadata-tab and the OCDS release package documentation for more details: http://standard.open-contracting.org/latest/en/schema/release_package/
92
93
Auto-populated fields
94
Columns in the Fixed and calculated values section of the input sheets are used to calculate fields which are required by the OCDS schema, but do not require users to enter data directly.
95
96
Assumptions
97
The following assumptions were used to create this simplified template:
98
99
1. There is a maximum of one award per contracting process
100
2. There is a maximum of one contract per contracting process