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 (?, ?, ?, ?)");

Bulk add unique data to an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to bulk insert a Google Sheet of data into an existing SQL Table, but only after checking the record does not already exist so that it can be safely re-run. This post improves upon this one for bulk adding data.

getStudentsTableUnique
This function starts the process by getting data from the Table so that we can later compare what we want to upload with what already exists. It uses a 'select' query detailed in this blog post to return current student numbers (StudentNo column). These are then pushed into an empty array and passed to the next function.

bulkAddUniqueStudents
Now that we have an array of student numbers currently in the SQL Table, we can pick up the data from the Google Sheet, loop through each row in turn and use an 'if' to check for matching student numbers. We can make use of a flag variable to determine what should happen when all student numbers have been compared.

Bulk delete records from an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to bulk delete records in an existing SQL Table, from data in a Google Sheet. This naturally follows on from being able to bulk add data in this post.

Rather than getting all of the data from the Google Sheet, this time we are just going to use the Student No which provides a unique value for each record (as it is never repeated). Once we have these values in an array we can loop through each row and use them in our SQL query.

Our query specifies which Table we want to remove the record from and a where condition that has to be met (without this all records would be deleted leaving the Table empty). The where condition in this example is finding a corresponding Student No match.

var sql = "DELETE FROM students "
+ "WHERE StudentNo=" + studentNo;

Bulk add data to an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to bulk insert a Google Sheet of data into an existing SQL Table. This post improves upon this one for adding a single piece of ad-hoc data.

Screenshot of Google Sheet data for SQL Table

This time we have a Google Sheet of data with columns matching those in the students Table. We start by collecting this data into an array that we can loop through to push the values into an SQL query. When we create the query we construct it slightly differently however as we need to include variables rather than static text (as their value will change for each row when the script loops through).

var sql = "INSERT INTO students (StudentNo, FirstName, LastName, Shoesize) "
+ "VALUES ('" + studentNo + "','" + firstName + "','" + lastName + "','" + shoesize + "')";

Get data from an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to query an existing Table to then copy the data into a Google Sheet. This post follows on from this one for adding data to an SQL Table.

This time we make use of a 'select' SQL query to pickup all columns (*) from the students Table. We use 'executeQuery' to run it as we are returning a set of data from the database.

var studentsTableData = stmt.executeQuery("SELECT * FROM students");

Next we collect some spreadsheet data and select our starting cell where the first row of data will be inserted from - A2 in this instance. We then create a 'while' loop that goes through row-by-row for each of the 4 columns and inserts data from the students Table.

We make use of a method called 'offset' which is detailed more in this blog post which allows us to iterate from the first column across to the last, then move down each row. Then 'getString' retrieves a string value from the Table's specified row and column which we can then enter into the cell.

var newCell = startCell.offset(row, i);
var value = studentsTableData.getString(i+1);
newCell.setValue(value);

Add data to an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to insert an ad-hoc entry (row of data) into an existing Table. This post follows on from this one for creating an SQL Table in Apps Script.

As before we begin by creating a statement for sending SQL code to the database. Then we can 'execute' the given SQL statement to 'insert into' the existing students Table the values provided.

var student = stmt.execute('INSERT INTO students'
                            + " VALUES ('456789', 'Donald', 'Duck', '9');"
                            );

4 values have been provided to match the 4 columns contained within the students Table:

1. StudentNo - 456789
2. FirstName - Donald
3. LastName - Duck
4. Shoesize - 9

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));'
                                  );