Adding Image from URL to specific Cell using Office script

Question

I am trying to add a QR Code on specific Cell using office script, The following code works fine but, I am not able to figure out how to specify the position(cell G1), I want that QR Code top left corner to start at Cell "G1".

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

    // Add the image to a worksheet.
    workbook.getWorksheet("Sheet1").addImage(image);
    }


    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 0 · Accepted Answer · answered Apr 13 '23 at 04:07

You will need to get the image object that you've just added to the workbook and set its left and top to be the left and top of the "anchor" cell (G1 in your case).

So something like this:

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

Adding Image from URL to specific Cell using Office script

1 Answers1