Find and Replace within a known range using typescript in the Script Editor

[Jrolle2022]

Hi, All!

I have a script within excel that returns csv data as json for use in a Power Automate flow. After a couple of tests, I discovered that one of the columns includes line breaks for multiple assigned tags from the source platform. Because this script is part of a larger automation, I want to be able to remove the line breaks and use a comma delimiter in the cell instead without manually finding and replacing the line breaks. So I have created a second script that I plan to splice into the main script once I have the working code. I have tried a couple of things so far and the following snippet is the closet I've gotten. 

function main(workbook: ExcelScript.Workbook) {
    // Your code here
    let sheet = workbook.getActiveWorksheet();
    let breakCol = sheet.getRange("O2:O25");
    let cellToOverwrite = breakCol.find("\n", { completeMatch: false });
    while (cellToOverwrite) {
      cellToOverwrite.setValue(",");
      cellToOverwrite = breakCol.find("no change", { completeMatch: false });
    }
}

I realize that I will need to add an iterator to account for the number of records, but where I am stuck at is figuring out which methods are most appropriate for traditional find and replace. Or if the ones here are what I'd need, how should I format them to arrive at my goal?

Any insight would be greatly appreciated!

Entries before script:

Entries after script(ran 5 times to simulate result once iterator is added):

[#2 · Accepted solution]

Hi @Jrolle2022 

I'm not a JavaScript dev. and don't have access to TypeScript with my 365 subscription so can't test the following that's only my understanding after reading the doc.

The Range interface has method replaceAll and after looking at the examples given for the ReplaceCriteria the following should work:

function main(workbook: ExcelScript.Workbook)
{
    let sheet = workbook.getActiveWorksheet();
    let breakCol = sheet.getRange("O2:O25");
    breakCol.replaceAll("\n", ",", { completeMatch: false });
}

or, given that completeMatch defaults to false:

function main(workbook: ExcelScript.Workbook)
{
    let sheet = workbook.getActiveWorksheet();
    let breakCol = sheet.getRange("O2:O25");
    breakCol.replaceAll("\n", ",");
}

Hope this helps a bit & your feedback will be appreciated, whatever it is

(It's my understanding that TypeScript issues should be raised at stackoverflow) 

[#3]

@L z. You are amazing! That was just what I needed. I figured the "find" method wasn't going to cut it, but really wasn't aware of a programmatic alternative. Thanks a ton for your help!

[#4]

@Jrolle2022 

Glad this worked as per the doc. Thanks for providing feedback & marking the solution (can help those who search this site)

Nice day...