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.

Publish to the Web

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:

https://spreadsheets.google.com/feeds/list/127o3zpDLAxdpK8s8YuDZsCdTQKd7RrztXT227yFH-i8/1/public/values?alt=json

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.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.