I have a staff training database in Google Sheets in the link below:
A Google Form populates columns A to J of the "Add Employee Form Responses" sheet with Employee info. That data is transferred to the "Qualifications" sheet by simple =cell functions. The issue is, and I can't work out why, is that when each new form is completed and the form answers populate the "Add Employee Form Responses" sheet, it changes the cell number in the next empty row in the Qualifications sheet to the next ensuing row numbers which link to blank cells in the "Add Employee Form Responses" sheet. For example, before the form is completed all of the cells in row 4 of the Qualifications sheet are drawing from row 4 of the "Add Employee Form Responses" sheet but after the form is populated row 4 of the Qualifications sheet is drawing from row 5 "Add Employee Form Responses" sheet which remains null.
I used to have another sheet with a Query function in between the two sheets which worked perfectly but I have since added the following apps script (rows 1 to 24 of the Script Editor) to cut the row when a date is entered in column B of the "Qualifications" sheet and paste it into the "Qualifications - Employees Left" sheet. The issue with this is that the Query just retrieves the data again from the "Add Employee Form Responses" sheet and repopulates the "Qualifications" sheet. In other words, the data that was just cut and pasted from the "Qualifications" sheet into the "Qualifications - Employees Left" sheet returns to the "Qualifications" sheet from the "Add Employee Form Responses" sheet by way of the Query.
// Cut Employees Left from Qualifications sheet and
// paste in Qualifications - Employees Left sheet
function onEdit(e) {
var ss = e.source;
var sheet = ss.getActiveSheet();
var sheetName = "Qualifications"
var range = e.range;
var editedColumn = range.getColumn();
var editedRow = range.getRow();
var column = 2;
var date = range.getValue();
// Object.prototype.toString.call(date) === '[object Date]' --> checks if value is date
// editedColumn == column && editedRow > 4 --> checks if edited cell is from 'Date Left'
// sheet.getName() == sheetName --> checks if edited sheet is 'Qualifications'
if(Object.prototype.toString.call(date) === '[object Date]' && editedColumn == column && editedRow > 4 && sheet.getName() == sheetName) {
var numCols = sheet.getLastColumn();
var row = sheet.getRange(editedRow, 1, 1, numCols).getValues();
var destinationSheet = ss.getSheetByName("Qualifications - Employees Left");
// Get first empty row:
var emptyRow = destinationSheet.getLastRow() + 1;
// Copy values from 'Qualifications'
destinationSheet.getRange(emptyRow, 1, 1, numCols).setValues(row);
sheet.deleteRow(editedRow);
sheet.hideColumns(column);
}
I will be happy if there is a simple fix for the row numbering issue between the "Qualifications" sheet and the "Add Employee Form Responses" sheet. Otherwise, the only way I believe this can be fixed is to reinstate the Query function and then get the apps script to remove the data from the "Add Employee Form Responses" sheet also. I would appreciate any suggestions. I really would like everything to be automated because there will be many computer illiterate users.
