Copy range from specific values to top of another sheet

Question

I want to copy everything from the range "Start" to "End" to the top row of another sheet (Sheet2).

Sheet1:

The data between "Start" to "End" can be any number of rows.

This is my current code. It copies everything from Sheet1 to Sheet2 but it can't copy from the specified range, and also doesn't append it to the top row.

function copy() {

  var sss = SpreadsheetApp.openById('1-y8PK1al2G0K90t_v6gKmbrM6W4BWpwMLaHa93qjWrs'); 
  var ss = sss.getSheetByName('Sheet1'); 

  //Get full range of data
  var SRange = ss.getDataRange();

  //get A1 notation identifying the range
  var A1Range = SRange.getA1Notation();

  //get the data values in range
  var SData = SRange.getValues();

  var tss = SpreadsheetApp.openById('1-y8PK1al2G0K90t_v6gKmbrM6W4BWpwMLaHa93qjWrs');
  var ts = tss.getSheetByName('Sheet2'); 

  //set the target range to the values of the source data
  ts.getRange(A1Range).setValues(SData);
}

Answer 1

I would loop over the rows of SData, looking for Start and End in the first column. The script uses the first appearance of Start and the last appearance of End, in case there are several. Their positions are recorded in startRow and endRow. Then the desired range is obtained (which includes the rows with Start and End themselves). All the logic with copying should happen in the last if block, which is executed only if the range is successfully found (both startRow and endRow are defined) and is nonempty (startRow > endRow).

var SRange = ss.getDataRange();
var SData = SRange.getValues();
var startRow, endRow;
for (var i = 0; i < SData.length; i++) {
  if (SData[i][0] == "Start" && !startRow) {
    startRow = i + 1; 
  }
  if (SData[i][0] == "End") {
    endRow = i + 1; 
  }
}
if (startRow && endRow && startRow > endRow) {
   var rangeToCopy = ss.getRange(startRow, 1, endRow - startRow + 1, sRange.getWidth());
   var dataToCopy = rangeToCopy.getValues();
   // copy the data wherever you want
}

Answer 2

This will copy the rows between the Start and End markers, not including the marker rows:

function copyToTop() {
  const sourceSheet = SpreadsheetApp
    .openById('1-y8PK1al2G0K90t_v6gKmbrM6W4BWpwMLaHa93qjWrs')
    .getSheetByName('Sheet1');
  const targetSheet = SpreadsheetApp
    .openById('1-y8PK1al2G0K90t_v6gKmbrM6W4BWpwMLaHa93qjWrs')
    .getSheetByName('Sheet2');
  let rowStart;
  let rowEnd;
  const data = sourceSheet
    .getDataRange()
    .getValues()
    .filter((row, index) => {
      if (!rowStart) {
        rowStart = row[0] === 'Start' ? index + 1 : undefined;
        return false;
      }
      if (!rowEnd) {
        rowEnd = row[0] === 'End' ? index + 1 : undefined;
        return !rowEnd;
      }
      return index + 1 < rowEnd;
    });
  if (!rowStart || !rowEnd) {
    throw new Error('Could not find the Start and End markers.');
  }
  targetSheet
    .insertRowsBefore(1, data.length)
    .getRange(1, 1, data.length, data[0].length)
    .setValues(data);
}

To include the Start and End marker rows, use this:

      if (!rowStart) {
        rowStart = row[0] === 'Start' ? index + 1 : undefined;
        return !!rowStart;
      }
      if (!rowEnd) {
        rowEnd = row[0] === 'End' ? index + 1 : undefined;
        return true;
      }

If both sheets are in the active spreadsheet, you can replace the two openById() calls with getActive().

Answer 3

Try this:

function copy() {
  const sss = SpreadsheetApp.openById('1-y8PK1al2G0K90t_v6gKmbrM6W4BWpwMLaHa93qjWrs'); 
  //const sss = SpreadsheetApp.getActive();
  const ssh = sss.getSheetByName('Sheet1');
  const start = ssh.createTextFinder("Start").findNext().getRow();
  const end = ssh.createTextFinder("End").findNext().getRow();
  const sr = start + 1;
  const numrows = end - start - 1
  const svs = ssh.getRange(sr, 1, numrows, ssh.getLastColumn()).getValues();
  const tss = SpreadsheetApp.openById('1-y8PK1al2G0K90t_v6gKmbrM6W4BWpwMLaHa93qjWrs');
  //const tss = SpreadsheetApp.getActive();
  const tsh = tss.getSheetByName('Sheet2');
  let lr = tsh.getLastRow();
  if (lr > 0) {
    tsh.insertRowsBefore(1, numrows);
  }
  tsh.getRange(1, 1, svs.length, svs[0].length).setValues(svs);
}