Variable substitution in rest api filter parameters

%3CLINGO-SUB%20id%3D%22lingo-sub-1587442%22%20slang%3D%22en-US%22%3EVariable%20substitution%20in%20rest%20api%20filter%20parameters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1587442%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20a%20beginner%20in%20powershell.I%20was%20trying%20to%20call%20Tableau%20REST%20APIs%20using%20powershell.To%20make%20the%20script%20generic%20i%20would%20need%20to%20pass%20the%20filter%20parameters%20by%20through%20a%20variable.In%20the%20example%20below%2C%3C%2FP%3E%3CP%3EI%20have%20a%20csv%20file%20filterfile%2Cwhich%20has%20two%20columns%20-Fit%20Name%2CSet%20Name.%3C%2FP%3E%3CP%3EFit%20Name%26nbsp%3B%20%26nbsp%3B%20Set%20Name%3C%2FP%3E%3CP%3EA%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BAA%3C%2FP%3E%3CP%3EB%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BBB%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20need%20the%20filter%20parameter%20in%20%24param%20to%20get%20expanded%20down%20the%20line%20in%20the%20context%20of%20invoke%20restmethod.Since%20the%20csv%20has%20only%20two%20rows(it%20can%20have%20N%20number%20of%20rows)%2Cthe%20expanded%20values%20should%20be%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInvoke-RestMethod%20-Uri%20%22%24server%2Fapi%2F3.5%2Fsites%2F%24siteID%2Fviews%2F%24responseD%2FPDF%3Fvf_Filter1%3D%3CSTRONG%3EA%3C%2FSTRONG%3E%26amp%3Bvf_Filter2%3D%3CSTRONG%3EAA%3C%2FSTRONG%3E%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInvoke-RestMethod%20-Uri%20%22%24server%2Fapi%2F3.5%2Fsites%2F%24siteID%2Fviews%2F%24responseD%2FPDF%3Fvf_Filter1%3D%3CSTRONG%3EB%3C%2FSTRONG%3E%26amp%3Bvf_Filter2%3D%3CSTRONG%3EBB%3C%2FSTRONG%3E%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esample%20code%3A%3C%2FP%3E%3CP%3E%24param%3D%22vf_Filter1%3D%24(%24_.%7BFit%20Name%7D)%26amp%3Bvf_Filter2%3D%24(%24_.%7BSet%20Name%7D)%22%3C%2FP%3E%3CP%3EImport-Csv%20%24filterfile%20%7C%20ForEach-object%7B%3C%2FP%3E%3CP%3EInvoke-RestMethod%20-Uri%20%22%24server%2Fapi%2F3.5%2Fsites%2F%24siteID%2Fviews%2F%24responseD%2FPDF%3F%24Param%22%3C%2FP%3E%3CP%3E%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESomeone%20kindly%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETIA%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1587442%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EWindows%20PowerShell%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1587993%22%20slang%3D%22en-US%22%3ERe%3A%20Variable%20substitution%20in%20rest%20api%20filter%20parameters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1587993%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F758818%22%20target%3D%22_blank%22%3E%40ann_joseph%3C%2FA%3E%26nbsp%3BI%20think%20this%20is%20a%20good%20usecase%20for%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowershell%2Fscripting%2Flearn%2Fdeep-dives%2Feverything-about-string-substitutions%3Fview%3Dpowershell-7%23format-string%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E-f%20format%20operator%3C%2FA%3E.%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20short%2C%20you%20put%20%22anchors%22%20in%20your%20string%20in%20the%20form%20of%20%3CSTRONG%3E%7B0%7D%3C%2FSTRONG%3E%20for%20the%20first%20one%20and%20%3CSTRONG%3E%7B1%7D%3C%2FSTRONG%3E%20for%20the%20second%2C%20etc.%20You%20can%20then%20%22inject%22%20actual%20values%20into%20those%20using%20%3CSTRONG%3E-f%3C%2FSTRONG%3E.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3EHere's%20an%20example%20of%20it's%20use%20in%20your%20sample%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powershell%22%3E%3CCODE%3E%24param%20%3D%20'vf_Filter1%3D%7B0%7D%26amp%3Bvf_Filter2%3D%7B1%7D'%0A%0AImport-Csv%20%24filterfile%20%7C%20ForEach-object%7B%0A%20%20%20%20%23%20Take%20%24param%20and%20%22inject%22%20%24_.'Fit%20Name'%20into%20%7B0%7D%20and%20%24_.'Set%20Name'%20into%20%7B1%7D%2C%0A%20%20%20%20%23%20assigning%20result%20into%20new%20variable%0A%20%20%20%20%24Filter%20%3D%20%24param%20-f%20%24_.'Fit%20Name'%2C%20%24_.'Set%20Name'%0A%0A%20%20%20%20%24Uri%20%3D%20%22%24server%2Fapi%2F3.5%2Fsites%2F%24siteID%2Fviews%2F%24responseD%2FPDF%3F%24Filter%22%0A%20%20%20%20Invoke-RestMethod%20-Uri%20%24Uri%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHope%20that%20makes%20sense!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1588241%22%20slang%3D%22en-US%22%3ERe%3A%20Variable%20substitution%20in%20rest%20api%20filter%20parameters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1588241%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F62408%22%20target%3D%22_blank%22%3E%40Joshua%20King%3C%2FA%3E%26nbsp%3B%20Thank%20you%20so%20much%20for%20the%20reply.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20to%20perform%20the%20inject%20step%20prior%20to%20the%20import-csv.%20So%20that%20the%20script%20can%20become%20more%20generic%20and%20others%20can%20just%20change%20the%20%24param%20and%20%24filter%20from%20the%20top%20portion%20of%20variable%20assignments%20rather%20than%20allowing%20them%20to%20edit%20inside%20the%20script.%3C%2FP%3E%3CPRE%3E%24param%20%3D%20'vf_Filter1%3D%7B0%7D%26amp%3Bvf_Filter2%3D%7B1%7D'%3C%2FPRE%3E%3CPRE%3E%20%24Filter%20%3D%20%24param%20-f%20%24_.'Fit%20Name'%2C%20%24_.'Set%20Name'%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EImport-Csv%20%24filterfile%20%7C%20ForEach-object%7B%3C%2FPRE%3E%3CPRE%3E%24Uri%20%3D%20%22%24server%2Fapi%2F3.5%2Fsites%2F%24siteID%2Fviews%2F%24responseD%2FPDF%3F%24Filter%22%0A%20%20%20%20Invoke-RestMethod%20-Uri%20%24Uri%0A%7D%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1589478%22%20slang%3D%22en-US%22%3ERe%3A%20Variable%20substitution%20in%20rest%20api%20filter%20parameters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1589478%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F62408%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40Joshua%20King%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%20I%20tried%20this%20solution.But%20the%20%24Filter%20in%20the%20Uri%20is%20space%20when%20we%20put%20it%20in%20uri.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EKindly%20help%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi All,

 

I am a beginner in powershell.I was trying to call Tableau REST APIs using powershell.To make the script generic i would need to pass the filter parameters by through a variable.In the example below,

I have a csv file filterfile,which has two columns -Fit Name,Set Name.

Fit Name    Set Name

A                   AA

B                   BB

 

I would need the filter parameter in $param to get expanded down the line in the context of invoke restmethod.Since the csv has only two rows(it can have N number of rows),the expanded values should be

 

Invoke-RestMethod -Uri "$server/api/3.5/sites/$siteID/views/$responseD/PDF?vf_Filter1=A&vf_Filter2=AA"

 

Invoke-RestMethod -Uri "$server/api/3.5/sites/$siteID/views/$responseD/PDF?vf_Filter1=B&vf_Filter2=BB"

 

sample code:

$param="vf_Filter1=$($_.{Fit Name})&vf_Filter2=$($_.{Set Name})"

Import-Csv $filterfile | ForEach-object{

Invoke-RestMethod -Uri "$server/api/3.5/sites/$siteID/views/$responseD/PDF?$Param"

}

 

Someone kindly help.

 

TIA

3 Replies

@ann_joseph I think this is a good usecase for the -f format operator.

In short, you put "anchors" in your string in the form of {0} for the first one and {1} for the second, etc. You can then "inject" actual values into those using -f.

Here's an example of it's use in your sample:

 

$param = 'vf_Filter1={0}&vf_Filter2={1}'

Import-Csv $filterfile | ForEach-object{
    # Take $param and "inject" $_.'Fit Name' into {0} and $_.'Set Name' into {1},
    # assigning result into new variable
    $Filter = $param -f $_.'Fit Name', $_.'Set Name'

    $Uri = "$server/api/3.5/sites/$siteID/views/$responseD/PDF?$Filter"
    Invoke-RestMethod -Uri $Uri
}

 

Hope that makes sense!

@Joshua King  Thank you so much for the reply.

 

I am looking to perform the inject step prior to the import-csv. So that the script can become more generic and others can just change the $param and $filter from the top portion of variable assignments rather than allowing them to edit inside the script.

$param = 'vf_Filter1={0}&vf_Filter2={1}'
 $Filter = $param -f $_.'Fit Name', $_.'Set Name'

 

Import-Csv $filterfile | ForEach-object{
$Uri = "$server/api/3.5/sites/$siteID/views/$responseD/PDF?$Filter"
    Invoke-RestMethod -Uri $Uri
}

 

@Joshua King  I tried this solution.But the $Filter in the Uri is space when we put it in uri.

Kindly help