web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate / office script return a...
Power Automate
Unanswered

office script return array and string value

(0) ShareShare
ReportReport
Posted on by 7

Hi All, 

 

I'm new to office scripts and I'm struggling to understand the interface syntax when trying to return a string array and a string value. Can anyone advise where I am going wrong?

 

 

 

function main(Workbook: ExcelScript.Workbook, sheetName: string): SelectedValuesToReturn {
 
 let range = Workbook.getWorksheet(sheetName).getRange("A1");
 let surroundingRegion = range.getSurroundingRegion();
 let values = surroundingRegion.getValues();
 let address = surroundingRegion.getAddress();

 return {address_out: address, valuesout: values}
}
interface SelectedValuesToReturn {
 address_out: string,
 valuesout: string[][]
}

 

 

 

valuesout gives the following error:

 

Capture.PNG

 

 

Categories:
  • Nived_Nambiar Profile Picture
    18,138 Super User 2026 Season 1 on at

    Hi @paddysheeran 

     

    Could you try this office script 

     

     

    function main(Workbook: ExcelScript.Workbook, sheetName: string): SelectedValuesToReturn {
    
     let range = Workbook.getWorksheet(sheetName).getRange("A1");
     let surroundingRegion = range.getSurroundingRegion();
     let values = surroundingRegion.getValues().map(row => row.map(cell => cell.toString()));
    
     let address = surroundingRegion.getAddress();
    
     return { address_out: address, valuesout: values }
    }
    interface SelectedValuesToReturn {
     address_out: string,
     valuesout: string[][]
    }
    
    

    Hope it helps !

    Mark it as solution if it resolves your query!

  • paddysheeran Profile Picture
    7 on at

    Hi there, I tried this but I can only then get the body of the return in my PA flow. 

    I have changed the code which appears to be working to extract the data I need but I am struggling with the output of the string array and how to then use this is my second script.

     

    First Script to Extract the data from the worksheet:

    function main(workbook: ExcelScript.Workbook, sheetName: string): SelectedValuesToReturn {
    
     let range: ExcelScript.Range = workbook.getWorksheet(sheetName).getRange("A1");
     let currentRegion: ExcelScript.Range = range.getSurroundingRegion();
     let values = currentRegion.getValues() as string[][];
     let address_out: string = currentRegion.getAddress();
    
     return { address_out: address_out, values_out: values }
    }
    
    interface SelectedValuesToReturn {
     address_out: string,
     values_out: string[][]
    }

    when I add a run script action I can see the outputs in a compose action:
    Capture.PNG

     

    Second script to add the data into a new worksheet in a separate workbook:

     

    function main(Workbook: ExcelScript.Workbook, sheetName: string, address: string, values: string[][]) {
     let range = Workbook.getWorksheet(sheetName).getRange(address);
     if (values.length === 0) {
     return;
     }
    
     let resizedRange = range.getAbsoluteResizedRange(values.length, values[0].length);
     resizedRange.setValues(values);
    }

    when I look at the parameter for the values in the second script in my flow it does not show the values_out output from the script:

    Capture.PNG

    If I use the compose output (which uses the values_out returned from the first script) I just get the string value of the returned string array in cell A1:

     

    Capture.PNG

    The aim is to use two scripts to scape data from one workbook and then add it as a new sheet in the destination workbook.

     

     

  • Nived_Nambiar Profile Picture
    18,138 Super User 2026 Season 1 on at

    Hi @paddysheeran 

     

    do one thing , write this as expression for values_out where you want to pass it in second script

    outputs('Run_script')?['body/result/valuesout']
     
     
    Hope it helps 🙂
     
  • paddysheeran Profile Picture
    7 on at

    Hi Nived,

    thanks fir your support on this.

     

    outputs('Run_script')?['body/result/valuesout'] - Returned nothing in the sheet

     

    I changed it to:

     

    outputs('Run_script')?['body/result/values_out']  - still getting the whole string in A1

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Leaderboard > Power Automate

#1
11manish Profile Picture

11manish 321 Super User 2026 Season 2

#2
Mohsin Ali Profile Picture

Mohsin Ali 244

#3
Henil Profile Picture

Henil 102

Last 30 days Overall leaderboard