Pass multiple Excel cell values to Power Automate

user_5298189 0
Viewed 93

I'm using Office Scripts to pass an Excel cell value stored in SharePoint to Power Automate. It works great for one cell, but I can't figure out how to pass multiple cell values. Here's my current Office Script that gets the value in cell B3. When I call this from Power Automate, I can refer to the "result" dynamic content from the Run Script action to get the B3 value.

I need to also pass the values of cells B4, B5, B11, B12, and B44 to Power Automate. How can I do that?

function main(workbook: ExcelScript.Workbook) {
    // Get the current worksheet.
    let selectedSheet = workbook.getActiveWorksheet();

  let CustomerName = selectedSheet.getRange("B3");
  return JSON.stringify(CustomerName.getValue());
}
office-scripts power-automate excel
1 Answers

If you have specific values that you want to return, you can create an object that has properties for each of the values that you want to return like the customer name. Then, you would return the values as that object to Power Automate. The script would look like this:

function main(workbook: ExcelScript.Workbook): SelectedValuesToReturn {
    // Get the current worksheet.
    let selectedSheet = workbook.getWorksheet("Sheet1");
    //values of range
    let customerNameValue= selectedSheet.getRange("B3").getValue().toString();
    let b4Value = selectedSheet.getRange("B4").getValue().toString();
    let b5Value = selectedSheet.getRange("B5").getValue().toString();
    let b11Value = selectedSheet.getRange("B11").getValue().toString();
    let b12Value = selectedSheet.getRange("B12").getValue().toString();
    let b44Value = selectedSheet.getRange("B44").getValue().toString();
    return {customerName: customerNameValue, b4: b4Value, b5: b5Value, b11: b11Value, b12: b12Value, b44: b44Value}
  }
}

interface SelectedValuesToReturn{
  customerName: string,
  b4: string, 
  b5: string,
  b11: string, 
  b12: string,
  b44: string
}

If you have an entire table that you want to return, you can follow along this tutorial as well: https://docs.microsoft.com/office/dev/scripts/resources/samples/get-table-data

Oh - and one thing I would change in your current script is that when you are getting a sheet using a script in Power Automate, you should specify the sheet by its name. So, instead of using the line: workbook.getActiveWorksheet(), you would use workbook.getWorksheet("Sheet Name") Hope this helps!

user_130146900
Related