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

Get data from an SQL Table in Apps Script