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;

	// 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);
	function bulkDeleteStudents() {
	// get spreadsheet
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var bulkDeleteStudentsSheet = ss.getSheetByName('bulkDeleteStudents');
	// get last row
	var lastRow = bulkDeleteStudentsSheet.getLastRow();
	Logger.log('Last row is: ' + lastRow);
	// get all the 'Student No' values from sheet
	var allStudentNos = bulkDeleteStudentsSheet.getRange(2, 1, lastRow-1, 1).getValues();
	Logger.log(allStudentNos);
	// loop through each row to delete matching student record from SQL Table ********************
	for (var i=0; i<lastRow-1; i++) {
	// assign the current 'Student No' to a variable so easier to manage
	var studentNo = allStudentNos[i][0];
	Logger.log('Current Student No is: ' + studentNo);
	// create a statement to perform a task
	var stmt = db.createStatement();
	// create SQL query to DELETE student record WHERE matching 'StudentNo' is found
	var sql = "DELETE FROM students "
	+ "WHERE StudentNo=" + studentNo;
	// execute the above query
	stmt.execute(sql);
	}
	// end of loop through each row to delete matching student record from SQL Table *************
	// close connections after access
	stmt.close();
	db.close();
	}

view raw Bulk delete records from an SQL Table in Apps Script.gs hosted with ❤ by GitHub

Bulk delete records from an SQL Table in Apps Script