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.
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?
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;
}