You are reading this article because you have a list of posts in wordpress and you want to grab them and pull them into Google sheets in order to create a control sheet of some kind.
WHY?
Sometimes, at JUCRA, we need to go through pages on clients site on a page by page basis and check certain items and use a checklist so we know whats completed. So we create a sheet in google sheet like below, and then import the raw data from WordPress.
Getting the data into the sheet though requires some custom coding in a custom script as below.
1. Create a file on your server called list-of-pages.php
2. Configure the path to your wp-load.php
3. Enter the $post_type (which will be page, post or custom post type name)
4. Edit the file to dictate how the data is printed to the page
5. Copy the data on the page and paste into your google sheet
Then in Google sheets just use the Data > Split Text to Columns feature and it will all be nicely formatted in the column in google sheets.
Why do we use the pipe format? If you use the standard separators like comma, tab or space, and your post title contains any of those, then the splitting of the text to columns might get confused, however if you have posts titles that contain pipes, then be creative!
The code below can be used to grab a list of posts or pages from your WordPress.
<?php
####################################
/*
Piped separated export to google sheets for WordPress posts/pages
created by craig@jucra.com on 4th Feb 2019
USAGE: https://www.yourwebsite.com/list-of-posts.php
HOT TIP GETTING THE PIPED DATA INTO COLUMNS IN GOOGLE SHEETS
1. Output the data in piped separated format
2. Paste the data in google sheets
3. Use Data > Split Text to Columns
4. Choose custom separator and enter |
Data will be split into the columns.
SIMPLES!
*/
####################################
//hook into wordpress
require('wp-load.php');
//do the query to get the posts
$post_type = "special-offers";
query_posts('showposts=999&post_type='.$post_type.'&orderby=date&order=DESC');
while ( have_posts() ) : the_post();
$post_id = get_the_ID();
$permalink = get_permalink();
$post_title = get_the_title();
//edit link (either within wordpress or divi builder)
$edit_link = "https://www.yourwebsite.com/wp-admin/post.php?post=$post_id&action=edit";
$edit_link = "$permalink?et_fb=1&PageSpeed=off";
//display data in piped separted format
echo $post_id . "|" . $post_title . "|". $permalink . "|". $edit_link. "<br>";
endwhile;
?>
