Importing profiles from a spreadsheet into your Community Box directory

If you already have information stored in a spreadsheet, you can quickly import it into your Community Box directory.

Watch a video tutorial

This video takes you through the process of importing profiles from a Google Sheet.

Jump to the written guide

Preparing your spreadsheet

Before importing data into your directory, it’s important to make sure your spreadsheet is structured correctly.

Preparing your spreadsheet properly helps ensure:

• profiles import smoothly
• locations appear correctly on maps
• information is placed in the correct fields
• your directory remains organised and easy to search

Use column headings

The first row of your spreadsheet should contain column headings. This will help you to match your spreadsheet data to fields in Community Box during the import process. For example:

Each column represents a type of information, and each row represents one directory profile.

Use one row per profile

Each row should represent one profile, for example:

Avoid splitting information across multiple rows for the same profile.

Keep data in separate columns

Each type of information should have its own column, for example:

Avoid combining information like this:

Keeping data separate makes it easier to map (match up) fields correctly during import.

Include location information

If you want profiles to appear on a map, your spreadsheet must include an address or location column, for example:

Community Box will try to automatically match addresses to map locations during import, but if you want an accurate map pin position, we recommend including the following details in your spreadsheet:

• Country
• The state, county, region or province
• The zip or postal code

For example, if you don't specify a country or postcode, there’s no way for Community Box to know that this is a directory for London, England and not London, Ontario in Canada.

Most accurate:

 123 Camden High Street, London, NW1 7JR UK

Less accurate:

Camden High Street, London

Least accurate:

Camden

Match your spreadsheet to your form fields

Before importing, check the Form Designer in your Community Box dashboard. Your spreadsheet columns should roughly match the fields in your directory form. For example:

You will tell Community Box which field corresponds to which spreadsheet column later so the names don't have to match exactly, but setting a name that makes sense in this step will make it easier!

If a field doesn’t exist yet, you can add it in the form designer before importing. You can also delete any fields you don't need in the Form Designer.

❗ If you have multiple directories in your account, make sure to select the correct one from the dropdown list at the top of the Form Designer page.

Use categories and tags (optional)

A category is a collection of tags, and there are a few options for adding these to your spreadsheet. In this example, Restaurant and Cafe are tags within the Category collection:

If a profile has more than one tag within a category, you can separate them with commas, for example:

Alternatively, you can have a column per tag and then match all of those columns to the same field in your directory, for example:

You can also have multiple categories with their own sets of tags, for example:

Just make sure that you have the corresponding number of category fields set up in Community Box before you import.

Import images

If you also want to import images such as profile pictures, you will need to add a link for each image to the relevant spreadsheet row.

You will need to have all of the images stored somewhere online such as Google Drive or Dropbox. 

❗ Make sure that the images (or the folder they are in) are set to be publicly available, normally this setting is called something like “anyone with the link can view”. If access to the images is restricted, Community Box won’t be able to import them.

Check for empty rows or duplicates

Before importing, check your spreadsheet for:

• empty rows
• duplicate profiles
• incorrect formatting

Cleaning these up beforehand helps ensure your directory imports correctly.

Importing your spreadsheet into Community Box

Once your spreadsheet is ready, you can import the data into your directory.

Open your directory

1. Log in to your Community Box dashboard
2. Open the directory you want to import profiles into
3. Select Integrations
4. Select Google Drive
5. Select Import from sheet

Share your spreadsheet with Community Box

1. Copy the integration email address provided in the dashboard
2. Open your spreadsheet in Google Sheets
3. Click share
4. Paste the email address into the sharing field

You can set the permission to:

• viewer (recommended)
• editor

Viewer access is normally enough since Community Box only needs to read the data. This only gives Community Box permission to view that specific Google Sheet; we cannot access any additional Google Drive data.

You can also uncheck the option notify people.

Click Share to complete this step.

Find your spreadsheet identifier

Next, copy the spreadsheet ID from the address bar. This is an example Google Sheet link:

docs.google.com/spreadsheets/d/1AbCdeFGhijkLMnopQRsTuvWxYZ123456/edit

The spreadsheet ID is the section in bold, between /d/ and /edit.

Connect your spreadsheet

1. Return to your Community Box dashboard
2. Paste the spreadsheet ID into the spreadsheet identifier field
3. Click Connect

If the connection is successful, Community Box will confirm that the spreadsheet has been connected.

Select the worksheet

If your spreadsheet contains multiple sheets, choose the one you want to import.

Community Box will detect the column headings from the first row.

Map your fields

Next, choose from the dropdown boxes to match your spreadsheet columns to the fields in your directory, for example:

Not every field needs to be mapped, and you can also match multiple columns to one field.

Launch the import

Once your fields are mapped, you can click on Launch Import to start the import process:

By default:

• all rows will be imported
• the first row (column headings) will be ignored

If you have a large spreadsheet with a lot of rows, we would recommend doing a test import for one or two profiles first to make sure everything works as expected. You can do this by updating the row numbers that will be imported.

Once the import is complete, click Close.

Review your imported profiles

After the import is complete, return to your directory. You should now see the profiles from your spreadsheet.

You can preview the directory to see how the profiles appear to visitors.

Troubleshooting spreadsheet imports

If you're having trouble importing data, the issue is usually caused by a small formatting issue or connection problem. Below are the most common problems and how to fix them.

My spreadsheet won’t connect

• Make sure the spreadsheet has been shared with the integration email address provided in your dashboard. Open your sheet, click share, and confirm the email is listed.
• Make sure you've copied the spreadsheet ID correctly from the URL, for example:

docs.google.com/spreadsheets/d/SPREADSHEET-ID/edit

Copy the section between /d/ and /edit.

My data imported but some fields are empty

This usually happens when spreadsheet columns were not mapped to directory fields during import.

Make sure each spreadsheet column is matched to the correct field, and then run the import again.

My locations aren’t appearing on the map

Locations may not appear (or appear in the wrong place) if addresses are too vague. For example, there are multiple cities/towns in different countries with the same name.

Most accurate:

 123 Camden High Street, London, NW1 7JR UK

Less accurate:

Camden High Street, London

Least accurate:

Camden

Update the address in your spreadsheet and re-import if needed.

My categories or tags didn’t import

Check whether:

• the category field exists in the Form Designer
• the category column was mapped during import

If there isn't a Category field in the Form Designer, the data cannot be imported and mapped correctly.

Some profiles didn’t import

This may happen if rows contain:

• missing required fields
• empty rows
• formatting errors

Check the spreadsheet and try importing again.

Duplicate profiles appeared

Duplicates usually happen if the same spreadsheet is imported more than once.

You can remove duplicates manually in your dashboard or clean the spreadsheet before importing again.

We recommend doing a test import with just a few profiles first to check everything is working as expected to avoid having to delete too many profiles again before reimporting.

Tips for successful imports

✔ Always include column headings in the first row

✔ Use one row per profile

✔ Keep information in separate columns

✔ Ensure addresses are clear and complete

✔ Check that spreadsheet columns are mapped correctly

Need any help importing your directory from Google Sheets? Send us a message via your Community Box dashboard or email help@communitybox.co