google spreadsheet swap two cells

in #google5 years ago

tool -> script editor
copy paste the code below
and save it
then
tool -> macros -> import
import swapCells
then
tool -> macros -> manage macros
set shortcut from 0 - 9
and use it freely!

function swapCells() {
  const activeSheet = SpreadsheetApp.getActiveSheet();
  const selection = activeSheet.getSelection();
  const rangeCount = selection.getActiveRangeList().getRanges().length;
  var firstRange = null;
  var secondRange = null;
  
  if(rangeCount > 1) {
    if(rangeCount > 2) {
      return showAlert("can't select more than two cells");
    }
    firstRange = selection.getActiveRangeList().getRanges()[0];
    secondRange = selection.getActiveRangeList().getRanges()[1];
    if(isMoreThanOneCell(firstRange) || isMoreThanOneCell(secondRange)){
      return showAlert("can't select more than two cells");
    }
    swapCellValues(firstRange, secondRange);
  }
  else if(rangeCount == 1) {
    const ranges = selection.getActiveRange();
    if(!isTwoCells(ranges)){
      return showAlert("can't select more than two cells");
    }
    if(ranges.getNumColumns() > 1){
      firstRange = ranges.getCell(1, 1);
      secondRange = ranges.getCell(1, 2);
      swapCellValues(firstRange, secondRange);
    }else if(ranges.getNumRows() > 1){
      firstRange = ranges.getCell(1, 1);
      secondRange = ranges.getCell(2, 1);
      swapCellValues(firstRange, secondRange);
    }
  }
}
function swapCellValues(firstRange, secondRange){
  const v1 = firstRange.getValue();
  const v2 = secondRange.getValue();
  firstRange.setValue(v2);
  secondRange.setValue(v1);
}
function isMoreThanOneCell(range){
  const countRows = range.getNumRows();
  const countColumns = range.getNumColumns();
  if(countRows + countColumns > 2){
    return true;
  }
  return false;
}
function isTwoCells(range){
  const countRows = range.getNumRows();
  const countColumns = range.getNumColumns();
  if(countRows + countColumns == 3){
    return true;
  }
  return false;
  
}

function showAlert(str) {
  var ui = SpreadsheetApp.getUi(); // Same variations.
  var result = ui.alert(str)
}
Sort:  

Congratulations @iamswain! You received a personal award!

Happy Birthday! - You are on the Steem blockchain for 2 years!

You can view your badges on your Steem Board and compare to others on the Steem Ranking

Vote for @Steemitboard as a witness to get one more award and increased upvotes!