CODEWITHSUNDEEP
power-automateoffice-scriptsms-office-script
Samundra
Samundra

Reputation: 19

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

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

Upvotes: 0

Views: 483

Answers (1)

Yutao Huang
Yutao Huang

Reputation: 1571

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: Power Automate flow with the HTTP action and Run script action

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: Use base64 function to encode the image data

Upvotes: 2

Related Questions