Building JSON for WPComplete in Excel with Concatenate

January 28, 2018
Category: TIL
Tags: Excel, Wordpress, and JSON

I’m using the ImportWP Pro plugin to quickly create pages in the Praxis curriculum portal. One of the custom fields I need to set is for the WPComplete course system we use. It takes JSON for which course the page belongs to and which page to redirect the to once the current page is marked complete. Here is an example:

{"course":"New Module 4 - Thinking","redirect":{"title":"You Have No Idea How Wrong You Are","url":"https://portal.discoverpraxis.com/bootcamp/new-module-4/no-idea-how-wrong"}}

I needed to build this JSON in Excel with the rest of the page info. The pages I’m setting the redirect title and URL for are being created in the spreadsheet, too. So I wanted to build this JSON cell from the contents of other cells. The commas and quotes were a problem for my typical way of building text from other cells: =B5&" "&C5

Here is the new solution I found: Break the JSON down into constituent parts, put each in its own cell, then build the JSON with the concatenate() function.

Here are the 5 cells I needed:

  1. {"course":"New Module 4 - Thinking","redirect":{"title":"
  2. The title from a spreadsheet column
  3. ","url":"https://portal.discoverpraxis.com/bootcamp/new-module-4/
  4. The page slug from a spreadsheet column
  5. "}}

I put 1 in A23, 3 in A24, and 5 in A25. Then pulled 2 and 4 from various cells in columns A and B. Then my JSON cells used this formula: =CONCATENATE(A23,A4,A24,B4,A25)

Concatenated together, it looks like this:

{"course":"New Module 4 - Thinking","redirect":{"title":"PAGETITLE","url":"https://portal.discoverpraxis.com/bootcamp/new-module-4/PAGESLUG"}}

Excel Concatenate