Automate Data Extraction from MYSQL to Google Sheet Using App Script

When I first joined my company more than 2 years ago, my tasks at that time was heavily focused on the analytical and dashboard reporting side. One of these task was to prepare daily business performance email which shows our the performance business metrics for different business segments (B2C, B2B, Ikea delivery, e-commerce parcels delivery for Shopee and Lazada and so on).

And all these data for this daily email report were coming from multiple data sources such as SQL database, Facebook, Google analytic, our third-party keyword search API, Our cloud storage (for manual upload data), then was transformed and display into Google Spreadsheet and finally email to our stakeholders on a daily basis.

I first did this task manually for a couple of weeks, which means I have to open our MySql workbench, run my SQL query, save the result in CSV. Then open it in a spreadsheet, paste the value into an intermediary table for transformation inside the spreadsheet so the final value will be reflected into the final table. And repeat this process for other data sources for our Facebook, Google Analytics, etc before compiling everything into one email.

I spend almost 1 hour every day doing this manual job and sometimes it was prone to human errors (like putting the wrong value in the wrong spreadsheet cell, forgot to extract data for the right date ), and almost drives me crazy.

After spending a couple of days researching, I noticed that I can almost automate everything mentioned above using Google App Script. On top of it, all of these services are free!!

So what is App Script?

What can Apps Script do?

App script is a development platform that allows you to create an application that integrates with Google Workspace. With App Script, you can basically :

• Add custom menus, dialogs, and sidebars to Google Docs, Sheets, and Forms.
• Write custom functions and macros 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.
• Build add-ons to extend Google Docs, Sheets, Slides, and Forms, and publish them to the Add-on store.
• Convert an Android app into an Android add-on so that it can exchange data with a user’s Google Doc or Sheet on a mobile device.
• Streamline Google Chat workflows by building a chat bot.

For the context of this article, I will write about how I use App Script to automate data extraction and transformation from MySQL and to Google Spreadsheet. The example here is; I need to show to my stakeholder our past 7 days booking performance relative to the days of my email.

So the automation flow of my AppScript would be:

1. Google Spreadsheet App Script will go to MySQL database.
2. Query all the required booking information from 7days ago to yesterday.
3. delete the previous day extracted data from the ‘Past 7 days bookings’ table inside Google spreadsheet
4. Paste the newly extracted data inside the ‘Past 7 days bookings’ table.

Let see how we can implement this automation workflow inside Google Sheet using App Script.

Here is our example table for the past 7 days booking table, this table will be under the ‘Booking’ tab. The dummy ‘Number of Bookings’ here is our yesterday's past 7 days' booking number.

To go to App Script, Go to Tool, and click Script Editor

Once you land inside App Script, click the ‘+’ icon from the file and select ‘Script’.

Now, before we connect App Script to our MySQL database, let's write our SQL query first. Here is the example of the query to extract our past 7 days booking number relative to yesterday

select
booking_date,
count(distinct job_number)
from database
where
country = 'Malaysia' 
and booking_date >= date_sub(curdate(), interval 8 day)
and booking_date <= date_sub(curdate(), interval 1day)

Now inside App Script, we are going to write a script that connects our Google sheet to our MySQL database, run the query above and delete yesterday's extracted data, and then paste the new data inside the Google Sheet table.

Lets first declare our function inside App Script.

function Past_7_days_bookings() {
}
var conn= Jdbc.getConnection('jdbc:mysql://your database', 'Your Username', 'Your Password');
var stmt = conn.createStatement();
stmt.setQueryTimeout(30);
var start = new Date()

method .createStatement() is for sending SQL statements to the database

while method .setQueryTimeout(30) is how long in second the App script need to wait before server time out.

Then we pass our SQL statement

var rs = stmt.executeQuery(
 " select"
+ " booking_date,"
+ " count(distinct job_number)"
+ " from database"
+" where"
+" country ='Malaysia' "
+" and booking_date >= date_sub(curdate(), interval 8 day)"
+" and booking_date <= date_sub(curdate(), interval 1day)"
)

Do remember, for each line of the SQL statement, it must start and end with a double-quote (example: “select”)

and each new line after the first one must start with ‘+’ sign (example: + “ From database” )

Now before we paste our latest data inside the Past 7 Days Booking Performance table, we need to clear it first

#the name of the sheet tab where the table is
sheetName = "Booking"
# Clear the old previous value content
sheetName.getRange('A:Y').clearContent()

Now lets paste the new value

var Avals = sheet.getRange("A1:A").getValues();
var Alast = Avals.filter(String).length
var row =Alast ;
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();

And you are done!

Here is the full code that covered all the activity we mentioned above

function Past_7_days_bookings() {
sheetName = 'Booking'
var conn= Jdbc.getConnection('jdbc:mysql://your database', 'Your Username', 'Your Password');
var stmt = conn.createStatement();
stmt.setQueryTimeout(30);
var start = new Date()
var rs = stmt.executeQuery(
 " select"
+ " booking_date as Date,"
+ " count(distinct job_number) as Booking_Number"
+ " from database"
+" where"
+" country ='Malaysia' "
+" and booking_date >= date_sub(curdate(), interval 8 day)"
+" and booking_date <= date_sub(curdate(), interval 1day)"
)
var sheet= spreadsheet.getSheetByName(sheetName); // Returns the currently active spreadsheet

sheet.getRange('A:Y').clearContent() //Clear Sheet Content
var Avals = sheet.getRange("A1:A").getValues();
var Alast = Avals.filter(String).length
var row =Alast ;
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())); // To generate script log. To view log click on View -> Logs.
}

To set app script on scheduled daily basis, click Trigger at the left pane.

Click Add Trigger at the down right corner

Choose the code script that we wrote inside the function to run

Select ‘Time-driven’ as an event source.

As we only want to run this script once a day, just select ‘Day Timer’ inside the type of time-based trigger.

As we want to run this script before office hours, so just select ‘1am-2am’ .

I choose ‘notify me immediately inside Failure Notification Setting.

So based on the configuration setting, Our App script will run the ingestion and transfer the data inside our Google Sheet every day between 1 am-2 am. Any failure will be reported through our email immediately.

So that is the end. Now I just wait until the next day to get the latest data from the MySQL database inside my Google Sheet before emailing it to the respective party. By the way, the reason I do not automate the email finding was that I also act as the final gatekeeper to make sure all the data is correct, (no missing value, no funny figure). A bit of hassle but in the end, I managed to save from more than half an hour to less than a couple of minutes.

Hope you learn something from reading my article :).