Integrate AI into Google Sheets for sales forecasting

Get AI where you work

This tutorial will show you how to leverage the Peltarion Platform from your Google Sheets documents.
You will use a spreadsheet with historical sales data to train a deep learning model, and get predictions for future dates back into the spreadsheet.

Person - Target audience: Beginners
Clock - Estimated time: Setup - 10 min | Training - 5 min | Deployment - 5 min

You will learn to
Peltarion logo - Create a training dataset from your spreadsheet.
Peltarion logo - Install Peltarion’s AI for Sheets add-on.
Peltarion logo - Fill your spreadsheet with predictions from an AI model.
Peltarion logo - Optional: Build a deep learning model, with the Sales forecasting tutorial.

Rather watch?


The data

Open this Google spreadsheet containing historical sales data, and make a copy that you can edit yourself freely: click on FileMake a copy, and save it in a place that is convenient to you.

The document has two sheets (tabs). The sheet called Dataset contains the data we’ll use to train a model.
We’ll use the Test predictions sheet later to test the add-on.

You should get familiar with the data, and get a feel for how different features affect the daily revenue.
For example, a shop has 0 daily revenue when it isn’t open, but not all shops close on weekends or on a holiday.

Export the spreadsheet as a CSV file

The data in the sheet must be organized in a way that can be used for training an AI model:

  • The first row contains the name of the different features, one feature per column.

  • The following rows contain the values for each feature, one row per example.

  • No empty cells are allowed.

Data available as a Google Sheets spreadsheet

The sheet Dataset is already well organized, so you don’t have to change anything.

Download the sheet as a CSV file by clicking on FileDownloadComma-separated values.

Google Sheets in other languages

Numbers inside the spreadsheet must be formatted with a dot as separator for decimal values, for instance 3.14. However, some countries use the comma as separator by default, e.g., 3,14.

Before you download the sheet as a CSV file, make sure that numbers use the correct format. You can do that by clicking on FileSpreadsheet settings and selecting United States as the Locale.


Train a model

Now is the time you would head to the Peltarion Platform to train and deploy a model from this data.

If you want, follow the Sales forecasting tutorial to learn how to do it and come back here afterward.

We have also deployed the forecasting model, so just keep reading if you prefer to see how to use that model in your spreadsheet.


Install the AI for Sheets add-on

If you’ve never used the add-on before, you’ll need to install it.

You can install the Peltarion AI for Sheets add-on directly within an open Google Sheets document:

  • Click on Add-onsGet add-ons

  • Fill in the Search apps text box with Peltarion - AI for Sheets

  • Click on the Peltarion - AI for Sheets result, then click on the Install button

Install the Peltarion AI for Sheets add-on

Get AI predictions in Google Sheets

Fill your spreadsheet with test data

Open the Test predictions sheet (tab) from your own copy of the Google spreadsheet.

This sheet contains the same kind of input information that the model was trained on but for dates in the future. This means that the model has never seen these exact combinations of promotion, holidays, advertisement, etc., while it was training, but it will make predictions based on what it has learned from the past data.

Feel free to edit some of the variables or to add more rows.

Get your model’s API information

Anyone can’t just use the model that you built. To get predictions from a deployed model, you will need two pieces of information:

  • The URL of the model

  • The Token of the model, which is like a password without which the model will not run

You can get this information in the API information on the Deployment view.

If you did the Sales forecasting tutorial, copy your very own URL and Token to use a model that only you have access to!
Otherwise, you can use these to get going:

Using the Peltarion AI for Sheets toolbar

In your Google Sheets spreadsheet, click on Add-onsPeltarion - AI for SheetsShow sidebar. A sidebar will appear on the right side to help you request predictions from your model.

  • Enter credentials
    Click on Enter credentials to fill in the URL and Token from the deployment’s API information. Click on Connect to validate.

  • Method
    Select Regression. The method is used to display the model predictions correctly in Google Sheets. Sales forecasting predicts a numerical value, which is regression problem.

  • Enter the header range
    Fill in the range of cells in the spreadsheet that contains the name of the input features expected by the model. In our spreadsheet, the Header range is C1:K1.

  • Enter the data range
    Fill in the range of cells in the spreadsheet that contains the value of the input features. In our spreadsheet, the Data range is C2:K100.

  • Enter the output range
    Fill in the range of cells where the predictions from the model should go. You can use the Output range L2:L100.

  • Predict
    Click on Predict to send the specified data to your model and fill the spreadsheet with the calculated predictions.

You can fill in a cell range easily by first selecting it on your spreadsheet, then clicking the Use selection button in the AI for Sheets toolbar.


Recap

You’ve learned how to blur the boundary between data science and everyday spreadsheet work. You can use the data that you have in Google Sheets to train an AI, and get smart predictions back directly inside your cells. All of that without a single line of code!

Was this page helpful?
YesNo