javascript - Reading an in-memory fetched xlsx file as JSON with Google App Script - Stack Overflow

admin管理员组

文章数量:1125966

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('.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"

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"

• javascript
• google-apps-script
• xlsx

Share Improve this question Follow edited 23 hours ago HernanATN 45011 silver badge1414 bronze badges asked Jan 9 at 2:56 ScriphyScriphy 10777 bronze badges 2

• Are you open to converting the excel file manually to a Google sheet file then fetch that Google sheet file instead? – 4thAnd1 Commented Jan 9 at 4:52
• The answer below does work for me and I selected it as a correct solution. I also got my exact code above to work by changing the line const data = XLSX.read(byteArray, { type: 'array' }); to be const data = XLSX.read(byteArray, { type: 'buffer' }); – Scriphy Commented 2 days ago

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 2

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

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' } ]

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.

本文标签： javascriptReading an inmemory fetched xlsx file as JSON with Google App ScriptStack Overflow