Excel Script ReplaceAll ignore formulas

[djmitz6]

Greetings,

I'm currently running an excel script where I am replacing all single quote characters with two single quote characters consecutively so that I can push the data in a table over to an SQL server utilizing Power Automate. One issue that I am experiencing is that when utilizing the below script it also replaces the characters inside of formulas:

// Replace SQL Reserved Characters
selectedSheet.replaceAll("'", "''", {
completeMatch: false,
matchCase: false
})

Example of how the formula in one of the cells originally existed:

=XLOOKUP(E4,'Sheet2'!E:E,'Sheet2'!F:F)

Example of how the formulas get formatted after running the script:

=XLOOKUP(E4,''Sheet2''!E:E,''Sheet2''!F:F)

Is there anyway to get the replaceAll function to ignore characters inside of formulas? I'm trying to eliminate the need to individually iterate through each cell as this is resource intensive. I also thought about just copying the range and pasting back the values only, but I feel this will also be resource intensive.

[#2]

Hi @djmitz6 

Post deleted: misunderstand on how this works - refer to 1st post from @SergeiBaklan below please

[#3]

@djmitz6 

Here's how you can modify your Excel Script to replace all single quotes while ignoring characters inside formulas:

Option 1: Using FilterBy and ReplaceAll:

1. Filter formulas: Use filterBy to filter out rows containing formulas:

JavaScript

const filteredRange = selectedSheet.getRange("A1:B10").filterBy(row => !row.getRange("A1").hasFormula());

Replace A1:B10 with the actual range you want to filter and A1 with the cell containing the formula you want to check.

1. Replace single quotes: Apply replaceAll to the filtered range:

JavaScript

filteredRange.replaceAll("'", "''", {
  completeMatch: false,
  matchCase: false
});

1. Optionally, replace back in the original range:

JavaScript

// If needed, replace back in the original range after modification
selectedSheet.getRange("A1:B10").values = filteredRange.getValues();

Option 2: Using a custom function:

1. Define a custom function:

JavaScript

function replaceExceptInFormulas(text, searchValue, replaceValue) {
  // Split the text into parts based on formulas
  const parts = text.split(/={1,2}[^=]+={0,2}/);

  // Replace in non-formula parts
  for (let i = 0; i < parts.length; i++) {
    if (!parts[i].startsWith("=")) {
      parts[i] = parts[i].replaceAll(searchValue, replaceValue);
    }
  }

  // Join the parts back together
  return parts.join("");
}

1. Apply the function:

JavaScript

selectedSheet.getRange("A1:B10").values = selectedSheet.getRange("A1:B10").values.map(row => row.map(cell => replaceExceptInFormulas(cell, "'", "''")));

Explanation:

• Option 1: This approach filters out rows containing formulas using filterBy before applying replaceAll to avoid modifying formulas.
• Option 2: This option defines a custom function replaceExceptInFormulas that splits the text into parts based on formulas and replaces characters only in non-formula parts.

Both options achieve the desired result of replacing single quotes while preserving formulas. Choose the option that best suits your preference and coding style.

Additional notes:

• Ensure you replace A1:B10 with the actual range you want to modify.
• Adjust the formula checking logic in Option 1 if your formulas have different patterns.
• Consider testing the script on a small sample of data before applying it to your entire dataset.

[#4]

@Lorenzo 

That doesn't work such way. Selecting special cells we split entire range on range areas, replaceAll can't be applied to RangeAreas object.

However, idea is great. We may iterate each area and make replacements within it

function main(workbook: ExcelScript.Workbook) {
    workbook
    .getActiveWorksheet()
    .getUsedRange()
    .getSpecialCells(ExcelScript.SpecialCellType.constants)
    .getAreas()
    .map(a => a.getUsedRange().replaceAll("'", "''", {}))
}

Iterating cell by cell in initial range is less preferable from performance point of view.

[#5]

@SergeiBaklan 

Selecting special cells we split entire range on range areas, replaceAll can't be applied to RangeAreas object

OK, I didn't read enough. Thanks for fixing my bad + sharing the appropriate code

[#6]

@SergeiBaklan 

Was initially confused by

but got it. Super powerful

I'm gonna delete my initial post - makes no sense to keep in it this discussion

[#7]

@Lorenzo 

I'm surprized you generated such solution without Automate on the board.

Just in case. Since that will be used with Power Automate which has no idea about active worksheet, we need to define the worksheet explicitly; or iterate all worksheets if replacements shall be done in each of them. Like

function main(workbook: ExcelScript.Workbook) {

    workbook
        .getWorksheets()
        .forEach(sheet => (
            sheet.getUsedRange() !== undefined ) ? 
            sheet
                .getUsedRange()
                .getSpecialCells(ExcelScript.SpecialCellType.constants)
                .getAreas()
                .map(a => a.getUsedRange().replaceAll("'", "''", {}))
             : ''
        )
}

In addition we check if the sheet is empty and skip it, otherwise script stops on it.

[#8]

@SergeiBaklan 

... without Automate on the board (very frustrating). Thanks again for sharing, very educative even if I can't play with it. Let's see if I understood... could line 12 be something like?

: console.log('${sheet} has no UsedRange')

and, is : <something> mandatory (otherwise error msg)?

[#9]

@Lorenzo 

We may use console.log here, but no sense if the script runs by Power Automate.

You variant returns

${sheet} has no UsedRange

if change on

console.log( sheet.getName() +  ' is empty')

it returns something like

Sheet3 is empty

===

Yes, without FALSE condition it returns

Line #13: ':' expected.

However, if instead of shortened "if" to use regular "if" we may skip it

function main(workbook: ExcelScript.Workbook) {

  workbook
    .getWorksheets()
    .forEach(sheet => {
      if( sheet.getUsedRange() !== undefined ) {
        sheet
          .getUsedRange()
          .getSpecialCells(ExcelScript.SpecialCellType.constants)
          .getAreas()
          .map(a => a.getUsedRange().replaceAll("'", "''", {}))
      }
    } ) 
}

[#10]

@SergeiBaklan 

We may use console.log here, but no sense if the script runs by Power Automate

Sure, that was just to check I understood we were making an IF

[#11]

@SergeiBaklan 

I guess I could have been a little clearer and included my entire code.

My Goal: To create a Universal function that allows me to take excel workbooks from emails that do not have excel formatted tables (just ranges) and convert them into tables in excel. That way I can use Power Automate to push the tables to my SQL Table. Here was the temporary solution I came up with to solve my first post problem; however, it doesn't feel very efficient to me. I am also super concious about data efficiency as I often hit the Power Automate run script limits with the Excel API.

function main(workbook: ExcelScript.Workbook, WorkSheet_Name: string, DeleteTopRows: number) {
  let selectedSheet: ExcelScript.Worksheet;
  if (WorkSheet_Name == "") {
    selectedSheet = workbook.getWorksheets()[4];
  } else {
    selectedSheet = workbook.getWorksheet(WorkSheet_Name);
  }
  if (DeleteTopRows > 0) {
    //Delete Top N Rows
    selectedSheet.getRange("1:" + DeleteTopRows).delete(ExcelScript.DeleteShiftDirection.up);
  } else {
  }
  // Clear Any Filters on Selected Sheet
  selectedSheet.getAutoFilter().remove();

  // Get used Range
  let usedRange = selectedSheet.getUsedRange();
  usedRange.copyFrom(usedRange,ExcelScript.RangeCopyType.values,false,false)
  // Add a new table
  let newTable = workbook.addTable(usedRange, true);
  // Replace SQL Reserved Characters
  selectedSheet.replaceAll("'", "''", {
    completeMatch: false,
    matchCase: false
  })
  // Create ID column for Power Automate ODATA Filter
  newTable.addColumn(-1, Number[0], "ID");
  let ID = newTable.getColumnByName("ID").getRangeBetweenHeaderAndTotal();
  ID.setNumberFormatLocal("0");
  ID.setFormula("=ROUNDUP((ROW()-1)/5000,0)")
  // Return Table ID to Power Automate
  return newTable.getId();
}

[#12]

@djmitz6 

Thanks for the sharing.

May I clarify. When you use

usedRange.copyFrom(usedRange, ExcelScript.RangeCopyType.values, false, false)

you copy values from the range, even if they are generated by formulae. Thus initial question is not applicable here since you have no formulae in the resulting table. The question is would you like to keep initial formulae in the resulting table; or convert them into the structured formulae (not sure how to do that); or just values is enough.

[#13]

The initial question never had the requirement to maintain the formula in the resultant table. (Maybe I should have been clearer on this.) I just needed the formula result. Since I do not push the formula to the SQL table, I only push it's value.

[#14]

@djmitz6 

When your script shall work. You have values only, all single apostrophes in texts will be doubled, and you have nothing but texts and numbers.

[#15]

Yes, my script is currently working as desired just looking for a more efficient solution. The problem I was having is if I didn't copy and paste the formula results, the formulas themselves would be come the cell value with additional apostrophes. It would have really been nice if the replaceAll function had an option to ignore formulas.

[#16]

@djmitz6 

So, you'd like to

- remove first N rows

- convert the rest into the structured table (keeping formulae in it as is)

- double ' for each cell within the table ignoring ones with formula

Correct?

[#17]

You got it!

[#18]

@djmitz6 

When it could be

function main(workbook: ExcelScript.Workbook) {

    // will be used as Power Automate parameters
    const WorkSheet_Name = "Sheet1 (2)"
    const DeleteTopRows = 11

    let selectedSheet: ExcelScript.Worksheet;
    if (WorkSheet_Name == "") {
        selectedSheet = workbook.getWorksheets()[4];
    } else {
        selectedSheet = workbook.getWorksheet(WorkSheet_Name);
    }
    if (DeleteTopRows > 0) {
        //Delete Top N Rows
        selectedSheet.getRange("1:" + DeleteTopRows).delete(ExcelScript.DeleteShiftDirection.up);
    } else {
    }
    // Clear Any Filters on Selected Sheet
    selectedSheet.getAutoFilter().remove();

    // Get used Range
    let usedRange = selectedSheet.getUsedRange();
    //usedRange.copyFrom(usedRange, ExcelScript.RangeCopyType.values, false, false)

    usedRange.getSpecialCells(ExcelScript.SpecialCellType.constants)
        .getAreas()
        .map(a => a.getUsedRange().replaceAll("'", "''", {}))


    // Add a new table
    let newTable = workbook.addTable(usedRange, true);
    // Replace SQL Reserved Characters

/*   
    selectedSheet.replaceAll("'", "''", {
        completeMatch: false,
        matchCase: false
    })
*/
    // Create ID column for Power Automate ODATA Filter
    newTable.addColumn(-1, Number[0], "ID");
    let ID = newTable.getColumnByName("ID").getRangeBetweenHeaderAndTotal();
    ID.setNumberFormatLocal("0");
    ID.setFormula("=ROUNDUP((ROW()-1)/5000,0)")
    // Return Table ID to Power Automate
    return newTable.getId();
}

[#19]

@djmitz6 

As for the performance I'm not sure. Perhaps your variant will be better. That's only to test.

[#20]

@djmitz6 IMHO this is the kind of thing that could help other users. When you have a minute feel free to click the Mark as answer link (at the bottom of each reply you get here) on the post that put you on track/solved the issue - Thanks