Hi LearningRPA,
This is assuming you are processing the queue in the same order as in the Excel sheet. Meaning - there is no filtering of data from the queue and there are no Retry Exception Attempts on the queues.
- You can use a counter variable and increment at each Get Next Item
- Then when you Mark the Queue items as Exception - Add an Action stage -> Use 'Set Cell Value' by giving the 'Cell Reference' parameter as "D" & counter where D is the column to update in Excel. Give the 'Value' parameter as "Exception". So in the first iteration cell D2 will be updated as "Exception" assuming there are headers in row 1.
If you have set Exception Retry attempts then you will have to decrement the counter whenever a queue item has an exception because the queue is going to process the same item again else your status will be updated in the next row of Excel.
- Add the same Set Cell Value stage as above when you come across the Mark as Completed
- Save the Excel at the very end so that if something goes wrong in the diagram it will start with a fresh new column the next time you run the process. But if you want to update the column and save at the same time when the Queue status is updated then you will need to handle it accordingly like saving the sheet after every update etc. Then you will need to clear the status column before the start of every run so that everything starts perfectly from the beginning.
- This logic is under general conditions and there may be other things to handle but the basic idea of updating the column is as above
In another case if your Excel data is not in alignment with the processing of the queue, then you will need to code its logic accordingly. This will include finding a way to match the key column of Excel with the Item Key that the Queue is currently processing. For example, when you do the 'Get Next Item' from the queue you can easily get its key column value (the one that is same as in the Excel), then you will need to search for this key inside the excel in that column. Perhaps there is no direct action available to do this. So either you will have to use a vlookup in some cell in the sheet (say Z column) to find the row number of this key and then use "D" & RowNumber as explained above in the Cell reference. You can clear this cell Z when the process is finished. Or you will have to write a macro to identify the row number that finds this key in the excel key column, macro should write the found row number to a cell (say Z1). This macro is to be called from the BP process and then use Cell Reference parameter on the row number found in cell Z1.
