2

I'm using importxml to pull data from websites. So far this portion is working just fine. However, I need to join the text in these cells into one cell in HTML text that I can upload to my website. I've attached the screenshot, I know the formula/coding is absolutely wrong! But it's ideally what I'm going for. How could I create a better formula/code for this? I'm still super new at this!

I use importxml to get the data I need: enter image description here

I'm trying to put all this data in one cell that has to be in HTML format: enter image description here

2

I see what the Asker is getting at, and I have an answer and some advice:

First, I should warn you, that down this path lies madness.

It may seem like something obvious to do, and it doesn't look that hard, but generating HTML using a spreadsheet (be it Google Sheets, Excel or any other spreadsheet package) may seem simple, but things can and will get over-complicated, or you will very soon reach a point in which you will need to write a macro to achieve your desired output.

Moreover, once you take the plunge and start writing your first couple of macros, if your sheet is not already overly complicated, you will hit the limits of the macro language pretty soon too.

But why? I'm not doing anything too intricate!

The reason you will quickly exhaust the features of a spreadsheet when using it to generate HTML is that generating structured markup is not well suited to the overall design of grid calculation paradigm. Which is the reason we have Word and Excel and that

"In the beginning there was Visicalc, and Wordstar, 
 and each did what they did 
 and they were not a single program" 

(From 'The book of Mozilla', I forget which verses).

Word processing is in itself, basically the process of generating a structured markup document. So there are software packages more suited to the task.

So what would be a wise thing to do?

My advice would be to keep the database in the spreadsheet, (a task for which it is well suited) and spare yourself deep frustration and unmet deadlines, and invest some time to learn a proper language; one that is easy to learn and efficient at templating HTML.

Python comes to mind. The alternative being taking advantage of the features of other Google products (since you're already doing Sheets) like Google Sites that makes it dead easy to create proper websites, and allows to embed and drive some of its pages from the data in a spreadsheet.

But I want to do it in Sheets! In fact, that's why I am asking here!

Agreed. However, I've been down that road, and would have appreciated that someone warned me that madness awaited, so I felt it would have been irresponsible and unfair to the Asker not to warn him of the perils ahead. (I'm looking at those who know, and didn't raise a voice.)

That being said, I've seen people abuse spreadsheets in manners far more troubling. So:

Here is my shot at good advice on the design of a structured-markup generating spreadsheet


You can see all this advice in action in this Google Spreadsheet, so you can examine the actual formulas at your leisure and see how these simple points will help you avoid creating an unmaintainable monstrosity and keep your sanity for a while more.

  1. Create a master tag table that calculates the attributes for basic tags from the values of two or more independent cells
  2. Take advantage of advanced features in the spreadsheet like named ranges to keep all your formulas simple by referencing the values on the master tag generating table
  3. For the named ranges, choose names that look like the markup they reference so your formulas are readable and maintainable, like say

     =CONCATENATE(p_s1, A4, p_close) or
     =CONCATENATE(span_s2, B7, span_close)
    
  4. Complexity will creep up quickly, keep your formulas simple and build the markup progressively from the inside out, taking advantage of using formulas in adjacent cells. for example, in column C surround a plain value with inner <p> tags, in column D, surround the value from C in outer <span> tags, in column E, surround whatever is in D with outer <div> tags and so on. one step at a time, you've got plenty of columns and then some

  5. Avoid at all costs doing two steps or complex markup in a single cell. Always rely on your master tags table, because you know when you're done, someone will suggest that the class for all titles should be something different than what you coded. You'll be glad then that you didn't hardcode those attributes in a formula that wraps around twice on the f(x) bar, did you?

Please do visit the example sheet since that will be a lot clearer that a gazillion screenshots or the dense prose I tend to write when I get all worked up. Hope this really helps!

  • 1
    Thank you so much for this response! I completely see what you mean! The complexity is already creeping up with headers, bolded letters, bullet points, etc. And trying to manage several different products at once. I am definitely trying to look into Python. I see a lot of tutorials for web scraping using python, beautiful soup, and sublime text. I've never worked with these, but I think it's worth the time to learn the basics of it first, and then try to get help through this forum. Thank you again for your detailed response, it really really helped me out so much! – robbie777 Apr 21 '17 at 3:31
  • 1
    Yup, a spreadsheet is not the best tool to generate HTML. It is, however, a good representation of a database. That doesn't mean it can't be done, just that the effort invested will be orders of magnitude greater compared to using a dedicated solution in addition to your already developed spreadsheet database. Let the spreadsheet handle the data, feed that data to a specialized HTML templating solution. – hlecuanda Apr 21 '17 at 3:37
3

So to start, you want to double each of the quotes inside of your HTML tags for it to show up properly and not cancel out:

so class="p1" would be class=""p1""

then for for the the list of p1 elements , you can use regexreplace to surround your data with the tags instead of concatenation (its just a little cleaner in my opinion)

So the regex for everything in a single cell would be (.*) this creates a capture group which we can later reference with $1

Using my own sample data to demonstrate: enter image description here for a single row this would like:

=REGEXREPLACE(A1,"(^.*$)","<p class=""p1"">$1</p>"))

which nicely prints out:

If you want to use join eventually so you don't have to copy the whole thing, you can use arrayformula first to stack the p1 elements with this formula:

=arrayformula(if(istext(A2:A),REGEXREPLACE(A2:A,"(^.*$)","<p class=""p1"">$1</p>"),))

enter image description here

then to group it all together into one cell:

=join(char(10),filter(B:B,istext(B:B)))

This is what the final would look like: enter image description here

  • =REGEXREPLACE(A1,"(^.*$)","<p class=""p1"">$1</p>")) looks to be the same as ="<p class=""p1"">"&A1&"</p>", isn't it? – Rubén Apr 8 '17 at 14:54
  • REGEXREPLACE? O.o He wants to do structured markup using a spreadsheet and you point him towards regular expressions? what is wrong with you, people?! sure, that may solve this instance of a single cell, but surely you know how fast this spreadsheet will become not unwieldy, but an unmaintainable monstrosity! a man's saneness is at stake! – hlecuanda Apr 18 '17 at 15:48
0

In Google Sheets formulas, the text should be quote enclosed. If the text includes quotes, the inner quotes should be doubled. Examples

Text without inner quotes
Formula: ="<p>Hello world!</p>"
Displayed result: <p>Hello world!</p>

Text with inner quotes
Formula: ="<h1 class=""p1"">Hello world!</h1>"
Displayed result: <h1 class="p1">Hello world!</h1>

Using scalar references
A1 value: Hello world!

Simple tags
B1 formula: ="<p>"&A1&"</p>"
B1 displayed value: <p>Hello world!</p>

Tags with attributes
B2 formula: ="<h1 class=""p1"">"&A1&"</h1>"
B2 displayed result: <h1 class="p1">Hello world!</h1>

Using range references
Input

    |         A
----+--------------------- 
 2  | Hello Mexico
 3  | Hello USA
 4  | Hello Canada

Formula

C1 formula: =ArrayFormula("<p>"&A2:A4&"</p>")

Result

C1:C3 displayed values:

    |         C
----+--------------------- 
 1  | <p>Hello Mexico</p>
 2  | <p>Hello USA</p>
 3  | <p>Hello Canada</p>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.