Refresh Connection with Defined Parameter Values
Con rispostaHello! I am attempting to use a Batch Refresh Connection command to refresh a connection within one of our spreadsheets. I would like to have a list of parameter values within the spreadsheet that users can maintain and use as the source and destination parameters.
I was able to do this successfully for only one of the parameter values by getting the sheet data that holds the parameter list, extracting that value, and inputting the extracted value as the source and destination parameters in the Batch connection refresh command.
I expect the list to be dynamic, meaning there will not always be the same amount of parameter values listed, which prevents me from just using multiple extract value commands to pull each of the values. I was able to iterate over the extract value command to pull each of the parameter values separately, but I am having trouble inputting these into the source and destination parameter fields within the refresh connection command, since there are multiple values that need to be input all at once to run the query. Is there any way to make this process dynamic?
-
Hi Talia. You may have an easier time if you build out your control sheet to have multiple columns that hold the unique values. For example, column A may be "Source Parameters" and column B "Destination Parameters". The article How to set up a Chain Control Sheet is a good reference. Then you could use a Group Command to iterate over the control sheet row by row with logic that includes a check if source or destination parameters should be used.
Instead of using Extract Value commands, you may want to consider using Dynamic Outputs.
Finally, if you are needing more complex logic in the construction of your batch connection refresh process, you can use Handlebars and the build-in helpers (like #if) to construct a JSON payload for the refresh command. You can see an example of the JSON structure here.
0
Hi Jeff Hickey! Thank you for your response. This is what the current output of my CSV to JSON command looks like:
Would you recommend that I add the dynamic output here, in the CSV to JSON command, or in the get sheet command (titled "Get Parameter Value" in my chain)? I quickly tested using the get sheet command ("Get Parameter Value") to get a dynamic output, but this is the error I received in the batch connection refresh command:
Sorry for any lack of awareness when it comes to dynamic outputs. I do not use them often!
0
I was able to reconfigure the dynamic output of the get sheet data command shown below:
and this was successful for returning only one of the values from the original list:
Is there a way to return multiple lines (May 24 and Jun 24 in this instance) to use in the connection refresh command?
0
Hi Talia, I may not have originally understood the question. To summarize, it sounds like the value for the Query multi-select parameter "Time" may have one or more values which will be controlled by the control sheet. Since you won't know how many values will be in the control sheet, you want to dynamically build the parameter value in the refresh connection node. Is this accurate? Assuming so, the below is a framework of a potential solution.
-
Get Sheet Data
Use this command to pull the column from the control sheet containing the specified value(s) -
CSV to JSON
Transform the Get Sheet Data output to JSON -
Render Text Template
Use the CSV to JSON output as input for a JSON Variable in this command. Then build a template to construct a comma separated list of values. The following template example uses "times" as the name of the JSON Variable: {{#each times}}"{{[Time Parameter Values]}}"{{#unless @last}},{{/unless}}{{/each}} -
POST
I don't believe the existing Batch Refresh Connection or Refresh Connection commands allow you to send in a dynamically generated list of values for the multi-value source/destination parameters. However, you can do it with the HTTP Requests Connector. You'll need to first make a call to get a bearer token, and then call the Refresh batch of connections or Refresh connection API endpoint and pass in the output from the Render Text Template. Below is a screenshot of the example body payload for the refresh batch of connections endpoint.
0
Hi Jeff, thank you so much for your help with this (and helping to understand handlebars in general). I was able to get my desired result using your response!
0
Hi Jeff Hickey! I am not sure if you have experienced something like this before, but I am implementing the method you described above the automate one of our reports and have found that any parameter values that I select from my list with an '&' symbol, like "AD&D", results in an error. Do you know why this is happening and if there is a possible workaround? The POST connection from the chain run is successful, but the connection fails to refresh in the spreadsheet and reads as "Bad Request". When I take out the values with this symbol from the parameter list and run the chain, the connection is successful in the spreadsheet.
UPDATE: I just looked at the post command logs and I can see that the "AD&D" parameter value is being transformed to "AD&D". Is there any way I can avoid this?
0
While this may not be the "best" solution, it should get it working for you with minimal edits. In your POST Command, transform the Rendered Text variable with a Replace Transformation. Set the Pattern = "&", Replacement = "&", and Match Type = "Exact".
0Accedi per aggiungere un commento.
Commenti
7 commenti