question

Thanks for the question! For the hyperlink syntax, formatting your code to look like this should work:

  workbook.getActiveWorksheet().getRange("A1").setFormulaLocal('=HYPERLINK("https://www.bing.com/", "Name of Link")');

Notice the ' instead of the " in the line above.

Also, in order to delete the columns, I believe the recorder generated code to delete them from a worksheet object, not a table object. The below example should delete columns L and M from the active worksheet but you can specify a specific worksheet like so: let selectedSheet = workbook.getWorksheet("Sheet1");

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

Let me know if you have any other questions and hope this helps!

Hi @PetraRonald-5920,
first of all, happy new year 2022 and thank you for your feedback.
With your syntax suggestions I could now solve the problem with deleting or hiding columns, but unfortunately,
I still have the problem with setting the hyperlink.
I think I already do it syntactically as you suggest.
The main difference is only that I call a For loop with dynamic values.
As a link description I only get to see the command and not the file name from the column to the left of it.

Do you have another idea what I am doing wrong here?

I just hope that the problem is with me and it is not a bug, because I really need this.

Regards
Herb

Happy New Year to you too!

I would first set a variable with the hyperlink's value. So for instance, if B1 has the hyperlink, you would do something like the below in the for loop:

 let hyperlinkValue = workbook.getActiveWorksheet().getRange("B1").getValue();

Then, for the range with the formula (in this case A1), try something like this line.

 workbook.getActiveWorksheet().getRange("A1").setFormulaLocal('=HYPERLINK("'+hyperlinkValue+'", "Name of Link")');

Hope this helps!

Hi @PetraRonald-5920,
I have now tried to implement your suggestion. In the first step, I did without the For loop and tried to set the hyperlink formula for a single cell. However, this does not work as I had imagined. In the tool tip for the command "setFomulatLocal" it also says:
„If the range contains multiple cells, each cell in the given range will be updated with the input data”.

Here's my little test script:

 function main(workbook: ExcelScript.Workbook) {
        
     let hyperlinkAddr = workbook.getActiveWorksheet().getRange("M2").getValue();
     let hyperlinkDesc = workbook.getActiveWorksheet().getRange("L2").getValue();
        
     workbook.getActiveWorksheet().getRange("N2").setFormulaLocal('=HYPERLINK("' + hyperlinkAddr + '";"' + hyperlinkDesc + '")');    
 }    

This now results in the formula being set for the complete Used range within the column, although I only want to set the formula for a clearly defined cell.

Here is the result:

In the For loop, the formula is then also set four times for all cells and not individually for each cell.
After the run of the For loop, the column is empty again.

Do you have an idea how I can set the formula individually for each cell ?

I am already looking forward to your feedback.

Regards
Herb

Can you share your script?
With the for loop, the code should look something like this:

   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 = 0; 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
     }
   }

Hi @PetraRonald-5920;
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