My data import (Sheet A) has all information from the last known events. It only changes them when another event occurs. So if the event is from two days ago it is still in my data import (Sheet A) until that event changes. I need to have my data import (Sheet A) checked against Sheet B, Sheet C, and Sheet D for identical rows. Then delete the row if it already exists.
So far I have:
function testFilter() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetA = ss.getSheetByName("SheetA");
var sheetB = ss.getSheetByName("SheetB");
var sheetC = ss.getSheetByName("SheetC");
var sheetD = ss.getSheetByName("SheetD");
var sheetE = ss.getSheetByName("SheetE");
var sheetF = ss.getSheetByName("SheetF");
var valuesA = sheetA.getRange('A2:AC400').getValues();
var valuesB = sheetB.getRange('A2:AC400').getValues();
var valuesC = sheetC.getRange('A2:AC400').getValues();
var valuesD = sheetD.getRange('A2:AC400').getValues();
var valuesE = sheetE.getRange('A2:AC400').getValues();
var valuesF = sheetF.getRange('A2:AC400').getValues();
for (var rowa in valuesA)
{
var deleteRowa = false;
for (var rowb in valuesB)
{
if(valuesA[rowa].join() == valuesB[rowb].join())
deleteRowa = true;
}
if(deleteRowa == true){
sheetA.deleteRow(rowa)
}
SpreadsheetApp.flush();
}
for (var rowa in valuesA)
{
var deleteRowa = false;
for (var rowc in valuesC)
{
if(valuesA[rowa].join() == valuesC[rowc].join())
deleteRowa = true;
}
if(deleteRowa == true){
sheetA.deleteRow(rowa)
}
SpreadsheetApp.flush();
}
for (var rowa in valuesA)
{
var deleteRowa = false;
for (var rowd in valuesD)
{
if(valuesA[rowa].join() == valuesD[rowd].join())
deleteRowa = true;
}
if(deleteRowa == true){
sheetA.deleteRow(rowa)
}
SpreadsheetApp.flush();
}
for (var rowa in valuesA)
{
var deleteRowa = false;
for (var rowe in valuesE)
{
if(valuesA[rowa].join() == valuesE[rowe].join())
deleteRowa = true;
}
if(deleteRowa == true){
sheetA.deleteRow(rowa)
}
SpreadsheetApp.flush();
}
for (var rowa in valuesA)
{
var deleteRowa = false;
for (var rowf in valuesF)
{
if(valuesA[rowa].join() == valuesF[rowf].join())
deleteRowa = true;
}
if(deleteRowa == true){
sheetA.deleteRow(rowa)
}
SpreadsheetApp.flush();
}
}This gives an Exception: out of bounds error on the first attempt of filtering. Does the deleterows() function work for picking out specific rows? Or am I going to have to throw it into a new list, then delete all rows from original sheet, then insert the list?
function testFilter() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const shts = ["SheetB", "SheetC", "SheetD", "SheetE", "SheetF"];
const A1rg = "A2:AC400";
let uA = [];//building a unique flat array of row join
ss.getSheets().filter(sh => ~shts.indexOf(sh.getName())).forEach(sh => {
sh.getRange(A1rg).getValues().forEach(r => {
let j = r.join("");
if (!~uA.indexOf(j)) {
uA.push(j);
}
});
});
let d = 0;
const shA = ss.getSheetByName("SheetA");
shA.getRange(A1rg).getValues().forEach((r, i) => {
if (~uA.indexOf(r.join(""))) {
shA.deleteRow(i + 2 - d++);
}
});
}
This version runs quite a bit faster for me
function testFilter() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const shts = ["Sheet1", "Sheet2", "SheetD", "SheetE", "SheetF"];
const A1rg = "A2:AC400";
let uA = [];
ss.getSheets().filter(sh => ~shts.indexOf(sh.getName())).forEach(sh => {
sh.getRange(A1rg).getValues().forEach(r => {
let j = r.join("");
if (!~uA.indexOf(j)) {
uA.push(j);
}
});
});
let d = 0;
const shA = ss.getSheetByName("Sheet0");
shA.getRange(2, 1, shA.getLastRow() - 1, shA.getLastColumn()).getValues().forEach((r, i) => {
if (~uA.indexOf(r.join(""))) {
shA.deleteRow(i + 2 - d++);
}
});
}
Here is about the same algo as Cooper's but, I hope, it's a little bit more readable.
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var names = ['SheetA', 'SheetB', 'SheetC', 'SheetD', 'SheetE' ]; // etc
const get_array_from_sheet = sheet_name =>
ss.getSheetByName(sheet_name).getRange('A2:AC400').getValues()
.map(x => x.join('')).filter(String);
var [sheetA, ...sheets_etc] = names.map(n => get_array_from_sheet(n));
sheets_etc = sheets_etc.flat();
var rows_to_delete = sheetA.filter(x => sheets_etc.includes(x))
.map(x => sheetA.indexOf(x) + 2);
rows_to_delete.reverse().forEach(x => ss.getSheetByName('SheetA').deleteRow(x));
}