Reading a URL's HTML and returning all rows in a Table

[JasonP]

1. My goal: To convert the table in to a table that can be filtered each in retool
2. Issue: I 'm getting a lot more than just the table header and rows
3. Steps I've taken to troubleshoot:
   I've connected to the URL in URL_Call and returning the data with:
   const parsedString =
   URL_Call.data.message.split('\n').map(function(ln){
   return ln.split('\t');
   });
   return formatDataAsArray(parsedString)

However given the number of rows will change with each call I don't know how to grab each data cell within each row. Using and ending with for each row would work but I have no dea how.

4. Additional info: Self-hosted, Screenshots
   
   

[JasonP]

As you can see the first table row is the headings 2-18, and then each 16 cells between the and would contain the next 16 cells of data for the next row.

[MiguelOrtiz]

Hey @JasonP and welcome to the forum!

So I tested this js query:

const html = {
  data: `
    <table>
      <tr>
        <th>Name</th>
        <th>Age</th>
        <th>City</th>
      </tr>
      <tr>
        <td>Alice</td>
        <td>30</td>
        <td>New York</td>
      </tr>
      <tr>
        <td>Bob</td>
        <td>25</td>
        <td>San Francisco</td>
      </tr>
      <tr>
        <td>Charlie</td>
        <td>35</td>
        <td>Chicago</td>
      </tr>
    </table>
  `
};

// Create a temporary HTML container
const container = document.createElement('div');
container.innerHTML = html.data;

// Select all rows
const rows = Array.from(container.querySelectorAll('tr'));

if (rows.length === 0) return [];

const headers = Array.from(rows[0].querySelectorAll('th, td')).map(cell =>
  cell.textContent.trim()
);

const result = rows.slice(1).map(row => {
  const cells = Array.from(row.querySelectorAll('td'));
  const rowObj = {};
  headers.forEach((header, index) => {
    rowObj[header] = cells[index]?.textContent.trim() || null;
  });
  return rowObj;
});

return result;

And it returns something like:

Which you can use in your table.

You could apply the same thing for your HTML and see it if works.

[JasonP]

Thanks very much for your help. I ended up converting it slightly differently putting it in to a JSON format using:
// Parse the HTML
const parser = new DOMParser();
const doc = parser.parseFromString(URL_Call.data.message, 'text/html');

// Select the first table
const table = doc.querySelector('table');
if (!table) return ; // Return empty array if no table is found

// Get rows
const rows = Array.from(table.querySelectorAll('tr'));

// Get header cells from the first row
const headers = Array.from(rows[0].querySelectorAll('th, td')).map(cell => cell.textContent.trim());

// Convert remaining rows to JSON objects
const data = rows.slice(1).map(row => {
const cells = Array.from(row.querySelectorAll('td'));
return headers.reduce((obj, header, i) => {
obj[header] = cells[i] ? cells[i].textContent.trim() : null;
return obj;
}, {});
});

return data;