How to Use Google Apps Script to Fetch Databases to a Google Spreadsheet

Image Courtesy: Webs Wheel

Exporting information from a WordPress database to an Excel sheet or in a CSV format is pretty commonplace now. However, the problem with exporting and importing files is that the process is not intuitive and all files need to be updated at regular intervals.

This can be resolved by using Google Apps Script to link your WordPress database to a Google spreadsheet which automates the workflow. Here we see how a Google Apps Script can be used to fetch database values from WordPress through the Google Apps Script Spreadsheet Service.

What is Google Apps Script?

Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services and build web applications.

Using the Google App Script you can do new things with Google Apps like Docs, Sheets, and Forms.

By using the Google App Script we can:

• Add custom menus, dialogs, and sidebars to Google Docs, Sheets, and Forms.
• Write custom functions for Google Sheets
• Publish web apps — either standalone or embedded in Google Sites
• Interact with other Google services, including AdSense, Analytics, Calendar, Drive, Gmail, and Maps

The Google Apps Script Spreadsheet service allows scripts to create, access, and modify Google Sheets files.

Creating a Google Apps script with Spreadsheet

The process of creating a Google Apps Script with spreadsheet is as follows:

• Create a New Spreadsheet
• Click on “Tools->Script Editor”
• You will see an editor window in a new tab
• There are several Classes and Methods available for writing the script. Some of them are as follows:
  • Classes
    1. BorderStyle
    2. DataValidation
    3. ContainerInfo
    4. Range
  • Methods
    1. getChartType()
    2. getRanges()
    3. setColours()
• Following is the Sample app script example that will store the spreadsheet data into the log:

function logProductInfo() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
for (var i = 0; i < data.length; i++) {
Logger.log('Product name: ' + data[i][0]);
Logger.log('Product number: ' + data[i][1]);
}
}

• After writing the script you need to debug the script by clicking on debug icon and if debugging is successful with no error then you can “Run” the script by clicking on run icon
• To view the Log just click on “View->Logs”

Google Apps Script and WordPress

Google Apps Script can be used with WordPress in many ways. One of them is to connect with the WordPress database.

Google Apps Script can access many relational databases via its JDBC service, which is a wrapper around the standard Java Database Connectivity standard. In Google Apps Script, the JDBC service supports, as standard, the Google Cloud SQL, MySQL, Microsoft SQL Server, and Oracle databases.

The following example will demonstrate the database connection:

function myFunction() {
// make the connection
var connection = Jdbc.getConnection("jdbc:mysql://db IP or host name:port number/DB name", "User name", "password");
// perform the query
var SQLstatement = connection.createStatement();
var result = SQLstatement.executeQuery("SELECT * FROM DB_NAME");
// choose a range in sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var cell = ss.getRange('A2');
// loop through result object, setting cell values to database data
var row = 0;
while(result.next()) {
for(var i=0; i<4; i++) { // four fields per record
cell.offset(row, i).setValue(result.getString(i+1));
}
row++;
}
// tidy up
result.close();
SQLstatement.close();
connection.close();
}

By running this code this will get the values from the database and will add the fetched values into the currently active spreadsheet.

We can also add the values into the database and those values will be used in WordPress. For instance, if you have all the users’ data in a spreadsheet and you want to register all these users in one go, it is possible through the Google Apps Script.

Endnote

Mapping your WordPress database to a Google Spreadsheet can be easily done by using the Google Apps Script. The advantage of opting for this method is that you simply have to run this script to update your spreadsheet as per your database. There is no need to constantly import and merge files to keep your online and offline databases in sync. The application can be highly useful for developers who use Google APIs for a variety of purposes.

What other applications in WordPress have you tried using Google Apps Script for? Let me know your thoughts below!

• Tutorials, WordPress Solutions, WordPress Tips & Tricks
• With tags: Google Apps Script, Google Apps Script applications, WordPress

Share:

• Vinay B

Please note, some of the links in this blog post might be affiliate links. This means if you go on to purchase a product using such a link, we receive a small commission (at no additional cost to you). This helps us support the blog and produce free content. We only recommend products we work with or love. Thank you for your support!