Hi @boyombo12
I was lucky enough to have the Excel team look at my script and I didn't initially get that error you mention, albeit i did in later release of Office Scripts. You need to define the types for the items in the array. If you take a look here https://github.com/DamoBird365/PowerAutomate/tree/main/ExcelInvoiceDemo you will be able to look at the excelscriptsv1 vs v2.
Below is v2
function main(
workbook: ExcelScript.Workbook,
invoiceNumber: string,
date: string,
paymentMethod: string,
chequeNo: string,
jobNo: string,
vatRate: number,
customer: Customer,
sales: Sales[],
) {
// Get the first worksheet
const sheet = workbook.getFirstWorksheet();
//Update Header of Sales Receipt
sheet.getRange("G2").setValue(invoiceNumber);
sheet.getRange("G3").setValue(date);
sheet.getRange("B9").setValue(paymentMethod);
sheet.getRange("D9").setValue(chequeNo);
sheet.getRange("E9").setValue(jobNo);
sheet.getRange("G27").setValue(vatRate);
//Update Customer Details
const formattedCustomer = [[customer.name], [customer.companyName], [customer.street], [customer.city], [customer.phone]];
sheet.getRange("C2:C6").setValues(formattedCustomer);
//Update Sales Lines
const salesOffset = 12; //starting row for items on invoice
for (let i = 0; i < sales.length; i++) {
const currentSales = sales[i];
const formattedSales = [[currentSales.quantity, currentSales.itemNumber, currentSales.description, currentSales.unitPrice, currentSales.discount]];
const saleCell = `B${salesOffset + i}:F${salesOffset + i}`;
sheet.getRange(saleCell).setValues(formattedSales);
}
}
interface Customer {
name: string,
companyName: string,
street: string,
city: string,
phone: string,
}
interface Sales {
quantity: number,
itemNumber: string,
description: string,
unitPrice: number,
discount: number,
}
Note that at the end of the script there is a definition for the Array variable as an interface. If you provide this as part of the script file, it should then hopefully work as planned and PowerAutomate will enforce the types that you specify.
The following video covers your scenario https://www.youtube.com/watch?v=Q7GLQnvJJF0
You can jump to the key moment in the video here https://www.youtube.com/watch?v=Q7GLQnvJJF0&t=263
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien
P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉
