It can really useful to use Awesome Table with data from an SQL database. You could achieve this by using  a proxy and call your database every time you load your Awesome Table, but it will be easier to automatically fill a spreadsheet with your data.

Why use Awesome Table with an SQL database? 

Awesome Table mainly uses Google Spreadsheet as its source database because of its simplicity. However, when building more complex projects it can be useful to pull your data from and existing SQL database.

Awesome Table allows you to transform and ugly SQL table to an app that will display your data dynamically and beautifully with filters and templates. You can also create several apps from the same database for a different usage or different users.

Why use Apps Script?

There are several ways you could use Awesome Table with data from a SQL database.

You could use a proxy to load data every time someone loads your app. This can be useful if you need your data to be as updated as possible. However, the loading time can be really long and our method refreshes data every minute.You can also use services like Supermetrics to fill your spreadsheets with data from an SQL database.

Apps script is the perfect tool to achieve this as it allows you to fully customize the way you handle your data. It also provides you with the JDBC connector (allowing you to get data from your SQL database) and of course access and edit your spreadsheet easily.

Code example

This is a code example on a very simple way to fill a spreadsheet with SQL data:

function sqlToAwesomeTable(){
	
	//connection to your SQL database
	var sqlConnection = connectToSql();
	var sqlStatement = sqlConnection.createStatement();
	
	//execute a sql query to get the relevant data
	var sql  = "SELECT * FROM TableName";
	var results = sqlStatement.executeQuery(sql);
	
	//
	var data = [];
	while (results.next()) {
		var dataIndex = data.length;
		data[dataIndex] = [];
		for (var a = 1 ; a < (numbersOfColumns + 1) ; a++){
			data[dataIndex].push(results.getString(a));
		}
	}
	
	updateSpreadsheet(data);
	
	results.close();
	sqlStatement.close();
	sqlConnection.close();
	
}

function updateSpreadsheet(data) {
	
	var lastRow = (data.length + 2);
	var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetName");
	
	//removing old data and write new data
	sheet.getRange("A3:D").clear();
	sheet.getRange("A3:D"+lastRow).setValues(data);
	
}

function connectToSql() {
	
	//your database params
	var params = {
		ip: "yourIpAddress",
		user: "username",
		password: "password",
		database: "database_name"
	}
	
	//connecting with JDBC : https://developers.google.com/apps-script/guides/jdbc
	var dbUrl = 'jdbc:mysql://' + params.ip + '/' + params.database;
	return Jdbc.getConnection(dbUrl, params.user, params.password);
	
}

To use this code, paste it in a script file attached to your spreadsheet (and set up a time trigger to launch sqlToAwesomeTable). Make sure you update these values (in red in the code example):

• sql: your sql query. A classic query would be "SELECT * FROM tableName" but you can use more advanced queries to get specific data.
• numberOfColumns: the number of columns you want to extract
• SheetName: the name of your sheet
• "A3:D": the range that you would like to update (replace D with the last column you want to update).
• params: update the params to connect to your sql database.