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

[Joseph_Nasr]

Hi everyone,

I am generating a BoQ Report in excel for every MSME I have in a Sharepoint List using Power Automate & Office Script. Everything was going well as I followed @DamoBird365's tutorial (link: https://www.youtube.com/watch?v=K3WtUTWZ-DQ&ab_channel=StuartRidout%3AProductivityCoach)

My problem is that in my excel report there is an extra cell in every BoQ row that should have the image of that BoQ which is stored in a Sharepoint Document Library.

How can I insert into every row's cell the related image? Kindly check my flow and script below:

My Flow

Closer look BoQ Items Array field mapping

As for my Office Script, it is working fine except for the image, for which I want to know how can I set the image along with the array to form a row.

async function main(workbook: ExcelScript.Workbook, msme: MSME[], boqs: BoQs[],) 
{
  // Get the first worksheet
  const sheet = workbook.getFirstWorksheet();

  //Update MSME Details
  sheet.getRange("D5").setValue(msme[0].businessName);
  sheet.getRange("D6").setValue(msme[0].representative);
  sheet.getRange("D7").setValue(msme[0].phoneNumber);
  sheet.getRange("D8").setValue(msme[0].suburb);
  sheet.getRange("H5").setValue(msme[0].numberOfEmployees);
  sheet.getRange("H6").setValue(msme[0].longitude);
  sheet.getRange("H7").setValue(msme[0].latitude);

  //Update BoQ Lines
  const boqOffset = 11; //starting row for BoQ items list
  for (let i = 0; i < boqs.length; i++) {
    const currentBoQs = boqs[i];

    // 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.
    workbook.getWorksheet("Sheet1").addImage(image) //Want to set this line into array below instead of currentBoQs.imageLink
    
    //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.imageLink , currentBoQs.note]];
    const boqCell = `A${boqOffset + i}:N${boqOffset + i}`;
    sheet.getRange(boqCell).setValues(formattedBoqs);
  }
}

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

interface MSME {
  businessName: string,
  representative: string,
  phoneNumber: string,
  suburb: string,
  numberOfEmployees: number,
  longitude: number,
  latitude: number,
  note: string,
}

[Yutao]

I think you can use setValues() to set the content of all the non-image cells. But you will need to handle the image cell separately.

Below is one possible way to handle the image cell, assuming anchorCell is the range of the cell where you want to place the image into that row.

const image = workbook.getWorksheet("Sheet1").addImage(imageBase64);
image.setTop(anchorCell.getTop());
image.setLeft(anchorCell.getLeft());
image.setHeight(anchorCell.getHeight());
image.setWidth(anchorCell.getWidth());
image.setPlacement(ExcelScript.Placement.twoCell);

The trick is to properly set the image position/dimension (left/top/width/height) to align with the cell itself and make sure to use the twoCell placement so the image can resize and move with the cell.