You’re offline. This is a read only version of the page.
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:
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:
Share
Reportfunction 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;
}
#1
Michael E. Gernaey
566
Super User 2025 Season 1
#2
David_MA
516
Super User 2025 Season 1
#3
stampcoin
492
All responses (
Answers (