Google Sheets IMORTHTML converting to scientific notation-CodePudding

I am importing a table to google sheets with a formula like:

=IMPORTHTML("http://TheServer/FetchBatchData.php,"table",1)

In a web browser I get data that looks like the following:

Batch   Batches Mix       Customer  Status
30DE    3       AGPDHIGH  Joe       Sent
30DF    3       KHM100    Nancy     Sent
30E0    1       DEER100   Bob       Sent
30E1    3       KHM100    Ted       Sent

My problem is that Google Sheets converts the Batch data (a simple Hex value) to scientific notation when it sees the "E" in the batch. The result is that "30E0" becomes 30 and "30E1" becomes 300. I have tried changing cell formats with no luck. Is there something that I am missing in setting up the formatting or the formula that can get Google to not help me?

CodePudding user response：

I made several tests using formulas. The values like "30E0" are always taken and calculated automatically, even with the fix in the comments.

Because of that, the only way I found to fix the issue was to create a custom formula that takes the values of the HTML and returns plain text automatically, which will fix the issue.

The custom formula was created based on the URL in the sample sheet "https://ex1.svfeeds.com." However, you can change it to the correct one.

Here is the sample code:

function parseXml() {

  // URL where the data is located and calling the URL fetch services. 
  let url = "https://ex1.svfeeds.com/";
  let xml = UrlFetchApp.fetch(url).getContentText();
  let document = XmlService.parse(xml);
  let root = document.getRootElement();

  // path to reach the raw data
  let body = root.getChild('body');
  let table = body.getChild('table');
  let tbody = table.getChild('tbody');
  let rows = tbody.getChildren('tr');

  // empty variable, it will be updated with the new array
  // with the data of the table
  let tableData = [];

  // starts the for loop to each of the rows and columns of the HTML table

  for (let i = 0;  i < rows.length; i  ){

    var columns = rows[i].getChildren('td');
    
    if (columns.length == 0){
      columns = rows[i].getChildren('th');
    }

    // empty variable, it will be updated with the new array
    // with the information in the rows 
  
    var rowValues = []

    // new "for" that will call the values in each cell in the table

    for (var j = 0;  j < columns.length; j  ){
      var cell = columns[j].getValue();

      // push the cell values to the rows

      rowValues.push(cell);
    }

    // push the values to the complete table

    tableData.push(rowValues);
  }

  return tableData;

}

After that, you can call the custom function by adding =parseXml() to A1 for example, like this:

And it will look like this:

The path of the values use in this sample code, can be change base on how is the information is place in the original link. For example for the current

Reference:

Let me know if you have further questions.

CodePudding user response：

I found a workaround though it is not something that I really like. Since the google Sheets are the only thing currently reading this table, I prepended a single quote to the output, so the 30E1 data becomes '30E1, and that will get me by until I can get Google Sheets to stop reformulating the data.

I'm still interested in more better if anyone has it.