Run an office script to add image is failing in power automate

[rbn_nue]

Hi Folks,

I'm trying to export data into excel which includes images with below method:

1. Create excel file - add base64 value of image content into one column in each rows
2. After creating the excel run an office script which replaces actual image from the base64 in to each of those cells.

This method works fine when I run the script in excel manually. When I use power automate it gives below error:

{"message":"The argument is invalid or missing or has an incorrect format.","code":"InvalidArgument","type":"Worksheet","method":"addImage","line":29}

Below is the office script I'm using:

async function main(workbook: ExcelScript.Workbook) {
    const sheet = workbook.getWorksheet("FORM A - Register of Workmen");
    //console.log(sheet);
    let table = workbook.getActiveWorksheet().getTables()[0];

    //gets the new table range between the header and total
    let tableRange = table.getRangeBetweenHeaderAndTotal();

    //gets the table values
    let tableValues = tableRange.getValues();

    //gets the number of rows and columns
    let tableRows = table.getRowCount();
    let tableColumns = table.getColumns().length;
    
    // Fetch the image from a URL.
    for(let i=1;i<4;i++){
        const link = sheet.getCell(i,1).getValue().toString();
        //console.log(link);
        //const response = await fetch(link);

        // Store the response as an ArrayBuffer, since it is a raw image file.
        //const data = await response.arrayBuffer();

        // Convert the image data into a base64-encoded string.
      //const image = convertToBase64(link);

        // Add the image to a worksheet.
      const imageShape = sheet.addImage(link);
        //console.log("C"+(i+1));
      sheet.getRange("B" + (i + 1)).clear();
        const range = sheet.getRange("B"+(i+1));
        imageShape.setLeft(range.getLeft());
        imageShape.setTop(range.getTop());
      sheet.getRange("B" + (i + 1)).getFormat().setRowHeight(100);
        imageShape.setHeight(100);
        imageShape.setWidth(80);

    }
    //const link = "https://raw.githubusercontent.com/OfficeDev/office-scripts-docs/master/docs/images/git-octocat.png";
    
}

/**
 * Converts an ArrayBuffer containing a .png image into a base64-encoded string.
 */
function convertToBase64(input: ArrayBuffer) {
    const uInt8Array = new Uint8Array(input);
    const count = uInt8Array.length;

    // Allocate the necessary space up front.
    const charCodeArray = new Array(count) as string[];

    // Convert every entry in the array to a character.
    for (let i = count; i >= 0; i--) {
        charCodeArray[i] = String.fromCharCode(uInt8Array[i]);
    }

    // Convert the characters to base64.
    const base64 = btoa(charCodeArray.join(''));
    return base64;
}

Below is how I run script in power automate:

Could any one help me find out what I'm missing. Is this a limitation while running scripts from PA?

Thanks

[MichelleRanMSFT]

Hi @rbn_nue, thanks for your question! Could you share a sample workbook (sensitive data removed as needed) so I can reproduce this issue?

Additionally, one thing that I noticed is that your script calls the getActiveWorksheet method. You should avoid using this method when the script is run through Power Automate because Excel may not be open when the flow runs, so it's not always clear what the active worksheet is. I'm not sure if this is what's causing the problem, but it's best practice to get a worksheet by name instead when using Power Automate.

[rbn_nue]

Hi @MichelleRanMSFT 

I did manage to fix the issue, it had something to do with the for loop. Although, when I try to replace the base64 image string with actual image, it's not giving expected output. The script runs fine but the images look like they got corrupted. 

Any thoughts on this. Do you think if we store the base64 string to excel it will get corrupted?

Thanks

[MichelleRanMSFT]

I'm not aware of any issue relating to base64 strings being corrupted. Could you share your updated script and a sample workbook so I can reproduce this and troubleshoot further?

[rbn_nue]

Hi @MichelleRanMSFT 

Appreciate your immediate response. I had move on from this approach as I had limited time for completing solution. I'm now sending base 64 string as parameter to script from power automate and it's functioning as expected so far. In case I check back the saving base 64 string to excel, I'll let you know. 

Thanks