CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user14523871
user14523871

Reputation: 35

Copy a specific row, that mets a condition, form one sheet to another

I made a similar request a few days ago using the following link:

How to copy a specific row from one sheet to another (Google Apps Script)

Now I have new requirements for which I need your help:

  1. the script should copy specific rows that have value "No" in column T from one sheet to another sheet. It is important that it copies the values and the formatting.

  2. the complete row should be copied.

  3. after successful copying, the row should be deleted in the sourcesheet.

Can you please help me? Thank you very much!

Below you can find @Marios solution so far. At this point again many thanks!

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName("[Overview] All_Cases");
  const tarSheet = ss.getSheetByName("OPS_FUNNEL");
  const data = srcSheet.getDataRange().getValues().filter(r=>r[21]=='No').map(r => [r[0]]);
  var tarlast = tarSheet.getRange("A:A").getValues().filter(String).length;
  
  if (data.length>0){
    tarSheet.getRange(tarlast+1,1,data.length,1).setValues(data);  
  }

Upvotes: 1

Views: 130

Answers (1)

Marios
Marios

Reputation: 27390

Explanation:

You can store the deleted row indexes inside the filter function itself and then use the classical approach to delete rows backwards.

Solution:

Column T has an array index of 19, not 21. Be careful with this.

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName("[Overview] All_Cases");
  const tarSheet = ss.getSheetByName("OPS_FUNNEL");
  const rowsD = [];
  const data = srcSheet.getDataRange().getValues()
               .filter((r,i)=>{
                       let val = r[19]=='No';
                       if(val){
                          rowsD.push(i+1);
                          return val;
                       }                      
                });
  if (data.length>0){
    tarSheet.getRange(tarSheet.getLastRow()+1,1,data.length,data[0].length).setValues(data);
    for (var i = rowsD.length - 1; i>=0; i--) {
      srcSheet.deleteRow(rowsD[i]); 
    }
  };
}

Upvotes: 2

Related Questions