tl;dr: See script at bottom.
An onEdit() function is inappropriate for your use case, as cell contents modified by spreadsheet functions are not considered "edit" events. You can read more about that in this answer. If you want this to be automated, then a timed trigger function would be appropriate. Alternatively, you could manually invoke the function by a menu item, say. I'll leave that to you to decide, as the real meat of your problem is how to ensure row-level uniqueness in your final data set.
Merging unique rows
Although your original code is incomplete, it appears you were intending to first remove duplicates from the source data, utilizing case-insensitive string comparisons. I'll suggest instead that some other JavaScript magic would help here.
We're interested in uniqueness in our destination data, so we need to have a way to compare new rows to what we already have. If we had arrays of strings or numbers, then we could just use the techniques in How to merge two arrays in Javascript and de-duplicate items. However, there's a complication here, because we have an array of arrays, and arrays cannot be directly compared.
Hash
Fine - we could still compare rows element-by-element, which would require a simple loop over all columns in the rows we were comparing. Simple, but slow, what we would call an O(n2) solution (Order n-squared). As the number of rows to compare increased, the number of unique comparison operations would increase exponentially. So, let's not do that.
Instead, we'll create a separate data structure that mirrors our destination data but is very efficient for comparisons, a hash.
In JavaScript we can quickly access the properties of an object by their name, or key. Further, that key can be any string. We can create a simple hash table then, with an object whose properties are named using strings generated from the rows of our destination data. For example, this would create a hash object, then add the array row to it:
var destHash = {};
destHash[row.join('')] = true; // could be anything
To create our key, we're joining all the values in the row array with no separator. Now, to test for uniqueness of a row, we just check for existence of an object property with an identically-formed key. Like this:
var alreadyExists = destHash.hasOwnProperty(row.join(''));
One additional consideration: since the source data can conceivably contain duplicate rows that aren't yet in the destination data, we need to continuously expand the hash table as unique rows are identified.
Filter & Concatenate
JavaScript provides two built-in array methods that we'll use to filter out known rows, and concatenate only unique rows to our destination data.
In its simple form, that would look like this:
// Concatentate source rows to dest rows if they satisfy a uniqueness filter
var mergedData = destData.concat(sourceData.filter(function (row) {
// Return true if given row is unique
}));
You can read that as "create an array named mergedData that consists of the current contents of the array named destData, with filtered rows of the sourceData array concatenated to it."
You'll find in the final function that it's a little more complex due to the other considerations already mentioned.
Update spreadsheet
Once we have our mergedData array, it just needs to be written into the destination Sheet.
Padding rows: The source data contains rows of inconsistent width, which will be a problem when calling setValues(), which expects all rows to be squared off. This will require that we examine and pad rows to avoid this sort of error:
Incorrect range width, was 6 but should be 5 (line ?, file "Code")
Padding rows is done by pushing blank "cells" at the end of the row array until it reaches the intended length.
for (var col=mergedData[row].length; col<mergedWidth; col++)
mergedData[row].push('');
With that taken care of for each row, we're finally ready to write out the result.
Final script
function appendUniqueRows() {
var ss = SpreadsheetApp.getActive();
var sourceSheet = ss.getSheetByName('Get Data');
var destSheet = ss.getSheetByName('Final Data');
var sourceData = sourceSheet.getDataRange().getValues();
var destData = destSheet.getDataRange().getValues();
// Check whether destination sheet is empty
if (destData.length === 1 && "" === destData[0].join('')) {
// Empty, so ignore the phantom row
destData = [];
}
// Generate hash for comparisons
var destHash = {};
destData.forEach(function(row) {
destHash[row.join('')] = true; // could be anything
});
// Concatentate source rows to dest rows if they satisfy a uniqueness filter
var mergedData = destData.concat(sourceData.filter(function (row) {
var hashedRow = row.join('');
if (!destHash.hasOwnProperty(hashedRow)) {
// This row is unique
destHash[hashedRow] = true; // Add to hash for future comparisons
return true; // filter -> true
}
return false; // not unique, filter -> false
}));
// Check whether two data sets were the same width
var sourceWidth = (sourceData.length > 0) ? sourceData[0].length : 0;
var destWidth = (destData.length > 0) ? destData[0].length : 0;
if (sourceWidth !== destWidth) {
// Pad out all columns for the new row
var mergedWidth = Math.max(sourceWidth,destWidth);
for (var row=0; row<mergedData.length; row++) {
for (var col=mergedData[row].length; col<mergedWidth; col++)
mergedData[row].push('');
}
}
// Write merged data to destination sheet
destSheet.getRange(1, 1, mergedData.length, mergedData[0].length)
.setValues(mergedData);
}
ssandnewSS. Then you get the data from sheet and save it in my data and write that to the Final Data sheet after the last filled row of the first sheet in your spreadsheet (ss.getlastRow()) – Robin Gertenbach