Google Apps Script

Update a WordPress post content directly from Google Doc with App Script

Posted on / No Comments

Developing this was a lot of fun.

Using the JDBC service, it is possible to update a WordPress post content from a Google document text body.

I have posted below all the code and the instructions; you can find the same in the GitHub repository also.

  1. Open a new Google document
  2. Click on ‘Tools > Script editor’
  3. Copy the code in this repository in new scripts files inside your new App Script project
  4. In the postUpdater.gs code, substitute the text in square brackets at lines 13, 14, 15 and 16 with your WordPress database credentials
  5. In the same file at line 22, substitute [POST ID] with the ID of the WordPress post you want to update
  6. Save all files in the App Script project and close it
  7. Refresh the document page, update its content and click on the menu to ‘WordPress > Update post content’
  8. Your WordPress post content will be automatically updated with the content of your Google document!

Note: The script will automatically append and remove HTML tags to your document, in order to transfer formatting styles, lists and links.

links.gs

function links() {
 
var body = DocumentApp.getActiveDocument().getBody();
var text = body.editAsText();
var idc = text.getTextAttributeIndices(); 
var bold = [];
var bold2 = [];
var bold3 = [];
var bold4 = [];
var bold5 = [];
var bold6 = [];

// Retrieve the exact indices where links are, and save it in arrays together with the links URLs 
  for (var i = 0; i < idc.length; i++) {
    if (text.getLinkUrl(idc[i]) != null) {
       bold.push(idc[i]);
       bold2.push(idc[i+1]);
       bold3.push(text.getLinkUrl(idc[i]));
       bold4.push(text.getLinkUrl(idc[i]).length);
       var sum = bold4.reduce(function(a, b) { return a + b; }, 0);
       bold5.push(sum);
    } 
  }

// Insert HTML for links
  text.insertText(bold[0], "<a href=''" + bold3[0] + "''>");
  text.insertText(bold2[0]+13+bold3[0].length, "</a>");
  
  for (var i = 1; i < bold.length; i++) {
    if (bold2[i] != undefined) {
    text.insertText(bold[i]+(17*i)+(bold5[i-1]), "<a href=''" + bold3[i] + "''>");
    text.insertText(bold2[i]+17+bold3[0].length+13+bold3[i].length, "</a>");
    }
    else {
    text.insertText(bold[i]+(17*i)+(bold5[i-1]), "<a href=''" + bold3[i] + "''>");
    var eh = body.getText();
    text.insertText(eh.length, "</a>");
    }
  }
}

list.gs

function list() {
  
var body = DocumentApp.getActiveDocument().getBody();
var text = body.editAsText();
var list = body.findElement(DocumentApp.ElementType.LIST_ITEM).getElement().editAsText();

var bold = [];

// Append HTML list tags to the lists elements   
for (var i = 0; i < DocumentApp.getActiveDocument().getNumChildren(); i++) {
var firstChild = DocumentApp.getActiveDocument().getBody().getChild(i);
if (firstChild.getType() == DocumentApp.ElementType.LIST_ITEM) {
    firstChild.replaceText(firstChild.getText(), "<li>" + firstChild.getText() + "</li>");
    var childIndex = firstChild.getListId();
    bold.push(i);
 }
}

// Append HTML main <ul> or <ol> lists tags to the lists 
 for (var i = 0; i < bold.length; i++) {
 if (bold[i-1] != bold[i]-1) {
    var firstChild = body.getChild(bold[i]);
    if (firstChild.getGlyphType() != DocumentApp.GlyphType.NUMBER) {
    firstChild.replaceText(firstChild.getText(), "<ul>" + firstChild.getText());
    }
    else {
    firstChild.replaceText(firstChild.getText(), "<ol>" + firstChild.getText());
    }
}}

 for (var i = 0; i < bold.length; i++) {
 if (bold[i+1] != bold[i]+1) {
    Logger.log(i); 
    var firstChild = body.getChild(bold[i]);
    if (firstChild.getGlyphType() != DocumentApp.GlyphType.NUMBER) {
    firstChild.appendText("</ul>");
    }
    else {
    firstChild.appendText("</ol>");
    }
}}
     
}

postFormatter.gs

function postFormatter() {

// This script adds <strong> and <em> tags to the text, preparing it to be uploaded in the WordPress database    
var body = DocumentApp.getActiveDocument().getBody();
var text = body.editAsText();
var idc = text.getTextAttributeIndices(); 

var bold = [];
var bold2 = [];
var bold3 = [];

// Identify where the Bold text is (indexes), and push it into arrays  
  for (var i = 0; i < idc.length; i++) {
    if (text.isBold(idc[i]) && idc[i+1] !== undefined) {
       bold.push(idc[i]);
       bold2.push(idc[i+1]);
    } 
    else if (text.isBold(idc[i]) && idc[i+1] == undefined) {
       bold.push(idc[i]);
    }
  }
  
   for (var i = 0; i < bold.length; i++) {
       if (bold2[i] !== undefined) {
       bold3.push(text.getText().slice(bold[i], bold2[i]));
       }
       else bold3.push(text.getText().slice(bold[i]));
   } 
bold3 = bold3.filter( function( item, index, inputArray ) {
  return inputArray.indexOf(item) == index;
});
  
// Add HTML tags for Bold  
  for (var i = 0; i < bold3.length; i++) {
       body.replaceText(bold3[i], "<strong>" + bold3[i] + "</strong>");
  } 


var idc = text.getTextAttributeIndices(); 
var bold = [];
var bold2 = [];
var bold3 = [];

// Identify where the Emphasized text is (indexes), and push it into arrays  
  for (var i = 0; i < idc.length; i++) {
    if (text.isItalic(idc[i]) && idc[i+1] !== undefined) {
       bold.push(idc[i]);
       bold2.push(idc[i+1]);
    } 
    else if (text.isItalic(idc[i]) && idc[i+1] == undefined) {
       bold.push(idc[i]);
    }
  }
  
   for (var i = 0; i < bold.length; i++) {
       if (bold2[i] !== undefined) {
       bold3.push(text.getText().slice(bold[i], bold2[i]));
       }
       else bold3.push(text.getText().slice(bold[i]));
   } 
bold3 = bold3.filter( function( item, index, inputArray ) {
  return inputArray.indexOf(item) == index;
});

// Add HTML tags for Emphasized text
  for (var i = 0; i < bold3.length; i++) {
       body.replaceText(bold3[i], "<em>" + bold3[i] + "</em>");
  } 
  
}

postUpdater.gs

function postUpdater() {

var body = DocumentApp.getActiveDocument().getBody();
var text = body.editAsText();
var list2 = body.findElement(DocumentApp.ElementType.LIST_ITEM).getElement().editAsText();

// Run the functions that prepare the text for HTML  
  postFormatter();
  links();
  list();

// Update the post content in the WordPress database (ID to be specified at line 22)
var address = '[DATABASE SERVER]';
var user = '[DATABASE USERNAME]';
var userPwd = '[DATABASE PASSWORD]';
var db = '[DATABASE NAME]';
var instanceUrl = 'jdbc:mysql://' + address;
var dbUrl = instanceUrl + '/' + db;
var bodt = body.getText();
var conn = Jdbc.getConnection(dbUrl, user, userPwd);
var SQLstatement = conn.createStatement();
var result = SQLstatement.executeUpdate("UPDATE wp_posts SET post_content='" + bodt + "' WHERE ID=[POST ID]");
SQLstatement.close();
conn.close();

// Remove the tags added to the Google document, restoring it as it was before our functions ran   
body.replaceText("<strong>", "");
body.replaceText("</strong>", "");
body.replaceText("<em>", "");
body.replaceText("</em>", "");
body.replaceText("<ul>", "");
body.replaceText("</ul>", "");
body.replaceText("<ol>", "");
body.replaceText("</ol>", "");
body.replaceText("<li>", "");
body.replaceText("</li>", "");
body.replaceText("<a href=''", "");
body.replaceText("</a>", "");
body.replaceText("''>", "");
body.replaceText("''>", "");
var body = DocumentApp.getActiveDocument().getBody();
var text = body.editAsText();
var idc = text.getTextAttributeIndices(); 

var bold3 = [];
for (var i = 0; i < idc.length; i++) {
    if (text.getLinkUrl(idc[i]) != null) {  
       bold3.push(text.getLinkUrl(idc[i]));
    } 
}
for (var i = 0; i < bold3.length; i++) {
       body.replaceText(bold3[i], "");
}

}

menu.gs

function onOpen(e) {

// Create a menu on the Google document from which to launch the update
   DocumentApp.getUi()
       .createMenu('WordPress')
       .addItem("Update post content", "postUpdater")
       .addToUi();
 
 }
Federico

Leave a Comment

Your email address will not be published. Required fields are marked *