cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Schedule/Recurrence Trigger Slows Down Flow's Performance Drastically

Hi all, I was wondering if anyone could help me troubleshoot an issue I've been having where changing the flow's trigger from manual to scheduled/recurrence slows down my flow's performance drastically.

 

My flow looks like this:

 

I am taking around 300 tasks off a Microsoft Planner Board and essentially parsing that data and adding the data to an excel file. Concurrency Control is on and set to 50 for both apply to each blocks. When I set the above flow to trigger manually seen here, the flow executes successfully and finishes in about roughly 20 minutes.

 

alexu23_1-1664393473565.png

 

However, when I change the trigger to recurrence, performance slows down drastically, with my flow taking 9 hours to finish. Taking a look at the excel file the flow writes to, rows are being added at a way slower pace compared to when the flow is ran with the manual trigger. 

 

alexu23_2-1664393618479.png

 

Taking a look at one of the iterations in my apply to each for updating a row in an excel file, one update took 2 hours...

alexu23_0-1664568130542.png

 

These are what I have my settings configured as for connection based blocks such as "Get Task Details", "Get a row", "Add Row into a table":

alexu23_3-1664394838176.png

 

Does anyone know how to fix this or what the problem could potentially be? 

 

19 REPLIES 19
Sundeep_Malik
Multi Super User
Multi Super User

Hey @Anonymous 

Not sure this would help you in your case. But check out the link below. 

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-p/1624706

Anonymous
Not applicable

I want to try this, but I cant import the flow due to this error: 

alexu23_0-1664477743418.png

In the video, I don't get a link that allows me to import and create the file as a new flow.

alexu23_0-1664478327465.png

 

I found this thread: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Delete/m-p/1634375#M735 where another user had the same issue with the excel batch delete flow. Removing the excel actions from the flow allows me to import the flow, however I want the excel batch update/create flow, not delete. Would you by chance have this on hand for Version V4? @takolota could you by chance provide this? I would greatly appreciate it

 

@Anonymous @Sundeep_Malik 

 

I’m travelling back home after a hurricane, but I’ll look back over the template flow set-up when I get back. I need to test with someone if I can put in dummy custom values in Excel table actions & if that will avoid these types of issues.

Anonymous
Not applicable

No worries. Whenever you're able to do so that would be amazing and greatly appreciated.

takolota
Multi Super User
Multi Super User

@Anonymous @Sundeep_Malik 

Alright Power Automate doesn't let me put in dummy custom values or leave any of that blank in any way, probably why I didn't try that to begin with.

But I exported the package, then went to the definition file that contains the flow JSON file and manually removed any Excel file, table, or script references. I was able to load it to an import screen on a second tenant, then it gave me the usual error message shown in the video where I could open the flow and change the connections & references.

Hopefully this can work for you too, then I can add it to most of my template download pages as an additional option for people with the same issues.

I look forward to hearing how it works for you...

Anonymous
Not applicable

Hey @takolota , so I tried importing the file you provided and I am now getting a different error with still no hyperlink to save it as a new flow:

 

alexu23_0-1664499671904.png

 

😑 Of course. Because it would be too easy if that actually worked.

... Alright on to the slightly more complicated solution. Here is the Scope code for most of flow that you can directly copy, then cntrl + V paste into the "My clipboard" section of a new action menu. But this method also usually has a bit of a bug where more complicated expressions in Select actions will not copy correctly. So there will be an extra step.

Scope code to copy & paste:

{"id":"719468c0-0575-4d8b-aeb7-3adb42fcab9c","brandColor":"#8C3900","connectionReferences":{"shared_excelonlinebusiness":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/39f5641011a343e8aac23b8d01bee065"}}},"connectorDisplayName":"Control","icon":"data&colon;image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Excel_Batch_Upsert_V4.1","operationDefinition":{"type":"Scope","actions":{"Office_Script_Batch_Update":{"type":"Compose","inputs":"function main(workbook: ExcelScript.Workbook,\n  TableName: string,\n  PrimaryKeyColumnName: string,\n  ForceMode1Processing: string,\n  UpdatedData: updateddata[],\n) {\n  let table = workbook.getTable(TableName);\n  let RowNum: number;\n  let TableRange = table.getRange()\n  let TableRangeNoHeader = table.getRangeBetweenHeaderAndTotal()\n  //If it is a blank table, add an empty row to prevent errors.\n  if (TableRangeNoHeader.getRowCount() < 1) { table.addRow() } else { };\n  let TableData = TableRange.getValues()\n  let ArrayPK = table.getColumn(PrimaryKeyColumnName).getRange().getValues().join(\"#|#\").split(\"#|#\")\n  let ArrayPKErrors = new Array(\"\")\n  let ColumnCount = TableRange.getColumnCount()\n  let TableSize = (TableRange.getRowCount()) * (ColumnCount)\n  let TableSizeBatchProcLimit = 1000000\n\n  console.log(`Table size is ${TableSize} cells.`);\n  if (TableSize > TableSizeBatchProcLimit) { console.log(`You have exceeded the ${TableSizeBatchProcLimit} total table cell limit for processing larger batches on larger destination tables in the office script, please either reduce your destination table size or use a batch size of 1000 or less in the cloud flow.`) }\n  // If the table is less than 1 million cells & not something likely big enough to make errors in the V2 batch processing method then use the batch processing, else use the V1 row by row update method that isn't as affected by table size, but does have a lower cloud flow batch size limit.\n  // So if someone generally uses large batch sizes, but then their table grows past 1 million cells, then this will revert to the V1 row by row processing with the smaller batch file size limit and the cloud flow will start to error and they will need to switch their flow settings back to smaller batch sizes as well.\n  if (TableSize < TableSizeBatchProcLimit && ForceMode1Processing != \"Yes\") {\n    \n    //Iterate through each object item in the array from the flow\n    for (let i = 0; i < UpdatedData.length; i++) {\n      //If the record's Primary Key value is found continue, else post to error log\n      if (ArrayPK.indexOf(UpdatedData[i].PK) > 0) {\n        //Get the row number for the line to update by matching the foreign key from the other datasource to the primary key in Excel\n        RowNum = ArrayPK.indexOf(UpdatedData[i].PK)\n\n        //Iterate through each item or line of the current object\n        for (let j = 0; j < Object.keys(UpdatedData[i]).length - 1; j++) {\n          //Update each value for each item or column given\n          TableData[RowNum][Number(Object.keys(UpdatedData[i])[j])] = UpdatedData[i][Number(Object.keys(UpdatedData[i])[j])]\n        }\n      }\n      //Post PK not found value to ArrayPKErrors\n      else { ArrayPKErrors.push(UpdatedData[i].PK) };\n    }\n    //Get array of 1st row formulas to re-apply to columns after posting the updates to the table\n    let FirstRowFormulas = [\"\"]\n    for (let c = 0; c < ColumnCount; c++) {\n      FirstRowFormulas.push(TableRangeNoHeader.getColumn(c).getRow(0).getFormula());\n    }\n    FirstRowFormulas.shift();\n\n    // If the entire table is smaller than 50,000 cells, then just post to the table. Else batch update.\n    if (TableSize < 50000) {\n      //Post table in memory to the Excel table\n      TableRange.setValues(TableData);\n    }\n    else {\n\n      // Run The Batch Update - (Batch update code source: https://docs.microsoft.com/en-us/office/dev/scripts/resources/samples/write-large-dataset)\n      const CELLS_IN_BATCH = 15000;\n\n      console.log(`Calling update range function...`);\n      const updated = updateRangeInBatches(TableRange.getCell(0, 0), TableData, 10000);\n      if (!updated) {\n        console.log(`Update did not take place or complete. Check and run again.`);\n      }\n\n      function updateRangeInBatches(\n        startCell: ExcelScript.Range,\n        values: (string | boolean | number)[][],\n        cellsInBatch: number\n      😞 boolean {\n        const startTime = new Date().getTime();\n        console.log(`Cells per batch setting: ${cellsInBatch}`);\n\n        // Determine the total number of cells to write.\n        const totalCells = values.length * values[0].length;\n        console.log(`Total cells to update in the target range: ${totalCells}`);\n        if (totalCells <= cellsInBatch) {\n          console.log(`No need to batch -- updating directly`);\n          updateTargetRange(startCell, values);\n          return true;\n        }\n\n        // Determine how many rows to write at once.\n        const rowsPerBatch = Math.floor(cellsInBatch / values[0].length);\n        console.log(\"Rows per batch: \" + rowsPerBatch);\n        let rowCount = 0;\n        let totalRowsUpdated = 0;\n        let batchCount = 0;\n\n        // Write each batch of rows.\n        for (let i = 0; i < values.length; i++) {\n          rowCount++;\n          if (rowCount === rowsPerBatch) {\n            batchCount++;\n            console.log(`Calling update next batch function. Batch#: ${batchCount}`);\n            updateNextBatch(startCell, values, rowsPerBatch, totalRowsUpdated);\n\n            // Write a completion percentage to help the user understand the progress.\n            rowCount = 0;\n            totalRowsUpdated += rowsPerBatch;\n            console.log(`${((totalRowsUpdated / values.length) * 100).toFixed(1)}% Done`);\n          }\n        }\n        console.log(`Updating remaining rows -- last batch: ${rowCount}`)\n        if (rowCount > 0) {\n          updateNextBatch(startCell, values, rowCount, totalRowsUpdated);\n        }\n        let endTime = new Date().getTime();\n        console.log(`Completed ${totalCells} cells update. It took: ${((endTime - startTime) / 1000).toFixed(6)} seconds to complete. ${((((endTime - startTime) / 1000)) / cellsInBatch).toFixed(8)} seconds per ${cellsInBatch} cells-batch.`);\n        return true;\n      }\n      /**\n       * A helper function that computes the target range and updates. \n       */\n      function updateNextBatch(\n        startingCell: ExcelScript.Range,\n        data: (string | boolean | number)[][],\n        rowsPerBatch: number,\n        totalRowsUpdated: number\n      ) {\n        const newStartCell = startingCell.getOffsetRange(totalRowsUpdated, 0);\n        const targetRange = newStartCell.getResizedRange(rowsPerBatch - 1, data[0].length - 1);\n        console.log(`Updating batch at range ${targetRange.getAddress()}`);\n        const dataToUpdate = data.slice(totalRowsUpdated, totalRowsUpdated + rowsPerBatch);\n        try {\n          targetRange.setValues(dataToUpdate);\n        } catch (e) {\n          throw `Error while updating the batch range: ${JSON.stringify(e)}`;\n        }\n        return;\n      }\n      /**\n       * A helper function that computes the target range given the target range's starting cell\n       * and selected range and updates the values.\n       */\n      function updateTargetRange(\n        targetCell: ExcelScript.Range,\n        values: (string | boolean | number)[][]\n      ) {\n        const targetRange = targetCell.getResizedRange(values.length - 1, values[0].length - 1);\n        console.log(`Updating the range: ${targetRange.getAddress()}`);\n        try {\n          targetRange.setValues(values);\n        } catch (e) {\n          throw `Error while updating the whole range: ${JSON.stringify(e)}`;\n        }\n        return;\n      }\n    }\n    //Re-apply the formulas from the 1st row to the entire columns after the update\n    for (let f = 0; f < ColumnCount; f++) {\n      if (FirstRowFormulas[f].toString().startsWith(\"=\")) {\n        TableRangeNoHeader.getColumn(f).getRow(0).setFormula(FirstRowFormulas[f])\n        TableRangeNoHeader.getColumn(f).getRow(0).autoFill(table.getRangeBetweenHeaderAndTotal().getColumn(f).getAddress(), ExcelScript.AutoFillType.fillDefault)\n      }\n    }\n  }\n  // Update row by row if the table is too large\n  else {\n    //Iterate through each object item in the array from the flow\n    for (let i = 0; i < UpdatedData.length; i++) {\n      //If the record's Primary Key value is found continue, else post to error log\n      if (ArrayPK.indexOf(UpdatedData[i].PK) > 0) {\n        //Get the row number for the line to update by matching the foreign key from the other datasource to the primary key in Excel\n        RowNum = ArrayPK.indexOf(UpdatedData[i].PK)\n\n        //Iterate through each item or line of the current object\n        for (let j = 0; j < Object.keys(UpdatedData[i]).length - 1; j++) {\n          //Update each value for each item or column given\n          TableRange.getCell(RowNum, Number(Object.keys(UpdatedData[i])[j])).setValue(UpdatedData[i][Number(Object.keys(UpdatedData[i])[j])])\n        }\n      }\n      //Post PK not found value to ArrayPKErrors\n      else { ArrayPKErrors.push(UpdatedData[i].PK) }\n    }\n  }\n  //Post ArrayPKErrors to flow results\n  console.log(\"Any primary key values not found are listed in the result array.\")\n  ArrayPKErrors.shift();\n  return ArrayPKErrors;\n}\n//The 1st few column indexes are provided incase anyone edits this for something else later. But the current flow & scripts should work fine with only the PK column here.\ninterface updateddata {\n  '0': (string | undefined),\n  '1': (string | undefined),\n  '2': (string | undefined),\n  '3': (string | undefined),\n  '4': (string | undefined),\n  '5': (string | undefined),\n  'PK': (string | undefined)\n}","runAfter":{},"description":"Go to an online Excel table, go to the Automate tab, select New Script, then replace the placeholder script code by pasting all this in the code editor. You may want to name the script BatchUpdateV4.","metadata":{"operationMetadataId":"7922f637-e66f-45ad-a5e1-c69106b4a210"}},"Office_Script_Batch_Create":{"type":"Compose","inputs":"function main(workbook: ExcelScript.Workbook,\n  TableName: string,\n  PrimaryKeyColumnName: string,\n  ForceMode1Processing: string,\n  CreateData: createdata[],\n) {\n  let table = workbook.getTable(TableName);\n  let TableRange = table.getRange();\n  let TableRangeNoHeader = table.getRangeBetweenHeaderAndTotal();\n  //If it is a blank table, add an empty row to prevent errors.\n  if (TableRangeNoHeader.getRowCount() < 1) { table.addRow() } else { };\n  let TableData = TableRange.getValues();\n  let PKColumnIndex = table.getColumn(PrimaryKeyColumnName).getIndex();\n  CreateData = JSON.parse(JSON.stringify(CreateData).split('\"PK\"').join(`\"${PKColumnIndex}\"`))\n  let ColumnCount = TableRange.getColumnCount();\n  let InitialNumberOfTableRows = TableRange.getRowCount();\n  let TableSize = (InitialNumberOfTableRows) * (ColumnCount);\n  let TableSizeBatchProcLimit = 1000000;\n  //const EmptyRow = \",\".repeat(TableNumberOfColumns - 1).split(\",\"); \n  let RowNum: number;\n  RowNum = InitialNumberOfTableRows;\n\n  console.log(`Table size is ${TableSize} cells.`);\n  if (TableSize > TableSizeBatchProcLimit) { console.log(`You have exceeded the ${TableSizeBatchProcLimit} total table cell limit for processing larger batches on larger destination tables in the office script, please either reduce your destination table size or use a batch size of 1000 or less in the cloud flow.`) }\n  // If the table is less than 1 million cells & not something likely big enough to make errors in the V2 batch processing method then use the batch processing, else use the V1 row by row create method that isn't as affected by table size, but does have a lower cloud flow batch size limit.\n  // So if someone generally uses large batch sizes, but then their table grows past 1 million cells, then this will revert to the V1 row by row processing with the smaller batch file size limit and the cloud flow will start to error and they will need to switch their flow settings back to smaller batch sizes as well.\n  if (TableSize < TableSizeBatchProcLimit && ForceMode1Processing != \"Yes\") {\n\n    //-1 to 0 index the RowNum\n    RowNum = RowNum - 1;\n    \n    //Iterate through each object item in the array from the flow\n    for (let i = 0; i < CreateData.length; i++) {\n      //Create an empty row at the end of the 2D table array & increase RowNum by 1 for the next line updating that row\n      TableData.push(\",\".repeat(ColumnCount - 1).split(\",\"));\n      RowNum++;\n\n      //Iterate through each item or line of the current object\n      for (let j = 0; j < Object.keys(CreateData[i]).length; j++) {\n        //Create each value for each item or column given\n        TableData[RowNum][Number(Object.keys(CreateData[i])[j])] = CreateData[i][Number(Object.keys(CreateData[i])[j])];\n      }\n    }\n    //Get array of 1st row formulas to re-apply to columns after posting the updates to the table\n    let FirstRowFormulas = [\"\"]\n    for (let c = 0; c < ColumnCount; c++) {\n      FirstRowFormulas.push(TableRangeNoHeader.getColumn(c).getRow(0).getFormula());\n    }\n    FirstRowFormulas.shift();\n\n      // Run The Batch Create - (Batch update code source: https://docs.microsoft.com/en-us/office/dev/scripts/resources/samples/write-large-dataset)\n      const CELLS_IN_BATCH = 15000;\n\n      console.log(`Calling update range function...`);\n      const updated = updateRangeInBatches(TableRange.getCell(0, 0), TableData, 10000);\n      if (!updated) {\n        console.log(`Update did not take place or complete. Check and run again.`);\n      }\n\n      function updateRangeInBatches(\n        startCell: ExcelScript.Range,\n        values: (string | boolean | number)[][],\n        cellsInBatch: number\n      😞 boolean {\n        const startTime = new Date().getTime();\n        console.log(`Cells per batch setting: ${cellsInBatch}`);\n\n        // Determine the total number of cells to write.\n        const totalCells = values.length * values[0].length;\n        console.log(`Total cells to update in the target range: ${totalCells}`);\n        if (totalCells <= cellsInBatch) {\n          console.log(`No need to batch -- updating directly`);\n          updateTargetRange(startCell, values);\n          return true;\n        }\n\n        // Determine how many rows to write at once.\n        const rowsPerBatch = Math.floor(cellsInBatch / values[0].length);\n        console.log(\"Rows per batch: \" + rowsPerBatch);\n        let rowCount = 0;\n        let totalRowsUpdated = 0;\n        let batchCount = 0;\n\n        // Write each batch of rows.\n        for (let i = 0; i < values.length; i++) {\n          rowCount++;\n          if (rowCount === rowsPerBatch) {\n            batchCount++;\n            console.log(`Calling update next batch function. Batch#: ${batchCount}`);\n            updateNextBatch(startCell, values, rowsPerBatch, totalRowsUpdated);\n\n            // Write a completion percentage to help the user understand the progress.\n            rowCount = 0;\n            totalRowsUpdated += rowsPerBatch;\n            console.log(`${((totalRowsUpdated / values.length) * 100).toFixed(1)}% Done`);\n          }\n        }\n        console.log(`Updating remaining rows -- last batch: ${rowCount}`)\n        if (rowCount > 0) {\n          updateNextBatch(startCell, values, rowCount, totalRowsUpdated);\n        }\n        let endTime = new Date().getTime();\n        console.log(`Completed ${totalCells} cells update. It took: ${((endTime - startTime) / 1000).toFixed(6)} seconds to complete. ${((((endTime - startTime) / 1000)) / cellsInBatch).toFixed(8)} seconds per ${cellsInBatch} cells-batch.`);\n        return true;\n      }\n\t\t\t/**\n\t\t\t * A helper function that computes the target range and updates. \n\t\t\t */\n      function updateNextBatch(\n        startingCell: ExcelScript.Range,\n        data: (string | boolean | number)[][],\n        rowsPerBatch: number,\n        totalRowsUpdated: number\n      ) {\n        const newStartCell = startingCell.getOffsetRange(totalRowsUpdated, 0);\n        const targetRange = newStartCell.getResizedRange(rowsPerBatch - 1, data[0].length - 1);\n        console.log(`Updating batch at range ${targetRange.getAddress()}`);\n        const dataToUpdate = data.slice(totalRowsUpdated, totalRowsUpdated + rowsPerBatch);\n        try {\n          targetRange.setValues(dataToUpdate);\n        } catch (e) {\n          throw `Error while updating the batch range: ${JSON.stringify(e)}`;\n        }\n        return;\n      }\n\t\t\t/**\n\t\t\t * A helper function that computes the target range given the target range's starting cell\n\t\t\t * and selected range and updates the values.\n\t\t\t */\n      function updateTargetRange(\n        targetCell: ExcelScript.Range,\n        values: (string | boolean | number)[][]\n      ) {\n        const targetRange = targetCell.getResizedRange(values.length - 1, values[0].length - 1);\n        console.log(`Updating the range: ${targetRange.getAddress()}`);\n        try {\n          targetRange.setValues(values);\n        } catch (e) {\n          throw `Error while updating the whole range: ${JSON.stringify(e)}`;\n        }\n        return;\n      }\n    //Re-apply the formulas from the 1st row to the entire columns after the update\n    for (let f = 0; f < ColumnCount; f++) {\n      if (FirstRowFormulas[f].toString().startsWith(\"=\")) {\n        TableRangeNoHeader.getColumn(f).getRow(0).setFormula(FirstRowFormulas[f])\n        TableRangeNoHeader.getColumn(f).getRow(0).autoFill(table.getRangeBetweenHeaderAndTotal().getColumn(f).getAddress(), ExcelScript.AutoFillType.fillDefault)\n      }\n    }\n  }\n  // Create row by row if the table is too large\n  else {\n    //Iterate through each object item in the array from the flow\n    for (let i = 0; i < CreateData.length; i++) {\n      //Create an empty row at the end of the 2D table array & increase RowNum by 1 for the next line updating that row\n      table.addRow()\n      RowNum = RowNum + 1\n      //Iterate through each item or line of the current object\n      for (let j = 0; j < Object.keys(CreateData[i]).length; j++) {\n        //Create each value for each item or column given\n        TableRange.getCell(RowNum, Number(Object.keys(CreateData[i])[j])).setValue(CreateData[i][Number(Object.keys(CreateData[i])[j])])\n      }\n    }\n  }\n}\n//The 1st few column indexes are provided incase anyone edits this for something else later. But the current flow & scripts should work fine with only the PK column here.\ninterface createdata {\n  '0': (string | undefined),\n  '1': (string | undefined),\n  '2': (string | undefined),\n  '3': (string | undefined),\n  '4': (string | undefined),\n  '5': (string | undefined),\n  'PK': (string | undefined)\n}","runAfter":{"Office_Script_Batch_Update":["Succeeded"]},"description":"Go to an online Excel table, go to the Automate tab, select New Script, then replace the placeholder script code by pasting all this in the code editor. You may want to name the script BatchCreateV4.","metadata":{"operationMetadataId":"f8ccc556-b345-429e-8c51-af7bb6b42452"}},"List_rows_Sample_source_data_-Placeholder-":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_excelonlinebusiness","operationId":"GetItems","apiId":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness"},"parameters":{"source":"me","drive":"b!y2rxipfTvUi7ALZMiyxLa89aQqkbMORMksjFtKI_dfKgJA8V37mjQICJm9mYgy-T","file":"01JWBUU4EEP2QHP4DFINBKWWCSYPOMRAA5","table":"{DDBD638A-A748-4FA1-9540-6DCA855A8A93}","$top":500,"dateTimeFormat":"Serial Number"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{"Office_Script_Batch_Create":["Succeeded"]},"description":"Pagination set to 25000. Initial top count set to a 500 row batch.","runtimeConfiguration":{"paginationPolicy":{"minimumItemCount":25000}},"metadata":{"01PCEUDVFYX74MYI46EJCZF6AUHSUSPNMU":"/SourceData.xlsx","operationMetadataId":"408aee2b-05c0-44e2-8468-78c78fb0530d","tableId":"{DDBD638A-A748-4FA1-9540-6DCA855A8A93}","01JWBUU4EEP2QHP4DFINBKWWCSYPOMRAA5":"/SourceData.xlsx"}},"Match_new_and_existing_data_key_values_then_batch_update":{"type":"Scope","actions":{"List_rows_present_in_a_table_Get_header_sample":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_excelonlinebusiness","operationId":"GetItems","apiId":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness"},"parameters":{"source":"me","drive":"b!y2rxipfTvUi7ALZMiyxLa89aQqkbMORMksjFtKI_dfKgJA8V37mjQICJm9mYgy-T","file":"01JWBUU4DRJEFUSQGBG5E2QSRSXUIHVZNB","table":"{DDBD638A-A748-4FA1-9540-6DCA855A8A93}","$top":2},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{"Select_Generate_update_data":["Succeeded"]},"description":"Input the Location, Doc Library, File, & Table information you normally would to get the table you want to update. It isn't meant to get the data, just the headers, so it is limited to just the 1st few rows.","metadata":{"01OVBUQJMSNAHSGEXJCNFISQKOJWJ2ZY3N":"/Book.xlsx","operationMetadataId":"64ff543c-8881-4e6c-b49e-7bbadd6fbdd2","tableId":"{DDBD638A-A748-4FA1-9540-6DCA855A8A93}","01PCEUDVFYX74MYI46EJCZF6AUHSUSPNMU":"/Book.xlsx","01PCEUDVCCZ7IYLT5RUBC3N6VXZWCYFNNI":"/DestinationData.xlsx","01JWBUU4DRJEFUSQGBG5E2QSRSXUIHVZNB":"/DestinationData.xlsx"}},"Compose_Reformat_headers":{"type":"Compose","inputs":"@skip(split(string(first(outputs('List_rows_present_in_a_table_Get_header_sample')?['body/value'])), '\",\"'), 2)","runAfter":{"List_rows_present_in_a_table_Get_header_sample":["Succeeded"]},"metadata":{"operationMetadataId":"54e295a2-10ec-41c8-b3f1-aa50c28f3e0c"}},"Select_List_header_column_numbers":{"type":"Select","inputs":{"from":"@range(0, length(outputs('Compose_Reformat_headers')))","select":{"@{item()}":"@split(outputs('Compose_Reformat_headers')[item()], '\":\"')?[0]"}},"runAfter":{"Compose_Reformat_headers":["Succeeded"]},"description":"This action will fail if your table is empty & doesn't have any rows. Make sure to add at least one blank row to any empty tables before starting.","metadata":{"operationMetadataId":"87bfb21b-ef06-4f23-99dc-20b0b95e4f0c"}},"Select_Generate_update_data":{"type":"Select","inputs":{"from":"@outputs('List_rows_Sample_source_data_-Placeholder-')?['body/value']","select":{"PrimaryKey":"@{item()?['SourceEmail']}@{item()?['SourceDate']}","DestinationC2":"@item()?['SourceC2']","DestinationC3":"@item()?['SourceC3']","DestinationC4":"@item()?['SourceC4']","DestinationC5":"@item()?['SourceC5']","DestinationC6":"@item()?['SourceC6']","DestinationC1":"@item()?['SourceC1']","DestinationC7":"@item()?['SourceC7']","DestinationC8":"@if(equals(item()?['SourceC6'], 'String5'), 'Edit where C6 = String5', '')","DestinationC9":"@null","DestinationC10":"@item()?['SourceC10']","DestinationEmail":"@item()?['SourceEmail']","DestinationDate":"@item()?['SourceDate']"}},"runAfter":{},"description":" Except PrimaryKey, input EXACT Excel column names on the left & updated values from other sources on the right. Set the PrimaryKey row /w matching key values from the other source. Blank '' values don't alter data, null values update cells to empty.","metadata":{"operationMetadataId":"b221bdd9-0a32-43e4-8498-a8116e6b0a83"}},"Select_Reformat_update_data_for_script":{"type":"Select","inputs":{"from":"@body('Select_Generate_update_data')","select":{"PK":"@item()['PrimaryKey']","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 1), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 1))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 1), '####')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 1), -1), \r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 1))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 2), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 2))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 2), '##&^&^&^~|^<##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 2), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 2))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 3), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 3))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 3), '##&^&^&`##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 3), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 3))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 4), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 4))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 4), '##&^&^&`&^##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 4), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 4))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 5), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 5))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 5), '##&^&`##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 5), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 5))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 6), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 6))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 6), '##&`##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 6), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 6))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 7), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 7))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 7), '##&`&`&`##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 7), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 7))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 8), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 8))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 8), '##&`&`&`^<^<##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 8), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 8))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 9), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 9))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 9), '##&`&`~|^<##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 9), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 9))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 10), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 10))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 10), '##^<##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 10), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 10))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 11), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 11))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 11), '##^<&^##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 11), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 11))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 12), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 12))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 12), '##^<&^&`##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 12), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 12))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 13), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 13))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 13), '##^<&^&`~|##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 13), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 13))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 14), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 14))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 14), '##^<&^&`~|~|##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 14), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 14))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 15), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 15))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 15), '##^<&^^<^<~|##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 15), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 15))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 16), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 16))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 16), '##^<&^^<~|##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 16), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 16))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 17), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 17))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 17), '##^<&^^<~|~|##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 17), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 17))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 18), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 18))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 18), '##^<&^~|##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 18), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 18))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 19), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 19))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 19), '##^<&`&^##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 19), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 19))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 20), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 20))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 20), '##^<&`&^~|##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 20), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 20))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 21), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 21))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 21), '##^<&`&`##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 21), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 21))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 22), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 22))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 22), '##^<&`&`~|##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 22), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 22))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 23), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 23))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 23), '##^<^~|<^<~|##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 23), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 23))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 24), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 24))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 24), '##^<^<##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 24), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 24))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 25), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 25))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 25), '##^<^<&^##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 25), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 25))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 26), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 26))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 26), '##^<^<&^&`##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 26), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 26))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 27), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 27))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 27), '##^<^<&`##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 27), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 27))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 28), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 28))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 28), '##^<^<&`&`##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 28), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 28))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 29), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 29))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 29), '##^<^<&`~|##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 29), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 29))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 30), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 30))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 30), '##^<^<^<##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 30), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 30))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 31), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 31))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 31), '##^<^<^<&^&`##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 31), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 31))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 32), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 32))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 32), '##^<^<^<&^&`~|##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 32), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 32))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 33), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 33))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 33), '##^<^<^<&`##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 33), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 33))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 34), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 34))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 34), '##^<^<^<&`&^##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 34), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 34))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 35), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 35))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 35), '##^<^<^<&`&`##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 35), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 35))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 36), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 36))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 36), '##^<^<^<&`~|##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 36), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 36))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 37), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 37))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 37), '##^<^<^<^<##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 37), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 37))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 38), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 38))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 38), '##^<^<^<^<&^~|##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 38), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 38))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 39), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 39))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 39), '##^<^<^<^<^<~|##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 39), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 39))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 40), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 40))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 40), '##^<^<^<^<~|##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 40), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 40))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 41), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 41))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 41), '##^<^<^<^<~|~|##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 41), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 41))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 42), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 42))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 42), '##^<^<^<~|##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 42), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 42))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 43), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 43))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 43), '##^<^<^<~|&^##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 43), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 43))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 44), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 44))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 44), '##^<^<^<~|^<##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 44), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 44))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 45), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 45))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 45), '##^<^<~|##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 45), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 45))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 46), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 46))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 46), '##^<^<~|&^##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 46), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 46))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 47), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 47))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 47), '##^<^<~|^<##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 47), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 47))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 48), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 48))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 48), '##^<^<~|^<~|##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 48), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 48))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 49), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 49))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 49), '##^<~|^<##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 49), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 49))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 50), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 50))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 50), '##^<~|^<&^##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 50), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 50))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 51), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 51))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 51), '##^<~|^<^<~|&`##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 51), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 51))]], '')","@{if(\r\nif(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 52), -1),\r\nnot(equals(item()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 52))]], '')),\r\nfalse),\r\nnthIndexOf(join(body('CheckInputs'), ''), 'T', 52), '##&`~|~|^<~|&`##')}":"@if(greater(nthIndexOf(join(body('CheckInputs'), ''), 'T', 52), -1),\r\nitem()?[outputs('ReformatHeaderObject')?[string(nthIndexOf(join(body('CheckInputs'), ''), 'T', 52))]], '')"}},"runAfter":{"CheckInputs":["Succeeded"]},"description":"Currently set to handle up to 50 columns across a table with any length of total columns. Formatting the items to use the destination column indexes as key labels & remove any unused values or cells helps decrease payload size & increase performance.","metadata":{"operationMetadataId":"30ae8314-fc0e-4757-81e3-20004b13a000"}},"Check_if_-Generate_update_data-_column_names_exist_in_Excel":{"type":"Scope","actions":{"Filter_array_Column_names_not_in_Excel_headers":{"type":"Query","inputs":{"from":"@body('Select_Update_data_column_names')","where":"@and(not(contains(body('Select_Excel_column_names'), item())), not(equals('PrimaryKey', item())))"},"runAfter":{"Select_Update_data_column_names":["Succeeded"]},"metadata":{"operationMetadataId":"927bab24-7614-4f67-80dc-9ff3db2792ad"}},"Condition_Check_column_name_error,_else_continue":{"type":"If","expression":{"greater":["@length(body('Filter_array_Column_names_not_in_Excel_headers'))",0]},"actions":{"Terminate":{"metadata":{"operationMetadataId":"0ac80bea-051c-4fa1-881c-57fe34d0ff6b"},"type":"Terminate","inputs":{"runStatus":"Failed","runError":{"code":"404","message":"One or more of the column names used in the \"Generate update data\" action was not found in the list of current Excel column names for the given \"List rows present in table Get header sample\" table.\nYou may want to ensure all the column names in the update data action exactly match the column names in Excel. Otherwise, not all the desired columns will be updated through the Excel script.\n\nColumn Names Not Found in Excel\n@{body('Filter_array_Column_names_not_in_Excel_headers')}"}},"runAfter":{}}},"runAfter":{"Filter_array_Column_names_not_in_Excel_headers":["Succeeded"]},"metadata":{"operationMetadataId":"22989d88-de35-42eb-a8d8-e15ccb456841"}},"Select_Excel_column_names":{"type":"Select","inputs":{"from":"@body('Select_List_header_column_numbers')","select":"@item()[split(string(item()), '\"')[1]]"},"runAfter":{},"metadata":{"operationMetadataId":"82dd3d4f-12a9-4d10-a8d6-51c06e0e4c72"}},"Select_Update_data_column_names":{"type":"Select","inputs":{"from":"@take(split(string(first(body('Select_Generate_update_data'))), '\":'), sub(length(split(string(first(body('Select_Generate_update_data'))), '\":')), 1))","select":"@split(item(), '\"')[sub(length(split(item(), '\"')), 1)]"},"runAfter":{"Select_Excel_column_names":["Succeeded"]},"metadata":{"operationMetadataId":"512da0e8-3002-4b9f-8175-493a74622110"}}},"runAfter":{"ReformatHeaderObject":["Succeeded"]},"metadata":{"operationMetadataId":"3e9f70ab-d52e-4ca9-a973-175ceaf161fe"}},"Run_script_Update_Excel_rows":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_excelonlinebusiness","operationId":"RunScriptProd","apiId":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness"},"parameters":{"source":"me","drive":"b!y2rxipfTvUi7ALZMiyxLa89aQqkbMORMksjFtKI_dfKgJA8V37mjQICJm9mYgy-T","file":"01JWBUU4DRJEFUSQGBG5E2QSRSXUIHVZNB","scriptId":"ms-officescript%3A%2F%2Fonedrive_business_itemlink%2F01JWBUU4CI62ZOXSKDIJD2KRGYGN2R4NX4","ScriptParameters/TableName":"Table1","ScriptParameters/PrimaryKeyColumnName":"GeneratePK","ScriptParameters/ForceMode1Processing":"No","ScriptParameters/UpdatedData":"@body('Select_Remove_blank_lines')"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{"Select_Remove_blank_lines":["Succeeded"]},"description":"Change Location, Doc Library, File, Script, TableName, PrimaryKeyColumnName, & KeepFormula fields to match your use-case. Make sure the primary key column listed is the column you want to be matched with the \"Generate update data\" PrimaryKey values.","limit":{"timeout":"PT15M"},"metadata":{"01PCEUDVFYX74MYI46EJCZF6AUHSUSPNMU":"/Book.xlsx","operationMetadataId":"713f593f-c5e4-43b6-ad08-3a71051bbf87","tableId":null,"01PCEUDVCCZ7IYLT5RUBC3N6VXZWCYFNNI":"/DestinationData.xlsx","01JWBUU4DRJEFUSQGBG5E2QSRSXUIHVZNB":"/DestinationData.xlsx"}},"Select_Remove_blank_lines":{"type":"Select","inputs":{"from":"@body('Select_Reformat_update_data_for_script')","select":"@json(\r\nreplace(\r\nreplace(\r\nreplace(\r\nreplace(\r\nreplace(\r\nreplace(\r\nreplace(\r\nreplace(string(item()), '\"##', ''), \r\n'##\":\"\",', ''), \r\n'~|', ''), \r\n'&^', ''), \r\n'&`', ''), \r\n'^<', ''),\r\n'##\":\"\"}', '}'),\r\n'null', '\"\"'))"},"runAfter":{"Select_Reformat_update_data_for_script":["Succeeded"]},"description":"Formatting the items to use the destination column indexes as key labels & remove any unused values or cells helps decrease payload size & increase performance.","metadata":{"operationMetadataId":"8c26a92e-a3f7-4f58-8f47-ef177a84a695"}},"ReformatHeaderObject":{"type":"Compose","inputs":"@json(replace(replace(replace(string(body('Select_List_header_column_numbers')), '[{', '{'), '}]', '}'), '},{', ','))","runAfter":{"Select_List_header_column_numbers":["Succeeded"]},"description":"Convert the array of JSON objects to a searchable object","metadata":{"operationMetadataId":"69b1d189-d56f-45fc-8dbb-a49740832a64"}},"CheckInputs":{"type":"Select","inputs":{"from":"@range(0, max(52, length(body('Select_List_header_column_numbers'))))","select":"@if(contains(\r\nstring(body('Select_Generate_update_data')[0]), \r\nif(\r\n\tcontains(string(outputs('ReformatHeaderObject')), concat('\"', item(), '\":')), \r\n\tconcat('\"', outputs('ReformatHeaderObject')[string(item())], '\":'), \r\n\t'_404NA_')),\r\n'T',\r\n'F')"},"runAfter":{"Check_if_-Generate_update_data-_column_names_exist_in_Excel":["Succeeded"]},"description":"Returns array of T or F where the column # matching the array index # is T if the 1st item in the Generate Update Data has a key label matching a ReformatHeaderObj value. So, is the column listed in the update data?","metadata":{"operationMetadataId":"1b764577-ee2a-45dd-9200-aa46db041631"}}},"runAfter":{"List_rows_Sample_source_data_-Placeholder-":["Succeeded"]},"description":"Fill out the Generate update data, List rows Get header sample, & Run script Update Excel rows actions with your data, workbook, & script information. Currently set to handle up to 50 columns of updates.","metadata":{"operationMetadataId":"175119c4-e881-4899-81d6-14aaa7f741f8"}},"Get_records_not_in_found_in_table_then_batch_create":{"type":"Scope","actions":{"Filter_array_Get_records_not_found_in_table":{"type":"Query","inputs":{"from":"@body('Select_Remove_blank_lines')","where":"@contains(outputs('Run_script_Update_Excel_rows')?['body/result'], item()['PK'])"},"runAfter":{},"description":"Get the records from a previous batch update Select action where the record primary key is in the list of primary keys not found in the Run script Update Excel action","metadata":{"operationMetadataId":"c340f3a3-1696-4ebb-9d2b-3b0463ae3773"}},"Run_script_Create_Excel_rows":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_excelonlinebusiness","operationId":"RunScriptProd","apiId":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness"},"parameters":{"source":"me","drive":"b!y2rxipfTvUi7ALZMiyxLa89aQqkbMORMksjFtKI_dfKgJA8V37mjQICJm9mYgy-T","file":"01JWBUU4DRJEFUSQGBG5E2QSRSXUIHVZNB","scriptId":"ms-officescript%3A%2F%2Fonedrive_business_itemlink%2F01JWBUU4F2LHYL4XTPENFZJI3WGXKF6ZWM","ScriptParameters/TableName":"Table1","ScriptParameters/PrimaryKeyColumnName":"GeneratePK","ScriptParameters/ForceMode1Processing":"No","ScriptParameters/CreateData":"@body('Filter_array_Get_records_not_found_in_table')"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{"Filter_array_Get_records_not_found_in_table":["Succeeded"]},"description":"Change Location, Doc Library, File, Script, TableName, PrimaryKeyColumnName, & KeepFormulas fields to match your use-case. Make sure the primary key column listed is the column you want to be matched with the \"Generate update data\" PrimaryKey values.","limit":{"timeout":"PT15M"},"metadata":{"01PCEUDVFYX74MYI46EJCZF6AUHSUSPNMU":"/Book.xlsx","operationMetadataId":"713f593f-c5e4-43b6-ad08-3a71051bbf87","tableId":null,"01PCEUDVCCZ7IYLT5RUBC3N6VXZWCYFNNI":"/DestinationData.xlsx","01JWBUU4DRJEFUSQGBG5E2QSRSXUIHVZNB":"/DestinationData.xlsx"}}},"runAfter":{"Match_new_and_existing_data_key_values_then_batch_update":["Succeeded"]},"description":"Fill out the Run script Create Excel rows action with your data, workbook, & script information.","metadata":{"operationMetadataId":"9e2a036b-c6aa-461a-81f3-efed57aa2f71"}}},"runAfter":{}}}


So to get the correct set-up for the more complicated "Select Reformat update data for script" action, you will need to import this flow package with just that Select action and some empty reference actions. That avoids any potential connector issues since it is just select & compose actions. So you will import the flow without the problem connectors & delete the 1st scope with the set of empty reference actions. Then you can paste the Scope above for the rest of the flow into that imported flow with just the given Select action.
Then you can find the same bad copy of the Select action in the copied Scope & replace it with the good imported copy of the action. Then you will redo the reference & formula in the following Select Remove blank lines action to properly reference & process the data from the good copy of the Select Reformat update data for script action.

Anonymous
Not applicable

Great, thank you for your quick replies! Truly appreciate it. I will try this tomorrow morning and let you know if everything succeeds 

takolota
Multi Super User
Multi Super User

@Yutao 

 

Is there any way Microsoft can fix the flow imports to be easier across tenants and/or fix the bugs when copying flow actions with more complex formulas to clipboard?

 

These work-arounds are getting ridiculous.

Anonymous
Not applicable

Hey @takolota , so I am currently attempting to set this up and wanted to make sure I am doing this right. I imported the flow, deleted the 1st scope copied the code into my clipboard and replaced the "Select Reformat update data for script" inside the copied code with the one that was provided when I imported the flow.  Couple screenshots I wanted to address to ensure I am doing everything correctly:

 

Do I need to change the formatting of the formulas for blocks if they look like this?

 

alexu23_0-1664563098455.png

 

alexu23_1-1664563123164.png

 

UpdatedData should be Select Remove Blank Lines here correct?

 

alexu23_2-1664563211797.png

 

And updatedData should be filter array get records not found in table here?

alexu23_3-1664563263629.png

 

Also, in my case I am getting data from each task on planner one by one using 'List tasks' and 'Get task Details'. This is done in 'Apply to each 2' seen above. In my case, should I paste the entire Excel Batch Upsert V4.1 inside my apply to each and pass in each task's data into the Select Generate update data?

alexu23_5-1664563654102.png

 

 

alexu23_4-1664563567517.png

 

Lastly, if I want to create/update rows in more than one excel file, would I just add another one of these actions and reference another excel file?

alexu23_6-1664563806241.png

 

takolota
Multi Super User
Multi Super User

@Anonymous 

 

For the Select Reformat update data action, that is the formatting error we were trying to avoid by importing that part of the flow. Did you copy & paste that Select action in, or did you drag it from the imported flow pieces?

 

 

But, a much bigger issue is how you are trying to use this.

The main benefit of this template is that it can send entire arrays of data to Excel at once instead of doing one row at a time in an Apply to each.

If you are using something that requires a loop & individual actions to get the source data like “Get task details”, then this template won’t help much because you’re still required to loop record by record to get the data.

 

Can you share the rest of your original flow & maybe I can suggest alternative improvements?

Anonymous
Not applicable

I had a feeling that would be the case...My main issue isn't the run time of flow with the manual trigger, but I thought maybe using this batch update/create could solve the issue as I'm not making as many api calls to excel such as 'Add row' and 'get row' which potentially could be the reason why the recurrence trigger is making my flow take so long. I'll dm my flow to you. Thank you.

Sure. You could technically save all that data pulled row by row to a Compose, then use a batch method to upload the array of data from that Compose, but if you're already adding an extra action to record each line of data, then why not just make it more direct & use a create or update Excel action there?

It may still give some improvement if the Update Excel logic is more complex & requires multiple actions, but it would only be a minor improvement and add unneeded complexity.

takolota
Multi Super User
Multi Super User

@Anonymous 

Alright a couple things on your flow...

Most importantly you likely have at least 5 different Excel actions in your loop that is set to do 50 runs concurrently. So if those 50 concurrent runs each do 2 loops per minute that is like 500 Excel api calls per minute. Five times the maximum allowed before its going to throttle your flow and seriously slow it down. 
https://sharepains.com/2018/07/17/power-automate-connector-limits/

So you can try...
-Creating a different new connection for each Excel action in your flow. If the limits on Excel are really per connection, then that should avoid throttling.
-Or you could limit your concurrency setting to something like 5 concurrent runs.


Secondly, you have a lot of actions running in this loop. Depending on your current license & how many items this flow must process, you may risk going over your license's total daily limit for all calls.
For this you can make your flow more efficient with less actions in the loop, reduce the number of actions & calls in other high-throughput flows on your account, buy a higher level license, purchase a flow capacity add-on, and/or start licensing you high-throughput flows with per-flow licenses.

For this specific flow I have two suggestions to make it require significantly less actions.
You have this long section with many Compose actions just for re-labeling things in dynamic content.
LongComposeSection.png
I'm not sure if you really need to save all these in Composes instead of using the original dynamic content, but if this is the way you prefer to do it, you can instead create one JSON object with all the relabeling and one Parse JSON action to then get all those items back in dynamic content.
LongComposeSectionReplacement.png
Scope code to copy & paste into the "My clipboard" tab of a new action

{"id":"8d1ac7c2-045d-4423-a457-f9477b7091fa","brandColor":"#8C3900","connectionReferences":{"shared_excelonlinebusiness_1":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/39f5641011a343e8aac23b8d01bee065"}},"shared_office365users":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_office365users/connections/88b73cab99bd4598bfce9895cb9a47b2"}},"shared_planner_1":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_planner/connections/b858ec8fa9754e709a9b9352ee37dcd5"}},"shared_teams_1":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_teams/connections/shared-teams-8112094b-7e7a-41be-97f4-1297c30cbf15"}},"shared_teams":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_teams/connections/32b18c72239040c2a48639c23542ef1a"}},"shared_excelonlinebusiness":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/shared-excelonlinebu-a88ef57a-9a34-4a54-b1a5-4a442e1c2cb1"}},"shared_office365users_1":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_office365users/connections/e1ba32aaddef4f92aae9f5edbb53f7a1"}},"shared_planner":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_planner/connections/shared-planner-f54d7002-d266-46e1-b80b-dbfe1818e509"}}},"connectorDisplayName":"Control","icon":"data&colon;image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"TaskFields","operationDefinition":{"type":"Scope","actions":{"TaskFieldsJSON":{"type":"Compose","inputs":{"task name":"@{items('Apply_to_each_2')?['title']}","bucketID":"@{items('Apply_to_each_2')?['bucketId']}","percentComplete":"@{items('Apply_to_each_2')?['percentComplete']}","createdDate":"@{items('Apply_to_each_2')?['createdDateTime']}","startDate":"@{items('Apply_to_each_2')?['startDateTime']}","dueDate":"@{items('Apply_to_each_2')?['dueDateTime']}","completedDate":"@{items('Apply_to_each_2')?['completedDateTime']}","Description":"@{outputs('Get_task_details')?['body/description']}","priority":"@{items('Apply_to_each_2')?['priority']}"},"runAfter":{}},"Parse_JSON_TaskFields":{"type":"ParseJson","inputs":{"content":"@outputs('TaskFieldsJSON')","schema":{"type":"object","properties":{"task name":{"type":"string"},"bucketID":{"type":"string"},"percentComplete":{"type":"string"},"createdDate":{"type":"string"},"startDate":{"type":"string"},"dueDate":{"type":"string"},"completedDate":{"type":"string"},"Description":{"type":"string"},"priority":{"type":"string"}}}},"runAfter":{"TaskFieldsJSON":["Succeeded"]}}},"runAfter":{"Check_if_task_id_contains_'-'_as_first_character":["Succeeded"]}}}

You will need to replace any references later in the flow with the new Parse JSON dynamic content.


Next you also had a long section with many conditionals to check if several date values were null before using a time conversion expression. You can get rid of all the conditional actions and just put that conditional logic directly into the expressions.
LongConditionsReplacement.png

Scope code to copy & paste into the "My clipboard" tab of a new action

{"id":"5ea2c160-9394-4c40-9962-80d8e69a7c33","brandColor":"#8C3900","connectionReferences":{"shared_excelonlinebusiness_1":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/39f5641011a343e8aac23b8d01bee065"}},"shared_office365users":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_office365users/connections/88b73cab99bd4598bfce9895cb9a47b2"}},"shared_planner_1":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_planner/connections/b858ec8fa9754e709a9b9352ee37dcd5"}},"shared_teams_1":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_teams/connections/shared-teams-8112094b-7e7a-41be-97f4-1297c30cbf15"}},"shared_teams":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_teams/connections/32b18c72239040c2a48639c23542ef1a"}},"shared_excelonlinebusiness":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/shared-excelonlinebu-a88ef57a-9a34-4a54-b1a5-4a442e1c2cb1"}},"shared_office365users_1":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_office365users/connections/e1ba32aaddef4f92aae9f5edbb53f7a1"}},"shared_planner":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_planner/connections/shared-planner-f54d7002-d266-46e1-b80b-dbfe1818e509"}}},"connectorDisplayName":"Control","icon":"data&colon;image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Convert_all_times_and_dates_from_UTC_to_PST","operationDefinition":{"type":"Scope","actions":{"Compose_Current_Date_and_Time":{"type":"Compose","inputs":"@convertTimeZone(utcNow(), 'UTC', 'Pacific Standard Time', 'M/dd/yyyy HH:mm:ss')","runAfter":{"Compose_completeDatePST":["Succeeded"]},"metadata":{"operationMetadataId":"a9f5370f-880a-4f71-902a-829dc71cb358"}},"Compose_createdDatePST":{"type":"Compose","inputs":"@if(empty(body('Parse_JSON_TaskFields')?['createdDate']), '', convertTimeZone(body('Parse_JSON_TaskFields')?['createdDate'],  'UTC', 'Pacific Standard Time', 'M/dd/yyyy HH:mm:ss'))","runAfter":{},"metadata":{"operationMetadataId":"98d17925-99cf-4321-80c1-0db83c3bed50"}},"Compose_startDatePST":{"type":"Compose","inputs":"@if(empty(body('Parse_JSON_TaskFields')?['startDate']), '', convertTimeZone(body('Parse_JSON_TaskFields')?['startDate'],  'UTC', 'Pacific Standard Time', 'M/dd/yyyy HH:mm:ss'))","runAfter":{"Compose_createdDatePST":["Succeeded"]},"metadata":{"operationMetadataId":"55b894a2-94d4-4d6c-b198-8a2ed383eb9e"}},"Compose_dueDatePST":{"type":"Compose","inputs":"@if(empty(body('Parse_JSON_TaskFields')?['dueDate']), '', convertTimeZone(body('Parse_JSON_TaskFields')?['dueDate'],  'UTC', 'Pacific Standard Time', 'M/dd/yyyy HH:mm:ss'))","runAfter":{"Compose_startDatePST":["Succeeded"]},"metadata":{"operationMetadataId":"2cee4dd0-2ec1-4ae6-b7c5-54ed66f50731"}},"Compose_completeDatePST":{"type":"Compose","inputs":"@if(empty(body('Parse_JSON_TaskFields')?['completedDate']), '', convertTimeZone(body('Parse_JSON_TaskFields')?['completedDate'],  'UTC', 'Pacific Standard Time', 'M/dd/yyyy HH:mm:ss'))","runAfter":{"Compose_dueDatePST":["Succeeded"]},"metadata":{"operationMetadataId":"a1faddc4-63ca-4e77-8164-ee006e1ac2a8"}}},"runAfter":{"Compose_and_store_task_related_fields":["Succeeded"]},"description":"Checking task date fields aren't null and converting times from UTC to PST. This is done using the function convertTimeZone","metadata":{"operationMetadataId":"c280abda-3014-4e15-a0bc-2d6368e0a86f"}}}
Anonymous
Not applicable

@takolota thank you for your help I really appreciate it. Trying to make some of the changes you suggested. My question is do you think the limit for api calls affects the flow's performance with the recurrence trigger differently than with the manual trigger? When looking at my flow's previous runs, I occasionally get a status 429 error code for some of my excel calls, which I believe is due to too many api calls in a short period of time (this usually slows down my flow by 1 or 2 minutes each time I get a status 429).  However, the 20 minute run time of the flow with the manual trigger isn't an issue. If the flow could finish in 20 minutes with the recurrence trigger there would be no issue

@Anonymous 

 

I really don’t know why the trigger would affect anything unless that is changing the time of day that this runs & MS has some stricter throttling settings during some peak hours or minutes or something.

 

If you can avoid the Excel API throttling & reduce the actions in your loop by like 1/3, then you should be able to get this to run in less than 30 minutes.

Anonymous
Not applicable

So I made the changes you suggested, getting rid of all the compose actions reassigning dynamic content, having the if statements directly into the compose actions for converting the times, and changing my concurrency to 5 instead of 50. With these changes, I noticed that I didn't really receive any performance increase in terms of running the flow with the manual trigger. Setting the flow to run with the recurrence trigger still makes my flow take several hours. What did you mean when you said "Creating a different new connection for each Excel action in your flow"? Are you referring to different microsoft accounts? I'd like to potentially try implementing that.

@Anonymous 

Not surprising as most of the slow-down should be due to the Excel API limits you are still working to resolve.

By using multiple connections I do not yet mean anything as complicated as creating multiple accounts. I'd only try that if adding new Excel connections on your 1 account doesn't help.

So you will go to each Excel action in your loop, then create & use a different connection for each...
multi-connect.pngmulti-connect2.png

Were you ever able to figure this out? I am also encountering this issue where using the Recurrence trigger makes the flow take much longer than when the manual trigger is used, 20-30 minutes instead of 1 minute.

Helpful resources

Announcements

Exclusive LIVE Community Event: Power Apps Copilot Coffee Chat with Copilot Studio Product Team

It's time for the SECOND Power Apps Copilot Coffee Chat featuring the Copilot Studio product team, which will be held LIVE on April 3, 2024 at 9:30 AM Pacific Daylight Time (PDT).     This is an incredible opportunity to connect with members of the Copilot Studio product team and ask them anything about Copilot Studio. We'll share our special guests with you shortly--but we want to encourage to mark your calendars now because you will not want to miss the conversation.   This live event will give you the unique opportunity to learn more about Copilot Studio plans, where we’ll focus, and get insight into upcoming features. We’re looking forward to hearing from the community, so bring your questions!   TO GET ACCESS TO THIS EXCLUSIVE AMA: Kudo this post to reserve your spot! Reserve your spot now by kudoing this post.  Reservations will be prioritized on when your kudo for the post comes through, so don't wait! Click that "kudo button" today.   Invitations will be sent on April 2nd.Users posting Kudos after April 2nd at 9AM PDT may not receive an invitation but will be able to view the session online after conclusion of the event. Give your "kudo" today and mark your calendars for April 3, 2024 at 9:30 AM PDT and join us for an engaging and informative session!

Tuesday Tip: Unlocking Community Achievements and Earning Badges

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!     THIS WEEK'S TIP: Unlocking Achievements and Earning BadgesAcross the Communities, you'll see badges on users profile that recognize and reward their engagement and contributions. These badges each signify a different achievement--and all of those achievements are available to any Community member! If you're a seasoned pro or just getting started, you too can earn badges for the great work you do. Check out some details on Community badges below--and find out more in the detailed link at the end of the article!       A Diverse Range of Badges to Collect The badges you can earn in the Community cover a wide array of activities, including: Kudos Received: Acknowledges the number of times a user’s post has been appreciated with a “Kudo.”Kudos Given: Highlights the user’s generosity in recognizing others’ contributions.Topics Created: Tracks the number of discussions initiated by a user.Solutions Provided: Celebrates the instances where a user’s response is marked as the correct solution.Reply: Counts the number of times a user has engaged with community discussions.Blog Contributor: Honors those who contribute valuable content and are invited to write for the community blog.       A Community Evolving Together Badges are not only a great way to recognize outstanding contributions of our amazing Community members--they are also a way to continue fostering a collaborative and supportive environment. As you continue to share your knowledge and assist each other these badges serve as a visual representation of your valuable contributions.   Find out more about badges in these Community Support pages in each Community: All About Community Badges - Power Apps CommunityAll About Community Badges - Power Automate CommunityAll About Community Badges - Copilot Studio CommunityAll About Community Badges - Power Pages Community

Tuesday Tips: Powering Up Your Community Profile

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!   This Week's Tip: Power Up Your Profile!  🚀 It's where every Community member gets their start, and it's essential that you keep it updated! Your Community User Profile is how you're able to get messages, post solutions, ask questions--and as you rank up, it's where your badges will appear and how you'll be known when you start blogging in the Community Blog. Your Community User Profile is how the Community knows you--so it's essential that it works the way you need it to! From changing your username to updating contact information, this Knowledge Base Article is your best resource for powering up your profile.     Password Puzzles? No Problem! Find out how to sync your Azure AD password with your community account, ensuring a seamless sign-in. No separate passwords to remember! Job Jumps & Email Swaps Changed jobs? Got a new email? Fear not! You'll find out how to link your shiny new email to your existing community account, keeping your contributions and connections intact. Username Uncertainties Unraveled Picking the perfect username is crucial--and sometimes the original choice you signed up with doesn't fit as well as you may have thought. There's a quick way to request an update here--but remember, your username is your community identity, so choose wisely. "Need Admin Approval" Warning Window? If you see this error message while using the community, don't worry. A simple process will help you get where you need to go. If you still need assistance, find out how to contact your Community Support team. Whatever you're looking for, when it comes to your profile, the Community Account Support Knowledge Base article is your treasure trove of tips as you navigate the nuances of your Community Profile. It’s the ultimate resource for keeping your digital identity in tip-top shape while engaging with the Power Platform Community. So, dive in and power up your profile today!  💪🚀   Community Account Support | Power Apps Community Account Support | Power AutomateCommunity Account Support | Copilot Studio  Community Account Support | Power Pages

Super User of the Month | Chris Piasecki

In our 2nd installment of this new ongoing feature in the Community, we're thrilled to announce that Chris Piasecki is our Super User of the Month for March 2024. If you've been in the Community for a while, we're sure you've seen a comment or marked one of Chris' helpful tips as a solution--he's been a Super User for SEVEN consecutive seasons!   Since authoring his first reply in April 2020 to his most recent achievement organizing the Canadian Power Platform Summit this month, Chris has helped countless Community members with his insights and expertise. In addition to being a Super User, Chris is also a User Group leader, Microsoft MVP, and a featured speaker at the Microsoft Power Platform Conference. His contributions to the new SUIT program, along with his joyous personality and willingness to jump in and help so many members has made Chris a fixture in the Power Platform Community.   When Chris isn't authoring solutions or organizing events, he's actively leading Piasecki Consulting, specializing in solution architecture, integration, DevOps, and more--helping clients discover how to strategize and implement Microsoft's technology platforms. We are grateful for Chris' insightful help in the Community and look forward to even more amazing milestones as he continues to assist so many with his great tips, solutions--always with a smile and a great sense of humor.You can find Chris in the Community and on LinkedIn. Thanks for being such a SUPER user, Chris! 💪 🌠  

Find Out What Makes Super Users So Super

We know many of you visit the Power Platform Communities to ask questions and receive answers. But do you know that many of our best answers and solutions come from Community members who are super active, helping anyone who needs a little help getting unstuck with Business Applications products? We call these dedicated Community members Super Users because they are the real heroes in the Community, willing to jump in whenever they can to help! Maybe you've encountered them yourself and they've solved some of your biggest questions. Have you ever wondered, "Why?"We interviewed several of our Super Users to understand what drives them to help in the Community--and discover the difference it has made in their lives as well! Take a look in our gallery today: What Motivates a Super User? - Power Platform Community (microsoft.com)

March User Group Update: New Groups and Upcoming Events!

  Welcome to this month’s celebration of our Community User Groups and exciting User Group events. We’re thrilled to introduce some brand-new user groups that have recently joined our vibrant community. Plus, we’ve got a lineup of engaging events you won’t want to miss. Let’s jump right in: New User Groups   Sacramento Power Platform GroupANZ Power Platform COE User GroupPower Platform MongoliaPower Platform User Group OmanPower Platform User Group Delta StateMid Michigan Power Platform Upcoming Events  DUG4MFG - Quarterly Meetup - Microsoft Demand PlanningDate: 19 Mar 2024 | 10:30 AM to 12:30 PM Central America Standard TimeDescription: Dive into the world of manufacturing with a focus on Demand Planning. Learn from industry experts and share your insights. Dynamics User Group HoustonDate: 07 Mar 2024 | 11:00 AM to 01:00 PM Central America Standard TimeDescription: Houston, get ready for an immersive session on Dynamics 365 and the Power Platform. Connect with fellow professionals and expand your knowledge. Reading Dynamics 365 & Power Platform User Group (Q1)Date: 05 Mar 2024 | 06:00 PM to 09:00 PM GMT Standard TimeDescription: Join our virtual meetup for insightful discussions, demos, and community updates. Let’s kick off Q1 with a bang! Leaders, Create Your Events!  Leaders of existing User Groups, don’t forget to create your events within the Community platform. By doing so, you’ll enable us to share them in future posts and newsletters. Let’s spread the word and make these gatherings even more impactful! Stay tuned for more updates, inspiring stories, and collaborative opportunities from and for our Community User Groups.   P.S. Have an event or success story to share? Reach out to us – we’d love to feature you!

Users online (5,044)