Tuesday 7 January 2020

Bulk add unique 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, but only after checking the record does not already exist so that it can be safely re-run. This post improves upon this one for bulk adding data.

This function starts the process by getting data from the Table so that we can later compare what we want to upload with what already exists. It uses a 'select' query detailed in this blog post to return current student numbers (StudentNo column). These are then pushed into an empty array and passed to the next function.

Now that we have an array of student numbers currently in the SQL Table, we can pick up the data from the Google Sheet, loop through each row in turn and use an 'if' to check for matching student numbers. We can make use of a flag variable to determine what should happen when all student numbers have been compared.
for (var j=0; j<allStudentNosLength; j++) {
    if (studentNo == allStudentNos[j]) {
        var exists = true;
        Logger.log(studentNo + ' already exists');
If a matching student number is not found then we can run the relevant SQL query and 'insert' our new data, as detailed in this previous blog post.

Bulk add unique data to an SQL Table in Apps Script

No comments:

Post a Comment