Question 421389 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Reading an in-memory 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 representation of binary data. How can I read this binary XLSX file contents, and output it as JSON?

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 data directly within App Script, in-memory. The following code uses the xlsx.full.min.js library.

When running:

        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);
      }
    }

I receive the following error: "Error during parsing: Unsupported ZIP Compression method NaN"

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Two solutions

    1.

    const data = XLSX.read(byteArray, { type: 'array' });

    changed to

    const data = XLSX.read(byteArray, { type: 'buffer' });

    The answer from HernanATN also solved my problem and seemed to be more trustworthy to go with a different version of xlsx that is more compatible with GAS


  2. 0

    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' } ]
    Login or Signup to reply.
  3. 0

    There are Two solutions

    The first Solution is

    const data = XLSX.read(byteArray, { type: 'array' });

    and second solution is change to

    const data = XLSX.read(byteArray, { type: 'buffer' });

    The reply from HernanATN too illuminated my issue and appeared to be more dependable to go with a diverse form of xlsx that’s more consistent with GAS.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search