The Challenge: Create a Spreadsheet from a React Application?

#react #javascript #excel #codenewbie

I recently signed on to build a small feature for an existing project, which involves developing the data reporting feature for a quiz app used by K-12 science teachers and students. As I had never explored data reporting in any of my applications before, I knew I had a lot of research and experimentation to do before jumping into the (somewhat unfamiliar) codebase of the application I would be working on.

Below, I'll share the first few steps of my research journey, and how I learned through experimentation with one of my existing apps. Then in the next two articles, I will share the solution I plan to implement in the science application.

The Specification

The application I will ultimately be working on is a quiz app that allows teachers to assign a quiz/activity to a group of students, the students can then individually complete the activity, and the class-level and individual results are stored in the database and can be displayed in the browser after the quiz is administered.

I was tasked with figuring out a way to export these class-level and individual results to a spreadsheet. I was given a sample spreadsheet of what the results should look like when the report is complete, which included two sheets, with quite a bit of custom styling and conditional formatting to help teachers understand and make instructional decisions based on the data. But beyond this example spreadsheet, I was given the freedom to decide which technologies to use to create the final product. The application was developed using a React/Next.js frontend and Node backend, with GraphQL.

My Research and Early Decisions

From the start, I knew that I could either develop an Excel-based solution or a Google Sheets-based solution. I suspected that the Excel-based solution might be easier to implement since it would likely not require authentication, whereas google-based solutions would require some variety of google sign-in or Oauth. However, as a former teacher, I know that most public schools run off of Google products, so integrating with Google Sheets would likely be the most teacher-friendly solution.

Option #1: excel4node

I briefly explored the excel4node package, and realized that my initial perception of Excel being the easier solution might not be accurate. I quickly learned that the spreadsheet would need to be created on the server side, and then sent to the client side. Once on the client side, there was quite a bit of code required to transform the workbook into a downloadable excel spreadsheet. I integrated this solution into one of my existing React apps, and found that all of the data I needed to access was already accessible in the frontend, so the server side work just felt very redundant. Therefore, I decided to move on to explore a Google Sheets solution.

However, if you're interested in going this route, check out the excel4node documentation and plan do do a lot of searching on StackOverflow to figure out various ways to download the workbook. If you'd like to see my messy experimentation, you can see my server-side code to create the Excel workbook here and my React component here.

import React from 'react';


const Excel = (data) => {

    // frontend code to download spreadsheet using axios for fetch and code to download excel spreadsheet
    function download(data) {
        const axios = require('axios');

        axios.get('/myreport', {responseType: 'blob'})
            .then(response => {
                let headerLine = response.headers['content-disposition'];
                let startFileNameIndex = headerLine.indexOf('"') + 1
                let endFileNameIndex = headerLine.lastIndexOf('"')
                let filename = headerLine.substring(startFileNameIndex, endFileNameIndex)
                const url = window.URL.createObjectURL(new Blob([response.data], 
                {type:'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'}));
                const link = document.createElement('a');

                link.href = url;
                link.setAttribute('download', filename);
                document.body.appendChild(link);
                link.click();
                link.remove();
            }).catch(error => {
                console.log(error)
            })
    }

    return <button onClick={download} className="block">Download Data to Excel</button>

}

export default Excel

After this messy experiment, I was ready to move on to option #2.

Option #2: Google Sheets API

When I started researching the Google Sheets API, I found that there was extensive documentation and some tutorials and sample code...but unfortunately none of the samples really suited my use case. The samples did show me that Google would be able to provide the right functionality, but I was essentially on my own to figure out how to make it work in the context of the application I would be working on. While the best tutorial example I found involved working on the server-side with Node.js, I started exploring what it might look like to generate and open a new Google Sheet from the frontend.

In the next article in the series, I'll outline the solution that worked in my demo application, which I plan to ultimately implement in the science application. You'll be able to follow along to create a Google Sheet with data from one of your own existing React applications. Check it out!