Updated on July 8, 2023
In the current stage, unfortunately, the built-in methods of Google Apps Script cannot directly retrieve the values from the XLSX data. From this situation, I have created DocsServiceApp. When this Google Apps Script library is used, the values are directly retrieved from XLSX data by parsing XML data of XLSX data.
Here, as another approach, I would like to introduce a sample script for directly retrieving the values from XLSX data using SheetJS with Google Apps Script. In the current stage, Google Apps Script can run with V8 runtime. By this, it seems that SheetJS can be used.
Sample script:
function myFunction_SheetJS() {
const fileId = "###"; // Please set the file ID of your XLSX file.
const sheetName = "Sheet1"; // Please set the sheet name you want to retrieve the values.
// Loading SheetJS.
const cdnjs = "https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js";
eval(UrlFetchApp.fetch(cdnjs).getContentText());
// Retrieve values from XLSX data.
const data = new Uint8Array(DriveApp.getFileById(fileId).getBlob().getBytes());
const book = XLSX.read(data, { type: "array" });
const worksheet = book.Sheets[sheetName];
const csv = XLSX.utils.sheet_to_csv(worksheet);
const ar = Utilities.parseCsv(csv);
const maxRow = ar.length;
const maxCol = ar[0].length
const values = [...Array(maxRow)].map((_, row) =>
[...Array(maxCol)].map((_, col) => {
const v = XLSX.utils.encode_cell({ r: row, c: col });
return worksheet[v] ? worksheet[v].v : null;
})
);
console.log(values);
}
-
When this script is run, the values are directly retrieved from “Sheet1” of the XLSX file of
fileId. -
This is a simple sample script. I have not tested all methods of SheetJS. Please be careful about this.
IMPORTANT
In this sample, the script of https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js is loaded with eval. If you cannot use this, you can also use this script by copying and pasting the script of https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js to the script editor. By this, you can remove eval(UrlFetchApp.fetch(cdnjs).getContentText());. When the script of xlsx.full.min.js is put in the script editor, the process cost can be reduced rather than that using eval(UrlFetchApp.fetch(cdnjs).getContentText());.
References
- I answered this script to this thread on Stackoverflow.
- In this answer, I introduced a sample script for a custom function to directly retrieve the values from XLSX data from the direct link of the XLSX file.
- Also, I answered this updated method to this thread on Stackoverflow.