Excel matching issue using PowerAutomateWeb

[AoLMatthieu]

Hi guys, hope you are doing good,

Having the following issue:

I have ExcelFile1 with some weekly data in Table1

I have ExcelFile2 with some daily data in Table2

Both excel are differents, not the same document

I am trying to match and retrieve the lines where Column A of both is similar. Retrieve full info of these lines from ExcelFile1 and add to it one column from ExcelFile2

Here is current flow:

-Foreach: it opens value of list lines in table1

-Foreach: it opens value of list lines in table2

-Compare when colum A from each document is similar

-Add the infos into a table variable

-Then I create a new document with a new table that have the data from there

Any Idea how to make it work ? Am I approaching it wrongly ?

Thanks,

[rzaneti]

Hi @AoLMatthieu ,

Based on your description, this is a possible approach to achieve your goal. However, it is not clear where exactly you are finding the error. 

To identify the source of the error, I ask you the following:

• Do you know which action is causing the error to your flow? If yes, please share the raw outputs from the action. If not, please share the raw inputs from your 'Ajouter a la variable de tableau', and confirm if the 'TableauFinal' variable is for data type 'Array'.
• Do you currently have any other actions after your loops? If yes, please 

To access the raw inputs/outputs, you just need to go to the flow run page, navigate until the specific action and click in these buttons:

It will open a JSON view in a sidebar or in a new tab:

Find the "body" property (you can search it with Ctrl+F). Make sure to remove any sensitive content from the image before sharing it here 

If you still have any trouble to find the raw inputs/outputs, let me know and I guide you! Also, this article provides additional details about how does these raw inputs/outputs work and how to access them: http://digitalmill.net/2023/06/29/how-to-access-the-raw-outputs-in-power-automate/

[AoLMatthieu]

Hey @rzaneti 

Thanks a lot for your answer,

The flow basically timesout while trying to get inside the first for each (which also seems limited to 256 entrance(my file has more lines than that)

I can't really see where it struggles because of that

Thanks,

[rzaneti]

Hi @AoLMatthieu ,

Thank you for the image. Apparently you are receiving a 'timeout' when looping your tables. 

As a first idea to try to solve the problem, try to change the concurrency control from your loops. You can find this option in the "settings" from your 'Apply to each' action (it will make your loop iterations run in a non sequential way):

You will find more details about how to do it in the accepted answer from this post: https://powerusers.microsoft.com/t5/Building-Flows/Apply-to-each-Timeout/td-p/539959 .

I also found this community post about it (https://powerusers.microsoft.com/t5/General-Power-Automate/Flow-timeout-on-apply-to-each/td-p/207403... ), and the conclusion is basically that you have some limits for the 'apply to each' action depending on your Power Automate license. I know that one of your tables has 247 records; how many records has the other one?

[AoLMatthieu]

Hey @rzaneti 

Thanks for your answer, i did the settings change but seems like it still doesnt work for the same issue

My documents do have 922 and 208 lines. Do you think there would be an other way to achieve what I am trying to do ?

Many thanks,

[rzaneti]

Hi @AoLMatthieu ,

A possible solution is the 'Office Scripts'. The Office Scripts are a JavaScript library that allows you to manipulate Excel and read/write data on it. It is similar to VBA, but for Excel Web. 

We can try to build a Script to add this extra column to your Excelfile1 to Excelfile2. To achieve it, please share a small sample with fake data from your table in Excelfile1 and your table in Excelfile2. This samples can have ~3 rows each... is just to understand the used data types and run the tests with a scenario close to the reality (in my environment, I will generate more rows in the same format from your sample, to achieve the approximated size of your tables).

[AoLMatthieu]

Hey @rzaneti 

Sorry for late answer, summer period :s 

I tried to create the following script but it aint working : 

Excel.run(function (context) {
  //open files
  var file1 = context.workbook.application.workbooks.open("pathfile1.xlsx");
  var file2 = context.workbook.application.workbooks.open("pathfile2.xlsx");

  // get worksheet of files
  var sheet1 = file1.worksheets.getActiveWorksheet();
  var sheet2 = file2.worksheets.getActiveWorksheet();

  // column range i want to compare between both documents
  var range1 = sheet1.getRange("H:H");
  var range2 = sheet2.getRange("H:H");

  // load data
  range1.load("values");
  range2.load("values");

  return context.sync().then(function () {
    // comparison
    var data1 = range1.values;
    for (var i = 0; i < data1.length; i++) {
      var value = data1[i][0];

      // check if it exists
      if (value !== "") {
        var existsInFile2 = false;
        var data2 = range2.values;
        for (var j = 0; j < data2.length; j++) {
          if (data2[j][0] === value) {
            existsInFile2 = true;
            break;
          }
        }

        // if exists, copy and paste into do3
        if (existsInFile2) {
          var rowToCopy = sheet1.getRange("A" + (i + 1) + ":Z" + (i + 1));
          var destinationRange = file1.worksheets.getItem("Sheet1").getRange("A1");

          // 
          rowToCopy.copyTo(destinationRange);

          // path3
          var file3Path = "pathfile3.xlsx";

          // save file3
          return file1.saveCopyAs(file3Path).then(function (file3) {
            // add infos of additional column
            var sheet3 = file3.worksheets.getActiveWorksheet();
            var range2B = sheet2.getRange("L:L");
            range2B.load("values");

            return context.sync().then(function () {
             
              var data2B = range2B.values;
              var columnBValues = [];
              for (var k = 0; k < data2B.length; k++) {
                columnBValues.push([data2B[k][0]]);
              }

              // add the new column at the end of the document of file3
              var usedRange3 = sheet3.getUsedRange();
              var lastColumn = usedRange3.getLastColumn();
              var destinationRangeB = usedRange3.getCell(1, lastColumn + 1);
              destinationRangeB.values = columnBValues;

              // save
              return file3.save();
            });
          });
        }
      }
    }

    // Close
    file1.close();
    file2.close();

    return context.sync();
  }).catch(function (error) {
    console.log(error);
  });
});

Having errors : 

See line 3, column 7: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 4, column 7: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 7, column 7: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 8, column 7: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 11, column 7: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 12, column 7: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 20, column 9: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 22, column 11: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 27, column 13: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 37, column 15: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 38, column 15: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 49, column 17: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 50, column 17: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 55, column 19: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 62, column 19: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 63, column 19: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 64, column 19: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

See line 56, column 19: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

Document look like this (attached). Document2 would look like the same with some ID in common, not all, rest of the data would also be the same, only spend column would be different

I take then the common lines (I use column campaign ID to compare) and put them in document 3, I add a new column to the document which is = to the column spend from document 2 (and of course it goes on the line with the correct ID)

Hope that's this is clear... if not let me know

Thanks a lot !

[rzaneti]

Hi @AoLMatthieu ,

I'm not sure if your approach with Office Scripts will be suitable to run integrated with Power Automate. Are you storing and running this script in the Excel Code Editor?

Some days ago (after your post), I answered another post here in the community with a partially similar problem: the user needed to collect data from an Excel file and use it in another. This is not the exact same problem as yours, but you can leverage the steps that we took there to extract data from your 2 files and insert it into the final file. 

This is the post: https://powerusers.microsoft.com/t5/Building-Flows/Compare-2-excel-and-highlight-the-row-with-differ...

Let me know if it works for you. I believe that you can use part or the code that you sent above in this new approach. 

[AoLMatthieu]

Hi @rzaneti 

I run the script from power automate and I store it in the excel code editor, this has been working for other scripts I have done, why shouldn't it there ?

Going to check it out !

Thanks,

[rzaneti]

Hi @AoLMatthieu ,

Your method is correct: you can call a script written in the Excel Code Editor from Power Automate. But the Script has some limitations, and it needs to have a main function, for example. I saw that you are using an Excel.Run method - which is used in the Office Add Ins -, but for manipulating the Scripts inside Excel Web and call them via Power Automate, you technically cannot use it. 

In summary, the logic of your code seems to be perfectly correct, but some small changes will be needed to fit it to the Office Scripts.

I'm sharing with you two articles about the basic use of the Office Scripts and its difference from the 'Add-Ins':

https://learn.microsoft.com/en-us/office/dev/scripts/resources/add-ins-differences

https://learn.microsoft.com/en-us/office/dev/scripts/develop/scripting-fundamentals

http://digitalmill.net/2023/06/19/get-started-with-office-scripts/