Excel OfficeScript Macro - Moving a Column over to the right

Question

Hello I am trying to write something that will allow me to copy an entire column and paste it to the right of it. I was able to do this but how can I update it to automatically keep going to the right? I understand I will need to offset but I do not know where to include it. Also would it be possible to remove anything that does not have a formula attached to it? I have included what I have bellow.

This is what I currently have using the record option.

function main(workbook: ExcelScript.Workbook) {
     let selectedSheet = workbook.getActiveWorksheet();
     // Paste to range BH:BH on selectedSheet from range BG:BG on selectedSheet
     selectedSheet.getRange("BH:BH").copyFrom(selectedSheet.getRange("BG:BG"), ExcelScript.RangeCopyType.all, false, false);
    // Clear ExcelScript.ClearApplyTo.contents from range BH54:BH57 on selectedSheet
    selectedSheet.getRange("BH54:BH57").clear(ExcelScript.ClearApplyTo.contents);
    // Clear ExcelScript.ClearApplyTo.contents from range BH59 on selectedSheet
    selectedSheet.getRange("BH59").clear(ExcelScript.ClearApplyTo.contents);
    // Clear ExcelScript.ClearApplyTo.contents from range BH98:BH109 on selectedSheet
    selectedSheet.getRange("BH98:BH109").clear(ExcelScript.ClearApplyTo.contents);
    // Clear ExcelScript.ClearApplyTo.contents from range BH132:BH134 on selectedSheet
    selectedSheet.getRange("BH132:BH134").clear(ExcelScript.ClearApplyTo.contents);
}

I appreciate any help.

Answer 1

Assuming the source data is in the last column, simplify the code for clearing destination cells.

Please provide details if your data layout is different.

function main(workbook: ExcelScript.Workbook) {
    // Get active worksheet
    let selectedSheet = workbook.getActiveWorksheet();
    // Get the last used column
    let lastColRange = selectedSheet.getUsedRange().getLastColumn();
    // Get the destination range by offsetting one column to the right
    let destRange = lastColRange.getOffsetRange(0, 1);
    // Copy column
    destRange.copyFrom(lastColRange, ExcelScript.RangeCopyType.all, false, false);
    // Define a list of row ranges to be cleared
    let rowList = ["54:57", "59:59", "98:109", "132:134"];
    // Loop through and clear the intersection with the destination range
    for (let i = 0; i < rowList.length; i++) {
        let cellRange = selectedSheet.getRange(rowList[i]).getIntersection(destRange);
        if (cellRange) {
            // Clear the contents
            cellRange.clear(ExcelScript.ClearApplyTo.contents);
        }
    }
    destRange.getEntireColumn().getFormat().autofitColumns();
}

---

Question: if it was possible to copy the entire column over and have it remove anything that doesn't include a formula

Note: When a user inputs '=TEST directly into a cell, the cell will display that text (w/o single quotation mark). However, the code treats any cell starting with '=' as a formula. Upated code is posted at the end.

function main(workbook: ExcelScript.Workbook) {
    // Get active worksheet
    let selectedSheet = workbook.getActiveWorksheet();
    // Get the last used column
    let lastColRange = selectedSheet.getUsedRange().getLastColumn();
    // Get the destination range by offsetting one column to the right
    let destRange = lastColRange.getOffsetRange(0, 1);
    // Copy column
    destRange.copyFrom(lastColRange, ExcelScript.RangeCopyType.all, false, false);
    for (let i = 0; i < destRange.getCellCount(); i++) {
        let cellRange = destRange.getRow(i);
        if (!cellRange.getFormula().toString().startsWith("=")) {
            // Clear the contents of non-formula cells
            cellRange.clear(ExcelScript.ClearApplyTo.contents);
        }
    }
    destRange.getEntireColumn().getFormat().autofitColumns();
}

Update

A more reliable approach to get all cells which don't have formula

function main(workbook: ExcelScript.Workbook) {
    // Get active worksheet
    let selectedSheet = workbook.getActiveWorksheet();
    // Get the last used column
    let lastColRange = selectedSheet.getUsedRange().getLastColumn();
    // Get the destination range by offsetting one column to the right
    let destRange = lastColRange.getOffsetRange(0, 1);
    // Copy column
    destRange.copyFrom(lastColRange, ExcelScript.RangeCopyType.all, false, false);
    let constCells = destRange.getSpecialCells(ExcelScript.SpecialCellType.constants);
    if(constCells){
        constCells.clear(ExcelScript.ClearApplyTo.contents);
    }
}