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.
- Target audience: Beginners
- Estimated time: Setup - 10 min | Training - 5 min | Deployment - 5 min
You will learn to
- Create a training dataset from your spreadsheet.
- Install Peltarion’s AI for Sheets add-on.
- Fill your spreadsheet with predictions from an AI model.
- Optional: Build a deep learning model, with the Sales forecasting tutorial.
Open this Google spreadsheet containing historical sales data, and make a copy that you can edit yourself freely: click on File → Make 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 sheet Dataset is already well organized, so you don’t have to change anything.
Download the sheet as a CSV file by clicking on File → Download → Comma-separated values.
Google Sheets in other languages
Numbers inside the spreadsheet must be formatted with a dot as separator for decimal values, for instance
However, some countries use the comma as separator by default, e.g.,
Before you download the sheet as a CSV file, make sure that numbers use the correct format. You can do that by clicking on File → Spreadsheet 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-ons → Get 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
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-ons → Peltarion - AI for Sheets → Show sidebar. A sidebar will appear on the right side to help you request predictions from your model.
Click on Enter credentials to fill in the URL and Token from the deployment’s API information. Click on Connect to validate.
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.
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.
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!