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

[moutinhoabreu]

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.

[Yutao · Accepted solution]

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:

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....

[moutinhoabreu]

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]

@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.