CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsrangerowgoogle-sheets-macros
user8652270
user8652270

Reputation:

How can row 1 (first row of sheet) be excluded in getActiveRange() when it is in active range (Google Sheets)?

How is it possible to exclude the top row or cell of sheet from getActiveRange() if it exists? How can shift() be integrated? A selection is only made within a column to define the area to be used in column 3.

function x() {
  var s = SpreadsheetApp.getActive().getActiveSheet();
  var lastColumn = s.getActiveRange().getLastColumn();
  var activeRange = s.getActiveRange().offset(0, -lastColumn+3); // column 3
  if (activeRange.getRow() == 1) {
    s.getRange('F1').copyTo(activeRange, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  }
  else {
  s.getRange('F1').copyTo(activeRange, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  }
}

If cells (adjacent) are activated within a column and only row 1 (title row), 5, 8 and 9 are displayed because of a filter, for example. Then F1 (x) is to be inserted into rows 5, 8 and 9 using copyTo(). It is not yet clear to me how I can integrate the suggested solutions. Here are some examples:

1:

function getRangeMinusHeaders(range) {
  var height = range.getHeight();
  if (height == 1) {
    return null;
  }
  var width = range.getWidth();
  var sheet = range.getSheet();
  return sheet.getRange(1, 1, height-1, width);
}

function x() {
  var s = SpreadsheetApp.getActive().getActiveSheet();
  var lastColumn = s.getActiveRange().getLastColumn();
  var range = s.getActiveRange().offset(0, -lastColumn+3); // column 3
  s.getRange('F1').copyTo(getRangeMinusHeaders(range), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
}

2:

function x() {
  var s = SpreadsheetApp.getActive().getActiveSheet();
  var lastColumn = s.getActiveRange().getLastColumn();
  const activeRange = s.getActiveRange().offset(0, -lastColumn+3); // column 3
  if (activeRange.getRow() == 1) {
    activeRange.getValues();
    activeRange.shift();
    activeRange.forEach(function(row, index) {
      s.getRange('F1').copyTo(row, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
    })
  }
  else {
  s.getRange('F1').copyTo(activeRange, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  }
}

Upvotes: 1

Views: 2749

Answers (3)

Aaron Dunigan AtLee
Aaron Dunigan AtLee

Reputation: 2072

To get the Range object corresponding to a given Range, minus a single header row:

function getRangeMinusHeaders(range) {
  var height = range.getHeight();
  if (height == 1) {
    return null;
  }
  var width = range.getWidth();
  var sheet = range.getSheet();
  return sheet.getRange(1, 1, height-1, width);
}

Upvotes: 3

Neven Subotic
Neven Subotic

Reputation: 1429

Just use shift when you getValues(), that removes the first row and leaves all others rows. 

const activeRange = s.getActiveRange().getValues();
activeRange.shift(); // this removes the first row
activeRange.forEach( function(row, index){
  // do something here with each row
  console.log(row)
})

Upvotes: 2

Anees Hameed
Anees Hameed

Reputation: 6544

Yes, you can.

function x() {
  var s = SpreadsheetApp.getActive().getActiveSheet();
  var activeRange = s.getActiveRange()
  if(activeRange.getRow() == 1){
    // If first row is 1, then resize the range.
    var height = activeRange.getHeight();
    activeRange = activeRange.offset(1, 0, height-1);
  }

  Logger.log(activeRange.getA1Notation())
}

Upvotes: 0

Related Questions