CODEWITHSUNDEEP
excelgoogle-apps-scriptgoogle-sheets
brashScript
brashScript

Reputation: 15

Move to the top function in google spreadsheets

This function takes a currently selected cell or range and moves it to the top of the "list", so to speak. Super useful in todo-lists. Thanks to Brian P for the help! 

function arrayShift(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var activeRange = sheet.getActiveRange();
  var toDoRange = sheet.getRange("A2:A9"); //the range of your todo list. 
  var howMany = activeRange.getNumRows();
  var topCell = activeRange.getRow();
  var rangeTop = toDoRange.getRow();
  var index = topCell - rangeTop; //gives the index of the selected cell
  var rangeArray = toDoRange.getValues();
  rangeArray.splice(index, howMany)

  if ( howMany == 1 ) {
    rangeArray.unshift(activeRange.getValues()[0]); 
  } else { // handle re-inserting multiple row values
    var values = activeRange.getValues();
    for ( var i = values.length - 1 ; i >= 0; i--) {
      rangeArray.unshift(values[i]);
    }
  }
  //Logger.log(rangeArray)
  toDoRange.clearContent();
  toDoRange.offset(0,0,rangeArray.length).setValues(rangeArray);
};

Upvotes: 1

Views: 61

Answers (1)

Bryan P
Bryan P

Reputation: 5051

try...

function arrayShift(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var activeRange = sheet.getActiveRange();
  var toDoRange = sheet.getRange("A2:A9"); //the range of your todo list. 
  var howMany = activeRange.getNumRows();
  var topCell = activeRange.getRow();
  var rangeTop = toDoRange.getRow();
  var index = topCell - rangeTop; //gives the index of the selected cell
  var rangeArray = toDoRange.getValues();
  rangeArray.splice(index, howMany)

  if ( howMany == 1 ) {
    rangeArray.unshift(activeRange.getValues()[0]); // "remove brackets"
  } else { // handle re-inserting multiple row values
    var values = activeRange.getValues();
    for ( var i = values.length - 1 ; i >= 0; i--) {
      rangeArray.unshift(values[i]);
    }
  }
  //Logger.log(rangeArray)
  toDoRange.clearContent();
  toDoRange.offset(0,0,rangeArray.length).setValues(rangeArray);
};

Upvotes: 1

Related Questions