Google Sheets Import

Installation

You can install this Google Workspace Add-on here or directly from Google Sheets using this button in the sidebar:

After installing, click the Kontent icon in the sidebar to load the Add-on's home page. Locate the following API keys in Kontent, then add them in the Addon's Project settings menu,

• Project ID
• Preview API key
• Management API key

Preparing the Sheet

You may create a new Sheet or edit an existing one. The Generate Sheet menu of the Add-on can also generate a new Sheet for you, along with the required headers, based on the project's content types.

If you have a local spreadsheet file, you can open it in Google Sheets by uploading it to Google Drive, then selecting Open with > Google Sheets.

Once the file is open in Sheets, you must re-save it as a Google Sheet file:

⚠️ The name of the Sheet must match the code name of the content type in your project which will be used to create items

If you used the Generate Sheet menu to create the Sheet, the code name of the content type will be automatically generated. If you are creating the Sheet manually, you can find the content type code name in Kontent:

Setting the headers

The headers (first row) of your Sheet must contain the code names of the content type's elements. If you use the Generate Sheet menu of the Add-on, these headers will be automatically generated. If adding headers manually, you can find the code names of the elements when editing the content type in https://app.kontent.ai.

In addition to the element code names, the header row 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.

The following is an example of what a Sheet named product might look like:

Formatting Cell Values

To avoid errors in importing data, the data in each column must be 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
• 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:

• 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).
• 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.

Setting Rich Text values

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 or assets by referencing and ID or 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":

You can insert content item links, asset links, and inline content items by entering the HTML or macro manually. Or, you can use the Rich text macros menu found in the add-on to insert macros:

Importing the Content

Click the Kontent icon in the sidebar and open the Import menu. You have two options before starting the import:

• 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:

Locating and updating existing items

Depending on what information is available in the sheet, existing items in your project are located differently:

• If the row contains an external_id:
  • Existing items are located based on external_id. Name is ignored, and if the external_id is not found, a new item is created with that ID.
  • For both new items and existing items, you are able to update the name and codename of the content item if those values are present in the row.
• If there is no external_id:
  • Existing items are located based on name. If no existing item is found with that name, a new item is created with that name.
  • If a new item is created, you can specify the codename and external_id for the new item.
  • If an existing item was found, you can only update the codename of the item. external_id cannot be updated, only specified on creation of the item.

Batching

The import process will run for a maximum of 30 seconds. If your Sheet contains a large amount of data that cannot be completed within 30 seconds, the import will be performed in batches. You will be shown a menu stating which rows were successfully processed, and you can click Resume to continue importing the Sheet.

Contributing

To develop and test this Google Script project, first install this Chrome extension which integrates Google Scripts with Git. Then fork this repository and create a new Google Script project on https://script.google.com/.

Use the new menus added by the extension (Repository, Branch, ..) to pull the code from your forked repository into the Script project. After you've made the desired code changes, test the add-on in a Google Sheet using the Run > Test as add-on menu.