Reputation: 137
The script below copies rows from multiple sheets and append them to a master spreadsheet. But it ends up producing way too many duplicates. I mean it copies and appends the content from the other sheets without the possibility of checking, if these rows and their respective content already exist in the master spreadsheet.
Here is the script:
function copyAndAddNewEntries() {
// Get ID of each source spreadsheet and name of each source sheet
var spreadsheets = [
{ssId: 'SOURCE_SHEET_ID1', sheetNames: ["Sheet1", "Sheet2",,,]},
{ssId: 'SOURCE_SHEET_ID2', sheetNames: ["Sheet1", "Sheet2",,,]},
,,
,
];
// Copy the relevant rows/content from each of the spreadsheets and sheets listed above
var {values, max} = spreadsheets.reduce((o, {ssId, sheetNames}) => {
SpreadsheetApp.openById(ssId).getSheets().forEach(s => {
if (sheetNames.includes(s.getSheetName())) {
var [, ...v] = s.getDataRange().getValues();
var temp = v.filter(e => e.join("") != "");
if (temp.length > 0) {
o.values = o.values.concat(temp);
var len = temp.length;
o.max = o.max < len ? len : o.max;
}
}
});
return o;
}, {values: [], max: 0});
values = values.map(r => {
var len = r.length;
return len < max ? r.concat(Array(max - len)) : r;
});
// Add the rows/content that were copied above to the MASTER WORKSHEET
var targetSheet = SpreadsheetApp.openById('TARGET_SHEET_ID').getSheetByName('TARGET_SHEET_NAME');
targetSheet.getRange(targetSheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
}
I am wondering how I can add in the script below from Google Apps Script official website (and which I have slightly modified) to check my data before the new rows are appended to the master spreadsheet. All script examples I´ve seen so far do the job afterwards (so, only after the new rows have been added). But I need the check to happen before or during the process of adding these rows.
function removeDuplicates() {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName("NAME_OF_MASTER_SPREADSHEET");
var data = sheet.getDataRange().getValues();
// var sheet = SpreadsheetApp.getActiveSheet();
var newData = [];
for (var i in data) {
var row = data[i];
var duplicate = false;
for (var j in newData) {
if (row.join() == newData[j].join()) {
duplicate = true;
}
}
if (!duplicate) {
newData.push(row);
}
}
sheet.clearContents();
sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}
Thank you so much in advance for your help :)
Upvotes: 1
Views: 609
Reputation: 14527
So you have the 2D array values
. And you have the Master sheet with data that can be grabbed as a 2D array as well (with the same number of columns, I suppose). You can left only unique rows in the values
array this way:
var master = [
['a', 'b', 'c'],
['d', 'e', 'f'],
['g', 'h', 'i'],
['j', 'k', 'l'],
];
var values = [
['a', 'b', 'c'], // <-- this line will be ignored
['g', 'h', 'i'], // <-- this line will be ignored
['m', 'n', 'o'], // <-- this line is unique
['p', 'q', 'r'], // <-- this line is unique
];
var joined_master = master.map(x => x.join()); // [[a,b,c],[d,e,f]] --> ['abc','def']
var unique_values = values.filter(x => !joined_master.includes(x.join()));
console.log(unique_values); // output: [ ['m', 'n', 'o'], ['p', 'q', 'r'] ]
But if you have 100 of spreadshees and an even higher number of sheets there can be problem with time limit for the script.
Update
Suppose your script works fine. It gives you the array values
after these lines:
values = values.map(r => {
var len = r.length;
return len < max ? r.concat(Array(max - len)) : r;
});
And the next line gives you the sheet targetSheet
:
var targetSheet = SpreadsheetApp.openById('TARGET_SHEET_ID').getSheetByName('TARGET_SHEET_NAME');
After this line you can get all rows from the target sheet this way:
var target_data = targetSheet.getRange(1,1,targetSheet.getLastRow(),values[0].length).getValues();
Now you can remove from the array values
all the rows that are in the array target_data
this way:
target_data = target_data.map(x => x.join());
values = values.filter(x => !target_data.includes(x.join()));
After that the array values
will contain only unique rows.
Now you can continue your script as usual:
targetSheet.getRange(targetSheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
Technically it should work. But actually if you have too much sheets and rows the script can excess the time limit.
Upvotes: 2