Forum Discussion

Ceres2186489's avatar
Ceres2186489
Copper Contributor
Jun 11, 2024

Office Script to add image

Hi! I am using the following code to add image into excel sheet automatically. However, it only works for JPG file and not for JPEG and PNG file. May I modify the following code and let it works for 3 types of image files? Thank you very much.

 

function main(workbook: ExcelScript.Workbook, base64ImageString: string,imageName: string) {
  // get table row count
  let sheet1 = workbook.getWorksheet('In progress');
  let table1 = workbook.getTable('Inprogress');
  
  let rowCount = table1.getRowCount();
  let n1:number = rowCount + 1;
  let range1:string = '';
  let imageAddress:string = '';
  let nameAddress:string = '';

  imageAddress = 'J' + n1.toString();
  nameAddress = 'N' + n1.toString();
  range1 = n1 + ':' + n1

  sheet1.getRange(range1).getFormat().setRowHeight(230);

  let range = sheet1.getRange(imageAddress);
  let image = sheet1.addImage(base64ImageString);
  image.setName(imageName);
  image.setTop(range.getTop());
  image.setLeft(range.getLeft());
  image.setWidth(300);
  image.setHeight(225);

  sheet1.getRange(nameAddress).setValue(imageName)
}

 

 

3 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    I assume this script is called from a Power Automate flow? If yes, you must make sure the image file is properly converted to a base64ImageString.
    • Ceres2186489's avatar
      Ceres2186489
      Copper Contributor

      JKPieterse Thanks for your reply. Here is my flow:

       

      After get attachment content from SharePoint list item, Base64() is used to convert 

      outputs('Get_attachment_content')?['body']. The base64() is extracting coding of outputs('Get_attachment_content')?['body']?['$context']. However, this flow is still only function for JPG file. How can I make this work for PNG file? Thanks a lot.

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Apparently that image function doesn't support that picture format. Perhaps there is a flow step that can do the conversion before this step?

Resources