Schedule/Recurrence Trigger Slows Down Flow's Performance Drastically

[Anonymous]

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.

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. 

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

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":

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

[Sundeep_Malik]

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]

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

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

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

[takolota]

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

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

[takolota]

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

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:

[takolota]

😑 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]

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

[takolota]

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

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?

UpdatedData should be Select Remove Blank Lines here correct?

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

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?

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?

[takolota]

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

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.

[takolota]

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]

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

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.

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

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: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]

@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

[takolota]

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

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.

[takolota]

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

[Calebm]

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.