Turn SharePoint Image URLs in Excel into Clickable Links

An Excel tutorial by Peter Kalmström

In the previous demo, Peter Kalmström showed how to give app users a possibility to add comments and pictures before they send info about performed tasks from the Open Recurring Tasks app to Excel.

The images were stored in SharePoint, but the previous steps did not give clickable image links in Excel. Now it is time to add that last feature to the Excel/Power Apps solution for Recurring Tasks management.

Peter will also change the Excel TypeScript code so that the values in two new columns, Comment and Picture, in the Tasks table are moved to the TasksArchive table together with the other task data. For that, he adds these two columns also to the TasksArchive table. All other changes in this demo are made in the AddTasks script.

Create a SetHyperLink function

The new SetHyperLink function takes a row as a parameter and creates a hyperlink from the column index of that row based on the text.

Create a new function called SetHyperLink. It should take two parameters, RowLink, which is an Excel script range and Index, which is a number:
```
function setHyperlink(RowLink:ExcelScript.Range,Index:number)
```
Add curly brackets to delimit the actions the function should execute.
To get the Picture cell from the row, use the const keyword to declare a PicCell variable whose value cannot be reassigned. It should be the Excel script range from the Index parameter of the RowLink object's GetColum method.
```
cons PicCell:ExcelScript.Range = RowToLink.getColumn(Index);
```
To get the value from the Picture cell, create another constant variable: CellValue. This value can be a string, a boolean or a number, and it should equal the GetValue function from the PicCell variable:
```
const CellValue: string |boolean |number = PicCell.getValue();
```
Set a condition: if there is a cell value and it is not equal to an empty string.
If the condition is true, a NewLink constant should be created with the object ExcelScript.RangeHyperlink. Declare it ={} to get IntelliSense on the object.
Set the NewLink Address to equal the CellValue.toString.
Set the NewLink textToDisplay to equal "Image".
Apply the PicCell RangeHyperLink to the cell. Use the setHyperhink function and the parameter NewLink.

Now the whole setHyperlink function, including the condition, looks like this:

function setHyperlink(RowLink:ExcelScript.Range,Index:number){
  cons PicCell:ExcelScript.Range = RowLink.getColumn(Index);
  const CellValue: string |boolean |number = PicCell.getValue();
  if(CellValue && CellValue !== ""){
    const NewLink: ExcelScript.RangeHyperlink = {};
    NewLink.address = CellValue.toString();
    NewLink.textToDisplay ="Image";
    PicCell.setHyperlink(NewLink);
  }
}

Modify the CopyToArchive Method

The Task class has information about how values in the Excel Tasks table should be moved to the TasksArchive table. We need to add the two new columns, Comment and Picture to the TasksArchive table and then modify the CopyToArchive method to include them.

Add two new Text columns to the right of the other columns in the TasksArchive table: Comment and Picture.
In the AddTasks script, find the CopyToArchive method in the Task class.
Cut the variable let DestionationRow: ExcelScript.Range and paste it higher up in the method, after the other let variables, to be declared there instead.
In the If statement, specify the DestinationRow variable to be equal to ArchiveTable.getRangeBetweenHeaderAndTotal().getLastRow();
Add the same value for the DestinationRow in the Else statement, so that we have it whether it is a new row or an existing row that has been updated.
Use the setHyperlink function for the DestinationRow and the column 9, which is the Picture column.

Now CopyToArchive method looks like this:

public CopyToArchive(WB:ExcelScript.Workbook): void {
    let ArchiveTable: ExcelScript.Table = WB.getTable("TasksArchive");
    let Values: (string | number | boolean)[][] = this.Row.getValues();
    let DestionationRow: ExcelScript.Range
    Values[0].pop(); //Remove the last column, the ArchiveRow;
    if(isNaN(this.ArchiveRow) || this.ArchiveRow === 0){
      ArchiveTable.addRow(-1,Values[0]);
      DestionationRow = ArchiveTable.getRangeBetweenHeaderAndTotal().getLastRow();
    }else{
      DestionationRow = ArchiveTable.getRangeBetweenHeaderAndTotal().getRow(this.ArchiveRow-2);
      DestionationRow.setValue(Values);
    }
    setHyperlink(DestionationRow,9);
  }