How to remove a row after it's been moved by this function?

user_14584605 0
Viewed 60

It basically moves rows from one sheet to another, based on a filter keyword. How do I then remove the moved rows from the original sheet?

I've tried, but my very small amount of knowledge in JavaScript and Apps Script hasn't been to much use.

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Ark1');
  const filter_sh = ss.getSheetByName('Filter');
  const data = sh.getRange('A1:B'+sh.getLastRow()).getValues();
  const sh_names = filter_sh.getRange('A1:A'+filter_sh.getLastRow()).getValues().flat();
  sh_names.forEach(s=>{
    if(!ss.getSheetByName(s)){
    ss.insertSheet().setName(s);}
    let sheet = ss.getSheetByName(s);   
    let f_data = data.filter(r=>r[0].includes(s));
    if(f_data.length>0){
    sheet.getRange(sheet.getLastRow()+1,1,f_data.length,f_data[0].length).setValues(f_data);}
}); 
}

For anyone interested, here is the file: Link

google-apps-script google-sheets javascript
1 Answers

Explanation:

  • The basic idea is to store the rows we would like to delete into an array and then delete that rows backwards (in descending order).

  • The difference between your current solution and this one, is that now we need to iterate also over the data, to determine which rows needs to be deleted.

Solution:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Ark1');
  const filter_sh = ss.getSheetByName('Filter');
  const sh_names = filter_sh.getRange('A1:A'+filter_sh.getLastRow()).getValues().flat();
  var deleteRows = [];

  sh_names.forEach(s=>{
    if(!ss.getSheetByName(s)){
    ss.insertSheet().setName(s);}
    var copyData = [];
    var new_sheet = ss.getSheetByName(s);
    var tempData = sh.getRange('A1:B'+sh.getLastRow()).getValues();
    tempData.reverse().forEach((r,i)=>{ 
        if(r[0].includes(s)){
        deleteRows.push(tempData.length-i);
        copyData.push(r);}});
   if(copyData.length>0){
   new_sheet.getRange(new_sheet.getLastRow()+1,1,copyData.length,copyData[0].length).setValues(copyData);}
}); 
deleteRows = deleteRows.filter((x, i, a) => a.indexOf(x) === i).sort((a, b) => b - a);
deleteRows.forEach(r=>sh.deleteRow(r));
}
user_112252910
Related