How can i transport specific columns in a row?(on Edit function)-CodePudding

I have this code where it uses a checkbox confirmation to transport and entire row to another sheet. The thing is, i don't want to transport the entire row, only some columns of data. This columns would be column A to E and column J. Is there a way to improve the code to do that function? (Code below)

    function onEdit(event) { // COD ATUALIZADO 22/11/22

  var ss = SpreadsheetApp.getActiveSpreadsheet(); // Ativa planilha
  var s = event.source.getActiveSheet(); // Página origem do evento
  var r = event.source.getActiveRange(); // Página fim do evento
  var row = r.getRow();

  if(s.getName() == "DEMANDAS" && r.getColumn() == 1 && r.getValue() == true && s.getRange(row,5).getValue() == "CORRETIVA") {
    var numColumns = s.getLastColumn(); // Número de colunas
    var targetSheet = ss.getSheetByName("EQUIPAMENTOS PARA CORRETIVA"); // Planilha alvo
    var target = targetSheet.getRange(targetSheet.getLastRow()   1, 1); // Local alvo
    s.getRange(row, 1, 1, numColumns).moveTo(target); // Insere no local alvo
    s.deleteRow(row); // Exclui a origem
    target.offset(0, numColumns).setValue(new Date()); // Inclui a data pós checkbox
  }
  if(s.getName() == "DEMANDAS" && r.getColumn() == 1 && r.getValue() == true) { // Se condições OK(IDA)
    var numColumns = s.getLastColumn(); // Número de colunas
    var targetSheet = ss.getSheetByName("LIBERADOS"); // Planilha alvo
    var target = targetSheet.getRange(targetSheet.getLastRow()   1, 1); // Local alvo
    s.getRange(row, 1, 1, numColumns).moveTo(target); // Insere no local alvo
    s.deleteRow(row); // Exclui a origem
    target.offset(0, numColumns).setValue(new Date()); // Inclui a data pós checkbox

  } else if(s.getName() == "LIBERADOS" && r.getColumn() == 1 && r.getValue() == false) { // Se condições OK(VOLTA)
    var numColumns = s.getLastColumn(); // Número de colunas
    var targetSheet = ss.getSheetByName("DEMANDAS"); // Planilha alvo
    var target = targetSheet.getRange(targetSheet.getLastRow()   1, 1); // Local alvo
    s.getRange(row, 1, 1, numColumns).moveTo(target); // Insere no local alvo
    s.deleteRow(row); // Exclui a origem
  }

}

CodePudding user response：

I believe your goal is as follows.

You want to copy the values of columns "A" to "E" and "J" instead of all columns by modifying your showing script.

When I saw your script, I thought that your script might be able to become simpler a little. In this case, how about the following modification?

Modified script:

function onEdit(event) {
  var ss = event.source;
  var s = ss.getActiveSheet();
  var r = event.range;
  var row = r.rowStart;
  var sheetName = s.getSheetName();
  var value = r.getValue();
  var numColumns = s.getLastColumn();
  var targetSheet;
  if (sheetName == "DEMANDAS" && r.columnStart == 1 && value === true) {
    targetSheet = ss.getSheetByName(r.offset(0, 4).getValue() == "CORRETIVA" ? "EQUIPAMENTOS PARA CORRETIVA" : "LIBERADOS");
  } else if (sheetName == "LIBERADOS" && r.columnStart == 1 && value === false) {
    targetSheet = ss.getSheetByName("DEMANDAS");
  }
  if (!targetSheet) return;
  var [a, b, c, d, e, , , , , j] = s.getRange(row, 1, 1, numColumns).getValues()[0];
  targetSheet.appendRow([a, b, c, d, e, j, new Date()]);
  targetSheet.getRange(targetSheet.getLastRow(), 1).insertCheckboxes();
  s.deleteRow(row);
}

In this modification, the values of columns "A" to "E" and "J" and new Date() are appended to the sheet you want. In your showing script, new Date() is put to target.offset(0, numColumns).setValue(new Date()). But, from your expected situation, I guessed that you might want to put new Date() in the last column of the appended value. So, I used targetSheet.appendRow([a, b, c, d, e, j, new Date()]);.

References:

CodePudding user response：

I actually managed to do it.

Here it goes

    function onEdit(event) { // COD ATUALIZADO 22/11/22

  var ss = SpreadsheetApp.getActiveSpreadsheet(); // Ativa planilha
  var s = event.source.getActiveSheet(); // Página origem do evento
  var r = event.source.getActiveRange(); // Página fim do evento
  var row = r.getRow();


  if(s.getName() == "DEMANDAS" && r.getColumn() == 1 && r.getValue() == true && s.getRange(row,5).getValue() == "CORRETIVA") {
    var numColumns = s.getLastColumn(); // Número de colunas
    var targetSheet = ss.getSheetByName("EQUIPAMENTOS PARA CORRETIVA"); // Planilha alvo
    var target = targetSheet.getRange(targetSheet.getLastRow()   1, 1); // Local alvo
    s.getRange(row, 1, 1, numColumns).moveTo(target); // Insere no local alvo
    
    s.deleteRow(row); // Exclui a origem
    target.offset(0, numColumns).setValue(new Date()); // Inclui a data pós checkbox
  }
  if(s.getName() == "DEMANDAS" && r.getColumn() == 1 && r.getValue() == true) { // Se condições OK(IDA)
    var numColumns = s.getLastColumn(); // Número de colunas
    var targetSheet = ss.getSheetByName("LIBERADOS"); // Planilha alvo
    var target = targetSheet.getRange(targetSheet.getLastRow()   1, 1);
    var target2 = targetSheet.getRange(targetSheet.getLastRow()   1, 11); // Local alvo
    s.getRange(row, 1, 1, 5).moveTo(target);
    s.getRange(row, 10, 1, 1).moveTo(target2); // Insere no local alvo
    s.deleteRow(row); // Exclui a origem
    target.offset(0, numColumns).setValue(new Date()); // Inclui a data pós checkbox
    }
}