Forum Discussion

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

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

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

Copper Contributor

Oct 23, 2023

ok it's work correctly, but Action recorder not.

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
  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.
  - SergeiBaklan
    Diamond Contributor
    Oct 23, 2023
    Jula78
    
    Not sure what is strange, depends on what you'd like to do. You r script is equivalent of
    
    function main(workbook: ExcelScript.Workbook) { let selectedsheet = workbook.getActiveWorksheet(); selectedsheet .getRange("A:A") .insert(ExcelScript.InsertShiftDirection.right) .insert(ExcelScript.InsertShiftDirection.right) selectedsheet .getRange("H:I") .moveTo(selectedsheet.getRange("A:B")) selectedsheet.getRange("H:I") .delete(ExcelScript.DeleteShiftDirection.left); }
    
    If you'd like to shift more columns it could be done by the cycle.