Hi Folks,
I'm trying to export data into excel which includes images with below method:
- Create excel file - add base64 value of image content into one column in each rows
- After creating the excel run an office script which replaces actual image from the base64 in to each of those cells.
This method works fine when I run the script in excel manually. When I use power automate it gives below error:
{"message":"The argument is invalid or missing or has an incorrect format.","code":"InvalidArgument","type":"Worksheet","method":"addImage","line":29}
Below is the office script I'm using:
async function main(workbook: ExcelScript.Workbook) {
const sheet = workbook.getWorksheet("FORM A - Register of Workmen");
//console.log(sheet);
let table = workbook.getActiveWorksheet().getTables()[0];
//gets the new table range between the header and total
let tableRange = table.getRangeBetweenHeaderAndTotal();
//gets the table values
let tableValues = tableRange.getValues();
//gets the number of rows and columns
let tableRows = table.getRowCount();
let tableColumns = table.getColumns().length;
// Fetch the image from a URL.
for(let i=1;i<4;i++){
const link = sheet.getCell(i,1).getValue().toString();
//console.log(link);
//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(link);
// Add the image to a worksheet.
const imageShape = sheet.addImage(link);
//console.log("C"+(i+1));
sheet.getRange("B" + (i + 1)).clear();
const range = sheet.getRange("B"+(i+1));
imageShape.setLeft(range.getLeft());
imageShape.setTop(range.getTop());
sheet.getRange("B" + (i + 1)).getFormat().setRowHeight(100);
imageShape.setHeight(100);
imageShape.setWidth(80);
}
//const link = "https://raw.githubusercontent.com/OfficeDev/office-scripts-docs/master/docs/images/git-octocat.png";
}
/**
* 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;
}
Below is how I run script in power automate:
Could any one help me find out what I'm missing. Is this a limitation while running scripts from PA?
Thanks
