I am getting an error when I attempt to import any version of this flow. I have imported other packages successfully, but this one keeps failing. Has anyone else experienced this?
The simpler expressions of the new V5 are also easier to fix when they error in the copy & paste clipboard import method, so I'll share that back-up import method here for anyone facing issues with the standard import method.
For this, you'll need to copy the code below to your clipboard. It contains the code for a Power Automate Scope action that contains the entire flow. You can then go to a new action in any flow, go to the "My clipboard" tab of the new action menu and control + v paste it into that tab menu so you can select it to add it to the flow. The extra hurdle with this method is the longer expressions in the Select actions usually get messed up & will be greyed out.
You will need to go to each Select action, copy the expression text listed on the action, remove the expression text, use the map button on the right-side of the action to toggle to the table view & back to the single textbox view, then click in the textbox view to bring up the content menu & expression tab, then paste the expression text you copied back into the expression editor & remove any initial or end characters that do not belong in the expression input like the starting '@' or ending '}'.
Excel Batch Upsert V5 Scope Code to Copy & Paste Into the "My clipboard" Tab of a New Action Menu
{"id":"84977e71-9686-4abc-8e74-db0046cae9f9","brandColor":"#8C3900","connectionReferences":{"shared_excelonlinebusiness":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/39f5641011a343e8aac23b8d01bee065"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Excel_Batch_Upsert_V5","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 BatchUpdateV5.","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 //let BlankLine: string [];\n //BlankLine = new Array(ColumnCount);\n let RowNum: number;\n RowNum = InitialNumberOfTableRows;\n //-1 to 0 index the RowNum\n RowNum = RowNum - 1;\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 //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(new Array(ColumnCount))\n //Earlier less efficient code --- 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 /**\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 //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 let Blank2dArray = new Array(CreateData.length).fill(null).map(() => new Array(ColumnCount).fill(null));\n let TableData = Blank2dArray;\n //Iterate through each object item in the array from the flow\n for (let i = 0; i < CreateData.length; i++) {\n //i is also the row number\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[i][Number(Object.keys(CreateData[i])[j])] = CreateData[i][Number(Object.keys(CreateData[i])[j])];\n }\n }\n //Append the in-memory TableData to the Excel table\n //Split the in-memory TableData into batches that addRows can handle without error & loop through to add each batch in addRows\n let BatchSize = 200;\n //length - 1 prevents a later slice(n, n) or slice(1000, 1000) which would return a null array & error the addRows\n let NumBatches = Math.floor((TableData.length - 1) / BatchSize);\n for (let k = 0; k <= NumBatches; k++) {\n let BatchRows = TableData.slice(k * BatchSize, (k + 1) * BatchSize)\n table.addRows(-1, BatchRows);\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 BatchCreateV5.","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":"01JWBUU4HB4HVGZLNYHNDLYN3M3UGHB2UT","table":"{00000000-000C-0000-FFFF-FFFF00000000}","$top":2000,"dateTimeFormat":"ISO 8601"},"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 2000 row batch.","runtimeConfiguration":{"paginationPolicy":{"minimumItemCount":25000}},"limit":{"timeout":"PT5M"},"metadata":{"01PCEUDVFYX74MYI46EJCZF6AUHSUSPNMU":"/SourceData.xlsx","operationMetadataId":"408aee2b-05c0-44e2-8468-78c78fb0530d","tableId":"{00000000-000C-0000-FFFF-FFFF00000000}","01JWBUU4EEP2QHP4DFINBKWWCSYPOMRAA5":"/SourceData.xlsx","01JWBUU4HB4HVGZLNYHNDLYN3M3UGHB2UT":"/SourceTable.xlsx"}},"Match_new_and_existing_data_key_values_then_batch_update":{"type":"Scope","actions":{"List_rows_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":"01JWBUU4CQU6JVUFYHMJDIJK4FSNCANGT5","table":"{00000000-000C-0000-FFFF-FFFF00000000}","$top":2},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{"SelectGenerateUpdateData":["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":"{00000000-000C-0000-FFFF-FFFF00000000}","01PCEUDVFYX74MYI46EJCZF6AUHSUSPNMU":"/Book.xlsx","01PCEUDVCCZ7IYLT5RUBC3N6VXZWCYFNNI":"/DestinationData.xlsx","01JWBUU4DRJEFUSQGBG5E2QSRSXUIHVZNB":"/DestinationData.xlsx","01JWBUU4CQU6JVUFYHMJDIJK4FSNCANGT5":"/DestinationTable.xlsx"}},"SelectGenerateUpdateData":{"type":"Select","inputs":{"from":"@outputs('List_rows_Sample_source_data_-Placeholder-')?['body/value']","select":{"PrimaryKey":"@item()?['SourceC1']","DestinationC2":"@item()?['SourceC2']","DestinationC3":"@item()?['SourceC3']","DestinationC4":"@item()?['SourceC4']","DestinationC5":"@item()?['SourceC5']","DestinationC6":"@item()?['SourceC6']","DestinationC17":"@null","DestinationC18":"","Destination-C 27":"1","DestinationC28":"@if(empty(item()?['SourceC6']), 0, 1)"}},"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"}},"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":"01JWBUU4CQU6JVUFYHMJDIJK4FSNCANGT5","scriptId":"ms-officescript%3A%2F%2Fonedrive_business_itemlink%2F01JWBUU4CI62ZOXSKDIJD2KRGYGN2R4NX4","ScriptParameters/TableName":"Table1","ScriptParameters/PrimaryKeyColumnName":"GeneratePK","ScriptParameters/ForceMode1Processing":"No","ScriptParameters/UpdatedData":"@outputs('ComposeDestinationJSON')"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{"ComposeDestinationJSON":["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","01JWBUU4CQU6JVUFYHMJDIJK4FSNCANGT5":"/DestinationTable.xlsx"}},"SelectDestinationColumnNumbersByName":{"type":"Select","inputs":{"from":"@range(0, sub(length(body('SelectReformatHeaders')), 1))","select":{"@{split(body('SelectReformatHeaders')[item()], '\":')?[0]}":"@item()"}},"runAfter":{"SelectReformatHeaders":["Succeeded"]},"metadata":{"operationMetadataId":"87bfb21b-ef06-4f23-99dc-20b0b95e4f0c"}},"ReformatHeaderObject":{"type":"Compose","inputs":"@json(replace(replace(replace(string(body('SelectDestinationColumnNumbersByName')), '[{', '{'), '}]', '}'), '},{', ','))","runAfter":{"SelectDestinationColumnNumbersByName":["Succeeded"]},"description":"Convert the array of JSON objects to a single searchable JSON object","metadata":{"operationMetadataId":"69b1d189-d56f-45fc-8dbb-a49740832a64"}},"SelectArrayOfArraysUpdateData":{"type":"Select","inputs":{"from":"@body('SelectAddRecordBreakToEach')","select":"@join(json(replace(\r\nreplace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(\r\nconcat('[', skip(take(string(item()), sub(length(string(item())), 1)), 1), ']'),\r\n'\":\"', '\",\":\",\"'), '\":\",\":\",', '\",\":\",\":'),\r\n'\":0', '\",\":\",0'), '\",\":\",\",\":\",0', '\",\":\",\":0'),\r\n'\":1', '\",\":\",1'), '\",\":\",\",\":\",1', '\",\":\",\":1'),\r\n'\":2', '\",\":\",2'), '\",\":\",\",\":\",2', '\",\":\",\":2'),\r\n'\":3', '\",\":\",3'), '\",\":\",\",\":\",3', '\",\":\",\":3'),\r\n'\":4', '\",\":\",4'), '\",\":\",\",\":\",4', '\",\":\",\":4'),\r\n'\":5', '\",\":\",5'), '\",\":\",\",\":\",5', '\",\":\",\":5'),\r\n'\":6', '\",\":\",6'), '\",\":\",\",\":\",6', '\",\":\",\":6'),\r\n'\":7', '\",\":\",7'), '\",\":\",\",\":\",7', '\",\":\",\":7'),\r\n'\":8', '\",\":\",8'), '\",\":\",\",\":\",8', '\",\":\",\":8'),\r\n'\":9', '\",\":\",9'), '\",\":\",\",\":\",9', '\",\":\",\":9'),\r\n'\":[', '\",\":\",['), '\",\":\",\",\":\",[', '\",\":\",\":['),\r\n'\":{', '\",\":\",{'), '\",\":\",\",\":\",{', '\",\":\",\":{'),\r\n'\":null', '\",\":\",\"null\"'), '\",\":\",\",\":\",\"null\"', '\",\":\",\":\"null\"'),\r\n'\":true', '\",\":\",\"true\"'), '\",\":\",\",\":\",\"true\"', '\",\":\",\":\"true\"'),\r\n'\":false', '\",\":\",\"false\"'), '\",\":\",\",\":\",\"false\"', '\",\":\",\":\"false\"'),\r\n',\":\",', ','), '_#-', '_#_')), '_#-')"},"runAfter":{"SelectAddRecordBreakToEach":["Succeeded"]},"description":"And filter out the JSON colon : items","metadata":{"operationMetadataId":"a5f08622-a062-4a8a-9c34-b09b0bd9b895"}},"SelectAddRecordBreakToEach":{"type":"Select","inputs":{"from":"@body('SelectGenerateUpdateData')","select":"@removeProperty(\r\naddProperty(\r\naddProperty(item(), 'PK', item()['PrimaryKey']),\r\n'_|-', ''),\r\n'PrimaryKey')"},"runAfter":{"ReformatHeaderObject":["Succeeded"]},"metadata":{"operationMetadataId":"33f058fd-c61a-4580-8957-7c263906bd72"}},"ComposeDestinationJSON":{"type":"Compose","inputs":"@json(concat('[{',\r\nskip(\r\ntake(\r\nreplace(join(body('FilterOnlyKeyValueStrings'), ','), ',\"_|-\":\"\",', '},{'),\r\nsub(length(replace(join(body('FilterOnlyKeyValueStrings'), ','), ',\"_|-\":\"\",', '},{')), 9)),\r\n0),\r\n'}]'))","runAfter":{"FilterOnlyKeyValueStrings":["Succeeded"]},"description":"Exceed the 210,000 ProcessAllCells limit & this may error & not parse. Either reduce your batch size or add more SelectDestinationIndexCells # actions to the ProcessAllCells Scope & FilterOnlyKeyValueStrings.","metadata":{"operationMetadataId":"9fc0353c-c586-4590-9366-c6df90dda58a"}},"FilterOnlyKeyValueStrings":{"type":"Query","inputs":{"from":"@json(replace(replace(concat(\r\nstring(body('SelectDestinationIndexCells')), '-#-',\r\nstring(body('SelectDestinationIndexCells_2')), '-#-',\r\nstring(body('SelectDestinationIndexCells_3')), '-#-',\r\nstring(body('SelectDestinationIndexCells_4')), '-#-',\r\nstring(body('SelectDestinationIndexCells_5'))\r\n), '-#-[]', ''), ']-#-[', ','))","where":"@not(equals(item(), '_||'))"},"runAfter":{"ProcessAllCells":["Succeeded"]},"description":"Combine all processed cell arrays & filter blanks","metadata":{"operationMetadataId":"d6b0a71e-6b59-42b5-ac8e-771cf48d6814"}},"ProcessAllCells":{"type":"Scope","actions":{"SelectDestinationIndexCells":{"type":"Select","inputs":{"from":"@range(0, max(Min(100000, length(outputs('CustomDelimitedCells'))), 0))","select":"@if(equals(mod(item(), 2), 0),\r\nif(equals(outputs('CustomDelimitedCells')[add(item(), 1)], '\"\"'), '_||',\r\nif(or(equals(outputs('CustomDelimitedCells')[item()], 'PK'), equals(outputs('CustomDelimitedCells')[item()], '_|-')),\r\nconcat('\"', outputs('CustomDelimitedCells')[item()], '\":\"', replace(replace(outputs('CustomDelimitedCells')[add(item(), 1)], '\"', '″'), 'null', ''), '\"'),\r\nconcat('\"', outputs('ReformatHeaderObject')[outputs('CustomDelimitedCells')[item()]], '\":\"', replace(replace(outputs('CustomDelimitedCells')[add(item(), 1)], '\"', '″'), 'null', ''), '\"')\r\n)\r\n),\r\n'_||'\r\n)"},"runAfter":{"CustomDelimitedCells":["Succeeded"]},"description":"1st 100000 of flattened array. Will error if the Generate update data mapping doesn't exactly match the destination columns.","metadata":{"operationMetadataId":"e1692a16-ad6c-4d26-a285-12522d144839"}},"CustomDelimitedCells":{"type":"Compose","inputs":"@split(Join(body('SelectArrayOfArraysUpdateData'), '_#-'), '_#-')","runAfter":{},"metadata":{"operationMetadataId":"39e5ede0-3b02-469c-b6f7-15221fee5668"}},"SelectDestinationIndexCells_2":{"type":"Select","inputs":{"from":"@range(100000, max(min(sub(length(outputs('CustomDelimitedCells')), 100000), 100000), 0))","select":"@if(equals(mod(item(), 2), 0),\r\nif(equals(outputs('CustomDelimitedCells')[add(item(), 1)], '\"\"'), '_||',\r\nif(or(equals(outputs('CustomDelimitedCells')[item()], 'PK'), equals(outputs('CustomDelimitedCells')[item()], '_|-')),\r\nconcat('\"', outputs('CustomDelimitedCells')[item()], '\":\"', replace(replace(outputs('CustomDelimitedCells')[add(item(), 1)], '\"', '″'), 'null', ''), '\"'),\r\nconcat('\"', outputs('ReformatHeaderObject')[outputs('CustomDelimitedCells')[item()]], '\":\"', replace(replace(outputs('CustomDelimitedCells')[add(item(), 1)], '\"', '″'), 'null', ''), '\"')\r\n)\r\n),\r\n'_||'\r\n)"},"runAfter":{"CustomDelimitedCells":["Succeeded"]},"description":"2nd 100000 of flattened array. Will error if the Generate update data mapping doesn't exactly match the destination columns.","metadata":{"operationMetadataId":"e1692a16-ad6c-4d26-a285-12522d144839"}},"SelectDestinationIndexCells_3":{"type":"Select","inputs":{"from":"@range(200000, max(min(sub(length(outputs('CustomDelimitedCells')), 200000), 100000), 0))","select":"@if(equals(mod(item(), 2), 0),\r\nif(equals(outputs('CustomDelimitedCells')[add(item(), 1)], '\"\"'), '_||',\r\nif(or(equals(outputs('CustomDelimitedCells')[item()], 'PK'), equals(outputs('CustomDelimitedCells')[item()], '_|-')),\r\nconcat('\"', outputs('CustomDelimitedCells')[item()], '\":\"', replace(replace(outputs('CustomDelimitedCells')[add(item(), 1)], '\"', '″'), 'null', ''), '\"'),\r\nconcat('\"', outputs('ReformatHeaderObject')[outputs('CustomDelimitedCells')[item()]], '\":\"', replace(replace(outputs('CustomDelimitedCells')[add(item(), 1)], '\"', '″'), 'null', ''), '\"')\r\n)\r\n),\r\n'_||'\r\n)"},"runAfter":{"CustomDelimitedCells":["Succeeded"]},"description":"3rd 100000 of flattened array. Will error if the Generate update data mapping doesn't exactly match the destination columns.","metadata":{"operationMetadataId":"e1692a16-ad6c-4d26-a285-12522d144839"}},"SelectDestinationIndexCells_4":{"type":"Select","inputs":{"from":"@range(300000, max(min(sub(length(outputs('CustomDelimitedCells')), 300000), 100000), 0))","select":"@if(equals(mod(item(), 2), 0),\r\nif(equals(outputs('CustomDelimitedCells')[add(item(), 1)], '\"\"'), '_||',\r\nif(or(equals(outputs('CustomDelimitedCells')[item()], 'PK'), equals(outputs('CustomDelimitedCells')[item()], '_|-')),\r\nconcat('\"', outputs('CustomDelimitedCells')[item()], '\":\"', replace(replace(outputs('CustomDelimitedCells')[add(item(), 1)], '\"', '″'), 'null', ''), '\"'),\r\nconcat('\"', outputs('ReformatHeaderObject')[outputs('CustomDelimitedCells')[item()]], '\":\"', replace(replace(outputs('CustomDelimitedCells')[add(item(), 1)], '\"', '″'), 'null', ''), '\"')\r\n)\r\n),\r\n'_||'\r\n)"},"runAfter":{"CustomDelimitedCells":["Succeeded"]},"description":"4th 100000 of flattened array. Will error if the Generate update data mapping doesn't exactly match the destination columns.","metadata":{"operationMetadataId":"e1692a16-ad6c-4d26-a285-12522d144839"}},"SelectDestinationIndexCells_5":{"type":"Select","inputs":{"from":"@range(400000, max(min(sub(length(outputs('CustomDelimitedCells')), 400000), 100000), 0))","select":"@if(equals(mod(item(), 2), 0),\r\nif(equals(outputs('CustomDelimitedCells')[add(item(), 1)], '\"\"'), '_||',\r\nif(or(equals(outputs('CustomDelimitedCells')[item()], 'PK'), equals(outputs('CustomDelimitedCells')[item()], '_|-')),\r\nconcat('\"', outputs('CustomDelimitedCells')[item()], '\":\"', replace(replace(outputs('CustomDelimitedCells')[add(item(), 1)], '\"', '″'), 'null', ''), '\"'),\r\nconcat('\"', outputs('ReformatHeaderObject')[outputs('CustomDelimitedCells')[item()]], '\":\"', replace(replace(outputs('CustomDelimitedCells')[add(item(), 1)], '\"', '″'), 'null', ''), '\"')\r\n)\r\n),\r\n'_||'\r\n)"},"runAfter":{"CustomDelimitedCells":["Succeeded"]},"description":"5th 100000 of flattened array. Will error if the Generate update data mapping doesn't exactly match the destination columns.","metadata":{"operationMetadataId":"e1692a16-ad6c-4d26-a285-12522d144839"}}},"runAfter":{"SelectArrayOfArraysUpdateData":["Succeeded"]},"description":"The Range( ) expression is required for this & has a limit of 100,000 items. This breaks things up if someone wants to load large tables up to 210,000 cells, ex: 7 columns & 30000 rows. Exceed 210,000 & the later JSON action will error & not parse.","metadata":{"operationMetadataId":"fec90f32-16ae-41f9-9b14-934fd19a3df3"}},"SelectReformatHeaders":{"type":"Select","inputs":{"from":"@skip(split(\r\nreplace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(\r\nstring(first(outputs('List_rows_Get_header_sample')?['body/value'])),\r\n'\":0', '\":\"0'),\r\n'\":1', '\":\"1'),\r\n'\":2', '\":\"2'),\r\n'\":3', '\":\"3'),\r\n'\":4', '\":\"4'),\r\n'\":5', '\":\"5'),\r\n'\":6', '\":\"6'),\r\n'\":7', '\":\"7'),\r\n'\":8', '\":\"8'),\r\n'\":9', '\":\"9'),\r\n'\":[', '\":\"['),\r\n'\":{', '\":\"{'),\r\n'\":null', '\":\"null\"'),\r\n'\":true', '\":\"true\"'),\r\n'\":false', '\":\"false\"'),\r\n'\":\"'), 2)","select":"@last(split(item(), ',\"'))"},"runAfter":{"List_rows_Get_header_sample":["Succeeded"]},"metadata":{"operationMetadataId":"dfd7c59f-8346-4a15-8f41-b5fe6200c746"}}},"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.","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":"@outputs('ComposeDestinationJSON')","where":"@contains(outputs('Run_script_Update_Excel_rows')?['body/result'], item()['PK'])"},"runAfter":{},"description":"Get the records from the batch DestinationJSON 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":"01JWBUU4CQU6JVUFYHMJDIJK4FSNCANGT5","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":"PT5M"},"metadata":{"01PCEUDVFYX74MYI46EJCZF6AUHSUSPNMU":"/Book.xlsx","operationMetadataId":"713f593f-c5e4-43b6-ad08-3a71051bbf87","tableId":null,"01PCEUDVCCZ7IYLT5RUBC3N6VXZWCYFNNI":"/DestinationData.xlsx","01JWBUU4DRJEFUSQGBG5E2QSRSXUIHVZNB":"/DestinationData.xlsx","01JWBUU4CQU6JVUFYHMJDIJK4FSNCANGT5":"/DestinationTable.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":{}}}New action menu tab to paste the code into...
New Scope in Your Flow...
Likely errors in the Select action expressions...
Again. You will need to go to each Select action, copy the expression text listed on the action, remove the expression text, use the map button on the right-side of the action to toggle to the table view & back to the single textbox view, then click in the textbox view to bring up the content menu & expression tab, then paste the expression text you copied back into the expression editor & remove any initial or end characters that do not belong in the expression input like the starting '@' or ending '}'.
@takolotaGreat work here! I have an issue I hope you (or anyone else!) can help me with.
I have a flow which takes a table and creates a new file with blank table for each project ID in the main table, and then batch create all the rows for each project into the corresponding blank table. The issue I have is that the script is not creating into the blank tables at all, but below, and I can't figure out why.
The log seems to have the right range.
Any ideas?
@takolota Thanks for your script! But same issue here on a Blank Table(cleared by Bulk Delete) with Bulk Create V5! All new data is out of the Table Range. That's weird!
++++
To mitigate this issue, my workaround is to use below script to resize table
//re-size table
let sheet = workbook.getActiveWorksheet();
let table = sheet.getTable(`${TableName}`);
table.resize(`${targetRange.getAddress()}`);
