Answered

Taking a SmartSheet html table to a collection - community discussion.

(0) Share

Report

Posted on by martinav

3,340

I have what I thought would be a simple task. Take a Smartsheet table, and pull it into a collection. Well, first of all. I was WRONG! The availability of commands to not exist to get to the data itself. At this point, I have been able to get lists of sheets, their identifiers, and to get an html table. Beyond that, I'm stuck. I have not been able to do anything with it beyond that. I have beat on this from many angles. I am posting my success here, I am needing some help on next steps to actually get the table into a usable collection so I can use queries and make tables or galleries from results.

Categories:

Building Power Apps

I have the same question (0)

All responses (20)

Answers (1)

Community Power Pla... on at

Like (2)

Report
Sounds like a fun challenge! Paging @Rambling-Reece for his Match() expertise!
I've got a regex that can extract the values here (assuming this is the same HTML table form your post yesterday)

//For values '>(.+)<\/td> //For headers '>(.+)<\/th>

We can use Match() to pull those using that regex. The challenge is how to distinguish which column they belong to.
I'll play around with this. Should be doable.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (1)

Report
Update
I've got the headers collected like this:
Collect(htmlHeaders,Ungroup(MatchAll(htmlTextInput.Text,"'>(.+)<\/th>",MatchOptions.Contains).SubMatches,"SubMatches"))
Then using this
Concat(htmlHeaders,Value,", ")
Resulting in this
DRAWING #, DESCRIPTION, NOTES, LAST UPDATED, LAST UPDATED BY

Was this reply helpful? Yes No
Community Power Pla... on at

Like (1)

Report
Update2
I've got values collected using this
ClearCollect(htmlValues,Ungroup(MatchAll(htmlTextInput.Text,"'>(.+)<\/td>",MatchOptions.Contains).SubMatches,"SubMatches"))
I'm wondering if we can use FirstN() to collect the first row of the table, referencing CountRows(htmlHeaders) as the N in FirstN like this
Concat(FirstN(htmlValues,CountRows(htmlHeaders)),"""" & Value,""", ")
giving us this (closely resembles JSON, just need the header values in there)
"201013", "JS75S PRV Dwg. 72784-2-1", " ", "1/10/2017 2:54:30 PM", "smith@company.com
We can then delete the FirstN rows, and collect again.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (1)

Report
I tweaked the code again to get it even closer to JSON.
"{" & Char(10) & Concat(FirstN(htmlValues,CountRows(htmlHeaders)),"""" & Value,""", " & Char(10)) & Char(10) & "}"
{ "201013", "JS75S PRV Dwg. 72784-2-1", " ", "1/10/2017 2:54:30 PM", "smith@company.com }

Was this reply helpful? Yes No
martinav 3,340 on at

Like (1)

Report

Dang! I wasnt even finished with my post yet. I was starting from zero to help people find their way through these smartsheet commands. They are not very intuitive. But... WOW! I will see what I can do with your info. Pretty impressive, I must say! I'll post more in a bit, and finish my initial email that gets me to the point of even finding the html table!

Was this reply helpful? Yes No
Community Power Pla... on at

Like (1)

Report
Update 3
Using this formula
ForAll(htmlHeaders, Collect(htmlData,{ColumnName: Value, ColumnValue: First(FirstN(htmlValues,CountRows(htmlHeaders))).Value}); Remove(htmlValues,First(htmlValues)) )
I'm able to create this

unfortunately there's not a way (that I know of) to pivot a collection. Closer still.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (1)

Report
Update 4
I've pulled in the number of rows and their first column value using this
ClearCollect(htmlRowCount,RenameColumns(Ungroup(MatchAll(htmlTextInput.Text,"<tr>(.+)<\/td>",MatchOptions.Contains).SubMatches,"SubMatches"),"Value","RowIdentifier"))
Which in this case returns 3 rows with the first value of each row, in your case the Drawing #.
I can then use that to loop through he previous formula like this adding the Drawing # to each data set (HTML row)
ForAll(htmlRowCount, ForAll(htmlHeaders, Collect(htmlData,{DataSetID: First(Match(RowIdentifier,"'>(.+)",MatchOptions.Contains).SubMatches).Value, ColumnName: Value, ColumnValue: First(FirstN(htmlValues,CountRows(htmlHeaders))).Value}); Remove(htmlValues,First(htmlValues))) )
Giving me this.
So this is technically usable, however not a traditional table. It also requires that the HTML table have a unique ID in the first column of the table.
I'll attach the .msapp of this so you can play around with it.

htmlTable.msapp

Was this reply helpful? Yes No
martinav 3,340 on at

Like (0)

Report
This is great. You are way ahead of me.

I'm still stuck on step one. I cannot get the statement to pull the data out with the >(.+)<\/th> masks. My results show everything all the html tags. Even with your cool app.

Where I should get this:

DRAWING #, DESCRIPTION, NOTES, LAST UPDATED, LAST UPDATED BY

I get this:

<tr><th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:171px'>DRAWING #</th><th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:599px'>DESCRIPTION</th><th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:575px'>NOTES</th><th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:150px'>LAST UPDATED</th><th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:213px'>LAST UPDATED BY

Was this reply helpful? Yes No

martinav

3,340 on at

Like (0)

Report

I used the sample data I provided, and your method works as described.

<table style='font-family:Arial,Helvetica,sans-serif;border-collapse:collapse;width:100%;'>
<tr><th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:171px'>DRAWING #</th>
<th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:599px'>DESCRIPTION</th>
<th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:575px'>NOTES</th>
<th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:150px'>LAST UPDATED</th>
<th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:213px'>LAST UPDATED BY</th>
</tr>

<tr><td style='border:1px solid #ddd; padding: 4px;'>201013</td>
<td style='border:1px solid #ddd; padding: 4px;'>JS75S PRV Dwg. 72784-2-1</td>
<td style='border:1px solid #ddd; padding: 4px;'> </td>
<td style='border:1px solid #ddd; padding: 4px;'>1/10/2017 2:54:30 PM</td>
<td style='border:1px solid #ddd; padding: 4px;'>smith@company.com</td>
</tr>

<tr><td style='border:1px solid #ddd; padding: 4px;'>201015</td>
<td style='border:1px solid #ddd; padding: 4px;'>JS75S316 General Arrg.</td>
<td style='border:1px solid #ddd; padding: 4px;'> </td>
<td style='border:1px solid #ddd; padding: 4px;'>1/10/2017 2:54:30 PM</td>
<td style='border:1px solid #ddd; padding: 4px;'>smith@company.com</td>
</tr>

<tr><td style='border:1px solid #ddd; padding: 4px;'>201017</td>
<td style='border:1px solid #ddd; padding: 4px;'>JS75S316 General Arrg. w/Flue</td>
<td style='border:1px solid #ddd; padding: 4px;'> </td>
<td style='border:1px solid #ddd; padding: 4px;'>1/10/2017 2:54:30 PM</td>
<td style='border:1px solid #ddd; padding: 4px;'>smith@company.com</td>
</tr>

Result:

DRAWING #, DESCRIPTION, NOTES, LAST UPDATED, LAST UPDATED BY

However, I added carriage returns in the data to help organize. When I use my raw data (below) the results are different.

<table style='font-family:Arial,Helvetica,sans-serif;border-collapse:collapse;width:100%;'><tr><th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:171px'>DRAWING #</th><th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:599px'>DESCRIPTION</th><th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:575px'>NOTES</th><th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:150px'>LAST UPDATED</th><th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:213px'>LAST UPDATED BY</th></tr><tr><td style='border:1px solid #ddd; padding: 4px;'>201013</td><td style='border:1px solid #ddd; padding: 4px;'>JS75S PRV Dwg. 72784-2-1</td><td style='border:1px solid #ddd; padding: 4px;'> </td><td style='border:1px solid #ddd; padding: 4px;'>1/10/2017 2:54:30 PM</td><td style='border:1px solid #ddd; padding: 4px;'>neisler@company.com</td></tr><tr><td style='border:1px solid #ddd; padding: 4px;'>201015</td><td style='border:1px solid #ddd; padding: 4px;'>JS75S316 General Arrg.</td><td style='border:1px solid #ddd; padding: 4px;'> </td><td style='border:1px solid #ddd; padding: 4px;'>1/10/2017 2:54:30 PM</td><td style='border:1px solid #ddd; padding: 4px;'>smith@company.com</td></tr><tr><td style='border:1px solid #ddd; padding: 4px;'>201017</td><td style='border:1px solid #ddd; padding: 4px;'>JS75S316 General Arrg. w/Flue</td><td style='border:1px solid #ddd; padding: 4px;'> </td><td style='border:1px solid #ddd; padding: 4px;'>1/10/2017 2:54:30 PM</td><td style='border:1px solid #ddd; padding: 4px;'>smith@company.com</td></tr><tr><td style='border:1px solid #ddd; padding: 4px;'>201020</td><td style='border:1px solid #ddd; padding: 4px;'>JS75L General Arrg.</td><td style='border:1px solid #ddd; padding: 4px;'> </td><td style='border:1px solid #ddd; padding: 4px;'>1/10/2017 2:54:30 PM</td><td style='border:1px solid #ddd; padding: 4px;'>smith@company.com</td></tr>

Result:

<tr><th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:171px'>DRAWING #</th><th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:599px'>DESCRIPTION</th><th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:575px'>NOTES</th><th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:150px'>LAST UPDATED</th><th style='padding:4px;padding-top:6px;padding-bottom:6px;text-align:left;background-color:black;color:white;width:213px'>LAST UPDATED BY

Thoughts?

Was this reply helpful? Yes No

Community Power Pla... on at

Like (1)

Report
Is this for the row count portion? A carriage return in regex is \n, so it would change like this:
//Old <tr>(.+)<\/td> //New <tr>\n(.+)<\/td>

Was this reply helpful? Yes No