Transferring Rows between Sheets via a like Identifier

Question

Evening everyone!

I asked this about a week back, but I think the thread got lost in the ether. We came close, but I'm trying to create a function where "Transfer a range of Rows from sheet 1 to sheet 2. Sheet 1 has order IDs in column E. G will have =unique to show me all current order IDs, with check boxes next to each unique reference. Check the box next to which ones you want to CUT over > Select a menu run add on > Run Script > all Rows from A:E that match the desired ID are moved".

[Picture Reference]

Sheet Reference

function onEdit(e) {
  e.source.toast('Entry')
  const sh = e.range.getSheet();
  if(sh.getName() == "Reference" && e.range.columnStart == 8 &&  e.range.rowStart > 1 && e.value == "TRUE") {
    e.source.toast('Gate1')
    let rg = sh.getRange(e.range.rowStart,1,1,5)
    let vs = rg.getValues();
    const osh = e.source.getSheetByName("Processing");
    osh.getRange(osh.getLastRow() + 1,1,1,5).setValues(vs);
    rg.deleteCells(SpreadsheetApp.Dimension.ROWS);
    e.range.setValue("FALSE");
  }
}

Here is what we had so far. Please let me know if anyone can help, thank you!

Answer 1

To get all rows that match the unique ID whose checkbox was ticked, use Array.filter(), like this:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEdit(e) function in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet.'
      + 'See https://stackoverflow.com/a/63851123/13045193.'

    );
  }
  moveRowsByUniqueId_(e);
}


/**
* Triggers on a checkbox click and moves rows that match a unique ID.
*
* @param {Object} e The onEdit() event object.
*/
function moveRowsByUniqueId_(e) {
  let sheet;
  if (e.value !== 'TRUE'
    || e.range.rowStart <= 1
    || e.range.columnStart !== 8
    || (sheet = e.range.getSheet()).getName() !== 'Reference') {
    return;
  }
  e.source.toast('Moving rows...');
  const uniqueId = e.range.offset(0, -1).getValue();
  const range = sheet.getRange('A2:E');
  const values = range.getValues();
  const targetSheet = e.source.getSheetByName('Processing');
  const _matchWithId = (row) => row[4] === uniqueId;
  const valuesToAppend = values.filter(_matchWithId);
  if (uniqueId && valuesToAppend.length) {
    appendRows_(targetSheet, valuesToAppend);
    range.clearContent();
    const remainingValues = values.filter((row) => !_matchWithId(row));
    range.offset(0, 0, remainingValues.length, remainingValues[0].length)
      .setValues(remainingValues);
    e.source.toast(`Done. Moved ${valuesToAppend.length} rows.`);
  } else {
    e.source.toast('Done. Found no rows to move.');
  }
  e.range.setValue(false);
}

For that to work, you will need to paste the appendRows_() and getLastRow_() utility functions in your script project.

Answer 2

It work almost like asked but :

• it's using a personal lib (available below)
• didn't make the part realtiv of removing range and aggregate result, I hope i can add it to the lib some day. However, empty cell are fill with -
• for an obscure reason, it doesn't like the TRUE/FALSE cell, but work like a charm with 1/0 or any other texte value, regex, ...
• Additional error handling are to be added if not any match or others possibilites

function onEdit(e){
  console.log(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Reference").getRange("H3").getValue())
  var tableReference = new TableWithHeaderHelper(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Reference").getRange("A1").getDataRegion());
  var tableReferenceId = new TableWithHeaderHelper(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Reference").getRange("G11").getDataRegion());
  var tableProcessing = new TableWithHeaderHelper(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Processing").getRange("A1").getDataRegion());

  // get value
  var id = tableReferenceId.getTableWhereColumn("Move to Sheet").matchValue(1).getWithinColumn("Unique Filter").cellAtRow(0).getValue(); 

  var tableWithinId = tableReference.getTableWhereColumn("Shipment ID").matchValue(id)
  for(var i=0 ; i < tableWithinId.length() ; i++){
    var rangeRowWithinId = tableWithinId.getRow(i);
    tableProcessing.addNewEntry(rangeRowWithinId);
    for(var cell in rangeRowWithinId.getRange()) cell.setValue("-");
  }
  //reset value
  tableReferenceId.getTableWhereColumn("Move to Sheet").matchValue(1).getWithinColumn("Move to Sheet").cellAtRow(0).setValue(0)
}

See below the app script file you need to create in order to use this utils function: https://github.com/SolannP/UtilsAppSsript/blob/main/UtilsGSheetTableHelper.gs