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

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

Array - sort 2-D items each with a value

The following Google Apps Script builds on this previous blog post by introducing sorting for a 2-D array that contains items with a numerical value. As before we want to sort the array in ascending order based on the numerical value, but also keep the text associated with it (which in this example is part of a larger script that counts the frequency of words in a piece of text).

var myArray = [["means", 5.0], ["you’ve", 3.0], ["help", 19.0], ["they", 17.0]];

All we ultimately need to do in the sortNumbers function is define which part of the array the 'sort' needs to look at. As an array starts at zero that would give us the text value which is not what we want, so instead we specify that a and b should use position one for the numerical value. In this example we have logged the value to verify the correct element of the array is being called.

function sortNumbers(a, b) {
  Logger.log(a[1]);
  Logger.log(b[1]);
  return a[1] - b[1];
}

So in this example we end up with the result.

[[you’ve, 3.0], [means, 5.0], [they, 17.0], [help, 19.0]]

Array - sort simple numerical values

The following Google Apps Script is designed to take an array of numerical items and sort them in ascending order (from lowest to highest).

We begin with an array of values and call a function named sortNumbers.

var myArray = [40, 100, 1, 5, 25, 10];
myArray.sort(sortNumbers);

The sortNumbers function takes 2 values as input parameters and sorts them according to the returned (negative, zero, positive) value. If the result is negative a is sorted before b. If the result is positive b is sorted before a. If the result is 0 no changes are done with the sort order of the two values.

function sortNumbers(a, b) {
  return a - b;
}

So in this example we end up with the result.

[1.0, 5.0, 10.0, 25.0, 40.0, 100.0]

Here is a link to a version with a 2-D array of items each with a value.