Help pulling data from Excel using JavaScript - can this be done?

Hi, I’m a pretty much brand-new developer looking to expand my knowledge and learn new skills. I’ve just complete the Introduction to JavaScript course and now I’m starting the Building Interactive JavaScript Websites course.

I’m also running a sweepstake based on the Formula One season which involves players choosing a driver for each race, and I could really use a way for them to see who they’ve used and make their next picks without my help - there are 54 of us this year and it’s taking more time than I expected!

I have all the data in an Excel spreadsheet, so what I want to do is build a webpage that does this:

  1. Player chooses their name from a list/drop-down
  2. Code pulls in the relevant data from Excel - which drivers they’ve already used - as a JavaScript array
  3. Code compares an array of the full driver list to the array of the player’s chosen drivers, and returns a third array of the drivers still available to choose.
  4. Player selects their next driver (this might need to be by emailing me their choice, but that’s OK).

I’ve Googled a lot but haven’t found a way to do this - specifically to pull certain cells from an Excel file. Is it possible?

Any help appreciated!

Thanks

You could save your excel file in csv format and pull the data from the csv with Javascript. That can be done with the FileReader object.

I haven’t understood where you plan to store the excel file. You’d either need to get that from a server or let the user chose the file from their machine with an input field of type=‘file’.

You get a string from the file reader object. That can be split at commas and new line (.split('\n')). That way you could get certain fields from the table.

1 Like

Thank you for the reply! I’m not sure if this method will work for me or not. The FileReader object seems to need the user to upload a file, that wouldn’t work for me. The Excel file is stored by me, and I want to pull in certain data from it. As for where I plan to store it, I’m hoping that I can keep it in the same directory as the HTML, CSS & JS (which will probably be on a OneDrive or similar).

It might help to give a concrete example: there is a column in the spreadsheet which contains the names of our players. I want to pull that data in so that I can use those cells’ values to populate the options in a drop-down HTML selector. When the user selects their name, I then want to pull in the data from the cells in the same row into an HTML list. Does that make my intentions clearer?

If I can use FileReader to load in the Excel data on page load and process it ready for the user to make their selections, then it might just work - but I have a feeling I’m biting off a lot more than I can currently chew!

My experience with the fileReader is limited to a use with the upload input field. I suppose it will work with a csv file stored remotely, either, as long as it is located on the same server as the html and js files. For security reasons you cannot use it with locally stored files, so you could develop with an upload button and then try to adjust the code so it gets the file from the server.

This is my working code snippet:

function getFileData(input) {
  let file = input.files && input.files[0];
  if(!file) return '';
  let reader = new FileReader();
  reader.readAsText(file);
  return new Promise((resolve, reject) => {
    // cannot post this snippet b/c it causes the 403 error...
  }
}

I call the function with an event listener for the change event:

uploadFileButton.addEventListener('change', async () => {
        let result = await getFileData(uploadFileButton);
        // here is the function call that works with the data from the csv file
});

The snippet I cannot post:

You can probably replace the event listener with a different code.

For such a task, you might be better off, or at least it’d be easier, to use a library whose purpose is managing spreadsheets. A quick Google search revealed two that people use a lot: ExcelJS and SheetJS.