Finally! We will complete our project by sending our sheets rows to OpenAI API as a completion request. When that completion is returned our posttoblog() function will post the generated content for us!
We’ll be working on the main function generateContent() today. It’s designed to pull each row from the sheet and send it with a chosen prompt to our proxy. It then waits until it receives the response and when it does sends it to the blog as a draft. It then moves on to the next row and repeats this until it runs out of content to post.
Step 1: Preparing your UI
Create a Sidebar.html file in your App Script project and save it.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<div>
<label for="wpkey">Wordpress API Key:</label><br>
<input type="text" id="wpkey" name="wpkey"><br>
<label for="wpurl">WordPress URL:</label><br>
<input type="text" id="wpurl" name="wpurl"><br>
<label for="glitchurl">Glitch URL:</label><br>
<input type="text" id="glitchurl" name="glitchurl"><br>
<label for="prompt">Rewrite Prompt:</label><br>
<input type="text" id="prompt" name="prompt"><br>
<label for="maxtokens">Max Tokens:</label><br>
<input type="number" id="maxtokens" name="maxtokens"><br>
</div>
<button onclick="runStoreSettings(); runParseFeeds()">Run Parse Feeds</button>
<button onclick="runStoreSettings(); runGenerateContent()">Run Generate Content</button>
<script>
function runStoreSettings() {
var glitchurl = document.getElementById('glitchurl').value;
var prompt = document.getElementById('prompt').value;
var maxtokens = document.getElementById('maxtokens').value;
var wpurl = document.getElementById('wpurl').value;
var wpkey = document.getElementById('wpkey').value;
// Check if values are not empty
if (glitchurl && prompt && maxtokens && wpurl && wpkey) {
google.script.run.storeSettings(glitchurl, prompt, maxtokens, wpurl, wpkey);
} else {
console.error('One or more form values are empty.');
}
}
function runParseFeeds() {
// No changes needed here for parseFeeds
google.script.run.parseFeeds();
}
function runGenerateContent() {
// You don't need to retrieve the form values here, as they are stored by runStoreSettings()
google.script.run.generateContent();
}
</script>
</body>
</html>
Step 2: Opening the Sidebar
To show the sidebar, add a custom menu in Google Sheets. When you click on a menu item, it will call the showSidebar() function that shows the sidebar.
Here’s how to create a custom menu:
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Show sidebar', 'showSidebar')
.addToUi();
}
function showSidebar() {
var html = HtmlService.createHtmlOutputFromFile('Sidebar.html')
.setTitle('Your Keys Etc')
.setWidth(350);
SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
.showSidebar(html);
}Step 2: Creating the generateContent() function
The generateContent() function will be reading each row in the Google Sheet, sending the content as a request to OpenAI via our Glitch proxy, and then creating a new WordPress post with the returned content.
function generateContent() {
// Retrieve the form variables from script properties
var scriptProperties = PropertiesService.getScriptProperties();
var storedGlitchUrl = scriptProperties.getProperty('glitchurl');
var storedPrompt = scriptProperties.getProperty('prompt');
var storedMaxTokens = scriptProperties.getProperty('maxtokens');
var storedWpUrl = scriptProperties.getProperty('wpurl');
var storedWpKey = scriptProperties.getProperty('wpkey');
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getRange(2, 2, sheet.getLastRow(), 2).getValues();
for (var i = 0; i < data.length; i++) {
var row = data[i];
var title = row[0];
var excerpt = row[1];
var package = storedPrompt + "Title: " + title + "nBody: " + excerpt;
var url = storedGlitchUrl;
var options = {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
payload: JSON.stringify({
'prompt': package,
'max_tokens': storedMaxTokens,
})
};
try {
var response = UrlFetchApp.fetch(url, options);
Logger.log("Response code: " + response.getResponseCode());
Logger.log("Response headers: " + JSON.stringify(response.getHeaders()));
Logger.log(response.getContentText()); // Log the raw response
var content = JSON.parse(response.getContentText());
if (content && content.choices && content.choices.length > 0) {
var newText = content.choices[0].text;
if (newText) {
createWordPressPost(storedWpUrl, storedWpKey, title, newText);
}
} else {
// Log error or handle the case where the API response is not as expected
Logger.log('Unexpected API response', content);
}
} catch (error) {
Logger.log('Error fetching or parsing response: ' + error.message);
continue; // Skip to next iteration if there's an error
}
}
}Then you can run this function and check the logs. If you see “Response code: 200”, that means your request to Glitch – OpenAI API was successfully returned! There is just one more step now, we have all of the functions.
Step 3: Tying Everything Together – From RSS to Post
In the previous lessons, we’ve built various pieces of our application, including the parseRSS(), generateContent(), and createWordPressPost() functions, and set up a Glitch proxy server. Now, it’s time to put all of these components together and see our application in action.
The only change that we really need to make is to the createWordPressPost() .. it’s currently just sending the sheet data, so we need to adjust it to send the data that’s returned from the proxy.
Here is how we would alter the function.
function createWordPressPost(storedWpUrl, storedWpKey, title, newText) {
{
var url = storedWpUrl;
var options = {
method: 'POST',
headers: {
'Content-Type': 'application/json',
'Authorization': 'Basic ' + Utilities.base64Encode(storedWpKey),
},
payload: JSON.stringify({
'title': title,
'content': newText,
'status': 'draft',
})
};
try {
var response = UrlFetchApp.fetch(url, options);
var post = JSON.parse(response.getContentText());
Logger.log('Created new post with ID: ' + post.id);
} catch (error) {
Logger.log('Error creating WordPress post: ' + error.message);
}
}
}Here’s an overview of how the components of our application work together:
- The
parseRSS()function fetches data from a given RSS feed and populates our Google Sheets with title and excerpt data from the feed. - The
generateContent()function reads the title and excerpt data row by row from our Google Sheets, constructs a creative prompt for the OpenAI API, sends a request to the Glitch proxy server, and receives a unique content response. This function also handles possible errors during these operations. - The
createWordPressPost()function takes the newly generated contentalong with the original title(yeah it still does this, I will fix it but it’s a pain) and pushes it to our WordPress blog as a new draft post.
At this point we should have all of the code in one sheet, in one app script.
OpenSidebar()
ParseRSS()
GenerateContent()
PosttoWordpress()I have an example sheet that you can copy and edit with your own variables if you need help with the functions.
Step 4: Testing
You can run the parseRSS() function to populate the sheet then run the generateContent() function to test it out. The function will read each row from your Google Sheets, send the content to OpenAI via your Glitch app, and create a new WordPress post with the returned content.
Make sure to check your WordPress site to see if the new posts are appearing as drafts. You can also view the Logs in Google Apps Script Editor (under View > Logs) to troubleshoot any potential errors.
Congratulations! You have successfully integrated Google Sheets, OpenAI, and WordPress using a Glitch app as a proxy. You can now automatically generate fresh trendy content and publish it in just a few minutes.
But Wait! There’s More! There is still the issue of Security and Scaling. I’m going to talk about how to improve the inputs, to sort and filter the data, and how to have QA on your outputs. We don’t just want automated, we want quality too!
See You next time!