How to iterate results of Get Sheet Data
回答済みI have a very simple request I cannot seem to get working. I have a Get Sheet Data command, which fetches n amount of rows with a header row from a WDesk spreadsheet. Then all I want to do is iterate over all the data rows, extract all the column values and assign to the chain input parameters and execute the chain for each row in the sheet.
However I am stumped at what to iterate over. I cannot seem to iterate directly with a group command over the output of a sheet. I cannot find any online examples and the docs are useless :(
-
Hi Waldo, if I am understanding you correctly, you want to iterate through each row of data selected from the Get Sheet Data command? If that is the case, after retrieving the data from the Sheet, you can use the Tabular Transformation command Split File to split the file into single row chunks. Then iterate over the Split File Chunks in the Group Node.
0
Jeff, Workiva should sit you down, (gently) extract all the (Workiva) info in your head and write it up neatly in guided tutorials and make that available here. It will make life so much easier. Many of these pieces seem disconnected if you do not know how they can (and should) be chained together.
Thanks again for coming to my rescue.
0
Putting in a request to have Jeff's intellect extracted 🤪
All kidding aside, you are correct, Jeff is amazing. Thanks Jeff Hickey! And your comments around trainings and what's needed are noted. I work closely with our Help team, so I'm happy to share those insights.0
As a followup - I managed to get my chain working but I have three concerns:
1. I need to extract 12 variables from 12 columns in my spreadsheet. I tried to do that in parallel with my Group Node to speed things up - however it seems there is a (undocumented?) limit on the amount of parallel nodes hanging off of Group Start that can be defined. I know this because when I tried to access the Extract Value values in my Run Chain command, I could only see something like 6 or 7 entries. When I chained them all in series I could see them all. Hence why I did it this way. Is there a limit on concurrent tasks?
2. Is there a more efficient method of extracting these cells from the sheet I am iterating over? Extracting the 12 variables takes about 30 seconds, adding several minutes to this chain. I am assigning each extracted value to a runtime input in the chain I am invoking at the bottom.
3. The chain always ends up in a failed state even though it completed. I added a conditional to ensure I skip the header row (the conditional basically just checks that Spreadsheet Id was extracted and is not blank). However it always ends up with an error which is wrong. How do I prevent that? It seems like the error is due to Extract Spreadsheet Id which fails on the first row as it is a header only row and is only 1 row long and not 2 like the others (I repeat the header). If I could test the row count of a split file iteration chunk I can fix it...
0
Hi Waldo,
1. I am not aware of a hard limit of the number of parallel nodes. The size of your dataset could play a role. I was able to successfully retrieve data from 12 columns using 12 Extract nodes in parallel. You could speed up performance by running 6 Extracts in parallel at a time.
2. You could convert it to JSON and reference the values by their location in the JSON array. For example, that are many cases when you reference an object's ID that is within a JSON array. A common instance is when you use the Workiva command to create a file in a Wdata Table, and then the next step is to import the file which is referenced by ID. You could use this same concept to reference sections of you JSON array that you built from the Get Sheet Data output. Or, if the above suggestion in number 1 speeds up your chain enough, then Extract nodes are acceptable too.
3. The chain is reported as failing because the first Extract node in the group node failed. If you are wanting the Extract nodes to ignore the header row, you can offset them by adjusting the Row field in the Extract nodes.
0
How is 1 above possible? To have the extracts in parallel, I need nested group nodes and that is not allowed: "Invalid link - non-grouped node cannot be connected to grouped node".
I would need to insert a group node after the first conditional to have all the 11 other extract commands run in parallel, and the output of that sub group node needs to feed into Run Chain. But as I said this is not possible as I cannot nest group nodes - so how would I go about that?
0
Here is a screenshot of what it may look like to run 6 extract nodes in parallel at a time. Its possible that an extract node in the first row may finish before the others and start on the second row extract. But since you have found that 6 or 7 run without issue, then this setup should allow for that. You could reduce the number ran at a time per row if needed or get creative by adding a conditional node in between the rows that all extracts feed into prior to running the second row.
One other tip, if the header row in the split file chunks is causing issue, you can uncheck the Prepend Header checkbox in the Split File node.
0
I do not understand above flow. If Extract Value 1 and Extract Value 7 finishes, and say Extract Value 2 is running still, when would Run Chain execute? Will it wait for all the nodes pointing in to it to finish? Run Chain should only execute once ALL the extract values have finished. It is not clear to me from above whether there is a thread synchronization on the input nodes to Run Chain.
0
Extract Value 7 will run after Extract Value 1 completes successfully. Extract Value 8 will run after Extract Value 2 completes successfully and so on. Run Chain will execute only after Extract Value 7 through 12 complete successfully. When Run Chain does execute, it will have access to all Extract Value node's values.
0
Hi Jeff,
Can you give more insight into the details of the ground and extract value commands used in your parallel solution above. I am trying the above solution to extract values of a multi-select parameter for my query. I tried below two chains but may not be implementing the commands right.
Chain A:
Chain B:
0
The Extract Value command is used to retrieve a value from a DSV file given the value's row index and column index. In your screenshot, you could use the Extract Value commands to retrieve specific data from the output of the Get Sheet Data command. The Extract Value command cannot use JSON files as input. I suspect that is the cause of the error in Chain B since you have a CSV to JSON node in your Chain. You can view more information of the failure by clicking on one of the failed nodes and selecting the "LOGS" tab.
0
Hi Jeff,
Thank you for responding. In chain A, I have tried to replicate the chain screenshot you shared in your earlier comment.
Are you using skip condition in extract value nodes to extract value one by one in each iteration? If yes, can you share the skip condition details as well?
Posting the chain A screenshot below again:
0
No skip conditions were necessary in my example above. In your Chain A, as long as each file chunk has the 5 pieces of data to extract, you shouldn't need a skip condition. If some file chunks have less than 5 pieces of information to extract, then skip conditions or other logic could be used. But for the errors in Chain A, a good place to start troubleshooting is to review the full error message in the "LOGS" tab of the failed nodes. This will give you information on the failure and what needs to be fixed for the node to pass.
0サインインしてコメントを残してください。
コメント
13件のコメント