These instructions will walk you through the process of importing project data into Copper from an external source. The instructions will show you how to add project data and related records to a Google Sheet and then bulk import the sheet to create Copper Project records.
Step 1: Import all four types of data
If you are using a unique identifier that is not a default field in Copper, you'll need to set up custom fields for all “Companies”, “People”, “Opportunities” and “Leads” data. For example we can create a custom field called “External ID” for your original data identifier.
Step 2: Make a copy of our "Project Import' spreadsheet
- Click the following link and make a copy of our spreadsheet template: Project Import Spreadsheet
- Click "Make a Copy" to create your own copy that you can now customize.
- If you have not already done so, download and install Copper's Custom Report Builder add-on. You can download it here.
Once installed, you'll see the Custom Report Builder in the "Add-ons" section of Google Sheets.
Part 3: Modify the copied spreadsheet
- On your copy of the spreadsheet, find the "Import Config" box and type in your registered Copper email address.
- In your Copper account, navigate to Settings ⇨ Integrations ⇨ API Keys and click Generate API Key. Label the API Key "Project Import" and copy and paste the key into the "Copper API Token" section of the "Import Config" box.
- On the Google Sheets menu, navigate to "Add-ons" ⇨ Copper CRM Custom Report Builder ⇨ Import data.
- In the pop-up that says "Let's load some data" select the first record type that you'll need to relate. In this example, we'll start with Companies. We need all Company records to import, so you can leave the "Choose a filter" option on "All Companies".
- Wait for the data to load.
- Notice that the data import has created a new tab at the bottom of the sheet called "All Companies - Companies"
- Rename this tab to "companies".
- Repeat steps 3 through 7 for the three other core record types; People, Opportunities, and Tasks.
Please note: You must rename the default tab name to "people", "opportunities" and "tasks" specifically. For Opportunities, if you have multiple pipelines, the importer will ask you which pipeline you'd like to import from. You may need to extract the data from each individual pipeline and combine it in a single sheet.
When you're finished, your sheet tabs should look like this:
Please note: If you are not using one of the record types, please create a blank sheet and name the tab to match the above screenshot. The tab must still be there even if there is no data on it.
- Next, you'll modify each of the data sheets. If you're using a legacy data ID, drag the "External ID" column to the first column of each sheet.
If you are only using the Copper Record ID, duplicate the first column.
- Repeat step 9 for each data sheet.
Part 4: Add your project data to Sheet 1
- Click on the tab for "Sheet1" and notice that there are 6 columns in which project data can be added.
Of these columns, only the first column, "Project Name", is required.
Of the five optional columns, “Related ID” and “Related Type” will give each project a primary related resource. “User Name” will assign the user to project. You can also specify the “Status” of project to “Open” or “Completed”.The last 2 columns are status indicators and do not need to be filled in. “*Progress*” can be “Done”, “Skipped” or “Not found” (when a related entity is not found on the data sheet). When one project is successfully imported, “*Project ID*” will be the newly created ID of that entry for your reference.
- The “Related ID” column is where you enter the “External ID” of the related entity that the project should be linked to. It meeds to match the “External ID” column of the corresponding data sheet.
- The “Related Type” column must be one of the 4 data types: “company”, “opportunity”, “person” or “task”. Data validation on this column can also help you obtain the correct input.
- For the “User Name” column, we've created a tool called “Get users list” to help you obtain the correct names of your Copper users.
To use the tool, simply navigate to the Google Sheets menu and click Copper ⇨ Get users list. Verify and allow the script to run and it will create a new tab on the sheet called "All Users" which will contain the name, email address, and user ID for each of your users.
Then, just paste the user name into the column so he will be the project assignee.
- The “Status” column can only be set to “Open” or “Completed”. Data validation can also help here.
- The “Description” column is where you can add the content of your projects. For example you might simply paste the description from your source data.
- After all columns are filled in, we can now start the importing process by navigating to the Google Sheets menu and clicking Copper ⇨ Import projects.
- You may need to authorize and allow the script permission to run. Please follow the prompts to do so.
- Once authorized, you will see a pop-up notifying you that the script is running.
- When the script finishes running, the import is complete! You will see the progress column report the progress of each Project you imported. When the import is successful, the progress column will read "Done" and a project ID will be generated in the next column over.
- When you're finished, you can verify the successful import of your projects by navigating to the Projects section of your Copper account. You should see your imported projects with the related records and description that you included in your import.
Still have questions? Ask in our Community, and get answers from our Customer Success Team as well as fellow users.