Importing to Craft CMS from Google Sheets with Feed Me

If you're working with a small amount of data or building a new website, entering the information manually into Craft is ridiculously easy and user friendly. Sometimes though, if you're replacing an existing website or building a new one with lots of existing data, it can be easier to import it all automatically.

Craft has a first-party plugin to help with this called Feed Me, and it makes importing data really simple - as long as the data you're giving it is formatted correctly. You can import from lots of data formats (XML, RSS, ATOM, CSV or JSON), but all of them are basically text files and not user friendly, so we set up a Google Sheet which the client can access. That's great for them, but converting that spreadsheet into a format Feed Me can understand has a few things to be aware of. This article will explain how we've done it on several projects, and things we've learned to make it easier.

On our most recent project, we used Feed Me to import 250+ building materials into a client's new website. This approach is not only useful to reduce manual data entry, but also:

• Helps the client to visualise all their information and standardise it all before it goes into the CMS
• Highlights if any entries are missing information
• It also helps the client to start thinking in terms of CMS fields and standardised information, rather than every product being an individual page/layout.

Here are the steps we take each time, and what you need to be aware of when setting up an import.

Make sure the data has a column with unique values

When you import data, Feed Me needs to be able to uniquely identify entries when deciding if it should create a new entry, or update an existing one. Often this will be the title of the entry, but in our most recent project, several of the products had the same names and were differentiated by categories. If this unique data doesn't exist, you'll need to create a new field in the spreadsheet (and Craft) like a simple incrementing number, then use that as the identifier. Once the import is complete, you can delete the field from Craft.

Structure the spreadsheet so each category goes in one cell

If you're allowing an entry to have multiple categories, they'll have to be in an array for Feed Me. In our building project, each products could have 1, 2 or 3 applications. For example, 1763 Granite might be used for Fireplaces, Exterior Walls and Interior Walls. If you put these all in one cell, Feed Me will create a category called "Fireplaces, Exterior Walls, Interior Walls" instead of 3 separate categories. Set them up so the spreadsheet has one column for each category (and add more columns as necessary). When you've got the JSON file (we're getting there), you'll be able to merge these into an array for import.

Export the file to JSON

Feed Me also supports XML, RSS, ATOM & CSV formats, but I like working with JSON files. We tried using CSV for the last project and found that the product descriptions had commas in, which meant those rows had more cells than others and broke the import. So this article is for JSON only. To get JSON out of a Google Sheet, you'll need to publish the file to the web (you can take it down afterwards). In Google Sheets, go to File > Publish to Web

Then, go to this link and replace the ID and sheet number: http://gsx2json.com/api?id=SPREADSHEET_ID&sheet=SHEET_NUMBER&q=QUERY (details: http://gsx2json.com). ID is the big long alpha-numeric code in the middle of the document URL. Sheet is the number of the sheet you want data from. Your first sheet is 1, your second sheet is 2, etc.

Remove the columns array

The JSON you copy and paste will have your data as both a columns array, and a rows array. You probably don't need columns and just want the row data from the spreadsheet, so delete the whole columns array. Keep the data that starts with:

{
 "rows": [
 {

Merge the categories

Ok, so earlier I said you'll want each category in a separate cell. That means you've now got some category data that looks like this:

{
 "application1": "Fireplaces",
 "application2": "Exterior Walls",
 "application3": "Interior Walls",
 "name": "1763 Granite",
 ...
},

Using most popular text editors (I'm on Visual Studio Code), you can use multi-select to select all instances of some text. So here I'd double click on application1 and hold cmd+d until I've got all instances of that selected in the JSON file. Assuming your file is formatted identically on every product (and you can use Prettier to help with that, or https://jsonformatter.curiousconcept.com), you can safely delete, cut, move the cursor and paste all your selections at the same time until they look like this:

{
 "applications": ["Fireplaces", "Exterior Walls", "Interior Walls"],
 "name": "1763 Granite",
 ...
},

That's why it's also important to export and keep the empty columns until this stage. If you try to multi-select everything, and some of the rows don't have application3, you'd have your cursor in different places on each row.

That's the format you'll need to import multiple categories at once using Feed Me into Craft.

Remove the zeroes

I discovered that when I exported from Google Sheets that any empty cells were represented as a 0 instead of a blank value (or ""). Using the same selection technique as before, or using Find and Replace, you can replace all instances of ": 0, with ": "",. Otherwise your imported data will fill empty fields in Craft with 0.

Create fields in Craft to hold your data

This probably goes without saying, but you'll need to create the fields in Craft first and add them to the section that's going to hold your data.

Import into Craft

Ok, so nothing out of the ordinary here. Install Feed Me and create a new Feed. It's worth noting that you can use a local URL by just putting the file in your document root / public folder and typing the filename; it doesn't have to be hosted online.

Set up the feed to import your entries into the right channel and entry type. On the next screen, change the primary element you're importing from to the array with all your data (probably rows):

Then you can map all the fields in your JSON file to the fields within Craft. For that categories field I mentioned, you can also make it automatically create all the categories used during the import by checking Create categories if they do not exist:

Run the import, and check the log files to make sure everything went smoothly. It was at this point that I realised I only had 215 out of the 254 entries I expected, and discovered that several rows were being skipped because they had duplicate titles, which is where using an incrementing number in a sequence column will come in handy.

And you're done!