Search code examples
power-automateoffice-scripts

Unable to add image to Excel file with Office Scripts

In Excel Office Scripts, I'm able to add an image to an Excel file with the script:

function main(workbook: ExcelScript.Workbook, idioma: string = "en", base64: string) {
    
      // Acrescenta a worksheet "Intro" na primeira posição
      try { workbook.addWorksheet("Intro").setPosition(0) } catch { };
      let sheet = workbook.getWorksheet("Intro");
    
      // 
      let base64Img: string;
      let base64ImgHTML: string;
    
      //Add image type to the pure base64
      base64Img = "data:image/png;base64," + base64
    
      //Add HTML tags to base64 imgBase64
      base64ImgHTML = '<img src="' + base64Img + '"/>'
    
      //Declare and add the image
      let img = sheet.addImage(base64Img);
    
      //Change the image name
      img.setName("Welcome.jpeg");
    
    };

With the "base64" parameter entered, like:

PHN2ZyB3aWR0aD0iNDQxIiBoZWlnaHQ9IjMyNSIgeG1sbnM9Imh0dHA6Ly93d3cudzMub3JnLzIwMDAvc3ZnIiB4bWxuczp4bGluaz0iaHR0cDovL3d3dy53My5vcmcvMTk5OS94bGluayIgeG1sOnNwYWNlPSJwcmVzZXJ2ZSIgb3ZlcmZsb3c9ImhpZGRlbiI+PGRlZnM+PGNsaXBQYXRoIGlkPSJjbGlwMCI+PHJlY3QgeD0iNTgyIiB5PSI0NCIgd2lkdGg9IjQ0MSIgaGVpZ2h0PSIzMjUiLz48L2NsaXBQYXRoPjwvZGVmcz48ZyBjbGlwLXBhdGg9InVybCgjY2xpcDApIiB ...

But running the script with the following flow, it fails with error:

We were unable to run the script. Please try again.
Office JS error: Line 18: Worksheet addImage: The argument is missing or invalid".

enter image description here

How to fix this?

Solution
    • The argument of addImage is base64 payload string without header.

    Try:

    let img = sheet.addImage(base64)
    • btw, pls use Base64 to Image to validate the Base64 string and ensure it's a valid argument.
    • A sample code to take snapshot of cell C1 and insert as an image on sheet
    function main(workbook: ExcelScript.Workbook) {
  //get Worksheet
  const firstWorksheet = workbook.getWorksheets()[0];
  const cell = firstWorksheet.getRange("C1")
  cell.setValue("ABC");
  cell.getFormat().getFill().setColor("00FF00");
  // get image
  let tableImage = cell.getImage();
  console.log(tableImage);
  // Add the image to the worksheet
  let imgShape = firstWorksheet.addImage(tableImage);
  imgShape.setName("Cell-C1");
}

    Output:

    iVBORw0KGgoAAAANSUhEUgAAAGkAAAAfCAIAAABrm3SxAAAAAXNSR0IArs4c6QAAAAlwSFlzAAASdAAAEnQB3mYfeAAAAhJJREFUaEPtmT1Sw0AMhddchBRAxQngBAwNFS0ddRpuwAFMCUegogFOAIcACt8EHnpjzdq7/oky48xuVrMTzFpyrC/PkgJV0zSumIlABXarw5Updt+DDvYdwBb5F3Z2eIVdYWcnYI8suivs7ATskaO6e3DuXFZoP+0pOnDBP2rvcrbyVvSa9ix2Ezk63yFb2ptzF937A7tj586cO233n9qD364nMH3Kzq23D+ee227S3+5d/79XII1w1S01/ASjnsN3i8Pf1xDd5A6oRd8i8c3hZ/ZZ0obckDmEA6FN2ol4fHl+azl+nIxM0mGAHUiBF1VzJ6+vm6eHMkfRZWoD7EjqUl6P5JmlDMft3ovCIQV4NRWV7PkBdmvhBWq0a5EhdeQbSj47LHsoDHVQo5KFMvPGY+zICLzUKMCX4Jrss1h4MHEMvjcz3zcLt0ifZYUCC38xWe2M0T6LUQamXbWWX7GZeD8duv+Y7nRSC8UxNP3SE00ZuDU8bLtZqE2TCNiRDsTy0V0cZed0DL02hudNQ5KCG7DTsS5MY3LQ42QD6dHYoLEzrtakeHVutlPvolVMqxXLWS31i55AA6C6eGG/wNGNngjEKSw2FpxKvQ522LFLjGRFEMquJxkQCTsDrhaOx1mwW/B/PfqtLpcBcMG/fQIZVy62ILtckGkehZ39Iy3sCjs7AXtk0V1hZydgjyy6s7P7A2t7s35favLTAAAAAElFTkSuQmCC