Best way for local HTML file to communicate with (read/write) local .xlsm Excel file

Question

I'm a teacher. My grade book is a .xlsm file, located in my Google Drive folder. I'm building a web app, also in my Drive folder, that shows the students names arranged according to the seating chart. I want to run the app on my Android phone, and click a student's name to add a participation mark to the grade book. The app should also display the students' existing participation scores (from the grade book). What is the best way to go about accomplishing something like this?

*I initially tried to do this using another .xlsm file but, when I realized that macros don't run on the Android version of Excel, I switched to an HTML-based app. Was that the right decision?

Here is a sample .xlsm file. In this case, the app would check cell A2 to make sure it's the correct student and then both read and write to cell B2.

Answer 1

If Excel isn’t doing it for you on Android then I’d strongly suggest looking in to Google Sheets. Creating an app for this yourself would be a fun project, if you enjoy that sort of thing, but Google Sheets sounds like it might do the job and you can be up and running in a few minutes.

---

Having got that out of the way I think it would be best to clear up how web apps work, it sounds like you have some confusion about how they work (or I am from your question!).

A web app is typically made up of two parts, a client and a server.

The client requests resources from the server and the server responds. For example, the client requests the resource associated with example.com, the server is listening for this request and fulfils it by returning a string of text with some meta saying its html. The client (lets say its a browser) understands this is html and begins to parse and render it. If it hits <link> or <script> (or some other stuff) it knows to go and request more resources from a server.

The client is totally detached from the server, it has limited access to the file system and must perform tasks by asking the server to do them. It can only ask, not enforce.

Often there is a 3rd piece which is storage of some sort, this could be a file system somewhere or a database. The client is also detached from this and the server (or another service) owns and manages the storage. In your case Google Drive can act as your storage.

A web client has no direct access to your Google Drive, although if its contents is public there is likely a scheme for you to get a resource (I don't know how Google Drive works but this is fairly likely). I'm not sure it will let you write to the Drive though, not without additional permissions (granted through authentication) being acquired. Many popular frameworks and libraries will allow you to interface with Google Drive and handle the auth handshake etc etc, they are often called a driver or connector.

Very basically, you'd likely need a couple of parts:

Storage -> Connector -> Service -> Client

You can get away with putting connector and service together, and you might be able to get away with connecting direct from the client, which would save you the trouble of creating, hosting and running a service.

The last piece of the puzzle is the conversion you must do from the .xls data into something JS on the client can work with (again, there are often multiple ways of doing things, you might decide to render your page on the server).

There are many tools out there that can convert from .xls to json, which JS can then parse and use (on client or server). I have used one a couple of times but I can't remember which one right now, a quick npm search throws up a number of hits.