CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Nate
Nate

Reputation: 23

How to shift cells down in specified columns? Google Sheets

I'm trying to use this script (Is there an easy way to shift down cells? - Google App Script) to shift 2 cells down at a time in specified columns only when that specified cell is edited. The issue I'm having is that regardless of which cell is edited even outside the column the specified cell shifts. Is there a way to fix this?

this is how I'm using it:`

var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0];

var range = sheet.getRange(3,1).insertCells(SpreadsheetApp.Dimension.ROWS); sheet.getRange(3,2).insertCells(SpreadsheetApp.Dimension.ROWS);`

Upvotes: 0

Views: 999

Answers (2)

Nate
Nate

Reputation: 23

Final code after including fix by "A-Change".

function onEdit(e){
  if (e.range.getColumn() === 1) {
      var ss = e.source; 
      var sheet = ss.getSheets()[0];
      var range = sheet.getRange(4,2).insertCells(SpreadsheetApp.Dimension.ROWS);
      sheet.getRange(4,1).insertCells(SpreadsheetApp.Dimension.ROWS);
  }
}

{
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "1","2") { //checks that we're on Sheet titled "1"&"2" or not
var r = s.getActiveCell();
if( r.getColumn() == 1 ) { //checks that the cell being edited is in column specified example 1=A
var nextCell = r.offset(0, 1);

nextCell.setValue(new Date());
  
}
}
}

Upvotes: 0

a-change
a-change

Reputation: 666

I think you should have onEdit function somewhere there and this is where you could specify a condition to check only for changes in a specific column:

function onEdit(e) {
  if (e.range.getColumn() === 3) {
      var ss = e.source; 
      var sheet = ss.getSheets()[0];
      var range = sheet.getRange(3,1).insertCells(SpreadsheetApp.Dimension.ROWS);
      sheet.getRange(3,2).insertCells(SpreadsheetApp.Dimension.ROWS);
  }
}

What happens here:

  • onEdit function runs when any cell in your spreadsheet is edited
  • it receives e object as an argument, this object has a reference of which cell was edited (e.range)
  • you can check in which column/row the edit was made by using e.range.getColumn()/e.range.getRow() in an if condition

More to read:

Upvotes: 1

Related Questions