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

[martinav]

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.  

<need to reboot, to be continued>

[Anonymous]

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>

[Anonymous]

@martinav 

Looks like the 'Notes' column also went from " " (one white space) to "" (Blank)

To fix that, we'll need to tweak all the regex like this (probably should have been this from the get-go)

//All references to this
(.+)

//Should now be this
(.*)

This will capture null entries. 

[martinav]

I'm not sure if I explained that well.  The data from the smartsheet function looks like this:

<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>

The data you used was the same data after I hit return on my keyboard after </td> to organize for clairty.  That gave this:

<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>

The results in your app with my raw data looks like this:

[Anonymous · Accepted solution]

Ooooooooooh okay that makes sense, sorry for the misunderstanding. That will shake the regex up, still doable.

GetHeaders formula becomes

ClearCollect(htmlHeaders,Substitute(Replace(MatchAll(htmlTextInput.Text,"(?:(?!'>).)*<\/th>",MatchOptions.Contains).FullMatch,1,1,""),"</th>",""))

GetValues formula becomes 

ClearCollect(htmlValues,Substitute(Replace(MatchAll(htmlTextInput.Text,"(?:(?!'>).)*<\/td>",MatchOptions.Contains).FullMatch,1,1,""),"</td>",""))

 GetRowCount becomes

ClearCollect(htmlRowCount,RenameColumns(MatchAll(htmlTextInput.Text,"(?:(?!<tr>).)*<\/td>",MatchOptions.Contains).FullMatch,"FullMatch","RowIdentifier"))

Set htmlData becomes

ForAll(htmlRowCount,
ForAll(htmlHeaders,
Collect(htmlData,{DataSetID: Substitute(Replace(Match(RowIdentifier,"(?:(?!'>).)*?<\/",MatchOptions.Contains).FullMatch,1,1,""),"</",""), ColumnName: FullMatch, ColumnValue: First(FirstN(htmlValues,CountRows(htmlHeaders))).FullMatch});
Remove(htmlValues,First(htmlValues)))
)

[Anonymous]

Updated version of the .msapp attached here

[martinav]

Definitely some good work here.  This is a very difficult problem to crack.  You did it!  I can figure out my transposing.  I have done that before in an SQL table that reported similarly.  I'll show my work later.  Great job, @Anonymous ! 

Lots of kudos coming! 

[Anonymous]

Thank you very much! It was fun working on it.

[Anonymous]

Martin How do you transpose your data?

[martinav]

@Anonymous 

Can you restate the question?  WHen I think transpose, I think of excel transpose, where you swap columns and rows.  

[Anonymous]

I think providing details would help. PowerApps doesn't have a very easy means of working with SmartSheet data. We have a historical SmartSheet and SmartSheet form that collects data. We want to keep the SmartSheet but convert the collection form to PowerApp. I've imported the data using this thread but I'm now stuck on how to put it in a format that PowerApp can use. I'd like to use the backbone of the smartsheet instead of building a custom form. To do that I know I need my columns as columns instead of rows.