How do I get import the original value from XLSX using SheetJS?

Question

I am using Google Apps Script with SheetJS library to import table from XSLX file to my Google Sheet.

Here is the code fragment:

function IMPORTXLSX(url, sheetName, range) {
  const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  if (res.getResponseCode() != 200) {
    throw new Error("XLSX file cannot be obtained. Please confirm the link again.");
  }
  const book = XLSX.read(new Uint8Array(res.getContent()), { type: "array" });
  const worksheet = book.Sheets[sheetName];
  if (!range) {
    const csv = XLSX.utils.sheet_to_csv(worksheet);
    const values = Utilities.parseCsv(csv);
    return values;
  }
  var rng = XLSX.utils.decode_range(range);
  const values = [];
  for (let row = rng.s.r; row <= rng.e.r; row++) {
    const temp = [];
    for (let col = rng.s.c; col <= rng.e.c; col++) {
      const v = XLSX.utils.encode_cell({ r: row, c: col });
      temp.push(worksheet[v] ? worksheet[v].v : null);
    }
    values.push(temp);
  }
  return values;
}

And, just in case, the way I call the function:

function refreshFinexData() {
  console.log("Trying to update Finex data.");
  const url = "https://api.finex-etf.ru/v1/fonds/nav.xlsx";
  const sheetName = "Report";

  // Fetch and parse the XLSX data
  const data = IMPORTXLSX(url, sheetName);

  // Check if data is fetched successfully
  if (data) {
    // Calculate the number of rows and columns in the data
    const numRows = data.length;
    const numCols = data[0].length;

    // Clear the previous data in the destination range
    const outputRange = realPortfolioSpreadsheet.getRangeByName("techListFinexOutputRange");
    outputRange.clearContent();

    // Set the values in the destination range
    // Note: You must adjust the output range's size to match the number of rows in the fetched data
    const destinationRange = techListSheet.getRange(outputRange.getRow(), outputRange.getColumn(), numRows, numCols);
    destinationRange.setValues(data);
    console.log("The Finex data was updated successfully.");
  } else {
    throw new Error("Data could not be fetched. Please check the URL and try again.");
  }
}

The problem is - when is the original file the value is equal to "0,797698", when I open the original xlsx file it is being displayed as "0,80" (I can still see the original value, but only if I click on the cell directly). And "0.80" is being copied to my sheet. But I need the original value.

It seems that problem arised only few days ago - it is possible that something in original file format has changed leading to this issue, but I am not sure.

The exact value is still in the file, but now it seems that I am unable to retrieve it.

Can you help me retrieve the original data?

Thank you!

Tried changing

temp.push(worksheet[v] ? worksheet[v].v : null);

to

temp.push(cell ? cell.w : null);

No results.

You probably just need to change the format of the cell in Google sheets. Also, where is `XLSX` defined? If you're using a library, provide it's Github link. — TheMaster, Jul 07 '23 at 08:57
Hi! There is no problem with Google Sheets cell format - by the moment value reaches it, it's already rounded. However, I bet if I change the format of the original cell in the source file (so it would not round it up for display) - that would solve the problem. Cannot do that, though. Regarding library - I am using SheetJS, here is the documentation https://docs.sheetjs.com/docs/ and here is git https://git.sheetjs.com/sheetjs/sheetjs — I_am_not_a_programmer, Jul 07 '23 at 09:11

score 1 · Accepted Answer · answered Jul 07 '23 at 12:30

When I tested your showing script, I noticed that when the values are retrieved as CSV data, it seems that the values are rounded. But, when I checked the options, I couldn't find the option for resolving it. So, as one direction, how about the following modification?

Modified script:

In this modification, the function IMPORTXLSX is modified.

function IMPORTXLSX(url, sheetName, range) {
  const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  if (res.getResponseCode() != 200) {
    throw new Error("XLSX file cannot be obtained. Please confirm the link again.");
  }
  const book = XLSX.read(new Uint8Array(res.getContent()), { type: "array" });
  const worksheet = book.Sheets[sheetName];
  if (!range) {
    const csv = XLSX.utils.sheet_to_csv(worksheet);
    const ar = Utilities.parseCsv(csv);
    range = XLSX.utils.encode_range({ s: { c: 0, r: 0 }, e: { c: ar[0].length - 1, r: ar.length - 1 } });
  }
  var rng = XLSX.utils.decode_range(range);
  const values = [];
  for (let row = rng.s.r; row <= rng.e.r; row++) {
    const temp = [];
    for (let col = rng.s.c; col <= rng.e.c; col++) {
      const v = XLSX.utils.encode_cell({ r: row, c: col });
      temp.push(worksheet[v] ? worksheet[v].v : null);
    }
    values.push(temp);
  }
  return values;
}

In the case of this modified script, the cell values are retrieved without rounding.
In this modification, it supposes that sheetjs library has already been able to be used. Please be careful about this.

Thank you! It works! – I_am_not_a_programmer Jul 08 '23 at 09:56 — I_am_not_a_programmer, Jul 08 '23 at 09:56

How do I get import the original value from XLSX using SheetJS?

1 Answers1

Modified script:

Linked