Copying data from a sheet to another with conditions-CodePudding

I have been trying to copy some data from a sheet to another but I have been running into some trouble.

I have to copy data and stop copying until the scripts finds an empty space, and I have to paste this data into another sheet where there's blank space (available space).

This is the code I have so far:

function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("sheet1");
  var pasteSheet = ss.getSheetByName("sheet2");
  var source = copySheet.getRange(11,1,1,10);
  var destination = pasteSheet.getRange(1,1,1,10);

  for (i = 1; i < 20; i  ) {

    if (destination.isBlank() == true) {
      destination = pasteSheet.getRange(i, 1, 1, 10);
      source = copySheet.getRange(i   10, 1, 1, 10);
      source.copyTo(destination);
    } else {
      destination = pasteSheet.getRange(i, 1, 1, 10);
    }
  }
}

It recognizes that the destination has an empty space, although it doesn't really paste it. The for (i = 1; i <20; i ) is for testing purposes.

CodePudding user response：

If your data doesn't have any blank rows in between the first row and last non-empty row, then you can use this:

Sample Data:

sheet1

sheet2

Script:

function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("sheet1");
  var pasteSheet = ss.getSheetByName("sheet2");
  // get last rows of each sheet
  var sLastRow = copySheet.getLastRow();
  var dLastRow = pasteSheet.getLastRow();

  // get data from sheet1 from row 11 to last row
  var source = copySheet.getRange(11,1,sLastRow - 10,10).getValues();  
  // paste data to sheet2's last row
  pasteSheet.getRange(dLastRow   1,1,source.length,source[0].length).setValues(source);
}

Output:

Alternative:

If you have blank rows in between your sheet1, you can filter the values in sheet1.

Sample Data:

blank row 13 in sheet1

function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("sheet1");
  var pasteSheet = ss.getSheetByName("sheet2");
  // get last rows of each sheet
  var sLastRow = copySheet.getLastRow();
  var dLastRow = pasteSheet.getLastRow();

  // get data from sheet1 from row 11 to last row
  // exclude rows with blank values in all columns
  var source = copySheet.getRange(11,1,sLastRow - 10,10).getValues()
                        .filter(row => row.filter(col => !col).length < row.length);    
  // paste data to sheet2's first blank row
  pasteSheet.getRange(dLastRow   1,1,source.length,source[0].length).setValues(source);
}