Trabajo para un importante fabricante de motores y necesito ayuda con un script de hojas de Google que necesito escribir (¿asumo que un script es el camino a seguir?)

Tengo un archivo de hojas de Google que se usa para monitorear problemas y mejoras planificadas para múltiples departamentos. El libro de trabajo tiene varias pestañas que necesito para copiar y pegar datos de alguna manera dentro de él.

He incluido un archivo de muestra para tratar de ayudar a explicarlo, es un archivo bastante complicado de tratar y explicar ... Haré mi mejor esfuerzo. Parece que necesito usar un script, pero soy nuevo en el uso de scripts.

Enlace a la hoja

Explicación del archivo: Semanalmente actualizo la hoja con los datos más recientes en la hoja "Datos maestros", esta hoja alimenta la hoja "eléctrica" mediante una consulta. El propietario del departamento de actualizaciones eléctricas actualiza la hoja "entrada eléctrica" con sus acciones y tiempos y esto también se alimenta en la hoja "eléctrica". La hoja "eléctrica", a su vez, alimenta la hoja de "datos de mejora" que alimenta el gráfico.

Está organizado de esta manera, de modo que cuando se agregan nuevos números y cambia el orden, los comentarios siguen en la hoja "eléctrica", y el propietario solo necesita actualizar la hoja "entrada eléctrica" con comentarios que aún no ha hecho.

Qué me gustaría probar y hacer: Me gustaría poder ejecutar un script que filtre o extraiga cualquier cosa en la hoja eléctrica con # N / A en su contra (lo que significa que la combinación de 3 celdas no se ha encontrado en la hoja de entrada) y copiar y pegar solo esos elementos en la hoja de "entrada eléctrica" en la siguiente línea disponible que no esté completa. En el archivo real hay varios departamentos, por lo que las variaciones del script deberán ejecutarse para cubrir los diferentes departamentos.

¿Alguna idea o solución por favor? gracias por adelantado.

Mate

-2
Matt 25 sep. 2017 a las 21:25

2 respuestas

La mejor respuesta

Actualicé la hoja de cálculo que compartiste con el código relevante. .
Puede ejecutar la secuencia de comandos a través del menú Add Ons->Master Data Utils->Clean Up Keys.
El menú se creó en la función OnOpen de esta secuencia de comandos.

Tenga en cuenta que hay un par de requisitos a continuación para que funcione el script. (Ya se han resuelto en el SS compartido)

  1. Listas de departamentos y detalles útiles para procesar todos los datos como se muestra a continuación.
  2. Debería haber una NamedRange llamada Department_List que sería la celda superior izquierda de la tabla que se muestra arriba.

enter image description here

  • Column to Clean -> Action Summary
    es la columna que usaríamos para buscar #N/A
  • Display Sheet Name -> Electrical
    es donde estaría #N/A.
  • Input Sheet Name -> Electricalinput
    es el nombre de la hoja que se actualizará.
  • Copy Cols From(Disp) -> B:D las columnas que se copiarían
  • Similar a d, pero aquí es donde se copiaría sata
    Pero por el momento, el código no usa esto y simplemente se pega en la primera celda disponible en la columna B

Aquí está el código que intenté, avíseme de cualquier sugerencia o modificación que lo haga más fácil de usar.

function cleanComponentList(curSS) {

  Logger.log("Init script cleanComponentList 'Department_List'")  
  curSS = curSS || SpreadsheetApp.getActiveSpreadsheet()

  var deptListStart = curSS.getRangeByName("Department_List")
  if(deptListStart==null){
    Logger.log("This script cannot be used without the named range 'Department_List'")
    return
  }

  var dept = deptListStart.offset(1,0)
  while( ! dept.isBlank() ){

    Logger.log("Started processing " + dept.getDisplayValue() + " Dept.")

    //Get Department Sheet
    var deptSht = curSS.getSheetByName(dept.offset(0,1).getValue())

    //Get Column that has Action Summary in department sheet
    var actSummCol=deptSht.getRange(2, 1)  
    //Get Department Input Sheet (Target Sheet where we need to copy the final values to
    var deptInputSht = curSS.getSheetByName(dept.offset(0,2).getValue())    

    //Find column with the "Column Header"/"Summary" in which we search #N/A
    while( ! (actSummCol.getDisplayValue() === dept.offset(0,3).getDisplayValue())){
      var tmp = actSummCol.getDisplayValue()
      var actColRng = actSummCol.getA1Notation()
      actSummCol=actSummCol.offset(0,1)
    }

    var actSummColAddress = actSummCol.getA1Notation();
    Logger.log("Found key \"" + dept.offset(0,3).getDisplayValue() + "\" for " + dept.getDisplayValue() + " @ " + actSummColAddress)

    var errRows = []
    //Get all rows that have error Action Summary
    var lastRow = deptSht.getLastRow()
    for(nRow=1;nRow<lastRow;nRow++){
      //There should be a better of checking #N/A
      if(actSummCol.offset(nRow,0).getDisplayValue().equals("#N/A")){
        errRows.push(nRow+actSummCol.getRow())
      }
    }
    Logger.log("Got " + errRows.length + " error rows for " + dept.getDisplayValue() + " Dept.")

    //Get Cell where data append should start from.
    var deptInputLastAvailableRow = deptInputSht.getRange("B1")
    while(!deptInputLastAvailableRow.isBlank() || deptInputLastAvailableRow.isPartOfMerge()){
      deptInputLastAvailableRow=deptInputLastAvailableRow.offset(1,0)
    }
    Logger.log(dept.getDisplayValue() + " Dept." + " Input will be updated from " + deptInputLastAvailableRow.getA1Notation())

    //Copy CDE from the filtered rows to Department Input Sheet
    var srcCols=deptSht.getRange(dept.offset(0,4).getDisplayValue())
    //There should be a better way of iterating. for-of throws syntax error!!!
    for(idx in errRows){
      var row = errRows[idx];
      var lc = srcCols.getLastColumn()
      var fc = srcCols.getColumn()
      var errKeyRangeAddress=srcCols.getCell(row,1).getA1Notation() + ":" + srcCols.getCell(row,lc-fc + 1).getA1Notation()
      var errKeyRange=deptSht.getRange(errKeyRangeAddress) 
      errKeyRange.copyTo(deptInputLastAvailableRow)
      deptInputLastAvailableRow=deptInputLastAvailableRow.offset(1,0)
    }
    Logger.log("Copied " + errRows.length + " entries to Sheet \"" + deptInputSht.getName() + "\"")
    Logger.log("Finished Processing " + dept.getDisplayValue() + " Dept.")
    dept=dept.offset(1,0)
  }
}

function onOpen(){
  //Choose which way you want your menu to appear.
  //SpreadsheetApp.getActiveSpreadsheet().addMenu("Monitor", [{name:"Clean Up Keys",functionName:"cleanComponentList"}])
  SpreadsheetApp.getUi().createAddonMenu().addItem("Clean Up Keys", "cleanComponentList").addToUi()
}
0
kaza 26 sep. 2017 a las 06:55

Este es un script de copia simple para ayudarlo a comenzar. copyTo está registrado aquí. El resto de los comandos se pueden encontrar en la misma documentación.

Después de haber hecho algunos de sus propios scripts, le resultará mucho más fácil crear los suyos propios en lugar de pasar todo el tiempo copiando scripts.

function copyPasteSelectedRows() 
{
    var ss=SpreadsheetApp.getActive();
    var sh1=ss.getSheetByName('Sheet2');
    var sh0=ss.getSheetByName('Sheet1');
    var rg0=sh0.getDataRange();
    var vA=rg0.getValues();
    for(var i=1;i<vA.length;i++)
    {
      if(vA[i][10]==1)//condition to meet to copy
      {
        var src=sh0.getRange(i+1,1,1,vA[i].length);//source range
        var tar=sh1.getRange(sh1.getLastRow() + 1,1,1,vA[i].length);//target range
        src.copyTo(tar);
      }
    }
}

Esta es Sheet1:

enter image description here

Esta es la Hoja2 después de la copia:

enter image description here

0
Cooper 25 sep. 2017 a las 21:57