Two Way Synchronization between Google Spreadsheet and AWS RDS using Google Apps Script

Google Spreadsheet can be a very nifty tool to satisfy dynamic data storage needs for any small to medium data analytics projects. Using Google Apps Script, Google Spreadsheet can fetch data from any RESTful API and act as an easily editable data source.

Google Spreadsheet can be directly used as a data source to most of the major analytical dashboard platforms like Tableau as well as operational dashboard platforms like Klipfolio. However, connectivity from Google Spreadsheet to Tableau is not perfectly stable yet and may run into issues on Tableau Server.

One way of getting a more robust method of connectivity from Tableau to Google Spreadsheet could be using an intermediate layer of RDS Database services of AWS. In Google Apps Script, the JDBC service supports, as standard, the Google Cloud SQL, MySQL, Microsoft SQL Server, and Oracle databases.

In this blog, we show you a step-by-step synchronization process of connecting Google Spreadsheet with Amazon’s RDS Web Service. We should bear in mind that Google Apps Script still does not provide connectivity to PostgreSQL DB.

Step 1: 

To demonstrate we have some dummy data on the Spreadsheet(Insights_Summary_Spreadsheet is the name of this Google Spreadsheet) which is extracted using RESTful APIs.

Step 2:  

This data needs to be moved to the database on cloud (AWS RDS MySQL instance). So, the next step involves setting up an RDS MySQL instance on AWS. After having setup the basic RDS MySQL instance, we need to whitelist a few IP addresses to allow Google Apps Script to access your database. Following are the address ranges you’ll need to whitelist:

 

  64.18.0.0 – 64.18.15.255

 64.233.160.0 – 64.233.191.255

 66.102.0.0 – 66.102.15.255

 66.249.80.0 – 66.249.95.255

 72.14.192.0 – 72.14.255.255

 74.125.0.0 – 74.125.255.255

 173.194.0.0 – 173.194.255.255

 207.126.144.0 – 207.126.159.255

 209.85.128.0 – 209.85.255.255

 216.239.32.0 – 216.239.63.255

 

Note: JDBC Service does not connect to port lower than 1025. So make sure that you are not allocating a lower port. 

To add the above IPs to the security group, select the RDS instance and then Edit the Inbound section. Add Rule(IPs) as per requirement. Once this is done, you are ready with your RDS instance to work with Google Apps Script.

Step 3:

In this step we fetch data from Google Spreadsheet into RDS. We need to make a connection to the RDS instance using JDBC Service. In the Google Apps Script project we build the following code.

 

// Replace the variables in this block with real values.

 var address = ‘database_IP_address‘; //End point provided by the RDS Instance

 var rootPwd = ‘root_password‘; //Root password given while configuring DB instance

 var user = ‘user_name‘; //Username given while configuring DB instance

 var userPwd = ‘user_password‘; //User password given while configuring DB instance

 var db = ‘database_name‘; //Database name to which you want to connect

 

 var dbUrl  = ‘jdbc:mysql://‘ + address + ‘/‘ + db; //Generates the database url to which you can connect to

 var conn = Jdbc.getConnection(dbUrl, user, userPwd);

 

Next, we build the code to select the spreadsheet from where we need to fetch the data. 

 

var ss = SpreadsheetApp.getActiveSpreadsheet(); /* This represents the whole data */

 var sheet = ss.getSheetByName(‘Insights_Summary_Spreadsheet‘); /* Replace with your own spreadsheet name */

 var range = sheet.getRange();   /* This represents the whole data */

 var values = range.getValues();

 

Then using prepare statement we can insert data into RDS.

 

/* Query to insert fields into the table on RDS. Here Insights_Summary_RDS is the table created in RDS. */

var stmt = conn. prepareStatement(‘INSERT INTO Insights_Summary_RDS(date,clicks,costs,ctr,cpc,impressions,cpm,avg_pos,budget)values(?,?,?,?,?,?,?,?,?)‘);

stmt.execute();

Step 4:

In this process we fetch data from RDS to Google Spreadsheet if required by our project. After establishing the Google Spreadsheet to RDS connection as shown previously, we build the following code:

var stmt = conn.createStatement();

var store_results = stmt.executeQuery(‘SELECT * FROM Insights_Summary_RDS’);

 

The data returned from the query is stored in a variable called store_results. Now we need to write the records by using a looping structure until the end of the data in the store_results.

 

while (store_results.next()) {

 var rowString = ‘ ‘;

 for (var column = 0; column < numCols; column++) {

     rowString += store_results.getString(col + 1) + ‘\t’;

     }

 }

 store_results.close();

 stmt.close();

 

Eventually a simple Google Apps Script can help us build a great analytical dashboard to get enhanced insights from the data.