Office Script to convert formulas to values

Cross-posted at https://stackoverflow.com/questions/...nerates-answer --6SJ

Hi

I'm trying to create a form which generates a unique ID which doesn't change, for each row/item.

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 create a macro which 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 have

Please Login or Register  to view this content.

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!

Welcome to the Forum excellearner167 !

Please review our guidelines, which recommend code tags for code. I have added them because it's your first post.

Also note that this code is not VBA, it is Office Script. Therefore I have updated your title to clarify it.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.

Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

I have added the link because you are a new member. Please notify us if you have posted the same question anywhere else.

Does this work for you?

PHP Code: 

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // Paste to range B10:B109 on selectedSheet from range B10:B109 on selectedSheet

    let x= selectedSheet.getRange("B10:B109").getSpecialCells(ExcelScript.SpecialCellType.formulas);
    if (x == undefined){
        console.log("No formulas");
        return;
    }
    let rng = selectedSheet.getRange(x.getAddress());
    let rMax = rng.getRowCount();    
    let vals = rng.getValues()
    for (let r=0; r < rMax; r++){            
        if (vals[r][0] !== "") {
            var c = rng.getCell(r, 0);
            c.setValue(vals[r][0]);
        }
    }
}