If you’ve recently discovered how easy it is to use Kentico Kontent to manage your content, you may think, "Why don’t we use this instead of our existing CMS?" The problem is, if you’ve been using another CMS for some time, you probably have hundreds or even thousands of content items already in that CMS which you’d like to migrate into Kontent. You could manually enter the content yourself, or you could create a tool that uses our Management API. Both of these options require investing time and resources—wouldn’t it be nice if there was a way to migrate the data automatically?

Installing the Add-On

With our new Google Sheets Add-on, you can import multiple content items from a spreadsheet with the click of a button! Not only can this tool import new items into your Kontent projects, but it can also update existing content items in any workflow step, using an external ID or name.

To get started, install the add-on from the G-Suite Marketplace or directly the from Google Sheets sidebar. After installing, click the Kontent icon in the sidebar to load the Add-on's home page.

Here’s how it works: Let’s say you have a hundred products in your old CMS. There may be a way to export this data in the interface, but if not, you can export the data directly from the database (more details on this later). You’ll see that all of your products have some sort of unique identifier- it may be an SKU number like "SKU-123" or a GUID like "4afa3a64-f525-4e2f-b7e7-784f969d333c."

Either way, this can be used as the external_id of your new Kentico Kontent items. This is a unique ID that isn’t displayed in our interface but links the content item to the corresponding record in your external database. If you need to re-run the import process, this tool can locate the appropriate content item based on this external_id and update the existing item instead of creating a new one.

Once you’ve exported your products to a spreadsheet and uploaded it to Google Sheets, you should take the time to ensure your rows are formatted according to the rules in the "Formatting cell values" section below. This will help prevent errors during the import process and ensure everything looks correct in Kontent. After that, you’re ready to import! In the next few sections, we’ll go into greater detail about each step.

Preparing the Spreadsheet

If you’re migrating your content from an existing database, you’ll first need to export the data into an Excel spreadsheet. There are a couple of ways to do this:

• SQL Server Export Wizard or OPENROWSET
• Copy results from Grid into Excel

However you do it, you’ll end up with a spreadsheet that contains headers in the first row, and your data below that. Let’s open the spreadsheet in Google Sheets so that we can prepare it for import into Kontent. Upload the spreadsheet to Google Drive by dragging the file onto your browser. Once the file is uploaded, right-click and choose Open with > Google Sheets:

Google will open the file in their editor, but it’s still an XLSX file. We need to save the spreadsheet as a Google Sheet to allow the use of Sheets Add-ons. In the File menu, choose Save as Google Sheets:

Your data should now be available as a Google Sheet, and the Add-ons menu will appear with the Kentico Kontent option for importing the data.

Setting the Project ID and Keys

Three keys will be needed to import content:

• Project ID
• Preview API key
• Management API key

You can find these on Kentico Kontent’s API Keys page:

 Once you’ve found these keys, open the main menu by clicking the Kentico Kontent icon in the sidebar, go to Project Settings to add the keys, then click Save.

Setting the Headers

As stated previously, the first row of the Sheet will contain the headers for your columns. Let’s say you’re migrating products from your external CMS into Kontent—your Sheet may look like this:

SKUGUID	SKUNumber	SKUName	SKUPrice	SKUCreated
F4891FB5-5215-4795-8A6F-18A4F68394FD	CO-ETH-YIRGACHEFFE	Ethiopia Yirgacheffe (decaf)	3.5	2017-01-10 15:46:54.5576119
DD84A64C-F0BE-42AA-9F47-228ED6520D27	CO-ETH-YIRGACHEFFE-5-lb	Ethiopia Yirgacheffe (decaf) (5 lb)	52	2017-01-10 16:02:44.6796146
031A9DE2-51F4-41F7-B2FE-5825FBAADD6C	CO-ETH-YIRGACHEFFE-4-oz	Ethiopia Yirgacheffe (decaf) (4 oz)	3.5	2017-01-10 16:02:44.6186085
FE991A97-3998-4FA0-9DF7-59A00622297B	CO-ETH-YIRGACHEFFE-16-oz	Ethiopia Yirgacheffe (decaf) (16 oz)	12	2017-01-10 16:02:44.5085975

These headers come from your external CMS, but the Kontent Sheets Add-on expects each header to be the code name of an element. If you open the content type you’ll be creating items for, you can view the code name of each element by clicking {#}:

You can also click Generate Sheet on the main menu to open a list that displays all of the content types in your project. When you click on one, a new Sheet will be created containing the elements as headers. In addition to the content type’s element code names, your Sheet should also contain 5 other headers:

• name (required): The name of the content item to create or update.
• external_id (optional): The external ID of the content item to update.
• codename (optional): The codename of the content item being created/updated.
• language (optional): The language of the variant to update. This should match the code name of a language in the project’s Localization page, and is case sensitive. If a language is not provided, the add-on will get the project's default language using Management API.
• currency_format (optional): Used to determine how values for Kontent "number" elements are parsed. US-formatted strings ("1,500.75") and EU-formatted strings ("1 500,75") are parsed into a valid number based on this setting. Should be either "US" or "EU." If omitted or empty, US formatting will be used.

See the Locating and updating existing items section to read more about how existing items are located and how to update content item names and codenames.

After adjusting the headers of the example product Sheet above, the Sheet may look like this:

external_id	sku	name	price	date_offered
F4891FB5-5215-4795-8A6F-18A4F68394FD	CO-ETH-YIRGACHEFFE	Ethiopia Yirgacheffe (decaf)	3.5	2017-01-10 15:46:54.5576119
DD84A64C-F0BE-42AA-9F47-228ED6520D27	CO-ETH-YIRGACHEFFE-5-lb	Ethiopia Yirgacheffe (decaf) (5 lb)	52	2017-01-10 16:02:44.6796146
031A9DE2-51F4-41F7-B2FE-5825FBAADD6C	CO-ETH-YIRGACHEFFE-4-oz	Ethiopia Yirgacheffe (decaf) (4 oz)	3.5	2017-01-10 16:02:44.6186085
FE991A97-3998-4FA0-9DF7-59A00622297B	CO-ETH-YIRGACHEFFE-16-oz	Ethiopia Yirgacheffe (decaf) (16 oz)	12	2017-01-10 16:02:44.5085975

Formatting Cell Values

To avoid errors in importing data, please ensure that the data in each column is formatted according to the element it will be stored in. Most elements (such as Text and Number) are straight-forward, but some require specific formatting:

• Rich text: See Setting Rich Text values
• Date & Time: The script will first try to parse the value using the JavaScript Date(string) constructor, so any valid DateTime string will succeed. A typical valid string would be in the format "mm/dd/yyyy hh:mm am/pm." The script will also accept strings that use dashes instead of slashes ("12-25-2019 6:00 AM") or timestamps from SQL ("2019-12-25 06:00:00.0000000").
• Number: If the value is not a valid float like "12.50" the script will try to parse the number based on the currency_format value (see Setting the headers). As US and EU formats are currently supported, some examples of valid numbers are: "1,500.75", "1.500,75", and "1 500,75". The cell should not contain letters or currency symbols.
  If you are using EU formatting, we recommend changing any Number columns in the Sheet to use Plain text formatting:

• URL Slug: There is no special formatting needed for URL Slug elements. If a value is provided, the element's mode will be changed to "custom" and no longer auto-generate based on another content type element. To revert the URL Slug element back to auto-generation, set the value to "#autogenerate#".
• Taxonomy and Multiple Choice: Values should be the code name of the items, separated by a comma (for example "on_sale, bestseller")
• Assets: Values for Asset elements should be a comma-separated list, as the element can accept multiple Assets. The format for a single asset is <identifier type>:<identifier> where the type is either id or external_id and the identifier is the corresponding value. An example of updating multiple Assets at once is "id:0013263e-f2a9-40b1-9a3e-7ab6510bafe5,id:08bf515c-3b0e-4760-907b-6db0a22d41f3."
• Linked Items: The value of this cell is the same as Assets. It should be a comma-separated list of items in the format <identifier type>:<identifier>. You can use id, external_id, or codename to reference content items, for example: "codename:birthday_party,id:eba1015a-dfd4-5736-abc1-5de3ed5df732."

Importing the Content

Once the spreadsheet has been prepared, there is one final step! The name of the Sheet must match the code name of the content type the imported items will use. You can right-click the Sheet’s name at the bottom of the screen and choose Rename to change the name. If you used the Generate Sheet menu to create the Sheet, it will already contain the code name of the content type.

Finally, you are ready to import the data. Open the main menu and select Import. You have 2 options before you begin:

• Update existing items: If enabled, an existing content item will attempt to be updated using the external_id of each row, or the name if there is no external ID column. If you disable it, a new item will always be created, but be aware that you may run into errors if you’ve provided an external ID that already exists in the system. As is the case with all errors, the script will simply skip that record and continue processing the rest of the Sheet.
• Preload content items: If enabled, the script will load all content items from your project at the start. When attempting to locate existing items, the script will use the cached data, greatly reducing the number of API calls and execution time.

After clicking the Run button, please wait while the script runs. When it’s finished, a new window Sheet is created containing a detailed record of the operations taken per-row and general information:

You can leave this Sheet on your Google Drive or store it locally as a record of your import history.

That’s it! With this new tool, you can quickly and easily import content into your Kentico Kontent projects without writing any code at all. Because the tool runs directly within Google Sheets, you also gain the added benefit of collaborative editing while preparing the data for import. Install the add-on here to start importing your content today.

If you have any questions, bugs, or feature requests, please submit them to the GitHub repository.