How to convert URL parameters (GET parameters) into Data table.(Power Automate Desktop)
This section shows how to expand the GET parameter of a URL into a data table.
Overall view of Flow
Robin(for copy and paste)
It can be copied and pasted into Power Automate Desktop.
SET Url TO $'''https://www.samurai-emblem.com/?v1=a&v2=b&v3=c#head'''
Text.ParseText.ParseForFirstOccurrence Text: Url TextToFind: $'''?''' StartingPosition: 0 IgnoreCase: False OccurrencePosition=> PositionQuestion
Text.ParseText.ParseForFirstOccurrence Text: Url TextToFind: $'''#''' StartingPosition: 0 IgnoreCase: False OccurrencePosition=> PositionSharp
IF PositionQuestion >= 0 THEN
IF PositionSharp >= 0 THEN
Text.GetSubtext.GetSubtext Text: Url CharacterPosition: PositionQuestion + 1 NumberOfChars: PositionSharp - PositionQuestion - 1 Subtext=> Subtext
ELSE
Text.GetSubtext.GetSubtextFrom Text: Url CharacterPosition: PositionQuestion + 1 Subtext=> Subtext
END
END
Excel.LaunchExcel.LaunchUnderExistingProcess Visible: False Instance=> ExcelInstance
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: $'''Key''' Column: 1 Row: 1
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: $'''Value''' Column: 2 Row: 1
SET RowCounter TO 2
Text.SplitText.SplitWithDelimiter Text: Subtext CustomDelimiter: $'''&''' IsRegEx: False Result=> TextList_Ampersand
LOOP FOREACH CurrentItem IN TextList_Ampersand
Text.SplitText.SplitWithDelimiter Text: CurrentItem CustomDelimiter: $'''=''' IsRegEx: False Result=> TextList_SplitEqual
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: TextList_SplitEqual[0] Column: 1 Row: RowCounter
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: TextList_SplitEqual[1] Column: 2 Row: RowCounter
Variables.IncreaseVariable Value: RowCounter IncrementValue: 1
END
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: 1 StartRow: 1 EndColumn: 2 EndRow: RowCounter - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
Excel.CloseExcel.Close Instance: ExcelInstance
Flow creation steps
Place an additional action in the How to leave only the GET parameter from the URL section.
Set up Launch Excel Action and select with a blank document to create an empty book.
Create the first row of an empty book created by setting up two Write to Excel worksheet.
The value to be written is the column name. This may be changed.
In the example, Key and Value are used.
| Parameter | Value of the first Action | Value of the second Action |
|---|---|---|
| Value to write | Key | Value |
| Write mode | On specified cell | On specified cell |
| Column | 1 | 2 |
| Row | 1 | 1 |
Set up Set Variables and create a variable %RowCounter% and set the initial value to 2.
Install Split text Action to split URL parameters with "&".
Install another Split text Action. Therefore, the Variables produced name is changed. (In the example, %TextList_Ampersand%)
| Parameter | Value |
|---|---|
| Text to spit | %Subtext% |
| Delimiter type | Custom |
| Custom delimiter | & |
| Is regular expression | OFF |
Set up For each and specify the result of splitting URL parameters with "&".(In the example, %TextList_Ampersand%)
Set up Split text Action in the For each section. This action further splits the text with "=".
The Variables produced name is %TextList_SplitEqual% in the example.
| Parameter | Value |
|---|---|
| Text to spit | %TextList_Ampersand% |
| Delimiter type | Custom |
| Custom delimiter | = |
| Is regular expression | OFF |
Place two Write to Excel worksheet Action in For each.
| Parameter | Value of the first Action | Value of the second Action |
|---|---|---|
| Value to write | %TextList_SplitEqual[0]% | %TextList_SplitEqual[1]% |
| Write mode | On specified cell | On specified cell |
| Column | 1 | 2 |
| Row | %RowCounter% | %RowCounter% |
Set up Increase variable Action in For each and count up by 1.
The For each part of the flow is complete. The flow is as shown below.
Under For each set up Read from Excel worksheet Action and configure as follows.
| Parameter | Value |
|---|---|
| Retrive | Value from a range of cells |
| Start column | 1 |
| Start row | 1 |
| End column | 2 |
| End row | %RowCounter - 1% |
| First line of range contains column names | ON |
Finally, close the book with Close Excel Action. No saving is required.
When this flow is executed, the Read from Excel worksheet Action
In the Variable produced(variable name %ExcelData% by default)
The URL parameter is stored in the Data table.
As an example, if you execute the flow for "www.samurai-emblem.com/?test1=123&test2=456&test3=789#abc", you will get the following Data table will be generated.
---