Cut and paste data range to bottom of another sheet

Question

I'm new to google app script and not sure how to solve this issue.

I have two sheets, "Main" and "Extract". Throughout the day, data is pasted into the "Extract" sheet, cleaned up (script removes certain columns and removes rows where a value in column A also exist in column A of the "Main" sheet), and then added to the "Main" sheet, which contains all of the cleaned up extracts pulled throughout the day.

I am trying to write a script that will take all of the cleaned up data in the "Extract" sheet and cut and paste it beneath the last row with data in the "Main" sheet.

Once cleaned up the "Extract" sheet always has 8 columns (A-H), but the amount of rows varys each time. I do not need the header or column 8 (H) pasted to the "Main" sheet, only A2:G (stopping at the last row with data).

So far this is all I have been able to come up with (it is likely very wrong)

var data = sheet.getDataRange().getValues()
.offset(0, 0, 1);

Answer 1

I believe this is what you are trying to do.

var spread = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spread.getSheetByName("Extract");
// skip row 1 (header) and beyond column G
var range = sheet.getRange(2,1,sheet.getLastRow()-1,7);
sheet = spread.getSheetByName("Main");
var rows = sheet.getRange(1,1,sheet.getLastRow(),1).getValues();
// search for first blank row column 1
for( var i=0; i<rows.length; i++ ) {
  if( rows[i][0] === "" ) {
    var offset = sheet.getRange(1,1).offset(i,0);
    range.copyTo(offset);
    break;
  }
}