Importing projects from a Spreadsheet using the Spreadsheet Project Import tool

This tool uses a predefined project template so spreadsheet columns map directly into your project fields — great for bulk-creating or updating projects from administrative lists or exported datasets.

Before you start

Decide which template you will use in PublicInput. The spreadsheet must contain columns that correspond to fields in that template (name, description, tags, any custom fields, and optional location fields).
Supported spreadsheet sources:
- CSV (.csv) — best for simplicity and predictability.
- Excel (.xlsx) — supported if your import screen accepts file uploads.
- Google Sheets — you can use a published/CSV-export URL or copy the sheet to a CSV for import.
If you plan to place projects on a map, include latitude and longitude columns (decimal degrees). If you don’t include coordinates, projects will import without map placement.

Formatting your spreadsheet

Header row: The first row should contain column names (e.g., project_id, project_name, description, tags, latitude, longitude, start_date). Use clear, short names.
Project identifier: Add a project_id column if you plan to re-run imports and update existing projects. The import tool uses this field to match and update previously imported projects.
Name and description: Include one column to be used as the project Name and another for Description.
Tags: If you have a tags column with multiple tags per cell, pick a consistent separator (comma, semicolon, pipe). You will tell the import tool which separator to use.
Field merges: Template fields are merged by name. In your templates use double brackets for merging (for example [[project_name]] or [[description]]) so the tool knows where to insert values from your spreadsheet.
Dates & numbers: Use consistent, machine-friendly formats. We recommend ISO dates YYYY-MM-DD and decimal degrees for coordinates.
Empty values: Null/blank cells are allowed, but blank values may result in missing fields in the final project. If you need a placeholder, enter it explicitly.

Step-by-step process

1. Prepare your spreadsheet

Ensure the header row contains the field names you want to import.
Verify any latitude/longitude columns and convert coordinates to decimal degrees if necessary.
Add a project_id column if you want imports to overwrite or update existing projects.

2. Export or publish the sheet

If using Google Sheets: either download as CSV (File → Download → Comma-separated values (.csv)) or use the sheet’s CSV export URL (ensure the sheet is shared appropriately so the import can access it).
If using Excel: save as .xlsx or export as .csv based on the import tool’s supported formats.

3. Open the Spreadsheet Project Import tool

In PublicInput go to Settings → Spreadsheet Project Import
Upload your spreadsheet in the Spreadsheet File field, and select "Upload & Preview"
Choose the Template Project that will be used for the merge
Choose your Destination Department for your STIP projects

4. Mapping your fields

Using your Import Preview, map your spreadsheet columns to the project template’s fields:
- Select the Name field (the column that contains the project title).
- Select the Description field.
- Select the Project ID field (optional but recommended for updates).
- Select the Tags field and specify the separator (e.g., comma).
- If present, map Latitude and Longitude to enable map placement.
Confirm that template tokens use double curly brackets (for example {{project_name}}) so merges happen correctly.

5. Limit initial imports (recommended)

For your first run, limit the number of rows (or import a test file with a few rows) to confirm mappings and output. This prevents large-scale errors and speeds up troubleshooting.

6. Run the import

Once mappings look correct, run the import. Large imports may take several minutes; do not close the window until the tool reports completion.
The import tool will create new projects and — if a project_id match is found — update projects previously imported using the same id. Typically, the service will overwrite the project name and description for matched items.

Post-import actions

Review newly imported projects for accuracy (name, description, tags, location).
If not all expected values appeared:
- Confirm column names match the template fields.
- Check for blank/null cells in your spreadsheet.
- Verify the tags separator is correct.
- For coordinates, confirm that latitude/longitude values are in decimal degrees and mapped correctly.
If you need to change the spreadsheet or template, make the change and re-run the import. The import will update projects it identifies by project_id.

Troubleshooting tips

Missing fields: Check for typos in your header row. The import relies on exact column-to-template mapping.
Tags imported as one string: Make sure you set the correct tag separator during preview.
Coordinates incorrect or not appearing: Confirm latitude and longitude are mapped to the correct template fields and are formatted as decimals (e.g., 37.7749, -122.4194).
Dates display incorrectly: Use ISO format (YYYY-MM-DD) or a format that your template expects.
Updates not applied: Make sure project_id values exactly match the ids used on previous imports and are unique per project.
Large import is slow: Use a smaller test import first; ensure no additional processing (large attachments or images) is required per row.

By preparing a clean, well-formatted spreadsheet and mapping its columns to a matching project template, you can quickly bulk-import or update projects in PublicInput. Start with a small test import, confirm the preview, then run the full import. The import will overwrite name/description for projects matched by project_id, so use that field for safe updates.