cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
moutinhoabreu
Advocate IV
Advocate IV

Get cell value or multiple cells value from excel using office script (Result)

Hi Powerusers,

 

Started today creating flows using the office script.

The scenario:

We receive and save at sharepoint numerous excel files with the same structure but without tables inside.

we want to grab 3 cells not close to each other and use them in power automate to store information in our sql and send an email to someone which value is under one of the three cells mentioned above.

What we managed to achieve so far:

We managed a solution, creating 3 office scripts for each cell value, here he goes an example to grab the S6 cell.

 

function main(workbook: ExcelScript.Workbook) {
  // Get the current worksheet.
  let selectedSheet = workbook.getActiveWorksheet();
  // Get the value of cell S6.
  let valor = selectedSheet.getRange("S6");
  // Print the value of S6.
  return JSON.stringify(valor.getValues());}

 

 

It's working, but on the power automate side there are some results coming in, with double brackets

 

{
  "result": "[[157.95]]",
  "logs": []
}

 

Question1?

Is there a way to receive these values from office script without these brackets? For now we're removing it with the replace action inside a compose step:

 

replace(replace(outputs('Run_script_Valor')?['body/result'],'[',''),']','')

 

 

In another office script, the one fetching the email, the result is even a little bit more confuse:

 

[["xxxxe.axxx10@hotmail.com"]]

 

Question2?

Instead of 3 office scripts to fetch each result separately, does anyone know how to do it in a single one ?

 

Appreciated.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Yutao
Microsoft
Microsoft

Hi @moutinhoabreu ,

 

Here is one possible way to return multiple data from a single script:

function main(workbook: ExcelScript.Workbook): MyData {
  let selectedSheet = workbook.getWorksheet("Sheet1");
  return {
    Data1: selectedSheet.getRange("S6").getValue(),
    Data2: selectedSheet.getRange("U10").getValue(),
    Data3: selectedSheet.getRange("Y7").getValue(),
  } as MyData;
}

interface MyData {
  Data1: string;
  Data2: string;
  Data3: string;
}

 

The basic idea is to define a custom data type (in our case MyData) that contains several fields to hold the content you acquire from multiple cells.

 

You then should be able to directly use those fields in other actions following the Run script action:

Yutao_0-1654546681575.png

 

BTW a small note regarding the use of the getActiveWorksheet in your original code. This API may not work as expected while running in Power Automate. I'd suggest using getWorkSheet if possible. More details regarding this limitation here: https://docs.microsoft.com/en-us/office/dev/scripts/testing/power-automate-troubleshooting#avoid-rel....

 

View solution in original post

3 REPLIES 3
Yutao
Microsoft
Microsoft

Hi @moutinhoabreu ,

 

Here is one possible way to return multiple data from a single script:

function main(workbook: ExcelScript.Workbook): MyData {
  let selectedSheet = workbook.getWorksheet("Sheet1");
  return {
    Data1: selectedSheet.getRange("S6").getValue(),
    Data2: selectedSheet.getRange("U10").getValue(),
    Data3: selectedSheet.getRange("Y7").getValue(),
  } as MyData;
}

interface MyData {
  Data1: string;
  Data2: string;
  Data3: string;
}

 

The basic idea is to define a custom data type (in our case MyData) that contains several fields to hold the content you acquire from multiple cells.

 

You then should be able to directly use those fields in other actions following the Run script action:

Yutao_0-1654546681575.png

 

BTW a small note regarding the use of the getActiveWorksheet in your original code. This API may not work as expected while running in Power Automate. I'd suggest using getWorkSheet if possible. More details regarding this limitation here: https://docs.microsoft.com/en-us/office/dev/scripts/testing/power-automate-troubleshooting#avoid-rel....

 

Hi @Yutao 

 

Worked at first attempt. Super!

Can I post you another question, related with this topic?

 

One excel cell is a decimal type. and in some workbooks it returns lots of decimal places. Is it possible to truncate the decimal to 2 positions from the Excel Script or in your opinion it's better to do this on the Power Automate side,

thanks

Yutao
Microsoft
Microsoft

@moutinhoabreu 

 

Great to hear the solution worked for you!

 

Regarding decimal places, yes it's possible to do that with Office Scripts. Here is one possible way of doing that (based on the above example):

function main(workbook: ExcelScript.Workbook): MyData {
  let selectedSheet = workbook.getWorksheet("Sheet1");
  return {
    Data1: Number(selectedSheet.getRange("A1").getValue()).toFixed(2),
    Data2: Number(selectedSheet.getRange("A2").getValue()).toFixed(2),
    Data3: Number(selectedSheet.getRange("A3").getValue()).toFixed(2),
  } as MyData
}

interface MyData {
  Data1: string;
  Data2: string;
  Data3: string;
}

 

So the trick is to first cast the cell data to a Number type then use the toFixed(2) method to convert it to a string representation with 2 digits after the decimal point.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (2,751)