Question

0

140

Views

Finding row Indexes based on ID Cell

I am trying to copy specific row based on ID, but for now i'm strugling and getting only 1 row copied to another sheet, what i need to do is when i select ID to copy all the rows with the same ID into another sheet

let array= [];
const catchID= (searchID) => {  
  for(let i=0; i < rangeID.length; i++) {
    if(rangeID[i][0] === trazeniID){
      array.push(rangeID[i]);
    }
  }
  return array;
}
catchID(searchID);

const copyToAnotherSheet= () => {
  
  const selectedCell = sheet.getActiveCell();
  const selectedCellRowIndex = selectedCell.getRow(); // get selected row 

  const sourceRange = sheet.getRange(selectedCellRowIndex,1,1,sheet.getLastColumn());
  

  const targetSheet = ss.getSheetByName('Another Sheet');
  const targetRange = targetSheet.getRange(targetSheet.getLastRow()+1,1,1);

  for(let i=0; i < array.length; i++) {
    if(array[i] == searchID) {
      if(statusCell.getValue() === 'Payed' || statusCell.getValue() === 'Reinvest') {
        sourceRange.copyTo(targetRange);
        
      }
    }else{
       SpreadsheetApp.getActiveSpreadsheet().toast('Something went wrong')
    }
  }
}

picture bellow shows for example: based on ID A277 should get copied all rows to another sheet which depends on the row index. I hope i explained my situation well.

about 4 years ago · Juan Pablo Isaza

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-07-04T13:52:07.909Z

I believe your goal is as follows.

When the columns "B" and "L" are "A277" and "Payed" or "Reinvest", you want to copy the rows from the source sheet to the destination sheet using Google Apps Script.

Modification points:

In your script, sheet, searchID and statusCell are not declared.
Unfortunately, I cannot understand rangeID. So I couldn't understand array.
- From your script, I thought that array might be the values from the source sheet.

When these points are reflected in a script, it becomes as follows.

Modified script:

const copyToAnotherSheet = () => {
  const searchID = "A277"; // Please set the search text.
  const srcSheetName = "Sheet1"; // Please set the source sheet name.
  const dstSheetName = "Sheet2"; // Please set the destination sheet name.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [srcSheet, dstSheet] = [srcSheetName, dstSheetName].map(s => ss.getSheetByName(s));
  const [,...srcValues] = srcSheet.getDataRange().getValues();
  const copyValues = srcValues.filter(r => r[1] == searchID && (r[11] == 'Payed' || r[11] == 'Reinvest'));
  dstSheet.getRange(dstSheet.getLastRow() + 1, 1, copyValues.length, copyValues[0].length).setValues(copyValues);
}

Reference:

filter()

0

140