Build forecasts in Google Sheets using Facebook Prophet
Here’s how you can use Facebook’s open-source Prophet library in Google Sheets to create accurate time-series predictions using simple and intuitive parameters.
This is a proof-of-concept solution for Prophet forecasting in Google Sheets. You might want to build on this to include additional parameters that will give you greater control over the forecast model.
What is Facebook Prophet?
Prophet is open source software released by Facebook’s Core Data Science team. It is a procedure for forecasting time series data and works best with time series that have strong seasonal effects and several seasons of historical data. It is available for download on CRAN and PyPI.
Overview
Here’s an overview of the data flow for this solution:
The user fills in the custom function and those variables are sent off to our Python script for forecasting and the results returned to Google Sheets.
Get started by building your own API with Python
We’ll be building our own API using FastAPI, which is a modern, fast (high-performance), web framework for building APIs with Python. This will take parameters from the Google Sheets function and do all the forecasting in the background with Facebook Prophet. I won’t go in to how to use FastAPI but you can read all you need to know here.
Here we’re using some of the more intuitive parameters for Prophet, you can read more about the additional parameters that are available to you in the Prophet documentation. I also found this Implementing Facebook Prophet efficiently post on Towards Data Science really useful.
Upload your API to Google Cloud Platform
We’ll be running our API on Google Cloud Platform using Cloud Run, which is a fully managed compute platform for deploying and scaling containerized applications quickly and securely. You’ll need to add a billing account to GCP if you haven’t already, but there is a generous free tier.
To deploy our API on Cloud Run we first need to containerize our app using Docker. If you’re not familiar with Docker, I recommend taking a look at their documentation. You’ll need to create a Dockerfile and deploy on Cloud Run which should look something like this:
Once you’ve containerized your API you can deploy to Cloud Run. Once complete, you’ll be given a URL like the one shown below, which will be used as the API endpoint in our Apps Script.
Finally, we use Apps Script to call our API
Now we’re ready to set up our custom function for Sheets in Apps Script. We’ll take the user inputs from our custom function and send these on to our API for forecasting. The script below sets up the custom function, calls the API and returns the forecast to the user. You’ll need to update the url variable to your own Cloud Run URL.
Using our new formula in Google Sheets
I’ve been using the formula to forecast Google Analytics data in Google Sheets using the Google Analytics add-on and our custom function. So for example, the following function would take the dates from column A and the metrics in column B and return a 30 day forecast that takes in to account national holidays in the UK:
In this example I then used the Prophet forecasts in a Google Data Studio dashboard to show predicted trends for KPIs.
Hopefully you found this post useful and it has inspired you to start using your own Python/R functions in Google Sheets, just think of all the cool stuff you could do!