Skip to content

Lesson 2: Publishing Content from Google Sheets to a Blog with App Script

Welcome back! So we’ve taken a RSS feed or another input and we’ve filled our sheet with titles and excerpts from it.

The end goal is to send them to OpenAI API with a prompt but that is the most complex step of this process, so we will have to save that for lesson 3 and 4.

Right now we are going to add the function that will publish the data after it is sent back. We started with the “import function” in the first lesson so now we are going to create the “export function” or the “posttoblog function”.

I’m personally using a WordPress application to authorize the posts. Because free WordPress sites are unable to post via email or use the API I have decided to also include Blogger as an accessible example.

Blogger and WordPress both allows you to post via email which makes this very easy to setup. I have included the WordPress API functions also if that is what you prefer. You could also easily customize this function for your own API or CMS if that is something you needed.

What is a API?

API (Application Programming Interface): This is a interface that allows programs, websites, and apps to talk to each other and exchange information. It’s the system of communication that we use to integrate different applications.

What is a CMS?

CMS (Content Management System): This is a software or an application that helps you create, manage, and modify content on a website without needing to know a coding language. In this case our CMS is Blogger or WordPress.

With that out of the way, this lesson should be very quick and easy! Lets get into it.

Let’s Create the SendtoBlog() Function Option 1: Post By Email

Sure, to simplify this process we can create a sidebar for the user to input their post-by-email address for either Blogger or WordPress.

Setting up your Blogger email: First things first, you’ll need to set up a unique email address to use with Blogger. To do this, log into your Blogger account and navigate to the settings menu. Once there, look for the “Email” section. Here you can create a secret email address that will be tied directly to your blog. Any content sent to this email address will automatically be posted to your blog.

Setting up your WordPress email: If you have Self Hosted or Upgraded WordPress you can also post by email. In your WordPress settings, navigate to the “Writing” section. Enable “Post by email”, and WordPress will provide a unique email address for you. Any content emailed to this address will be automatically posted to your blog.

Creating a Custom Sidebar for User Inputs:

  • In Google Sheets, click on Extensions > Apps Script.
  • In the Apps Script editor, click on File > New > Html File.
  • Name this new file “Sidebar.html”.
  • Copy and paste the following HTML into Sidebar.html:
  • Save the Sidebar.html file.
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <div>
      <label for="email">Email:</label><br>
      <input type="text" id="email"><br>
      <button onclick="saveEmail()">Save Email</button>
      <button onclick="clearField()">Clear Field</button>
    </div>
    <script>
      function saveEmail() {
        var email = document.getElementById('email').value;
        google.script.run.withSuccessHandler(function(){
          document.getElementById('email').value = '';
        }).storeEmail(email);
      }

      function clearField() {
        document.getElementById('email').value = '';
      }
    </script>
  </body>
</html>

Create the sendToBlog function:

  • Still in the Apps Script editor, click on File > New > Script file.
  • Name this new file, for instance, “BlogPost.gs”.
  • Copy and paste the following code into BlogPost.gs:
  • Save the BlogPost.gs file.
function storeEmail(email) {
  // Store the email
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty('email', email);
}

function sendToBlog() {
  // Get stored email
  var scriptProperties = PropertiesService.getScriptProperties();
  var email = scriptProperties.getProperty('email');

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();

  for (var i = 0; i < data.length; i++) {
    var column = data[i];
    var title = column[1]; 
    var content = column[2]; 

    if (title && content) {
      MailApp.sendEmail({
        to: email,
        subject: title,
        body: content
      });
    }
  }
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
    .addItem('Show sidebar', 'showSidebar')
    .addItem('Send to Blog', 'sendToBlog')
    .addToUi();
}

function showSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('Sidebar.html')
      .setTitle('Enter post-by-email address');
  SpreadsheetApp.getUi().showSidebar(html);
}

Run the Script

  • Go back to your Google Sheet. Refresh the page.
  • You will see a new menu item labeled “Custom Menu” on the menu bar. Click on “Custom Menu” then “Show sidebar”.
  • Enter your Blogger or WordPress post-by-email address into the fields in the sidebar. Click “Save Email”.
  • Click on “Custom Menu” then “Send to Blog”.

SendtoWordPressAPI() Function Method 3: WordPress (via API)

This method involves using the WordPress REST API to create a new blog post directly from Google Sheets.

Here’s a step-by-step guide:

  1. Set up WordPress Application Passwords:
    • Log into your WordPress account.
    • Navigate to your profile section.
    • Look for “Application Passwords”.
    • Enter the name for the new password and click “Add New Application Password”.
    • Take note of the generated username:password pair as you’ll need it later.
  2. Creating a Custom Sidebar for User Inputs:
    • In Google Sheets, click on Extensions > Apps Script.
    • In the Apps Script editor, click on File > New > Html File.
    • Name this new file “Sidebar.html”.
    • Copy and paste the following HTML into Sidebar.html:
    • Save the Sidebar.html file.
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <div>
      <label for="username">Username:</label><br>
      <input type="text" id="username"><br>
      <label for="password">Password:</label><br>
      <input type="password" id="password"><br>
      <button onclick="saveCredentials()">Save Credentials</button>
      <button onclick="clearFields()">Clear Fields</button>
    </div>
    <script>
      function saveCredentials() {
        var username = document.getElementById('username').value;
        var password = document.getElementById('password').value;
        google.script.run.withSuccessHandler(function(){
          document.getElementById('username').value = '';
          document.getElementById('password').value = '';
        }).storeCredentials(username, password);
      }

      function clearFields() {
        document.getElementById('username').value = '';
        document.getElementById('password').value = '';
      }
    </script>
  </body>
</html>

Modify the Google Apps Script:

  1. Still in the Apps Script editor, click on File > New > Script file.
  2. Name this new file, for instance, “WordPressIntegration.gs”.
  3. Copy and paste the JavaScript code for storing the credentials and sending the API request into WordPressIntegration.gs.
  4. Remember to replace ‘https://yourwordpresssite.com/wp-json/wp/v2/posts‘ with the URL of your WordPress site in the sendtoWordPressAPI functio
function storeCredentials(username, password) {
  // Store the credentials
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty('username', username);
  scriptProperties.setProperty('password', password);
}

function sendtoWordPressAPI() {
  // Get stored credentials
  var scriptProperties = PropertiesService.getScriptProperties();
  var username = scriptProperties.getProperty('username');
  var password = scriptProperties.getProperty('password');

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();

  for (var i = 0; i < data.length; i++) {
    var column = data[i];
    var title = column[1]; 
    var content = column[2]; 

    if (title && content) {
      var url = 'https://yourwordpresssite.com/wp-json/wp/v2/posts';
      var options = {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
          'Authorization': 'Basic ' + Utilities.base64Encode(username + ':' + password),
        },
        payload: JSON.stringify({
          'title': title,
          'content': content,
          'status': 'draft',
        })
      };
      UrlFetchApp.fetch(url, options);
    }
  }
}
  1. Run the Script:
  • Go back to your Google Sheet.
  • If it’s the first time running the script, you’ll see a new menu item labeled “Custom Menu” on the menu bar.
  • Click on “Custom Menu” then “Show sidebar”.
  • Enter your WordPress credentials into the fields in the sidebar.
  • Click “Submit”.
  • If everything is set up correctly, the entered blog posts should be pushed to your WordPress site as drafts.

Did it work? Nice! There are only two more steps to actually completing the functions! Next we will create a proxy to communicate with the OpenAI API before we finally create the function to push our sheet data to it.

Almost there! See you in the next one.

Remember to replace 'https://yourwordpresssite.com/wp-json/wp/v2/posts' with the URL of your WordPress site in the sendToWordPressAPI function. Also, please note that the script assumes that the title of your post is in the second column and the content is in the third column of your sheet. If your sheet is arranged differently, you’ll need to adjust the column indexes accordingly.