Average the column of same Excel but multiple sheets

Help Studio
1

Hey hey! My bot needs to calculate the average of “EpisodeRating” of the same excel but multiple unknown sheets. There’s a proper linq to do this? Thanks!

2

@Margarida_Ferreira

For getting the unknown sheets, you can use Excel Application scope and inside that use Get Sheets activity this will give you all the sheet names in a string array format

Use For Each activity and inside that you can use Read Range activity and in the sheet name you can put item.Tostring

Now you can use linq query to calculate the average as below thread

Hope this may help you

Thanks

3

You don’t entirely do this with LINQ.

Excel Application Scope

  • Get list of sheets
  • For Each through sheets
    – Read Range each sheet
    – Merge Datatable from currentSheetDT into FinalDT

Then you get the average from FinalDT which contains all rows in the spreadsheet.

4

@Margarida_Ferreira Try this

  • Get the sheet names from your excel and store in variable of type Array of Strings
  • Loop through each sheet and within the loop use excel scope and read the data from the current sheet
  • Apply linq and update the result in the same sheet
  • Now, loop goes back and get the next sheet and do the same process