Batch add data to an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to insert a Google Sheet of data into an existing SQL Table in batches, rather than row-by-row, to greatly improve performance. This post improves upon this one for bulk adding data.

By using the 'addBatch' method we can collate each row of student data into a single SQL statement that can be used to connect to the database once and execute multiple times. In order to do this we use 'prepareStatement' to which we can then feed in our values

var stmt = db.prepareStatement("INSERT INTO students (StudentNo, FirstName, LastName, Shoesize) "
                                 + "VALUES (?, ?, ?, ?)");

As we gather data for the current row in our 'for loop' we can populate the above empty values through 'setObject' which requires the column and value to be provided. Then add this as a batch to the SQL statement.

stmt.setObject(1, studentNo);
stmt.setObject(2, firstName);
stmt.setObject(3, lastName);
stmt.setObject(4, shoesize);
stmt.addBatch();

Once all of the data has been gathered into batches we can 'executeBatch' to connect to the database and add them all in one go.

Batch add data to an SQL Table in Apps Script