Auto Refresh A Connection to a Query Through WData?
AnsweredHI there - through chains I can automatically run a query every day. This same query is connected to a Spreadsheet. Is there a way to have the spreadsheet automatically refresh each time the chain is run to run the query? This way, we dont have to manually refresh the connection between the spreadsheet and the WData query? Below is the screen shot of the spreadsheet and the button that needs to be refreshed.
Thanks for any help.
-
The easiest way to do this would be to modify your Chain to have it update the spreadsheet rather than connecting the spreadsheet to the query. The first step in the Chain would be to Run Query (https://support.workiva.com/hc/en-us/articles/360036005471-Workiva-Commands#section48) which returns the query results. With the query results, the next step would be to push the data to the spreadsheet and sheet using Export Query Results (https://support.workiva.com/hc/en-us/articles/360036005471-Workiva-Commands#section22). Each time the Chain runs, it will automatically get the data from the query and push it to the spreadsheet.
0
Jeff - Thanks so much. This is very helpful. And really going to help us. I like the simplicity of having the results go right from the query to the spreadsheet. And since we have configured the chain to run daily, the spreadsheet will update automatically.
I still ran into an issue. Don't I have to convert the output of the query (that is a JSON file) to a CSV file using the [Tabular Transformation - Convert JSON to CSV] command that can then overwrite the spreadsheet?
How do I get the Query Results into the "Convert JSON to CSV" command? I thought I could just select the " <> Query Results" as the input file as shown in the screenshot below and have the command convert from the results from JSON To CSV. But the spreadsheet is only getting updated with the a single row of the following 9 fields and not the actual data from the query:
[bytesScanned], [created], [duration], [id], [queryId], [queryText], [status], [updated], [userId]
Thanks again for your help and any insight you can provide on the above.
0
Good catch on this. I recommended an incorrect endpoint which is why you ran into your above issue. The correct endpoint is actually Export Query Results (https://support.workiva.com/hc/en-us/articles/360036005471-Workiva-Commands#section22) rather than Overwrite Sheet Data in the particular case. After Run Query, use the Export Query Results node to push the data to the Sheet. In the Export Query Results, set the value of Query Result ID to Run Query > Query Result and set the value of the Spreadsheet URL to the full URL path of the Sheet you want to push the data to. Hope this helps and is a little easier to build in Chains.
0
Jeff - this worked like a charm. This is taking our reporting process to a new level. Thanks so much.
0
I am looking to implement a simiilar solution as the wdata connections are very within spreadsheets. this will create a problem when the user wanted to roll forward every month. they need someone to manually update the spreadsheet id's in the chains. is there a better way to push data to spreadsheets without having to change the chains?
0
Hi Ganesh, I do have a guide on adding automation for rolling forward and using a new Spreadsheet. The guide was written to help avoid the Spreadsheet max cell limit, but can be used for cases when a new Spreadsheet is built during a roll forward process in general. This may help with the use case you're working on. The guide is located at https://support.workiva.com/hc/en-us/community/posts/360073681672-Tutorial-How-to-roll-forward-Spreadsheets-via-Chains-to-avoid-max-cell-limit. There are three options in the guide were option 1 is manual, option 2 is partial automation, and option 3 is full automation.
0
Hi Jeff,
Is there anyway we can refresh the connection via chains rather than going for export query results? I am asking this because query connection from spreadsheet to query helps us track the trail for data from document till the source in wdata. Tracing back the data and links was a big selling point for us.0
Hi Krishma, there isn't a way to refresh a connected Sheet via Chains at this time. An alternative method is described above. A similar question was asked here and the information was passed to the Product Team for further consideration.
0
Update - The Connection Management API endpoints have been added to the Wdata API. Developer documentation for these endpoints can be found at https://developers.workiva.com/workiva-wdata/reference/wdata-connectionmanagement
0Please sign in to leave a comment.
Comments
9 comments