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

How to use 'offset' in a Google Sheet

The following Google Apps Script is a simple example of using 'offset' to return a new range from one previously given. So if we take cell A1 for instance and offset by 1 row & 1 column we would end up with cell B2. If we take cell A1 and offset by 2 rows & 3 columns we would end up with D3.

Screenshot of Google Sheet with values from offset function

Count word frequency within text

The following Google Apps Script is designed to take a chunk of text (pasted into the Google Sheet) and then count how many times each word appears within that text, displaying the results in a hierarchical list in another sheet.

The script also makes use of Stopwords which are designed to be ignored from the main body of text and not counted, so like 'they', 'are', 'a' and 'the' are all commonly used English words which would unnecessarily be at the top of the results list each time. The list of Stopwords can be adjusted as required if you want to add/remove some.

Screenshot of example word count results