Forum Discussion

Jrolle2022's avatar
Jrolle2022
Copper Contributor
Jun 16, 2022
Solved

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

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 multip...
  • Lorenzo's avatar
    Jun 16, 2022

    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 https://docs.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.range?view=office-scripts has method https://docs.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.range?view=office-scripts#excelscript-excelscript-range-replaceall-member(1) and after looking at the examples given for the https://docs.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.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 https://stackoverflow.com/questions/tagged/typescript) 

Resources