Java Application Ran on a Google Drive Database

Have you ever wanted to create an application that runs on a dynamic database that is easy to work with, allows non-tech savvy users to input data into, and doesn't run down company resources? Well, then I have a solution for you. Today, we will be building an application that runs on Google Drive (well, specifically Google Sheets) and can be used for a variety of different purposes. Although this tutorial will focus on building an Event-based app to show a list of events happening around a college campus, this project can easily be re-written to function in a multitude of ways, be it a calculator app to an app that monitors stocks for products. I have attached a copy of my application if you would like to see what we will be making. Unzip the zip file and run the JAR inside of it. And now, without further ado, let's begin!

To get started with this project, you'll need the following resources:

• Netbeans
  • I recommend the Java EE download as it provides server support, but if you don't want some unnecessary files or the extra disk space, Java SE will work as well. Netbeans will serve as out IDE for coding and compiling our application.
• Jsoup
  • I have included this in the Instructables for you to download. It is an HTML parser that will allow us to pull information from the published spreadsheet.
• Java SDK (V8)
  
  • Download whatever file fits your system. If you already have the Java SDK in a previous version, I recommend updating. Some of my functions use new lambda expressions native to v8, and the code may not work without them depending on what you do.
• Visual Studio (Optional)
  • Completely optional. Although NetBeans works amazing for compiling and packaging our app, I am not a huge fan of the development studio. I prefer to code in VS, as it has a nicer interface. If you don't like this, there are plenty of other IDEs online, so find whatever you like best.
• Starter Code
  • I have included the starter code in the resources of this, and also published it to GitHub. In this, I have the main file (Event) that provides the structure for the file that actually runs the app, as well as EventTester, which uses JavaFX to create the GUI for the app. If you wish to get the full experience, I don't recommend copying and pasting. Take your time and read through this.

Others:

• Basic knowledge of Java. It will be helpful to be knowledgeable in Java, like writing functions, creating objects, etc.

To begin the project, we first need to go into Google Drive and create the sheet we will be using to run our application. Head over to drive.google.com and click the "New" icon in the top left corner, and select "Sheets" underneath this.

Once your sheet has loaded, go ahead and rename this to something easily recognizable. After doing so, go ahead and fill in the top row with your data names, such as the things you will be putting in each column. Looking at my example here, I have labeled the top row with things such as "Event Name," "Date," etc.

After doing so, begin to populate your spreadsheet with the data you want to fill. Remember to format all of your data similarly, so that the code is able to work with it without throwing errors. For example, if you plan to use dates in your code, be careful to format each date the same, otherwise the code will not be able to parse it.

After putting in your data, publish the spreadsheet by going to "File" -> "Publish to the web." From here, you'll want to select the entire document and make sure it is published as an HTML file, that way our app is able to draw the data correctly. After publishing your spreadsheet, make sure to make note of the link it provides. This will be needed later on in the app.

Now that we have our spreadsheet, it's time to set up NetBeans so we can get started coding. After you download and install NetBeans and your Java SDK, go ahead and create a new project. When choosing a type, chose the "Java" category, and the "Java Application" project. Chose whatever you would like your project to be called (I named mine simply "Event"). Select the checkbox beside "use dedicated folder for storing libraries," as well as the one besides "Create main class." After this, NetBeans should create a project and project directory for us to begin working in, much like the one pictured.

Before we will begin coding, we will also need to make sure NetBeans has the JSoup library it will need to parse our spreadsheet. In NetBeans, right click the "Libraries" icon under your project's directory. Under the pop-up menu, select the button to add a .jar file. Now, navigate to wherever you placed your jsoup download (most likely your Downloads folder, unless you specified elsewhere). Choose this file and add it the library. If you expand your Libraries folder in NetBeans, you should now see the jsoup.jar in this area. Having done so, we can now begin coding our app.

So, the first step in coding our app is creating your main class. Your main class will be where we create our objects, have the methods that interact with JSoup, and more. Assuming everyone reading this has experience coding, go ahead and use the following imports:

import java.util.Collections;

import java.util.List;

import java.util.ArrayList;

import java.util.Date;

import java.util.stream.Stream;

import java.util.stream.Collectors;

import java.text.SimpleDateFormat;

import java.text.ParseException;

import org.jsoup.Jsoup;

import org.jsoup.nodes.Document;

import org.jsoup.nodes.Element;

import org.jsoup.select.Elements;

import javafx.beans.property.SimpleStringProperty;

This may seem like a lot, and depending on your project, all may not be necessary. As we continue to code, NetBeans will let you know if you have any unused imports, so we can always delete them later. However, this is what we need for now.

Having got our import statements, let's go ahead and declare our class. If you plan on using Dates or any non-basic object when developing your class-specific object, I recommend adding an "implements Comparable" to your class declaration. This will allow you to compare Objects, which will allow you to sort a List of ClassObjects later. After doing this, go ahead and declare all the instance variables you will need. For every public string you create, you will also need to create SimpleStringProperty for it. These are JavaFX objects that will allow us to work with our main class objects later on.

Now, go ahead and declare you access functions. As far as your basic variables go, you can name your functions whatever you choose. However, for you SSP access functions, you must use the format getFunctionNameHere(). This is because later on we will use JavaFX to connect to these functions, and the functions we will use require us to begin our SSP functions with get. You can see an example above.

After defining all of your access variables, go ahead and define any other functions you might need. This is very user specific, as the functions you need with differ from project to project. If you need some inspiration, check my Javadoc or the actual code and see some of the functions I made. For instance, I created a sort function which sorts a List<Event> by Date, as well as functions that returned only Events with group statuses of public, and more. Although it is ok to make these static so you can do some testing, I recommend not having any static methods once you finish debugging, to avoid errors when we get to the next step of the project.

Now comes possibly the most important part of the code, where we are going to define our create() method, which is what will actually access our webpage and get us the data. It it important to note that you will need to add a throws Exception line to your method declaration, so we don't have to write try blocks into our code. To begin, go ahead and declare an empty List of your object. In my case, it looked like

Event events = new ArrayList()).

Now, go and find that URL you copied down earlier to the published spreadsheet. Declare this link as a string in Java, and call it whatever you want. Now, go ahead and declare a new Jsoup document. You can do this by creating a new document object, such as

Document doc = new Document();

Now, go ahead and set your document to connect to our URL and get the data. To do so, try:

Document doc = Jsoup.connect(url).get();

Now, we need to get the body of our document, which is where the actual data is being stored.

String body = doc.body().text();

Now, we need to begin pulling data out of the body. Since our data is in a table (since it was a spreadsheet), we need to pull the table out of the body. Let's try

Element table = doc.select("table").get(0);

This selects the First table. In this case, there is only one. Now, type

Elements rows = table.select("tr");

This gets us all the rows in said table.

So, now, all of our data is inside this rows variable. That's good and all, but the whole point in writing this function inside of this class is so we can create objects out of it. So, before we can return this, we need to create a List<Object> from our rows. To do this, we can use a for loop. I must note that this took me a little trial and errors to get right. I realized that when pulling from the rows, some of our data is not useful for this situation, as it provides things like the individual sheet's name, the first row with our data ideas on it, etc. In the end, I set the initial entering number for the for loop to 2, so it bypasses these items and can create our items. In the end, I developed a look with the code

for (int i = 2; i < rows.size(); i++) {

Element row = rows.get(i);

Elements cols = row.select("td");

Now, to create an object, do something like

Object name = new Object(cols.get(0).text());

Essentially, the cols.get(0) will get the data from row(i) column(0), and turn it into a String which can then be passed into the object's constructor.

After setting up your construction, add it to the List we created earlier with list.add(), such as

events.add(name);

Now, close your for loop, and call any functions you may need to now. For instance, I called my sort function to get the Events in order of date. After doing so, return your List<Object> and then you are finished with this section!

Create a new file and name it whatever you choose. You will need the following imports:

import java.util.List;

import java.util.ArrayList;

import java.util.Date;

import javafx.geometry.Pos;

import javafx.scene.layout.HBox;

import javafx.application.Application;

import javafx.collections.transformation.FilteredList;

import javafx.scene.text.Font; import javafx.scene.control.*;

import javafx.collections.FXCollections;

import javafx.collections.ObservableList;

import javafx.geometry.Insets;

import javafx.scene.Group;

import javafx.scene.Scene;

import javafx.scene.control.Label;

import javafx.scene.control.cell.PropertyValueFactory;

import javafx.scene.layout.VBox;

import javafx.stage.Stage;

I know this may seem like a lot, but believe me, they are necessary for us to run create our application. Go ahead and declare your class, and make sure it extends Application, as this is a necessary component of the project. In the beginning, declare a new instance variable that is a TableView of your object, such as

private TableView table = new TableView();

Also, declare a main method, which we will use to launch the app. Essentially, it should look like the picture at the top.

Now, we need to create our start method. Make sure it throws Exception, since we will be calling the create() method from our former class. Make a new event with empty parameters, simply so we can call the create method with it. Define a new List<Object>, and set it equal to the result of create(). Now, create a new ObservableList, which will be used to populate our table with our data. Define it like:

ObservableList data = FXCollections.observableArrayList();

Now, create a new scene with:

Scene scene = new Scene(new Group());

Set the title, width, height, and anything else you need to for whatever works for you. You can see my values in the image at the top. Now, we can begin to set up our table. For all info you want to display, create a TableColumn, like:

TableColumn eventCol = new TableColumn("Event Name");
eventCol.setMinWidth(100); eventCol.setCellValueFactory( new PropertyValueFactory("sName"));

The "sName" parameter should be filled with whatever the name of your SSP access functions were, so it can get the values you need for the given objects. Make as many Columns as you need, then add them to a table with

FilteredList flEvent = new FilteredList(data, p -> true);

table.setItems(flEvent);

table.getColumns().addAll(eventCol, statCol, groupCol, datingCol, descCol, locationCol);

If you would like to add a search bar like I did, check the code for information on how to create a choiceBox and a textField, which will allow your user to filter the table by specific values. If you have chosen to do so, you will also have to make an hBox to contain these, with

HBox hBox = new HBox(choiceBox, textField);

hBox.setAlignment(Pos.CENTER);

You will also need to add hBox into the .addAll() method below.

Otherwise, simply create a new vBox to hold our data in by doing

final VBox vbox = new VBox();

vbox.getChildren().addAll(label, table);

((Group) scene.getRoot()).getChildren().addAll(vbox);

stage.setScene(scene); stage.show();

Now, compile your code and run it, and see if it works. Use NetBeans to find any errors, which will appear as red bars on the right side of the screen. Keep running this until you have no more errors, and the project runs.

After completing your coding, I will recommend creating a Javadoc of your code so that people can see what your code does. To do so, under the "Run" button at the top of the screen, simply hit "Generate Javadoc." You can find a copy of my Javadoc by looking inside the zip file on the first page and choosing the index.html file.

Once you have sufficiently debugged your and made it run successfully, you can finally compile this into a JAR file, which can then be published so others can run this software without the need for NetBeans or Jsoup.

Before compiling your app, make sure that everything is done. If you wish to add documentation and create a JavaDoc, go ahead an do so. If you have any System.out commands which print to your console, remove them. Essentially, make sure your app has no unwanted commands or functions, and that it has everything it needs to be packaged.

After doing so, right click on the project name in NetBeans. It should pop up a menu. Hit properties (at the very bottom of the menu), then hit "Packaging" on the left side of the new pop up menu. Now, make sure all the checkboxes are marked. Your screen should look like the one above.

After doing this, once again right click your project in NetBeans. At this point, hit the "Clean and Build" button, and NetBeans will begin taking your libraries and files and compiling them into a working JAR file. If all goes well, you should see a message in the console after a few moments telling you your JAR has finished compiling, and the file can now be run. Run this app and make sure everything works. If not, debug and restart the process until errors have been worked out.

Congratulations! If you followed all the instructions correctly and coded everything well, then you should have your very own working application. The cool thing is that now whenever you or anyone else who has access to your spreadsheet edits the data, your app will be able to change and react to the new data. Here is a quick video of how mine turned out.

If you are looking for ways to keep improving and building, I recommend checking out some of JavaFX's more advanced features, like FancyText or FancyButton, which can add some higher end graphics to your application. Good luck, and drop a comment if you need any help or notice an error in my code!