Reading in a fetched xlsx file as JSON with Google App Script

I am using Google App Scripts built in function, UrlFetchApp.fetch, in order to request data from a URL. When you navigate to this URL in the browser, it just initiates a file download of an XLSX file. If I console.log the response, it is just a string of garbled data. How can I parse a string representing this XLSX file contents into a JSON array?

I am not able to save this file to drive and read it back in, I'm looking for a way that parses the response string directly within App Script. The following code uses the xlsx.full.min.js library. I receive an error "Error during parsing: Unsupported ZIP Compression method NaN"

    function parseExcelToJson() {
  try {
    // Step 1: Fetch the Excel file from the URL
    const response = UrlFetchApp.fetch('https://www.nasdaqtrader.com/Content/ProductsServices/TRADING/TRF-Chicago-Test-Securities.xlsx');
    
    // Step 2: Convert the response to a Blob (in memory)
    const blob = response.getBlob();
    
    // Step 3: Convert the Blob to a byte array (needed for xlsx.js)
    const byteArray = blob.getBytes();
    
    // Step 4: Use xlsx.js to parse the byte array
    const data = XLSX.read(byteArray, { type: 'array' });
    
    // Step 5: Access the first sheet
    const sheet = data.Sheets[data.SheetNames[0]];
    
    // Step 6: Convert the sheet to JSON format
    const json = XLSX.utils.sheet_to_json(sheet);
    
    // Step 7: Return the JSON result
    return json;
    
  } catch (e) {
    // Log any errors during parsing
    Logger.log("Error during parsing: " + e.message);
  }
}

Solution

So the thing is, while Google Apps Script is a JS runtime, it does not implement all of JavaScript features:

It actually implements a bespoke subset of the ECMAScript standard, and not necesarilly compliant with latest features from V8 engine (present in modern browsers and runtimes as Node,Deno, Bun). So you can't assume that modern libraries will work: luckily for old-enough libraries, a little of trail and error and you'll find a suitable version to use! I've found that XLSX 0.17.0 works inside GAS, so, you should fetch and evaluate that version:

eval(UrlFetchApp.fetch('https://cdn.sheetjs.com/xlsx-0.17.0/package/dist/xlsx.full.min.js').getContentText());

Then, the following script works as intended

function parseExcelToJson() {
eval(UrlFetchApp.fetch('https://cdn.sheetjs.com/xlsx-0.17.0/package/dist/xlsx.full.min.js').getContentText());
  try {
    // Step 1: Fetch the Excel file from the URL
    const response = UrlFetchApp.fetch('https://www.nasdaqtrader.com/Content/ProductsServices/TRADING/TRF-Chicago-Test-Securities.xlsx');
    
    // Step 2: Convert the response to a Blob (in memory)
    const blob = response.getBlob();
    
    // Step 3: Convert the Blob to a byte array (needed for xlsx.js)
    const byteArray = blob.getBytes();
    
    // Step 4: Use xlsx.js to parse the byte array
    const data = XLSX.read(byteArray, { type: 'array' });
    
    // Step 5: Access the first sheet
    const sheet = data.Sheets[data.SheetNames[0]];
    
    // Step 6: Convert the sheet to JSON format
    const json = XLSX.utils.sheet_to_json(sheet);
    
    // Step 7: Return the JSON result
    return json;
    
  } catch (e) {
    // Log any errors during parsing
    Logger.log(e.stack);
    Logger.log("Error during parsing: " + e.message);
  }
}
console.log(parseExcelToJson())

Output:

3:17:11 Información [ { Item: 1,
    Symbol: 'CBO',
    'Symbol Name': 'CBO (Listing Market - NYSE - Networks A/E) Common Stock',
    Product: 'CTS' },
  { Item: 2,
    Symbol: 'CBX',
    'Symbol Name': 'CBX (Listing Market NYSE Networks AE) Common Stock',
    Product: 'CTS' },
  { Item: 3,
    Symbol: 'IBO',
    'Symbol Name': 'IBO (Listing Market - NYSE Amex Network B F) Common Stock',
    Product: 'CTS' },
  { Item: 4,
    Symbol: 'IGZ',
    'Symbol Name': 'IGZ (Listing Market NYSE Arca Network B F) Common Stock',
    Product: 'CTS' },
  { Item: 5,
    Symbol: 'NTEST',
    'Symbol Name': 'NYSE Tick Pilot Test Sym-Control',
    Product: 'CTS' },
  { Item: 6,
    Symbol: 'ZBZX',
    'Symbol Name': 'BATS BZX Exchange test issue',
    Product: 'CTS' },
  { Item: 7,
    Symbol: 'ZEXIT',
    'Symbol Name': 'IEX Test Company Test Symbol Two for IEX',
    Product: 'CTS' },
  { Item: 8,
    Symbol: 'ZIEXT',
    'Symbol Name': 'IEX Test Company Test Symbol One for IEX',
    Product: 'CTS' },
  { Item: 9,
    Symbol: 'ZTEST',
    'Symbol Name': 'BATS BZX Exchange Common Stock (test issue)',
    Product: 'CTS' },
  { Item: 10,
    Symbol: 'ZVV',
    'Symbol Name': 'NYSE ARCA test stock',
    Product: 'CTS' },
  { Item: 11,
    Symbol: 'ZXIET',
    'Symbol Name': 'IEX Test Company Test Symbol Three for IEX',
    Product: 'CTS' },
  { Item: 12,
    Symbol: 'ZAZZT',
    'Symbol Name': 'Tick Pilot Test Stock Class A Common Stock',
    Product: 'UTP' },
  { Item: 13,
    Symbol: 'ZBZZT',
    'Symbol Name': 'Test Pilot Test Stock Class B Common Stock',
    Product: 'UTP' },
  { Item: 14,
    Symbol: 'ZCZZT',
    'Symbol Name': 'Tick Pilot Test Stock Class C ',
    Product: 'UTP' },
  { Item: 15,
    Symbol: 'ZVZZC',
    'Symbol Name': 'NASDAQ TEST STOCK Nextshares Test Security',
    Product: 'UTP' },
  { Item: 16,
    Symbol: 'ZVZZT',
    'Symbol Name': 'NASDAQ TEST STOCK',
    Product: 'UTP' },
  { Item: 17,
    Symbol: 'ZWZZT',
    'Symbol Name': 'NASDAQ TEST STOCK',
    Product: 'UTP' },
  { Item: 18,
    Symbol: 'ZXZZT',
    'Symbol Name': 'NASDAQ TEST STOCK',
    Product: 'UTP' } ]