| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
1 | Simple OCDS spreadsheet template | |||||||||
2 | ||||||||||
3 | Version | 0.1 (draft) | ||||||||
4 | ||||||||||
5 | This template demonstrates how a spreadsheet can be used to produce OCDS data. The OCDS Data Review Tool can be used to check, explore and convert data directly from this template. 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 in the sheet represents a single contracting process and each column represents a field of data relating to that contracting process. | |||||||||
20 | ||||||||||
21 | The Input: Contracting processes sheet is structured as follows: | |||||||||
22 | ||||||||||
23 | ||||||||||
24 | Checking, exploring and converting data | |||||||||
25 | Use the following URL to use the OCDS Data Review Tool to check the structure and format of your data, explore your data in a visualization tool and convert your data to JSON format: | |||||||||
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 Input: Contracting processes 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 for a list of OCDS fields and paths. | |||||||||
36 | 4. Enter any additional information for users in the Publisher specific 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 | ||||||||||
39 | Adding a field without an OCDS mapping to the template | |||||||||
40 | ||||||||||
41 | 1. Insert a new column in the Input: Contracting processes sheet. | |||||||||
42 | 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. | |||||||||
43 | 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 | |||||||||
44 | 4. Enter any additional information for users in the Publisher specific guidance row of the new column, e.g. the form or report from which the data should be entered. | |||||||||
45 | 5. Contact the OCDS helpdesk to agree an appropriate value to use in the # OCDS field path row for the new column. | |||||||||
46 | ||||||||||
47 | One to many relationships | |||||||||
48 | 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. | |||||||||
49 | ||||||||||
50 | Single sheet, multiple column representation | |||||||||
51 | 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. | |||||||||
52 | ||||||||||
53 | Contracting processes input sheet | |||||||||
54 | OCDS field path | ocid | tender/title | contracts/0/title | contracts/1/title | contracts/2/title | ||||
55 | Data | ocds-123456 | Purchase of office furniture | Contract for supply of 30 desks | Contract for supply of 10 chairs | Contract for supply of 40 laptop stands | ||||
56 | ||||||||||
57 | Multi sheet, single column representation | |||||||||
58 | 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. | |||||||||
59 | ||||||||||
60 | Tenders input sheet | |||||||||
61 | OCDS field path | ocid | tender/title | |||||||
62 | Data | ocds-123456 | Purchase of office furniture | |||||||
63 | ||||||||||
64 | Contracts input sheet | |||||||||
65 | OCDS field path | ocid | contracts/0/title | |||||||
66 | Data | ocds-123456 | Contract for supply of 30 desks | |||||||
67 | ocds-123456 | Contract for supply of 10 chairs | ||||||||
68 | ocds-123456 | Contract for supply of 40 laptop stands | ||||||||
69 | ||||||||||
70 | Refer to the flatten-tool documentation for more details: https://flatten-tool.readthedocs.io/en/latest/unflatten/ | |||||||||
71 | ||||||||||
72 | Adding new sheets | |||||||||
73 | To add a sheet which will be used to enter data, duplicate the Input: Contracting processes sheet and add/remove columns as required. | |||||||||
74 | ||||||||||
75 | 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. | |||||||||
76 | ||||||||||
77 | Changing the structure of the input sheet(s) | |||||||||
78 | The Meta sheet defines the structure of the Input: Contracting processes sheet. The following parameters are set in the first row of the sheet: | |||||||||
79 | ||||||||||
80 | Parameter | Description | ||||||||
81 | skiprows | This parameter defines how many rows are above the OCDS field path row in the Input: Contracting processes sheet, these rows will be skipped by the OCDS Data Review Tool | ||||||||
82 | headerrows | This parameter defines how many rows are below the OCDS field path row in the Input: Contracting processes sheet before the data, these rows will be skipped by the OCDS Data Review Tool | ||||||||
83 | hashcomments | This parameter means that any sheets or columns beginning with '#' will be skipped by the OCDS Data Review Tool | ||||||||
84 | ||||||||||
85 | Refer to the flatten tool documentation for more details: https://flatten-tool.readthedocs.io/en/latest/unflatten/#configuration-properties-skip-and-header-rows | |||||||||
86 | ||||||||||
87 | Updating the publisher metadata | |||||||||
88 | The Meta sheet also sets the metadata about the publication. This should be updated with the details of the publisher. | |||||||||
89 | ||||||||||
90 | 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/ | |||||||||
91 | ||||||||||
92 | Auto-populated fields | |||||||||
93 | Columns B-L of the Input: Contracting processes sheet are hidden by default and are used to calculate fields which are required by the OCDS schema, but may not need information to be entered directly by users of the spreadsheet template. | |||||||||
94 | ||||||||||
95 | Assumptions | |||||||||
96 | The following assumptions were used to create this simplified template: | |||||||||
97 | ||||||||||
98 | 1. There is a maximum of one award per contracting process | |||||||||
99 | 2. There is a maximum of one contract per contracting process | |||||||||
100 | 3. There is a maximum of one supplier per contracting process | |||||||||