Exporting content using Google Sheets

In case you haven’t heard about our awesome Google Sheets add-on, feel free to browse the GitHub repository and check it out! This handy tool integrates directly into Google Sheets and allows you to update your existing content or create new content items from any spreadsheet. This makes it easy to import data from an external system or database.

Until now, if you wanted to update existing content items from your Sheet, the rows needed to be populated manually. With this new feature, you can export all content items from Kontent.ai into your Sheet with their existing data. Let’s say, for example, that your manager recently brought up an issue with articles on your site—you accidentally set a taxonomy element to Blog instead of Article! But, there are hundreds of these content items and it would take so long to update them...how can you quickly swap the taxonomy term for all these items?

Exporting your content

First, let’s take a look at the export feature and how it works. When you open the add-on, you’ll see a new menu option:

When you run the export, the add-on will create multiple new Sheets for you—one for each content type in your project. Each Sheet contains the individual content items for that content type including the headers and data; everything you need to run the import tool to update your content in the Kontent.ai project:

There is also a new column added to Sheets created using the Export or Generate menu: rich_text_components. You can read about this column in detail here, but the general idea is that this column will contain all of the data needed for each rich text element of the content item. The export feature will automatically populate this column for you, but you can also provide the full JSON data for new/updated content items when importing as well.

Importing content from another project

You may have noticed that the Export menu contains an option called Translate IDs. This add-on uses the Management API to retrieve content items, which always returns the internal ID for linked items, taxonomy, etc. This means that for elements like linked items and taxonomy, the values will look something like this: id:9b86141d-b667-4c35-82b1-826bc151d72a. That’s not very human-readable, and when transferring data between two different projects, the same taxonomy term will have different IDs.

If you enable the Translate IDs option, internal IDs will be transformed into a code name or external_id if available. For example, the ID from the previous example may now look like this: codename:announcements. This means that if you want to export all articles from Project A then import them into Project B, you can follow these steps:

1. Create the Article content type in Project B with the same elements and code names as Project A
2. Create a taxonomy group in Project B with the same code name and terms as Project A (e.g., Announcements)
3. In Google Sheets, use the Export menu to export your articles and enable the Translate IDs option
4. In the Project Settings menu, change the API keys to target Project B
5. Run the Import with the Article Sheet selected

Example - solving our Article problem

Let’s go back to the start: remember your manager yelled at you for accidentally tagging a hundred articles with the sitemap taxonomy term set to Blog instead of Article? It could take a while to find all the articles and change the taxonomy to the correct one, so how can we use the Sheets add-on to fix this?

First, we can export all our content items from the project using the new Export menu. Be sure to enable the Translate IDs option, as this will make it easier to find articles with the wrong taxonomy and set the correct value. This will generate multiple Sheets for all your content types, but don’t worry; we only care about the Article content type so we can delete/ignore the other Sheets.

Now if you select the Article Sheet you will see the sitemap column with incorrect values like codename:blog. To fix all of these references in one easy step, go to the Sheets Edit > Find and replace menu (or push CTRL+H) to open this menu:

Type in the incorrect reference codename:blog and replace it with codename:article. In the Search option, you could select This sheet to fix the problems in the currently-selected Sheet, or you can update all your Sheets at once. If you’re sure you know what you’re doing, click Replace all to update your Sheet, or you can use the Find button to view each occurrence of the search term and choose to replace or skip it.

Now that you’ve fixed the mistakes, go to the Import menu and make sure Update existing items is enabled. Run the import function with the Article Sheet selected, and all the articles in your Kontent.ai project will be updated with the new data from your Sheet. Check the result log generated after the import to make sure everything ran smoothly, then show it to your manager to impress him with how quickly you fixed the problem!

Important notes on the new release!

The new feature and the add-on as a whole can be useful as a one-time utility or integrated into your development/editing process. However, it is important to note that the Google Sheets add-on does not create a full project backup—you can only manage content items with it. If you need a solution that offers full backup/restore functionality for your projects, please use one of the other great tools found in our documentation.

As the tool was just recently released, we recommend testing the functionality in a development environment before your production environments. Also, the export function doesn’t currently use the same batching process as the import feature and is subject to Google’s 45-second execution limit. If you have thousands of content items, the request could potentially time out.

Finally, with the release of the export feature, the format for several elements such as taxonomy has changed. Previously, your Sheets could contain comma-separated code names like announcements, articles. With this release, the taxonomy, multiple choice, linked item, and asset elements follow the same format which you can read about in the GitHub repository.