CODEWITHSUNDEEP
exceltypescriptsharepointpower-automateoffice-scripts
Joseph Nasr
Joseph Nasr

Reputation: 13

Office Script And Power Automate to export Images from Sharepoint Document Library in Excel

I am trying to generate an excel BoQ report using Office Script and Power Automate, in which every row contains information about a BoQ item in addition to its picture.

I am getting the data from a Sharepoint List and the URL of each picture (as the pictures are in a Sharepoint Document Library) using Power Automate. All the data and rows are being filled correctly in the excel sheet using Office Script, but not the pictures.

I am getting the image from the URL and converting it to Base64 as per the documentation: https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/add-image-to-workbook#sample-code-add-an-image-from-a-url-to-a-workbook

My question is how do I add the picture to the last column of the row of the BoQ item, as I did with the Array 'formattedBoqs' where the array becomes the row itself? My script is as follows:

    async function main(workbook: ExcelScript.Workbook, boqs: BoQs[],) 
    {
      // Get the first worksheet
      const sheet = workbook.getFirstWorksheet();
    
      //Loop through BoQs and fill them in array formattedBoqs
      const boqOffset = 11; //starting row for BoQ items list
      for (let i = 0; i < boqs.length; i++) {
        const currentBoQs = boqs[i];
    
//Code Block is from the documentation linked above to get and convert the img
        // Fetch the image from a URL.
        const link = currentBoQs.imageLink;
        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
        sheet.addImage(image)
//End of Documentation's code block
        
        //Set BoQ item row into array then fill into excel row
        const formattedBoqs = [[currentBoQs.boqNumber, currentBoQs.location, currentBoQs.category, currentBoQs.item, currentBoQs.damageLevel, currentBoQs.repairType, currentBoQs.unit, currentBoQs.quantity, currentBoQs.width, currentBoQs.BoQlength, currentBoQs.thickness, currentBoQs.direction, currentBoQs.note /*,NEED TO ADD IMG HERE*/]];
        const boqCell = `A${boqOffset + i}:M${boqOffset + i}`;
        sheet.getRange(boqCell).setValues(formattedBoqs);
      }
    }
    
    //Bill of Quantities
    interface BoQs {
      boqNumber: number,
      location: string,
      category: string,
      item: string,
      damageLevel: string,
      repairType: string,
      unit: string,
      quantity: string,
      width: string,
      BoQlength: string,
      thickness: string,
      direction: string,
      imageLink: string,
      note: string,
    }

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

Upvotes: 0

Views: 803

Answers (1)

Jay Rathi - Microsoft
Jay Rathi - Microsoft

Reputation: 251

Excel does not allow the value of a cell to be an image, so you cannot use Range.setValues to add the image.

addImage returns an Excelscript.Shape. You can use the Shape APIs to set the position of the image. Example code -

...
        // Get the Shape object for the added image
        let picture = sheet.addImage(image)  
        //End of Documentation's code block
        
        //Set BoQ item row into array then fill into excel row
        const formattedBoqs = [[currentBoQs.boqNumber, currentBoQs.location, currentBoQs.category, currentBoQs.item, currentBoQs.damageLevel, currentBoQs.repairType, currentBoQs.unit, currentBoQs.quantity, currentBoQs.width, currentBoQs.BoQlength, currentBoQs.thickness, currentBoQs.direction, currentBoQs.note]];
        const boqCell = `A${boqOffset + i}:M${boqOffset + i}`;
        sheet.getRange(boqCell).setValues(formattedBoqs);

        // Set the position of the image to be next to the range
        picture.setLeft(sheet.getRange(boqCell).getWidth());
        picture.setTop(sheet.getRange(boqCell).getHeight());

You can set other properties on the image too, like picture.setHeight, picture.setWidth and so on.

Upvotes: 2

Related Questions