Office Script to turn cell text to formula

[samdthompson]

Hello, I have a table from power query in which some the cell contents are a formula but as text. If I click in the cell and hit enter, it calculates fine but i don't want the cycle through the cells to do this. Is there anyway to automate with an office script? I know it can be done with VBA but that's not going to be an option here.
Cheers

 

[Zaifee]

Yes, you can automate the recalculation of Power Query formulas in Excel using an Office Script. Here's an example of how you can achieve this:

function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getActiveWorksheet();
  let table = sheet.getTables()[0]; // Assuming you have one table in the worksheet
  
  // Get the range of the table
  let tableRange = table.getRange();
  
  // Iterate through each cell in the table range
  tableRange.getValues().forEach((row, rowIndex) => {
    row.forEach((cellValue, columnIndex) => {
      let cell = tableRange.getCell(rowIndex, columnIndex);
      
      // Check if the cell contains a formula as text
      if (cellValue && cellValue.startsWith("=")) {
        // Set the formula and force recalculation
        cell.setFormulaLocal(cellValue);
        cell.calculate();
      }
    });
  });
}

This Office Script iterates through each cell in a table range and checks if the cell contains a formula as text (indicated by starting with "="). If a cell contains a formula as text, it sets the formula using setFormulaLocal() and then forces the recalculation of the cell using calculate().

To use this script:

1. Open your Excel workbook.
2. Go to the "Automate" tab in the Excel ribbon.
3. Click on "Script Lab" to open the Script Lab pane.
4. In the Script Lab pane, create a new script or open an existing one.
5. Replace the default script code with the provided code.
6. Click the "Run" button in the Script Lab pane to execute the script.

 

[samdthompson]

Thanks for that. A wee change is needed as cellValue.startsWith is not a valid function:

function main(workbook: ExcelScript.Workbook) {
let sheet = workbook.getActiveWorksheet();
let table = sheet.getTables()[0]; // Assuming you have one table in the worksheet
// Get the range of the table
let tableRange = table.getRange();
// Iterate through each cell in the table range
tableRange.getValues().forEach((row, rowIndex) => {
row.forEach((cellValue, columnIndex) => {
let cell = tableRange.getCell(rowIndex, columnIndex);
// Check if the cell contains a formula as text
if (typeof cellValue === 'string' && cellValue.startsWith("=")) {
// Set the formula and force recalculation
cell.setFormulaLocal(cellValue);
cell.calculate();
}
});
});
}

Cheers

 

[samdthompson]

For what its worth to anyone else wanting to do this, if you have carriage returns in the formula you want to convert, this process fails. I needed to paste:

=LET(col,INDEX(TEST_Capacity,,29),day,INDIRECT(CELL("address",XLOOKUP([@Lookup]&"Day",[Lookup],col,,,1))&":"&CELL("address",XLOOKUP([@Lookup]&"Day",[Lookup],col,,,-1))),ef,INDIRECT(CELL("address",XLOOKUP([@Lookup]&"EF",[Lookup],col,,,1))&":"&CELL("address",XLOOKUP([@Lookup]&"EF",[Lookup],col,,,-1))),lvl,INDIRECT(CELL("address",XLOOKUP([@Lookup]&"Lvl",[Lookup],col,,,1))&":"&CELL("address",XLOOKUP([@Lookup]&"Lvl",[Lookup],col,,,-1))),calc,SUM(day*ef*lvl),calc)

rather than:

=LET(
col,INDEX(TEST_Capacity,,29),
day,INDIRECT(CELL("address",XLOOKUP([@Lookup]&"Day",[Lookup],col,,,1))&":"&CELL("address",XLOOKUP([@Lookup]&"Day",[Lookup],col,,,-1))),
ef,INDIRECT(CELL("address",XLOOKUP([@Lookup]&"EF",[Lookup],col,,,1))&":"&CELL("address",XLOOKUP([@Lookup]&"EF",[Lookup],col,,,-1))),
lvl,INDIRECT(CELL("address",XLOOKUP([@Lookup]&"Lvl",[Lookup],col,,,1))&":"&CELL("address",XLOOKUP([@Lookup]&"Lvl",[Lookup],col,,,-1))),
calc,SUM(day*ef*lvl),
calc)