Drupal Migrate content from Google Sheets
I was given with a task to import content to a Drupal 8 site from data in a XLS file, I know that there is a high risk that the data in the XLS will change soon so I thought, if I have the data in a Google Sheet and I implement a migration process to import the data from the Google Sheet will be very practical, I can save a lot of time writing once and running multiple times, and that is what I did. This post explains how to implement a migration configuration for a Google Spread Sheet Source.
I’m assuming that you know what migration modules are and how it works and is enabled in your site.
First, The Source
So, we need a google sheet as source with data in it, I have created this sheet to give an example, is a list of shops for an entity that will be called the shop as well.
https://docs.google.com/spreadsheets/d/127o3zpDLAxdpK8s8YuDZsCdTQKd7RrztXT227yFH-i8/edit#gid=0
As you can see we have these fields
id: The id of the source, we need one and must be unique. It’s required by migration.
title: The name of shop.
website: An url to the shop brand.
phone: A simple text for the phone number.
description_es: The shop description in spanish.
description_en: The shop description in english.
description_br: The shop description in portuguese.
logo: An url to the logo image.
category: A string with the shop category, is the term text of a taxonomy that already has the string imported in the site.
Any sheet in google sheet can be published to anybody and there is an API to pull the sheet data as json, we will use this feature to get the data from the Sheet.
To publish the sheet go to File > Publish and follow the modal.
Once is published we can get a json file with the data in the sheet using this URL:
https://spreadsheets.google.com/feeds/list/KEY/SHEET_NUMBER/public/values?alt=json
KEY is the hash of our sheet, in my case sample is 127o3zpDLAxdpK8s8YuDZsCdTQKd7RrztXT227yFH-i8, you can get it from the URL.
The SHEET_NUMBER is the number of the tab, starting on one. So for the example is one.
My url will be:
The destination
The entity shop will have these fields.
title: Drupal Node Title
website: a link field.
phone: a text field.
description: a text area field.
logo: an image field.
category: an entity reference to a taxonomy term in the bundle shop_categories.
The site has support for Spanish, English and Portuguese.
Required Modules
To implement this we will need these modules enabled in the site:
- migrate - migrate_drupal - migrate_tools - migrate_plus - migrate_google_sheets
The most important module is Migrate Google Sheets, this module allows us to import directly from a published sheet on google.
https://www.drupal.org/project/migrate_google_sheets
Download and install all.
Creating the migration configuration
The first thing that we need is to create a migration configuration file to be imported. Into sites/default create a folder called migration, inside of it lets create a file called
migrate_plus.migration.migrate_shop.yml
And let’s start with the basic entries, nothing weird here:
id: migrate_shop label: Migrate Shops migration_tags: - Shop status: true migration_group: migrate_shop
Next, let’s configure the destination, it will be a entity:node.
destination: plugin: entity:node
The next part is the juicy one, the source, we will use the url plugin, we will set as parse the google_sheet, we will set the source to the sheet json using the url explained before, last we need to map the rows from the sheet to fields to be used in the migration, the name of the fields will be set automatically from the sheet first row, the result will be like that.
source:
plugin: url
data_fetcher_plugin: http
data_parser_plugin: google_sheets
urls: 'https://spreadsheets.google.com/feeds/list/1jB5Dmc_z6dPNmXbudMKd_zA0LVB4X96qEtKZE9mn3-c/1/public/values?alt=json'
fields:
-
name: id
label: 'id'
selector: 'id'
-
name: title
label: 'title'
selector: 'title'
-
name: website
label: 'website'
selector: 'website'
-
name: phone
label: 'phone'
selector: 'phone'
-
name: descriptiones
label: 'descriptiones'
selector: 'descriptiones'
-
name: descriptionen
label: 'descriptionen'
selector: 'descriptionen'
-
name: descriptionbr
label: 'descriptionbr'
selector: 'descriptionbr'
-
name: logo
label: 'logo'
selector: 'logo'
-
name: category
label: 'category'
selector: 'category'
ids:
id:
type: integer
The ids entries define which field will be the ID of each record.
The process
For the process section, we will set the type, the title, and the status as published.
Next, we will set the description field with descriptions (we will see in another post how to import the other languages).
Field logo will use file_import plugin to pull the images from the urls in the sheet, will save the images locally and bind the image to the field.
Shop uri will use the url and set the title of the link to empty (you can set something better if you needed).
Last, in the sheet I have the term value, no the entity id, so I need something to convert the string to the vid, the entity_lookup plugin is the one to do that, it will lookup in the entity_type using the value_key (the field to search) and the source (as the value, field = value) to get the bundle key (the output, the vid). This plugin will help us to avoid any custom code to lookup into the database.
The result is something like this.
process:
type:
plugin: default_value
default_value: shop
title: title
status:
plugin: default_value
default_value: true
field_description_shop/value: descriptiones
field_description_shop/format:
plugin: default_value
default_value: full_html
field_logo_shop:
plugin: file_import
source: logo
skip_on_missing_source: true
skip_on_error: false
field_website_shop/uri: website
field_website_shop/title:
plugin: default_value
default_value: ''
field_category_shop:
plugin: entity_lookup
source: category
value_key: name
bundle_key: vid
bundle: shop_category
entity_type: taxonomy_term
ignore_case: true
That’s it. You have your file.
Importing the configuration and running
Once we have the configuration file ready we need to import it to make it available in the migration list.
Go to your settings.php and add this line at the end
$config_directories['migrations'] = 'sites/default/migrations';
That will include the migration directory to lookup for more configs.
Once is done, let’s import the file:
drush cim migrations --partial --diff
You must see an output of the diff and then a prompt to confirm the import, type yes and proceed.
Once is done you must have a new migration group in the status list to run.
drush migrate:status
----------------------------- ------------------- -------- ------- ---------- ------------- --------------- Group Migration ID Status Total Imported Unprocessed Last Imported ----------------------------- ------------------- -------- ------- ---------- ------------- --------------- migrate_shop (migrate_shop) migrate_shop Idle N/A 0 N/A ----------------------------- ------------------- -------- ------- ---------- ------------- ---------------
So now we can rum the import process:
$ drush migrate:import migrate_shop [notice] Processed 20 items (20 created, 0 updated, 0 failed, 0 ignored) - done with 'migrate_shop'
That’s it, you are ready and with your content imported. In another post, we will do an additional config to import the other languages.
