Migrating Content with the Google Sheets Add-On

By Eric DugreJan 27, 2020

When migrating content from an external system, you most likely have hundreds of items stored in your database. Manually creating these content items in Kentico Kontent would be a dreadful and time-consuming task... Surely there’s a better way?

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. When you create a new Sheet or open an existing one, the tool can be found in the Add-ons menu (it may take a second to load):

Kentico Kontent

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.

Kentico Kontent

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:

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:

Kentico Kontent

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:

Kentico Kontent

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:

Kentico Kontent

 Once you’ve found these keys, open the Add-ons > Kentico Kontent > Configure menu in Google Sheets and 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:

SKUGUIDSKUNumberSKUNameSKUPriceSKUCreated
F4891FB5-5215-4795-8A6F-18A4F68394FDCO-ETH-YIRGACHEFFEEthiopia Yirgacheffe (decaf)3.52017-01-10 15:46:54.5576119
DD84A64C-F0BE-42AA-9F47-228ED6520D27CO-ETH-YIRGACHEFFE-5-lbEthiopia Yirgacheffe (decaf) (5 lb)522017-01-10 16:02:44.6796146
031A9DE2-51F4-41F7-B2FE-5825FBAADD6CCO-ETH-YIRGACHEFFE-4-ozEthiopia Yirgacheffe (decaf) (4 oz)3.52017-01-10 16:02:44.6186085
FE991A97-3998-4FA0-9DF7-59A00622297BCO-ETH-YIRGACHEFFE-16-ozEthiopia Yirgacheffe (decaf) (16 oz)122017-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 {#}:

Kentico Kontent

You can also click Add-ons > Kentico Kontent > Generate to open a menu 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 3 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 (overrides name header).
  • 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.

The "name" column is used to locate an existing content item in Kontent. If an external ID is provided, that is used to locate an existing content item instead, but "name" is still required to create a new content item if the external ID wasn’t found. After adjusting the headers of the example product Sheet above, the Sheet may look like this:

external_idskunamepricedate_offered
F4891FB5-5215-4795-8A6F-18A4F68394FDCO-ETH-YIRGACHEFFEEthiopia Yirgacheffe (decaf)3.52017-01-10 15:46:54.5576119
DD84A64C-F0BE-42AA-9F47-228ED6520D27CO-ETH-YIRGACHEFFE-5-lbEthiopia Yirgacheffe (decaf) (5 lb)522017-01-10 16:02:44.6796146
031A9DE2-51F4-41F7-B2FE-5825FBAADD6CCO-ETH-YIRGACHEFFE-4-ozEthiopia Yirgacheffe (decaf) (4 oz)3.52017-01-10 16:02:44.6186085
FE991A97-3998-4FA0-9DF7-59A00622297BCO-ETH-YIRGACHEFFE-16-ozEthiopia Yirgacheffe (decaf) (16 oz)122017-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:

  • 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").
  • Taxonomy and Multiple Choice: Values should be the code name of the items, separated by a comma (for example "on_sale, bestseller")
  • Rich Text: This element will most likely require the most pre-processing; try to avoid complex HTML and text formatting. The list of supported HTML elements and their syntax can be found in our documentation.
    Notably, you can insert links to other content items by referencing their external ID using the format described in this section. For example: "Buy our <a data-item-external-id="F4891FB5-5215-4795-8A6F-18A4F68394FD">new coffee</a>." You can also add inline content items using the syntax here, for example "<object type="application/kenticocloud" data-type="item" data-external-id="59713"></object>".
    Or, you can use special macros designed for this add-on. In the list below, the identifier_type can be "id" or "external-id":
MacroDescriptionFormatExample
link-itemInserts a link to a content itemmacro:identifier_type:identifier:text##link-item:id:5946ca5d-cebe-4be1-b5f0-4cd0a0e43fb5:coffee is good##
link-assetInserts a link to an assetmacro:identifier_type:identifier:text##link-asset:id:0013263e-f2a9-40b1-9a3e-7ab6510bafe5:asset##
itemInserts an inline content itemmacro:identifier_type:identifier##item:external-id:article6##
  • 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."


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 Add-ons > Kentico Kontent > Generate 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 Add-ons > Kentico Kontent > Import menu. You have 2 choices:

  • Update existing, create if not found
  • Always create new

If you choose the first option, 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 choose the second option, 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.

After clicking the Import button, please wait while the script runs. When it’s finished, a new window will open up containing a detailed record of the operations taken per-row, as well as the total number of:

  • API calls made
  • New content items created
  • Language variants upserted
  • Errors

You can copy this information and 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.

Written by
Eric Dugre

I am a Support Platform Specialist who joined Kentico in 2015. When I’m not helping customers, I focus on developing open-source projects, integrations, and SDKs for Kentico products.

More articles from Eric

Subscribe to Kentico Kontent Newsletter

Stay in the loop. Get the hottest updates while they’re fresh!