moveColumns not moving to correct location when column number is lower than target-CodePudding

I am currently using the moveColumns() function to move a column whenever it is not placed as column J (10). When the column is moved to a higher position, the function correctly moves the column back to J. However, when the column is placed at a lower position, the column is moving to I instead.

My script is as follows currently:

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataName = "Data";

  var dataSheet = ss.getSheetByName(dataName); // Data sheet

  var barOnePos = 10;
  var barOnePosA1Notation = columnToLetter(barOnePos);
  var barOneName = "Royalty Splits / Rights Management";

  var currBarOnePos = getColumnId(dataSheet, barOneName);
  var currBarOnePosA1Notation = columnToLetter(currBarOnePos)   "1";

  Logger.log(currBarOnePos);
  Logger.log(currBarOnePosA1Notation);

  if (currBarOnePos != barOnePos) {
    SpreadsheetApp.getUi().alert("\""   barOneName   "\" should be column "   barOnePosA1Notation   ". This will be fixed automatically.", SpreadsheetApp.getUi().ButtonSet.OK);
    dataSheet.moveColumns(dataSheet.getRange(currBarOnePosA1Notation), barOnePos);
  }
}

Could anyone explain why this is the case?

CodePudding user response：

Modification points:

I thought that in the case of When the column is moved to a higher position, the function correctly moves the column back to J., the columns below the column "J" are not moved. By this, the column is moved to the correct position.
On the other hand, in the case of when the column is placed at a lower position, the column is moving to I instead., for example, when the column "A" is moved to the column "J", one column is reduced from the columns below the column "J".
- I thought that the reason of your issue is due to this.

In order to move the column to the correct position, how about modifying your script as follows?

From:

dataSheet.moveColumns(dataSheet.getRange(currBarOnePosA1Notation), barOnePos);

To:

var srcCol = dataSheet.getRange(currBarOnePosA1Notation);
dataSheet.moveColumns(srcCol, srcCol.getColumn() < barOnePos ? barOnePos   1 : barOnePos);