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

	// Database Credentials
	var dbAddress = '';
	var dbUser = '';
	var dbPassword = '';
	var dbName = '';
	// connect to SQL database
	var db = Jdbc.getConnection('jdbc:mysql://' + dbAddress + ':3306/' + dbName, dbUser, dbPassword);
	/*
	This Function bulk adds a list of students to the 'students' Table from
	the 'bulkAddStudents' sheet.
	*/
	function bulkAddStudents() {
	// get spreadsheet
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var bulkAddStudentsSheet = ss.getSheetByName('bulkAddStudents');
	// bulk get student data from sheet
	var studentData = bulkAddStudentsSheet.getDataRange().getValues();
	// get length of student data for loop
	var studentDataLength = studentData.length;
	// loop through each row to add student data to SQL Table *******************************
	for (var i=1; i<studentDataLength; i++) {
	// assign values to variables so easier to manage
	var studentNo = studentData[i][0];
	var firstName = studentData[i][1];
	Logger.log('FirstName is: ' + firstName);
	var lastName = studentData[i][2];
	var shoesize = studentData[i][3];
	Logger.log('Shoesize is: ' + shoesize);
	// create a statement to perform a task
	var stmt = db.createStatement();
	// create SQL query to insert student values into existing students Table
	var sql = "INSERT INTO students (StudentNo, FirstName, LastName, Shoesize) "
	+ "VALUES ('" + studentNo + "','" + firstName + "','" + lastName + "','" + shoesize + "')";
	// execute the above query
	stmt.execute(sql);
	}
	// end of loop through each row to add student data to SQL Table ************************
	// close connections after access
	stmt.close();
	db.close();
	}

view raw Bulk add data to an SQL Table in Apps Script.gs hosted with ❤ by GitHub

Bulk add data to an SQL Table in Apps Script