WordPress/Google Spreadsheet Chimera Community


flickr photo shared by NASA on The Commons with no copyright restriction (Flickr Commons)

Many professors don’t want students having to go into the backend of WordPress. Imagine also that you’re dealing with many hundreds of students and don’t want the hassle of people asking you about password resets or any other attendant drama. You’re willing to give up some things in return for as much simplicity as possible.

You’ve got a few options for front-end editors. Gravity Forms is one I use all the time but it doesn’t give you the full WP editor which is pretty handy if you’re asking students to write posts of any complexity. AccessPress Anonymous Post Pro Forms is another option that I used on this project. It’s worth checking out and I think there’s a free version as well. Alan and Brian’s SPLOT tools are also worth checking out if your actions fit into those patterns consistently.

That helps address one problem but it opens up a few other issues. We’ve got lots of students and lots of individual sections. How do we group them into courses or teams? How do we aggregate individual student’s work to their own page if they aren’t authors (in the WordPress sense)? Can we do that without drama?

After a few false starts, I think this works pretty decently and it’s a possible twist on some of the ideas in this conversation about Feed WordPress alternatives.

For students to be able to create their own individual pages I created a custom post type called “Personal.” It lets the student pick a banner image, give the page a name (their name, a pseudonym, whatever) . . . and a custom field. We’re suggesting an email address or something like that. That custom field is added to all the front end submission areas. The student page then essentially queries all the posts and returns any that have the matching custom field.

The relevant chunk of the code is below. It’s based on a 6×2 grid of thumbnails across the top and then all 12 assignments with full content below that.

<?php 
				$stu_id = get_post_meta( get_the_ID(), 'stu_id', true );
				// Check if the custom field has a value.				
				$the_query = new WP_Query( array( 
					'meta_key' => 'stu_id',                    // Custom field key.
    				'meta_value' => $stu_id,                // Custom field value.
				 ) );
				// The Loop				
				if ( $the_query->have_posts() ) :
				   echo '<div class="personal-images">';	
				while ( $the_query->have_posts() ) : $the_query->the_post();
				  $post_id = $post->ID;
				  //image previews across top 
				   echo '<a href="' . get_permalink() . '">';
        		   echo get_the_post_thumbnail($post_id,'thumbnail', array( 'class' => 'alignleft personal' ));
        		   echo '</a>';
				endwhile;
				   echo '</div><div class="personal-full">';
				while ( $the_query->have_posts() ) : $the_query->the_post();
				  $post_id = $post->ID;
				  //image previews across top 
				   echo '<div class="personal-full-item"><a href="' . get_permalink() . '">';
				   echo get_the_post_thumbnail($post_id,'full', array( 'class' => 'aligncenter personal-big' ));
				   echo '</a><h3><a href="' . get_permalink() . '">';
        		   echo get_the_title();
        		   echo '</a></h3>';
        		   echo '<div class="meta">' . get_the_date() . '</div>';
				   $content = apply_filters( 'the_content', get_the_content() );
				   echo $content . '</div>';
				endwhile;
				   echo '</div>';   
				endif;
				// Reset Post Data
				wp_reset_postdata();
				?>

Pretty decent way to get many of the benefits of a student focused page but without any of the overhead. Not perfect certainly but a different take on things that might be valuable for someone.

Team Pages

Now we get a bit stranger.

Imagine we have many people teaching sessions but we really don’t want to manage them as users in WordPress but we want them to be able to create little class focused aggregate pages. This is harder than the student example because they’re going to be dealing with a bunch of students and probably some changes/typos etc. so they need to be able to edit things. This brings up an interesting challenge.

I ended up with a “solution” that may or may not be a disaster but it’s at least fairly unique. Maybe even ¬°innovative! . . .

Imagine1 now a Google Spreadsheet that just has a list of student emails (or whatever they’ve used to uniquely identify themselves).
Screen Shot 2016-08-13 at 10.50.17 PM

So now we have another custom post type called “Teams.” It’s doing essentially the same thing only it’s pulling all the custom field values from your spreadsheet.

I made one version that could do it by pulling the SS ID from the URL and so you’d only have one actual “team page” that would be dynamically populated depending on the SS ID passed in the URL (fridge poetry style). With that we could have done a bunch of other things like setting the banner, instructor contact info etc. ALL THROUGH A GOOGLE SPREADSHEET.2

In the end, we pulled it back and had the front end form submission with the Google SS URL as a custom field. The key here is just reading the values from the SS JSON and then loading the values into the query. You can see most of that below.

<?php
//get google ss json
				$gss_url = get_post_meta( get_the_ID(), 'gss_url', true );
				$json_gss = 'https://spreadsheets.google.com/feeds/list'.substr($gss_url, 38) . '1/public/basic?alt=json';
				// Check if the custom field has a value.								
    			
				$json = @file_get_contents($json_gss);

				if ( $json === false )
					{
					   echo '<div class="warning">Looks like your Google spreadsheet might not be public.<br> Try going back to the spreadsheet and choosing <em>File>Publish to the web.</em></div>';
					} else {
    								
				$data = json_decode($json, TRUE);
				$teamMembers = [];
				//print_r($obj);
				foreach ($data['feed']['entry'] as $item) {
				  array_push($teamMembers, $item['title']['$t']);  
				}

				$stu_id = get_post_meta( get_the_ID(), 'stu_id', true );
				// Check if the custom field has a value.				

				$the_query = new WP_Query( array( 
					'meta_query' => array(
					        array(
					            'key' => 'stu_id',
					            'value' => $teamMembers,
					            'compare' => 'IN'
					        ),
					    )
					)
				  );

Presto. A team page that you control aspects of via an external Google Spreadsheet.


1 Your imagination is so powerful it created a screenshot. What a waste.

2 Note- this may really be a really terrible idea but it’s still fun.

Comments on this post

No comments.

Leave a Reply

Trackbacks and Pingbacks on this post

No trackbacks.

TrackBack URL