This is what the product details for one of our products look like and is formatted before we import it into Shopify to be shown on our e-com store:

<ul>
<li>Comfort: waterproof, windproof, lightweight, engineered ventilation</li>
<li>Material: 100% polyester with polyurethane coating</li>
<li>Water column pressure: 4000mm</li>
<li>Fit: Casual unisex</li>
<li>Snap closure</li>
<li>Placket with snap fastenings</li>
<li>Drawstring hood with cap brim</li>
<li>Snap adjustable cuffs</li>
<li>Back yoke with concealed vents</li>
<li>Two side pockets with snaps</li>
<li>Eyelets at arm holes</li>
<li>Ultrasonically welded seams</li>
<li>Fishtail hem</li>
</ul>

What I would like to achieve is to convert that ^ into this:

<table>
   <tr>
    <td>Comfort:</td>
    <td>waterproof, windproof, lightweight, engineered ventilation</td>
  </tr>
   <tr>
    <td>Material:</td>
    <td>100% polyester with polyurethane coating</td>
  </tr>
   <tr>
    <td>Water column pressure:</td>
    <td>4000mm</td>
  </tr>
   <tr>
    <td>Fit:</td>
    <td>Casual unisex</td>
  </tr>
</table>
<ul>
<li>Snap closure</li>
<li>Placket with snap fastenings</li>
<li>Drawstring hood with cap brim</li>
<li>Snap adjustable cuffs</li>
<li>Back yoke with concealed vents</li>
<li>Two side pockets with snaps</li>
<li>Eyelets at arm holes</li>
<li>Ultrasonically welded seams</li>
<li>Fishtail hem</li>
</ul>

The logic behind it: All lines that has a colon should be in a table format. The rest should be in a list format.

When the line has a colon, it should be split into two different columns inside the table. For example, <li>Material: 100% polyester with polyurethane coating</li> is turned into

<tr>
    <td>Material:</td>
    <td>100% polyester with polyurethane coating</td>
  </tr>

Can this be automatically done in Google Sheets where each cell contain product details for one product.

Thanks!

CodePudding user response：

Description

Using App script the following example script will take each row and convert any rows that contain : into a table. I've converted to a 2D array in case you want to put it back to a spreadsheet but it could just as easily convert your data to HTML.

Code.gs

function test() {
  try {
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
    let data = sheet.getDataRange().getValues();
    let table = [];
    for( let i=0; i<data.length; i   ) {
      // remove old tags
      if( ( data[i][0] === '<ul>' ) || ( data[i][0] === '</ul>' ) ) continue;
      data[i][0] = data[i][0].replace("<li>","");
      data[i][0] = data[i][0].replace("</li>","");
      // now get lines that are part of a table
      table.push(data[i][0].split(":"));
    }
    // now add the new tags
    let results = [];
    let newTable = false;
    let newUl = false;
    for( let i=0; i<table.length; i   ) {
      if( table[i].length > 1 ) {
        if( newUl ) {
          // close out unordered list
          results.push(["</ul>"]);
          newUl = false;
        }
        if( !newTable ) {
          // create new table
          newTable = true;
          results.push(['<table>']);
        }
        // add a row
        results.push(["<td>" table[i][0] "</td><td>" table[i][1].trim() "</td>"]);
      }
      else {
        if( newTable ) {
          // close out old table
          results.push(["</table>"]);
          newTable = false;
        }
        if( !newUl ) {
          // create new unordered list
          newUl = true;
          results.push(["<ul>"]);
        }
        // add a list item
        results.push(["<li>" table[i][0] "</li>"]);
      }
    }
    if( newTable ) results.push(["</table>"]);
    if( newUl ) results.push(["</ul>"]);
    console.log(results);
  }
  catch(err) {
    console.log(err);
  }
}

Execution log

7:45:13 AM  Notice  Execution started
7:45:15 AM  Info    [ [ '<table>' ],
  [ '<td>Comfort</td><td>waterproof, windproof, lightweight, engineered ventilation</td>' ],
  [ '<td>Material</td><td>100% polyester with polyurethane coating</td>' ],
  [ '<td>Water column pressure</td><td>4000mm</td>' ],
  [ '<td>Fit</td><td>Casual unisex</td>' ],
  [ '</table>' ],
  [ '<ul>' ],
  [ '<li>Snap closure</li>' ],
  [ '<li>Placket with snap fastenings</li>' ],
  [ '<li>Drawstring hood with cap brim</li>' ],
  [ '<li>Snap adjustable cuffs</li>' ],
  [ '<li>Back yoke with concealed vents</li>' ],
  [ '<li>Two side pockets with snaps</li>' ],
  [ '<li>Eyelets at arm holes</li>' ],
  [ '<li>Ultrasonically welded seams</li>' ],
  [ '<li>Fishtail hem</li>' ],
  [ '</ul>' ] ]
7:45:15 AM  Notice  Execution completed

CodePudding user response：

Try

function myFunction() {
  console.log(input.replace(/<\/ul>/g, '</table>')
    .replace(/<ul>/g, '<table>')
    .replace(/<\/li>/g, '</td></tr>')
    .replace(/<li>/g, '<tr><td>')
    .replace(/:/g, '</td><td>'))
}