Import data from spreedsheet with multiple web links

[SIPRIAN]

Hello,


Trying to import data from a website.(www.motionindustries.com)
I have a spreadsheet with multiple links to specific products.( ex. https://www.motionindustries.com/productDetail.jsp?sku=00544513)

How an I make Excel click on the link from spreadsheet, copy data from website( price and description) and paste in specific cell.


Thank you,
J

 

[SIPRIAN]

https://www.excelforum.com/excel-ge...heet-with-multiple-web-links.html#post4787856

 

[SIPRIAN]

SKU Data
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added SKUtext" = Table.AddColumn(Source, "SKUtext", each Text.PadStart(Text.From([SKU]),8,"0")),
#"Invoked Custom Function" = Table.AddColumn(#"Added SKUtext", "fnSKUdata", each fnSKUdata([SKUtext])),
#"Expanded fnSKUdata" = Table.ExpandTableColumn(#"Invoked Custom Function", "fnSKUdata", {"Product", "Description", "Price"}, {"Product", "Description", "Price"})
in
#"Expanded fnSKUdata"



fnSKUdata

(SKU) =>
let
Source = Lines.FromBinary(Web.Contents("https://www.motionindustries.com/productDetail.jsp?sku=" & SKU)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each Text.Contains([Column1], "<h1>") or Text.Contains([Column1], "<h2 class=""short-description"">") or Text.Contains([Column1], "<span class=""product-detail-uom"">")),
#"Extracted Text Between Delimiters" = Table.TransformColumns(#"Filtered Rows", {{"Column1", each Text.BetweenDelimiters(_, ">", "<", 0, 0), type text}}),
#"Transposed Table" = Table.Transpose(#"Extracted Text Between Delimiters"),
#"Renamed Columns" = Table.RenameColumns(#"Transposed Table",{{"Column1", "Product"}, {"Column2", "Description"}, {"Column3", "Price"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","$","",Replacer.ReplaceText,{"Price"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Product", type text}, {"Description", type text}, {"Price", Currency.Type}})
in
#"Changed Type"



SKU SKUtext Product Description Price

 

[SIPRIAN]

Source

= Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

Added SKUtext

= Table.AddColumn(Source, "SKUtext", each Text.PadStart(Text.From([SKU]),8,"0"))

Invoked Custom Function

"new column name"
fnSKUdata
"function Query"
fnskudata
"sku optional"
skutext

= Table.AddColumn(#"Added SKUtext", "fnSKUdata", each fnSKUdata([SKUtext]))


Expanded fnSKUdata

= Table.ExpandTableColumn(#"Invoked Custom Function", "fnSKUdata", {"Product", "Description", "Price"}, {"Product", "Description", "Price"})