Adding QR code on my Excel Online using office script and Powerautomate

Question

I am using the following code to insert the QR code on my workbook, while this code work when i run it from office script, I am not able to run this script from Power automate because it looks like power automate does not support using fetch in office script. Can someone help me with work around to achieve this. I am fine with using Premium connecter to achieve this but i don't want to use 3rd Party connectors.

async function main(workbook: ExcelScript.Workbook) {
    // Fetch the image from a URL.
    const link = "https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=Samundra";
    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(data);

    const imageShape = workbook.getWorksheet("Sheet1").addImage(image);
    const range = workbook.getWorksheet("Sheet1").getRange("N15");
    imageShape.setLeft(range.getLeft());
    imageShape.setTop(range.getTop());
}

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

score 2 · Accepted Answer · answered Apr 15 '23 at 05:12

Unfortunately, external network calls are not currently supported when running Office Scripts in Power Automate.

The recommended solution for this is normally to use the "HTTP" connector in your Power Automate flow. In your case, you can use the HTTP connector action to send a request to the QR code API first then pass the response image as base64-encoded data into the Run script action.

You will need to modify your script to accept a parameter:

function main(workbook: ExcelScript.Workbook, base64Image: string) {
    const imageShape = workbook.getWorksheet("Sheet1").addImage(base64Image);
    const range = workbook.getWorksheet("Sheet1").getRange("N15");
    imageShape.setLeft(range.getLeft());
    imageShape.setTop(range.getTop());
}

And here is a sample flow:

You can see there is an input box base64Image for the Run script action if you select the updated script (with the new base64Image parameter) from the dropdown list.

You also need to use the built-in base64() function to convert the image data to a base64-encoded string:

Not sure that this is entirely correct, the `FETCH` command does exist but I am in favour of your answer as it's much easier to get working through a HTTP action. — Skin, Apr 18 '23 at 08:15
@Skin Yea you can use `fetch` when running Office Scripts interactively in Excel for the web but the same script code currently won't work when running in Power Automate. — Yutao Huang, Apr 18 '23 at 16:53

Adding QR code on my Excel Online using office script and Powerautomate

1 Answers1