Forum Discussion

Jula78's avatar
Jula78
Copper Contributor
Oct 23, 2023
Solved

ACTION SCRIPT RECORDER - MOVE COLUMN

Try to register a simple column movement like this:   function main(workbook: ExcelScript.Workbook) {   let selectedSheet = workbook.getActiveWorksheet();   // Insert cut cells from C:C on select...
Excel for web
  • SergeiBaklan's avatar
    SergeiBaklan
    Oct 23, 2023

    Jula78 

    moveto method only copy values from one range to another, it keeps source range empty after that.

    After shifting column A to the right you shift all other columns as well. Thus column C becomes column D. You may move values from it to new column A and after that remove that column

    function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();

  selectedSheet
    .getRange("A:A")
    .insert(ExcelScript.InsertShiftDirection.right);
  selectedSheet
    .getRange("D:D")
    .moveTo(selectedSheet
    .getRange("A:A"));
  selectedSheet
    .getRange("D:D")
    .delete(ExcelScript.DeleteShiftDirection.left)
}
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 23, 2023

Jula78 

moveto method only copy values from one range to another, it keeps source range empty after that.

After shifting column A to the right you shift all other columns as well. Thus column C becomes column D. You may move values from it to new column A and after that remove that column

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();

  selectedSheet
    .getRange("A:A")
    .insert(ExcelScript.InsertShiftDirection.right);
  selectedSheet
    .getRange("D:D")
    .moveTo(selectedSheet
    .getRange("A:A"));
  selectedSheet
    .getRange("D:D")
    .delete(ExcelScript.DeleteShiftDirection.left)
}
  • Jula78's avatar
    Jula78
    Copper Contributor
    Oct 23, 2023
    ok it's work correctly, but Action recorder not.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Oct 23, 2023

      Jula78 

      Action recorder is good enough as the starting point. The code returned is not always optimal and, as we see, could give wrong result.

      • Jula78's avatar
        Jula78
        Copper Contributor
        Oct 23, 2023

        ok yes sure, i noticed that !!!

        what happens if I move other columns in sequence on the code?
        I tried repeating the code on another column to be moved.

        selectedsheet.getRange("A:A").insert(ExcelScript.InsertShiftDirection.right);
        selectedsheet.getRange("G:G").moveTo(selectedSheet.getRange("A:A"));
        selectedsheet.getRange("G:G").delete(ExcelScript.DeleteShiftDirection.left);

        selectedsheet.getRange("B:B").insert(ExcelScript.InsertShiftDirection.right);
        selectedsheet.getRange("H:H").moveTo(selectedSheet.getRange("B:B"));
        selectedsheet.getRange("H:H").delete(ExcelScript.DeleteShiftDirection.left);


        it happens something strange.

Resources