Syntax for formula command

Question

Hi all,
I am trying to create my first office script today and unfortunately run into some problems right away.
I have an Excel file where in column L are file names and in column M are links to these files in a SharePoint site.
I now want to set the formula for a hyperlink with a For loop with the file name and the file link in column N.
After that I want to either hide or delete the columns L and M.
The For-loop I have already managed so far, but I have not yet understood how I have to make the setting of the formula for the hyperlink syntactically correct.

Here are my first attempts to set the file name from the L column as the link description:
function main(workbook: ExcelScript.Workbook) {
let currentWorksheet = workbook.getActiveWorksheet();
let usedRange = currentWorksheet.getUsedRange();
let rowCount = usedRange.getRowCount();

  for (var i = 1; i <= rowCount; i++) {
    if (usedRange.getCell(i, 11).getValue()) {
      // usedRange.getCell(i, 13).setFormulaLocal("=HYPERLINK(\"https://xxx.sharepoint.com/sites/CS-Reisekostenabrechnung//Freigegebene Dokumente/ Positionen/number9.png\"; \"' usedRange.getCell(i, 11).getValue() '\")");

      // usedRange.getCell(i, 13).setFormulaLocal("=HYPERLINK(\"https://xxx.sharepoint.com/sites/CS-Reisekostenabrechnung//Freigegebene Dokumente/ Positionen/number9.png\"; \"usedRange.getCell(i, 11).getValue()\")");

      // usedRange.getCell(i, 13).setFormulaLocal("=HYPERLINK(\"https://xxx.sharepoint.com/sites/CS-Reisekostenabrechnung//Freigegebene Dokumente/ Positionen/number9.png\"; \" & usedRange.getCell(i, 11).getValue() & \")");

      usedRange.getCell(i, 13).setFormulaLocal("=HYPERLINK(\"https://xxx.sharepoint.com/sites/CS-Reisekostenabrechnung//Freigegebene Dokumente/ Positionen/number9.png\"; \" ${usedRange.getCell(i, 11).getValue()} \")");

      console.log(`The current cell's value is ${usedRange.getCell(i, 11).getValue()}`);

    }
  }
}

All 4 attempts show then in the column N only the command as string, but not the file name. What would be the correct syntax here?

For deleting or hiding the columns I used the macro recorder. Unfortunately, the macro recorder shows me a syntax error in its own generated code in both cases.

function main(workbook: ExcelScript.Workbook) {
  let backup = workbook.getTable("Backup");
  // Delete range L:M on backup
  backup.getRange("L:M").delete(ExcelScript.DeleteShiftDirection.left);
}

Expected 0 arguments, but got 1 (2554).

If I start the script anyway, then all columns from A to M are either completely deleted or all hidden.

Can anyone help me further here?

Regards
Herb

Answer 1

Hi @Petra Ronald ;
First, thank you for your patience ;-)
I have now tested your script suggestion. I have adjusted only two small things in your script. I deliberately start the For loop at 1 instead of 0, because 0 are my column headers, where I do not need or want to set the hyperlink formula. Since I have a German Office 365 license, I must replace the comma between the link and the link description with a semicolon.
If I now start your script with these two changes, then the problem described before occurs again. All rows in column N get the formula of the last loop pass (see picture from my last post). This behavior can be explained by the description of the SetFormulaLocal command („If the range contains multiple cells, each cell in the given range will be updated with the input data”.).
With a little trick I could solve this now. I have extended the If loop within the For loop by "Else". If there is nothing in column L, then write a hyphen in column N in the same line. So that this does not get the blue color of a link again, I set the color back to black.
This is how it works now!
Here is the revised script:

function main(workbook: ExcelScript.Workbook)  
{  
  let usedRange = workbook.getActiveWorksheet().getUsedRange();  
  let rowCount = usedRange.getRowCount();  
  let values = usedRange.getValues(); //I suggest getting the values for the entire range outside of the for loop to make your script run faster  
    
  for (var i = 1; i < rowCount; i++) { //is there any reason you are skipping the first row? the first row has index 0  
    if (values[i][11] != "") { //this would be how you get the value of a specific cell now since you got the values outside of the for loop  
      let hyperlinkDesc = values[i][11]; //this is the L column for each row assuming your used range starts in column A  
      let hyperlinkValue = values[i][12]; //this is the M column with the hyperlink value  
      usedRange.getCell(i, 13).setFormulaLocal('=HYPERLINK("' + hyperlinkValue + '"; "' + hyperlinkDesc + '")'); //setting column N  
    }   
    else  
    {  
      usedRange.getCell(i, 13).setValue("-");  
      usedRange.getCell(i, 13).getFormat().getFont().setColor(("Black"));  
    }  
  }  
}  

But now I can't really estimate how performant my loop extension is. In productive use I have Excel tables with about 500 rows. In the future, the script will be called via a Power Automate Flow, which will generate 4 Excel tables with 500 rows each. The flow itself does several other actions and now already takes over an hour.
If you tell me that now nothing more can be optimized here at the loop, then I would close my request herewith again and thank you already once for your support.

Regards
Herb