New add-on for Google Sheets adds Machine Learning to your data

The first version of BigML’s add-on for Google Sheets has been released! The BigML add-on provides an easy way to fill the blanks in your spreadsheets using the predictions of models and clusters in BigML. As we explained in a previous post, now you can fill in the columns in your spreadsheeet by using the existing BigML decision tree models to generate predictions based on the sheet data. Thus, using the add-on you can, for instance, score your sales prospects based on the historic sales records in your Google Sheets. Similarly, you can group your customer data into segments according to the clusters they belong to.

Get the add-on running

The add-on is available at the Chrome Web Store or directly from your Google Sheet by using the Get add-ons item in the Add-ons menu. The BigML add-on appears under the Bussiness Tools category. Just click the +Free button to install it, and a new BigML submenu item will appear under the Add-ons menu.

The first time it is run, the add-on will ask for some permissions:

By accepting them,you allow the add-on to acces the data in your Google Sheet and your models and clusters at BigML. The add-on will read the Sheet data and download the BigML models or clusters to Google servers, where all the predictions and cluster labelings will be done. Then the add-on will appear as a sidebar in your Google Sheet. In order to authenticate and use the models and clusters in BigML, you will need to provide your credentials:

that will be stored and used from then on (you can check anytime your credentials at BigML). Then you will be ready to start using the add-on.

Using the add-on step by step

Just click on BigML > Start under the Add-ons menu to see the sidebar that will show your models or clusters in BigML.

In BigML you can build your resources in a development environment (no cost involved) or in production. You can also organize your resources in projects. The search form in the add-on works in both environments, allowing to filter your resources by name or project.

To fill the blanks in any column of your Google Sheet you will need a model that can predict the field in this column from the contents of the rest of columns in the same row. Select from the list the one that best fits your data and click on it. A detailed description of the model will appear in the sidebar, and the model will be ready to use in your Google Sheet.

Finally, select the range of rows that you would like to complete and click the Predict button to see the predictions appear. Note that the columns in your selection are expected to match the fields in the model (listed in the model description on your sidebar).

The blank cells will be filled with the predicted values (that in this case must belong to a list of categories) and a confidence rate that ranges from 0 (no confidence) to 1 (total confidence) will be placed in the last available column. If the column to fill has numerical values in it, the associated model will have a numerical objective field and the predicted values will be also numerical. Then a new column will be added to show the associated error for the prediction.

Using clusters is quite the same. In this case, select the range of rows that contains the instances of data you want to segment and two new columns will be added to your Sheet. The first one will contain the label of the segment that the row belongs to (or centroid name).  The second one shows the distance of the data in that row to the centroid, or central point for each segment. You can check this video to see how the add-on works in basic use cases.

First time BigML-GAS users

To use BigML for the first time, you’ll need to Sign up on our web site. As a result, you will land in a development environment with some data sources available to make your first steps in the platform. Still, to start working with your add-on you will need to either:

• create a model from one of the available sources or your own historical data

or

• clone an existing model from our model gallery.

In order to create your first models you can upload any local or remote CSV file. When uploading from a public Google Sheet, use its export to CSV feature. The corresponding URL can be pasted in the remote URL form and the data in your Google Sheet will be uploaded to BigML.

The data will be transformed into a source, where each column will be described as a field and its type will be inferred from the uploaded contents. Then it only takes one click to generate a dataset, where all statistical information per field is stored. Select the field that you want to fill with predictions (or objective field) and another click will give you a model for your data. This model will be immediately available in your Google Sheets through the BigML add-on.

You can also search the Gallery of models for a model that fits your data. Remember that BigML will use the first row in your selection or range of data as a headers row, and the names of the columns there should match the ones in the model you use to fill the blanks. Once you find a model that suits your needs in the Gallery, you can get it from there clicking the label at the top-right corner:

and it will be cloned in your account, ready to use from your Google Sheets through the add-on.

This is all you need to enrich the information in your Google Sheets using BigML’s add-on. Let BigML bring Machine Learning to your Google Sheets!