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.

Schenck

I'm confused as to what you mean when you say your building an app in your Drive folder. What you want is definitely possible to create using a Javascript web app. If you provide a small demo .xlsm file I can point you in the right direction. – Beeman

I mean that the html file is located in my Google Drive folder. In other words, it's accessible both through the internet or locally on my machine. I wasn't sure if that information was important or not. – Schenck

See updated question for demo .xlsm file. – Schenck

Anyone? I think I have a quasi-solution of using a "3rd party" text file and having both the app and grade book read/write to that file. But this means extra steps each time. – Schenck

You can try google spreadsheet api, as your data is already in google drive. it's the best way as far as I know. This way you can read / write to excel file directly. You have to import into google spreadsheet, and export back to xlsm though. developers.google.com/sheets/api/quickstart/android – Lazaro

As a teacher&developer, I recommend you install Moodle on your local machine. That way you won't have to build your own web-app, and you'll have much more features. My answer is based on the fact that you've already installed PHP server on your local machine and that you can access to your local machine by using your Android phone. You can try Moodle-Bitnami, easy installation – Anuradhapura

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.

Wadi

Well, you're making it sound like completing this task is going to involve domain hosting, but actually I think I'm on the right track now, without that. You say that "the client is totally detached from the server", but now I have installed Apache and PHP on my PC - making it both the client and the server. Now I can use PHP to read and write to a text file and Excel VBA to read from the same file. – Schenck

Yeah, thats correct, Apache and PHP serve stuff to your client, which is the browser. You can almost forget that it is running on the same computer, the client is detached and lives in your browser (basically). If Google Drive will serve you the xls file you can probably request it straight from the client, but saving back changes will be difficult (I imagine) or impossible. – Wadi

You also mention running from your Android phone, which would be another client. – Wadi

And also the server. Running PHP on Android as well. – Schenck

Answer 2

Your best option is not to use Google Drive at all. If you are interested in doing something like you described, I would strongly recommend using either PHP and MySQL (a popular combination) or Google Cloud Platform's App Engine (they provide a lot of help, even allowing you to easily build an app that you can sign in to from your Android device with your Google account). If you're more in the mood for Do-It-Yourself, I would probably recommend Spring Boot (These tutorials look pretty good)

When it comes to integrating with Google Drive, it is technically possible to do, but so difficult that you would be much better off writing your own system from scratch and perhaps exporting to an Excel file. (See this page for a tutorial on exporting to Excel from Java)

You have several concerns with the final solution for your problem:

1. Security (You don't want students breaking into it, like I probably would have done)
2. Accessibility (You want to get at the information on your phone and your computer)

If I was in your position, I would probably write a Spring Boot application (which can house it's own database, website and API for a computer/phone to communicate with) and an Android application that talks to it.

Also, it would be helpful to know what programming languages and/or libraries/frameworks you have used in the past in order to make better recommendations for your situation. What have you used in the past?

If you need help or have questions, just message or comment.

Thenceforward

Well, this was supposed to be a tiny, quick project; not some endeavor to learn a new platform, build a database, or anything like that. And I'm not using Google Drive for anything, I was just pointing out that the files could be accessed either locally or through the web; I wasn't sure if that info was relevant or not. Also, I am the only user, so there are no security concerns involving my students or anything like that. – Schenck

How about I tell you the solution I'm using, and you can tell me if I'm on the right track or if there's an easier method? I installed PHP on my phone and put the files in the htdocs directory. My web app reads and writes to a text file in my Drive folder using PHP. Then my Excel grade book reads in the scores from that same file. – Schenck

It may work, but it is overly complicated and inconsistent at best. Tell me this: How is your excel file structured? – Thenceforward

The reason I suggested a real database is that Excel is what I'll call a "human" database, but doesn't work well for computer programs to read and write to. – Thenceforward

What do you mean how is my Excel file structured? There's a bunch of cells that have data in them, exactly what you would expect a grade book to be. If my method is overly complicated, then what is the simpler method? – Schenck

The complicated part which introduces the most potential for error is that you're asking a computer to use a database designed for humans (Excel) and not computers. In order to make such things work, you have to go through a lot of extra translating and modifying, etc. which makes for more steps that have to be coded and hence more errors. – Thenceforward

What I mean by the structure is: how many columns are there, how many sheets are there, what columns are in each sheet and what data and type of data goes into each column? – Thenceforward

Well, all Excel needs to do is read in the data from the text file. That part is easy; I've got that covered. And it doesn't really matter how many columns, sheets, etc. – Schenck

At this point, the main issues are getting php and JS to play nice together when reading in, manipulating, and then writing the text file. The other issue is getting the text file to a place where both my phone and computer can access it. – Schenck

Well, computers don't do very well with text files, either. The number of colums and sheets, etc. is information that would be necessary to create a schema for a proper database. – Thenceforward

As far as getting PHP and JavaScript to play nice, they aren't usually a problem together. With regard to getting the text file to a place where your phone and computer can get at it, what you're trying to do isn't possible the way you want to do it. You need to do it a different way. – Thenceforward

Yes, you appear to be correct. My phone doesn't have permission to write or save the text file to Drive or anywhere useful. I think I'm going to have to just manually transfer the file every time I want to update the grade book. – Schenck

I would strongly recommend against that approach as it won't necessarily net you much real benefit. What you're really asking for (in order to get the result you've described) is a proper app or at least web app with a server (ie: not Google Drive). For your text file, at least look into using Json or XML to store the information unless it's a CSV (Google any of these terms that confuse you) – Thenceforward

Also, if you're more comfortable with HTML, JavaScript, CSS, etc. you may want to check out Apache Cordova. It's a framework for building apps for your phone as if it's a website. – Thenceforward

I do happen to have an AWS domain. So I guess I'll upload the app to that and see if I can figure out the permissions to allow PHP to write a file which can then be accesses by Excel. – Schenck

I'd really recommend against Excel. Once you make a website that you can use to edit grades, you've pretty much got everything you need as long as you write a page to display the grades (which you could print). Excel isn't very good for anything that ever goes outside of the local computer. – Thenceforward