Create an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to create a Table in an existing database.

db.gs
To begin we need to establish a remote connection to the database via the 'JDBC Service' to which we pass a number of credentials.

var db = Jdbc.getConnection('jdbc:mysql://' + dbAddress + ':3306/' + dbName, dbUser, dbPassword);

createStudentsTable.gs
With the connection made we next need to create a statement for sending SQL code to the database.

var stmt = db.createStatement();

Then we can 'execute' the given SQL statement which creates a Table called students containing 4 columns and their datatypes.

var studentsTable = stmt.execute('CREATE TABLE students'
                                   + ' (StudentNo int, FirstName varchar(255), LastName varchar(255), Shoesize varchar(255));'
                                  );

To finish cleanly it is good practice to then 'close' any open connections we have to the database.

stmt.close();
db.close();

	// Database Credentials
	var dbAddress = '';
	var dbUser = '';
	var dbPassword = '';
	var dbName = '';
	// connect to SQL database
	var db = Jdbc.getConnection('jdbc:mysql://' + dbAddress + ':3306/' + dbName, dbUser, dbPassword);
	function createStudentTable() {
	// create a statement to perform a task
	var stmt = db.createStatement();
	// create a students table (columnName & dataType)
	var studentsTable = stmt.execute('CREATE TABLE students'
	+ ' (StudentNo int, FirstName varchar(255), LastName varchar(255), Shoesize varchar(255));'
	);
	// close connections after access
	stmt.close();
	db.close();
	}

view raw Create an SQL Table in Apps Script.gs hosted with ❤ by GitHub

Create an SQL Table in Apps Script