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


enter image description here

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
  • @pnuts yes, inclusive of Start and End. The copy should be static. Thank you for your help. – hmzfier Jul 4 '17 at 9:51

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.