Power Platform Community Forum Thread Details

Hi there,

I'm trying to get two office scripts to communicate using Power Automate. I have two Desktop Office Scripts, which work fine as Desktop flows. The first reads the contents of an Excel table and converts into JSON (the interface is defined in the script). The JSON is well-formed, and when I paste this output as input to the next script it works as expected. This second script parses the JSON, and then pastes it into a different workbook.

However, when I try to connect the two scripts using Power Automate in the web client, I cannot get the data to be output in a form that can be read by the second script.

I have collapsed the two steps into a single flow:

Manual trigger
Run first script
Take output of first script, as input to second script

I must be doing something wrong, because the output of the first script is always limited to the values in the last column.

I have defined the return value from the first script to be string (and am currently using the JSON stringify function to serialize the JSON data). The documentation suggests that I should be able to return the JSON object, but I can't figure out how to do this (the script fails if I change the return type to be the object type and try to return the object instead of the string).

Help!

The script is included below:

function main(workbook: ExcelScript.Workbook, name: string):string {
 // set the active worksheet
 let ws = workbook.getWorksheet(name);
 ws.activate();

 // Get the table on the current sheet
 const table = workbook.getActiveWorksheet().getTables()[0];

 // Create data array
 let timesheets: Timesheet[] = [];
 const dataValues = table.getRangeBetweenHeaderAndTotal().getValues();
 for (let i = 0; i < dataValues.length; i++) {
 let row = dataValues[i];
 let currentTimesheet: Timesheet = {
 EmployeeName: row[table.getColumnByName("Employee Name").getIndex()] as string,
 Team: row[table.getColumnByName("Team").getIndex()] as string,
 FTE: row[table.getColumnByName("FTE").getIndex()] as number,
 BillablefromBudget2024: row[table.getColumnByName("Billable from Budget 2024").getIndex()] as number,
 ReportingCountry: row[table.getColumnByName("Reporting Country").getIndex()] as string,
 HourstobeLogged: row[table.getColumnByName("Hours to be Logged").getIndex()] as number,
 Hourslogged: row[table.getColumnByName("Hours logged").getIndex()] as number,
 Difference: row[table.getColumnByName("Difference").getIndex()] as number,
 Leavelogged: row[table.getColumnByName("Leave Logged").getIndex()] as number,
 WorkingHours: row[table.getColumnByName("Working Hours").getIndex()] as number,
 BillableExpected: row[table.getColumnByName("Billable Expected").getIndex()] as number,
 BillableLogged: row[table.getColumnByName("Billable Logged").getIndex()] as number,
 Difference2: row[table.getColumnByName("Difference2").getIndex()] as number,
 Percentofbillabletarget: row[table.getColumnByName("% of billable target").getIndex()] as number,
 NonBillableServices: row[table.getColumnByName("Non-Billable Services").getIndex()] as number,
 TotalBillable: row[table.getColumnByName("Total Billable + Services non-billable").getIndex()] as number
 }
 timesheets.push(currentTimesheet);
 }

 // timesheets is the JSON object
 console.log(timesheets);
 // return timesheets;

 // sheetstring is the stringified JSON
 let sheetstring: string = JSON.stringify(timesheets);
 //console.log(sheetstring);
 return sheetstring;
}

// Interface for the timesheet details as JSON
interface Timesheet {
 "EmployeeName": string;
 "Team": string;
 "FTE": number;
 "BillablefromBudget2024": number;
 "ReportingCountry": string;
 "HourstobeLogged": number;
 "Hourslogged": number;
 "Difference": number;
 "Leavelogged": number;
 "WorkingHours": number;
 "BillableExpected": number;
 "BillableLogged": number;
 "Difference2": number;
 "Percentofbillabletarget": number;
 "NonBillableServices": number;
 "TotalBillable": number;
}

Categories:

Building flows