Tuesday, 19 November 2019

Create an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to create a Table in an existing database.
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);
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.

Create an SQL Table in Apps Script


  1. How to get the below credentials, sorry i am a new to this.
    var dbAddress = '';
    var dbUser = '';
    var dbPassword = '';
    var dbName = '';

    1. Hi

      Those credentials will need to come from the setup of your SQL Database that you host/manage.

      Kind regards