Ever since Google Sheets killed the native JSON feeds, a number of projects of mine have broken and been left adrift. One of those projects was my activities list for conferences and various things like that. On Tuesday, we were asked to revisit our work profiles. One of the requested items was “Relevant publications, presentations, or online portfolios.”
I’d been debating either moving the sheets data to WordPress or going to the hassle of remaking the JSON endpoint in Google Sheets.
Today, I decided to make the move to WordPress. I figure it gives me more flexibility and control in the long run.
In today’s post, I’ll just document how I wrote the CSV importer. It’s a handy pattern I’ve used a number of times before. Naturally, I have no memory if I’ve written it up before and am too lazy to even look. Plus, I’ve got a few tweaks on this version that I’m relatively sure I haven’t written up.
I’ve now got a slightly out of date list of activities now. Now I have to add stuff I’ve forgotten and improve the design. Funny how doing work keeps generating more work?1
Get the Data
Nothing too fancy here. I exported the Google Sheet as a CSV and put it in a directory named ‘data’ in the theme I made. You could put it anywhere. This was just an easy option for me. fopen and fgetcsv are native PHP functions.
$file_path = get_stylesheet_directory() . '/data/activities.csv';
if(isset($file_path)){
$filename = $file_path;
$file = fopen($filename, "r");
fgetcsv($file);
while (($getData = fgetcsv($file, 1000, ",")) !== FALSE) {
//DO STUFF HERE LATER PER ROW
}
fclose($file);
}
Row by row
I’ve now thrown that CSV getter into a function to make it easier to use later.
The while loop goes row by row through the CSV. You can choose columns with the square brackets. The column count starts at 0.
You can now stick this function someplace in a theme and it’ll var_dump the titles so you know it’s all working.
function tww_parse_csv(){
$file_path = get_stylesheet_directory() . '/data/activities.csv';
if(isset($file_path)){
$filename = $file_path;
$file = fopen($filename, "r");
fgetcsv($file);
while (($getData = fgetcsv($file, 1000, ",")) !== FALSE) {
$title = $getData[0];//GET FIRST COLUMN
$link = $getData[4];//GET FIFTH COLUMN
if($title != ''){
//DO STUFF IF A TITLE EXISTS
var_dump($title);
}
}
fclose($file);
}
}
Make Content
Now we’ve got the right pieces in place to get the CSV and then to move through it row by row.
The next step is to do something with the data. We’ll user WordPress’s wp_insert_post() function.
I end up throwing 7 variables in there for the 7 columns I have in my original spreadsheet.
function tww_csv_make_activities($title, $description, $year, $month, $type, $conference, $link){
$args = array(
'post_title' => wp_strip_all_tags($title),
'post_type' => 'activity',//my custom post type
'post_status' => 'publish',//go ahead and publish it
'post_content' => $description,
'tax_input' => array(
'years' => $year,//these end up wanting arrays of IDs so we'll make do that translation elsewhere
'months' => $month,
'types' => $type,
'conferences' => $conference,
),
);
$post_id = wp_insert_post($args);//post gets made with the arguments we set
add_post_meta($post_id, 'activity_link', $link, true);//just adding a custom field in case I want to do something with this
}
Add Custom Taxonomy Terms and/or Get ID
I added custom taxonomies for years, months, types, and conferences.
To be able to add those terms as the CSV is processed, I need to create the term if it didn’t exist or get the ID of the term if it already existed. To do that we’ll use wp_insert_term and term_exists.
This is the portion that probably took the longest to get straight and I might have gone overboard on it but it works.
function tww_term_checker($term, $tax){
if($term){//we've got a term to look for right?
if(!term_exists($term, $tax)){//doesn't exist yet?
$new = wp_insert_term($term,$tax);
return $new;
} else {//exists
$term = term_exists($term, $tax);
return $term["term_id"];//get ID
}
} else {
return '';//you get nothing because you had nothing
}
}
All together now
Now you can see the three functions together.
tww_parse_csv gets the csv and loops through all the rows. For all rows with a title, it calls tww_csv_make_activities and sets the variables.
I just threw the function in the header of the theme and would refresh the page to run it. You could get fancier but for one-time things like this I don’t see the need.
function tww_parse_csv(){
$file_path = get_stylesheet_directory() . '/data/activities.csv';
if(isset($file_path)){
$filename = $file_path;
$file = fopen($filename, "r");
fgetcsv($file);
while (($getData = fgetcsv($file, 1000, ",")) !== FALSE) {//GET THE VARIABLES SET
$title = $getData[0];
$year = array(tww_term_checker($getData[1],'years'));//makes sure the term exists and returns the term ID
$month = array(tww_term_checker($getData[2], 'months'));//makes sure the term exists and returns the term ID
$type = array(tww_term_checker($getData[3], 'types'));//makes sure the term exists and returns the term ID
$link = $getData[4];
$conference = array(tww_term_checker($getData[5],'conferences'));//makes sure the term exists and returns the term ID
$description = $getData[6];
if($title != ''){
tww_csv_make_activities($title, $description, $year, $month, $type, $conference, $link);//DO THE WORK
}
}
fclose($file);
}
}
function tww_csv_make_activities($title, $description, $year, $month, $type, $conference, $link){
$args = array(
'post_title' => wp_strip_all_tags($title),
'post_type' => 'activity',
'post_status' => 'publish',
'post_content' => $description,
'tax_input' => array(
'years' => $year,
'months' => $month,
'types' => $type,
'conferences' => $conference,
),
);
$post_id = wp_insert_post($args);
add_post_meta($post_id, 'activity_link', $link, true);
add_post_meta($post_id, 'activity_conference', $conference, true);
}
function tww_term_checker($term, $tax){
if($term){
if(!term_exists($term, $tax)){
$new = wp_insert_term($term,$tax);
return $new;
} else {
$term = term_exists($term, $tax);
return $term["term_id"];
}
} else {
return '';
}
}
1 Like this blog post.