{"id":397,"date":"2022-10-25T13:45:44","date_gmt":"2022-10-25T13:45:44","guid":{"rendered":"https:\/\/jonathan-wright.com\/?p=397"},"modified":"2022-10-25T13:45:47","modified_gmt":"2022-10-25T13:45:47","slug":"wordpress-to-google-sheets","status":"publish","type":"post","link":"https:\/\/jonathan-wright.com\/wordpress-to-google-sheets\/","title":{"rendered":"Automatically Send Post &#038; Page Data from WordPress to Google Sheets"},"content":{"rendered":"\n<p>If you use Google Sheets to manage your content workflow, then getting your post and page data automatically sent from WordPress to Google Sheets via a webhook may be just what you&#8217;re looking for to take that workflow to the next level.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">WordPress To Google Sheets Summary<\/h2>\n\n\n\n<p>We&#8217;re going to use two free solutions to get your WordPress data into Google Sheets.<\/p>\n\n\n\n<p>The first one is an add-on that makes Sheets capable of receiving a webhook and the second is the Post Webhook for WordPress plugin, built by yours truly, that will send the data from WordPress to Google Sheets. Both are totally free and the process of setting it all up is surprisingly simple.<\/p>\n\n\n\n\n\n<h2 class=\"wp-block-heading\">Step 1 &#8211; Install and setup the Webhook for Sheets Add-on<\/h2>\n\n\n\n<p>Google Sheets does not support webhooks by default, but fortunately, some kind soul has built an add-on that fixes this.<\/p>\n\n\n\n<p>For this step, I&#8217;m simply going to say watch the video below which is from Sourabh Choraria the developer of the Sheets add-on we are going to use.<\/p>\n\n\n\n<p>Just remember at the end of your setup process, make sure you copy the URL the script gives you as you are going to need it.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Webhooks for Sheets \u2014 Google Workspace Add-on \u2014 Product demo\" width=\"1200\" height=\"675\" src=\"https:\/\/www.youtube.com\/embed\/hj3y0QTykdQ?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Step 2 &#8211; Install my Post Webhook Plugin<\/h2>\n\n\n\n<ul><li>Go to your WordPress site and log in to your Admin area<\/li><li>Go to Plugins &gt; Add new and search for my plugin, which is called Post Webhook (or directly from the&nbsp;<a href=\"https:\/\/wordpress.org\/plugins\/post-webhook\/\" target=\"_blank\" rel=\"noreferrer noopener\">WordPress Plugin Directory<\/a>)<\/li><li>Install the plugin and activate it<\/li><li>Go to Settings &gt; Post Webhook and enter the URL that you got from step 1 and click the Save Webhook URL button<\/li><li>It is useful at this stage to publish a post or update a post, so that you have some data to play with (you can simply go to an existing post and add and then delete a space, just so you can select the Update button but not actually change your post)<\/li><\/ul>\n\n\n\n<p>To find out how the plugin works and what data it sends you can read my article &#8211;&nbsp;<a href=\"https:\/\/jonathan-wright.com\/post-webhook-plugin\/\" target=\"_blank\" rel=\"noreferrer noopener\">Post Webhook Plugin \u2013 How to Use<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 3 &#8211; Watch in awe as your data magically appears in your Sheet<\/h2>\n\n\n\n<p>If you now go back to your Google Sheet and, assuming you have actually published, updated or deleted a post since setting this all up, you should see that by magic (well actually code, but magic sounds more impressive) you have data in your Sheet.<\/p>\n\n\n\n<figure class=\"gb-block-image gb-block-image-37d6aa3f\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"89\" class=\"gb-image gb-image-37d6aa3f\" src=\"https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/Wordpress-to-Sheets-Table.webp\" alt=\"Wordpress to Sheets Table\" title=\"Wordpress to Sheets Table\" srcset=\"https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/Wordpress-to-Sheets-Table.webp 1024w, https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/Wordpress-to-Sheets-Table-300x26.webp 300w, https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/Wordpress-to-Sheets-Table-768x67.webp 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>That is basically it (but do take note of step 4), now every time you publish, update or delete a previously published post\/page on your WordPress site that data will be automatically sent to Sheets.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 4 &#8211; Dealing with Duplicates (optional step)<\/h2>\n\n\n\n<p>OK, I said that was basically it but you&#8217;ll probably want to do something about the default behavior of this process and that means dealing with duplicates.<\/p>\n\n\n\n<p>Every time you trigger an event (publish, update, delete) that data gets sent to Google Sheets.<\/p>\n\n\n\n<p>So if you publish a post and then later update that post, it will appear as two separate entries (two rows) in your Sheet. <\/p>\n\n\n\n<p>This makes total sense as that&#8217;s what webhooks are meant to do, but it&#8217;s probably not what you want in your content workflow system i.e. the same post duplicated multiple times. So let&#8217;s deal with that.<\/p>\n\n\n\n<p>To deal with duplicates, the most efficient, set-it-and-forget-it way, is to use some Google apps script and set it to run automatically.<\/p>\n\n\n\n<p><strong>1.<\/strong> Go to Extensions > Apps Script in your menu bar (whilst in the sheet you have just created)<\/p>\n\n\n\n<figure class=\"gb-block-image gb-block-image-477e4e52\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"513\" class=\"gb-image gb-image-477e4e52\" src=\"https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/1-Go-to-Apps-Script.webp\" alt=\"Go to Apps Script\" title=\"1 Go to Apps Script\" srcset=\"https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/1-Go-to-Apps-Script.webp 1024w, https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/1-Go-to-Apps-Script-300x150.webp 300w, https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/1-Go-to-Apps-Script-768x385.webp 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p><strong>2.<\/strong> In the Files section, click the + symbol and select Script<\/p>\n\n\n\n<figure class=\"gb-block-image gb-block-image-5ba9a0ce\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"513\" class=\"gb-image gb-image-5ba9a0ce\" src=\"https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/2-Add-Script.webp\" alt=\"Add Script\" title=\"2 Add Script\" srcset=\"https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/2-Add-Script.webp 1024w, https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/2-Add-Script-300x150.webp 300w, https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/2-Add-Script-768x385.webp 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p><strong>3.<\/strong> Name your new script (in my case Remove Duplicates) and paste the following code in. Make sure that there is no other code present on that screen as Google often pre-populates it with a starter function which we don&#8217;t want.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: jscript; title: ; notranslate\" title=\"\">\nfunction myPostWebhookFunction() {\n  var sh = SpreadsheetApp.getActiveSheet();\n\n  \/\/ get values of column F\n  var col = sh.getDataRange().getValues().map(x =&gt; x&#x5B;5]);\n\n  \/\/ get indexes of duplicated values in the column\n  var duplicates = col.map((x,i) =&gt; \n    col.slice(i+1).includes(x) ? i+1 : '').filter(String); \n\n  \/\/ remove rows by the indexes\n  duplicates.reverse().forEach(x =&gt; sh.deleteRow(x));\n}\n<\/pre><\/div>\n\n\n<p>Your screen should look like this when you&#8217;re done:<\/p>\n\n\n\n<figure class=\"gb-block-image gb-block-image-79c7374c\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"478\" class=\"gb-image gb-image-79c7374c\" src=\"https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/4-Paste-Script-in.webp\" alt=\"Paste Script in\" title=\"4 Paste Script in\" srcset=\"https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/4-Paste-Script-in.webp 1024w, https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/4-Paste-Script-in-300x140.webp 300w, https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/4-Paste-Script-in-768x359.webp 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>This code basically says, using Post GUID as the unique identifier (which is in Column F by default), delete any previous rows that contain the same Post GUID. Leaving you with the most recent event for that post\/page.<\/p>\n\n\n\n<p>If for whatever reason, Post GUID is not in column F in your setup then adjust the number in line 5 of the code accordingly <code>map(x => x[5])<\/code>, so if your Post GUID column is G you would change the code to <code>map(x => x[6])<\/code> because G is the 6th column along (counting from 0).<\/p>\n\n\n\n<p><strong>4.<\/strong> Now we have that script in place, we don&#8217;t want to have to run it manually every time. So go to Triggers (the clock symbol in the left-hand nav) and set your trigger up as per the screenshot.<\/p>\n\n\n\n<figure class=\"gb-block-image gb-block-image-b9a4ebd3\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"509\" class=\"gb-image gb-image-b9a4ebd3\" src=\"https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/5-Configure-Trigger.webp\" alt=\"Configure Trigger\" title=\"5 Configure Trigger\" srcset=\"https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/5-Configure-Trigger.webp 1024w, https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/5-Configure-Trigger-300x149.webp 300w, https:\/\/jonathan-wright.com\/wp-content\/uploads\/2022\/10\/5-Configure-Trigger-768x382.webp 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>This trigger sets the script to run automatically every hour. You can adjust the time period to daily or weekly if that suits you better, hourly is probably overkill.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p>So you are all set up with an automated process and it will be great to see how you use that data in your content workflow (so please leave comments below to let me know how you&#8217;re using it).<\/p>\n\n\n\n<p>I&#8217;ll be posting some articles soon on how I&#8217;m using it.<\/p>\n\n\n\n<p>If you&#8217;re already an Integromat\/Make, Zapier, Pabbly etc. user then you can use any of those instead of the Webhook for Sheets Add-on to do this as well.<\/p>\n\n\n\n<p>If you use Airtable instead of Google Sheets for your content workflow, then you&#8217;ll want to read <a href=\"https:\/\/jonathan-wright.com\/wordpress-to-airtable\/\" target=\"_blank\" data-type=\"post\" data-id=\"315\" rel=\"noreferrer noopener\">How to Automatically Send Post &amp; Page Data from WordPress to Airtable<\/a> where I show you how to set up the same system but using Airtable.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you use Google Sheets to manage your content workflow, then getting your post and page data automatically sent from WordPress to Google Sheets via a webhook may be just what you&#8217;re looking for to take that workflow to the next level. WordPress To Google Sheets Summary We&#8217;re going to use two free solutions to &#8230; <a title=\"Automatically Send Post &#038; Page Data from WordPress to Google Sheets\" class=\"read-more\" href=\"https:\/\/jonathan-wright.com\/wordpress-to-google-sheets\/\" aria-label=\"More on Automatically Send Post &#038; Page Data from WordPress to Google Sheets\">Read Full Post<\/a><\/p>\n","protected":false},"author":1,"featured_media":414,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"limit_modified_date":"","last_modified_date":"","inline_featured_image":false,"footnotes":""},"categories":[8,7],"tags":[],"_links":{"self":[{"href":"https:\/\/jonathan-wright.com\/wp-json\/wp\/v2\/posts\/397"}],"collection":[{"href":"https:\/\/jonathan-wright.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jonathan-wright.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jonathan-wright.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jonathan-wright.com\/wp-json\/wp\/v2\/comments?post=397"}],"version-history":[{"count":23,"href":"https:\/\/jonathan-wright.com\/wp-json\/wp\/v2\/posts\/397\/revisions"}],"predecessor-version":[{"id":430,"href":"https:\/\/jonathan-wright.com\/wp-json\/wp\/v2\/posts\/397\/revisions\/430"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/jonathan-wright.com\/wp-json\/wp\/v2\/media\/414"}],"wp:attachment":[{"href":"https:\/\/jonathan-wright.com\/wp-json\/wp\/v2\/media?parent=397"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jonathan-wright.com\/wp-json\/wp\/v2\/categories?post=397"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jonathan-wright.com\/wp-json\/wp\/v2\/tags?post=397"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}