google apps script - sheets - Eliminar valores de fila en más de 1 hoja si existe en otra hoja

google spreadsheet macro

Question

El código a continuación es de una respuesta de esta publicación con respecto a la copia de valores de fila a una nueva hoja si existe en otras hojas.

Ahora, ¿qué sucede si en lugar de copiar los valores duplicados en la hoja 3, quiero eliminarlos de las hojas 1 y 2 si existen en la Hoja 3. Con la misma hoja de cálculo , tengo 3 hojas. El valor único que se comparará en las primeras 2 hojas es la primera columna, "ID NÚMERO".

Dados los valores, 784 | John Steep | IT Department 784 | John Steep | IT Department 784 | John Steep | IT Department , que existe en las 3 hojas, el mismo valor de fila debe eliminarse en la Hoja 1 y 2 y conservar el mismo valor en la Hoja 3.

function copyRowtoSheet3() { 
  var s1 = SpreadsheetApp.openById("ID").getSheetByName(''Sheet1'');
  var s2 = SpreadsheetApp.openById("ID").getSheetByName(''Sheet2''); 
  var s3 = SpreadsheetApp.openById("ID").getSheetByName(''Sheet3''); 
  var values1 = s1.getDataRange().getValues();
  var values2 = s2.getDataRange().getValues();
  var resultArray = [];
  for(var n=0; n < values1.length ; n++){
    var keep = false;
    for(var p=0; p < values2.length ; p++){
      Logger.log(values1[n][0]+'' =? ''+values2[p][0]);
      if( values1[n][0] == values2[p][0] && values1[n][3] == values2[p][4]){
        resultArray.push(values1[n]);
        Logger.log(''true'');
        break ;// remove this if values are not unique and you want to keep all occurrences...
      }
    }
  }  
  s3.getRange(+1,1,resultArray.length,resultArray[0].length).setValues(resultArray);
}

Parece que no puede encontrar la solución correcta. Intenté varios scripts pero no pude hacerlo funcionar.

Gracias por cualquier consejo / sugerencia.

Answer 1

Aunque la otra respuesta funciona (no probé, pero creo que sí) usa muchas llamadas a la hoja de cálculo y puede ser lenta si tienes muchos datos.

Es posible obtener este resultado usando solo matrices (si no necesita mantener el formato de hoja y / o fórmulas).

El enfoque es ligeramente diferente, ya que es más fácil mantener los datos en lugar de eliminarlos.

Seguramente hay muchas soluciones posibles, a continuación se muestra la que probé: Creé una matriz especial que contiene solo la primera columna de la hoja 3 para simplificar la búsqueda duplicada.

function removeDupsInOtherSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s1 = ss.getSheetByName("Sheet1").getDataRange().getValues();  
  var s2 = ss.getSheetByName("Sheet2").getDataRange().getValues(); 
  var s3 = ss.getSheetByName("Sheet3").getDataRange().getValues();  
  // iterate s3 and check in s1 & s2 if duplicate values exist
  var nS1 = [];
  var nS2 = [];
  var s3Col1 = [];// data in column1 of sheet3
  for(var n in s3){
    s3Col1.push(s3[n][0]);
  }
  for(var n in s1){ // iterate sheet1 and test col 1 vs col 1 in sheet3
    var noDup1 = checkForDup(s1[n],s3Col1)
    if(noDup1){nS1.push(noDup1)};// if not present in sheet3 then keep
  } 
  for(var n in s2){  // iterate sheet2 and test col 1 vs col 1 in sheet3
    var noDup2 = checkForDup(s2[n],s3Col1)
    if(noDup2){nS2.push(noDup2)};// if not present in sheet3 then keep
  }
  Logger.log(nS1);// view result
  Logger.log(nS2);
  ss.getSheetByName("Sheet1").getDataRange().clear();// clear and update sheets
  ss.getSheetByName("Sheet2").getDataRange().clear();
  ss.getSheetByName("Sheet1").getRange(1,1,nS1.length,nS1[0].length).setValues(nS1);
  ss.getSheetByName("Sheet2").getRange(1,1,nS2.length,nS2[0].length).setValues(nS2);
}

function checkForDup(item,s){
  Logger.log(s+'' = ''+item[0]+''  ?'')
    if(s.indexOf(item[0])>-1){
      return null;
    }
  return item;
}

Answer 2

Sheet1

ID NUMBER   NAME        DEPARTMENT
784         John Steep  I.T.
901         Liz Green   H.R.

Sheet2

ID NUMBER   NAME        DEPARTMENT
784         John Steep  I.T.
653         Bo Gore     Marketing

Sheet3

ID NUMBER   NAME        DEPARTMENT
784         John Steep  I.T.
999         Frank White Sales
121         Abid Jones  Engineering
901         Liz Green   H.R.

Guión

function main() {
  var ss = SpreadsheetApp.openById("ID");

  var s1 = ss.getSheetByName("Sheet1");  
  var s2 = ss.getSheetByName("Sheet2");  
  var s3 = ss.getSheetByName("Sheet3");  

  var idCol = 1;  // Assuming location of ID column is same in all sheets.

  var s1RowCount = s1.getLastRow();  

  for (var i = 2; i <= s1RowCount; i++) {  // Start at var i = 2 to skip the 
                                           // first row containing the header.
    var id = s1.getRange(i, idCol, 1, 1).getValue();
    deleteDuplicates(s2, id);
    deleteDuplicates(s3, id);
  }
}

function deleteDuplicates(sheet, id) {
  var idCol = 1; // Assuming location of ID column is same in all sheets.
  var rowCount = sheet.getLastRow();
  for (var i = 2; i <= rowCount; i++) {
    var data = sheet.getRange(i, idCol, 1, 1).getValue();
    if (data === id) {
      // Use this to test out the function.
      Logger.log("Duplicate of ID " + id + " in sheet " + 
          sheet.getSheetName() + " at row " + i);
      // Uncomment the next line when ready.
      // sheet.deleteRow(i);
    }
  }
}

Salida de registro

[14-11-04 09:16:04:551 PST] Duplicate of ID 784 in sheet Sheet2 at row 2
[14-11-04 09:16:04:587 PST] Duplicate of ID 784 in sheet Sheet3 at row 2
[14-11-04 09:16:04:727 PST] Duplicate of ID 901 in sheet Sheet3 at row 5