google-sheetsgoogle-apps-script

Copying data from a sheet to another with conditions

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.

DATA THAT I WANT TO COPY

WHERE I WANT TO PASTE

Solution

  • 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 sheet1

    sheet2 enter image description here

    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:

    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 sample

    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);
}

    Output:

    output