How can I append rows from a sheet to another sheet in GoogleSheets, based on a columns criteria

Question

Does anyone know how can I Transpose/copy a row from one sheet to another sheet in Googlesheets?

Here is what I want to do: so I have a google spreadsheet file. It has 2 tabs/sheets.

Now, sheet 1 has a column named: "total."
Now, what I got to do is to see if this column: "total" in sheet 1 is empty or not.
If its empty, then I should copy the entire row which has the empty column in sheet 1, to sheet 2.

I know I must use appendRow() function. But that requires hard-coded values. something like this: sheets.appendRow({"a", "b"}) but the row I am trying to append is entered by the users.

What I need to do is to see which row has a empty cell at column I or column 8.

Then take that entire row and copied it over. So far, I was able to get the row which has the empty column at column I. But I can't figure out how to append it/copy it.

I would appreciate your input on this. Because I haven't been able to find any resources that relates to what I need exactly.

Here's the code I have so far.

 function insertRecord() {
 var sss = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];
 var values = sss.getDataRange().getValues();
 var getRangeRow2 = sss.getDataRange().getRow()[5];
 var getRangeCol2 = sss.getDataRange().getColumn()[1];
 var placeHolder = sss.insertRowsBefore(5, 1);

 var ss = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

 var values = ss.getDataRange().getValues();
 var n = 4;
 var c = 8;
 var getRangeRow = ss.getDataRange().getRow()[n];
 var getRangeCol = ss.getDataRange().getColumn()[c];


 for(n=4;n<values.length;n++){  // Loops through all values in column 8
 var column = values[n][c];   // n represents my rows in the getSheets()[0] // number 8 is the column 8.
 var row = values[n]; 
 var col = values[c];
 if(column == null)
 {  // Currently this will get me the rows where the column I has no value in it. This is what its supposed to do. << I am happy with this.
   if(placeHolder)
   {
     placeHolder = ss.getDataRange(getRangeRow, getRangeCol).copyTo(getRangeRow2, getRangeCol2);
   // NOTE: SORRY NOT YELLING. BUT THIS PART IS THE PROBLEM I AM HAVING. IT 
   // WON'T WORK. IT WONT COPY THE ROW TO THE OTHER SHEET.
   }
Logger.log(row, col);
  }
}
}

what's with the insertRows and how is it relevant to the question? See minimal reproducible example — TheMaster
@TheMaster Good questions. So to the purpose of that is to insert one empty row before the first record on top of the page. Like after the first record on row 4, I want a empty row where i can insert the new record from sheet 1. I hope I haven't messed this up though. Also as far as the getRow()[5] goes, the first record on sheet 1 aka the first tab, starts at row 5. So, I wanted to grab records starting from that row. — mrRay
Please refer official documentation. getRow() returns Integer and not a array. Therefore it can't be indexed with [5]. Get it? Any way the below answer should work. — TheMaster
@TheMaster just so if you happen to use the below script in future. Yeah. it did work. Thank you all. — mrRay

Alessandro Alessandro · Accepted Answer · 2020-03-30T15:33:14

Append row does not require hardcoded values, you can get those values from the Sheet1. I would proceed getting all the rows from your Sheet1 and copying the ones with the empty 8th column in the Sheet2. Here is a working snippet:

function insertRecord() {
   var sheet_2 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];

   var sheet_1 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

   var rows = sheet_1.getDataRange().getValues(); // Gets the rows with data

   rows
   .filter(row => row[8] == '') // Gets the rows that have the 8th col empty
   .map(row_w_empty8col => sheet_2.appendRow(row_w_empty8col)); // Appends the rows to the second sheet

}

References:

.appendRow(rowContents)

How can I append rows from a sheet to another sheet in GoogleSheets, based on a columns criteria

1 Answers

References: