Import WordPress data into your Google Sheet

I’ve found out recently how powerful Google Apps scripts are. Very few Excel experts know about the power of Google Apps scripts and what powerful things you can do with it.

A client of mine needed to do reporting on a WordPress custom table. Instead of trying to build him a front-end in WordPress, it is so much easier to connect the MySQL database table up with Google Apps scripts and pull the data real-time or on a schedule interval into Google Sheets.

The client feels much more comfortable in Google Sheets than WordPress, so we have a very happy client.

Here is a short snippet to load all the columns and rows from the MySQL table to your Google Sheet:

function myMySQLFetchData() {

    var conn = Jdbc.getConnection(‘jdbc:mysql://host/database’, ‘username’, ‘password’); // Change it as per your database credentials
        var stmt = conn.createStatement();
    var start = new Date(); // Get script starting time

    var rs = stmt.executeQuery(“SELECT columnnames FROM wp_custom_table”);

    //change table name as per your database structure

    var doc = SpreadsheetApp.getActiveSpreadsheet(); // Returns the currently active spreadsheet 
    //doc.
    doc.getSheets()[0].clear();
    var cell = doc.getRange(‘a1’);
    var row = 0;
    var getCount = rs.getMetaData().getColumnCount(); // Mysql table column name count.

    for (var i = 0; i < getCount; i++){
        cell.offset(row, i).setValue(rs.getMetaData().getColumnName(i+1)); // Mysql table column name will be fetch and added in spreadsheet.
    }

    var row = 1;
    while (rs.next()) {
        for (var col = 0; col < rs.getMetaData().getColumnCount(); col++) {
            cell.offset(row, col).setValue(rs.getString(col + 1)); // Mysql table column data will be fetch and added in spreadsheet.
        }
        row++;
    }

    rs.close();
    stmt.close();
    conn.close();
    var end = new Date(); // Get script ending time
    Logger.log(‘Time elapsed: ‘ + (end.getTime() — start.getTime()));
}

It uses a JDBC connector which can help you connect to multitude of different database providers.

Here is really good tutorial to get your started on Google Apps scripts:

Automate Google Sheets: An Introduction to Google Apps Script - The Ultimate Guide to Google Sheets…

Some other cool things that you can do with Google app scripts I’ve seen so far:

• Sent scheduled emails.
• Sent emails with a list of email address in your spreadsheet, so you can do a mail merge sent.
• Create buttons with actions.
• You can do HTTP requests and even import JSON data.

It is also worth looking at the libraries of add-ons for the Google products. Adding extra utilities and features to your Google apps.

I hope this has triggered your interests a bit more for you to go out and explore this functionality a little bit further and how you can hack your Google Sheets next.