Office Script - copy paste as value when formula generates answer

Question

In Excel 365 I'm trying to create a sheet which generates a unique ID which doesn't change, for each row/item in a table.

To do this I am going to use a formula to generate a unique ID but to prevent it from changing later, I would like to write an Office Script which, when run, copies and pastes the cell contents as a value, to remove the formula, once the cell value has been generated. HOWEVER, I want the copy and paste to only work on cells in the column which have a value that isn't blank (e.g., so for empty columns which may be filled later, the formula doesn't get removed).

So far I've got

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // Paste to range B10:B109 on selectedSheet from range B10:B109 on selectedSheet
    selectedSheet.getRange("B10:B109").copyFrom(selectedSheet.getRange("B10:B109"), ExcelScript.RangeCopyType.values, false, false);
}

which copies and pastes the whole table column, but I'm unsure how to add in the 'IF' statement (so if the value is not blank, then do it, if the value is blank, do not copy paste as value).

Would anyone be able to help me write this? Apologies, it's been a really long time since I last dabbled in VBA.

Equally, if you have a better way of generating a unique ID that doesn't change per row, that isn't a manual task, please let me know!

taller · Accepted Answer · 2024-08-20 11:57:33Z

0

Pls try.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // get the range object B10:B109
    const dataRng = selectedSheet.getRange("B10:B109")
    // get cell contents
    const dataVals = dataRng.getTexts();
    for (let i = 0; i < dataVals.length; i++) {
        if (dataVals[i][0].length) {
            // convert formula to static text
            dataRng.getCell(i, 0).setFormula(dataVals[i][0]);
            // highlight cell, just for debugging
            dataRng.getCell(i, 0).getFormat().getFill().setColor("#ffff00"); 
        }
    }
}

edited Aug 20, 2024 at 11:57

answered Aug 20, 2024 at 11:15

taller

19.7k2 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Office Script - copy paste as value when formula generates answer

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related