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.
from fastapi import FastAPI
from pydantic import BaseModel
import pandas as pd
from fbprophet import Prophet
class Data(BaseModel):
length: int
ds: list
y: list
model: str
daily: bool = False
weekly: bool = False
annual: bool = False
upper: float = None
lower: float = 0.0
national_holidays: str = None
app = FastAPI()
@app.post("/prophecise/")
async def create_item(data: Data):
# Create df from base model
df = pd.DataFrame(list(zip(data.ds, data.y)), columns =['ds', 'y'])
# Add the cap and floor to df for logistic model
if data.model == "logistic":
df['y'] = 10 - df['y']
df['cap'] = data.upper
df['floor'] = data.lower
# make basic prediction
m = Prophet(growth=data.model,
weekly_seasonality=data.weekly,
daily_seasonality=data.daily,
yearly_seasonality=data.annual
)
# Add national holidays
if data.national_holidays is not None:
m.add_country_holidays(country_name=data.national_holidays)
# Fit data frame
m.fit(df)
# Create data frame for future
future = m.make_future_dataframe(periods=data.length)
# Add the cap and floor to future for logistic model
if data.model == "logistic":
future['cap'] = data.upper
future['floor'] = data.lower
# Prophecise!
forecast = m.predict(future)
# Print values
print(list(forecast[['ds']].values))
# Return results
return [forecast[['ds']], forecast[['yhat']], forecast[['yhat_lower']], forecast[['yhat_upper']]]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:
FROM tiangolo/uvicorn-gunicorn-fastapi:python3.7
COPY ./app /app
WORKDIR /app
RUN apt-get -y update && apt-get install -y \
python3-dev \
apt-utils \
python-dev \
build-essential \
&& rm -rf /var/lib/apt/lists/*
RUN pip install --upgrade setuptools
RUN pip install cython
RUN pip install numpy
RUN pip install pandas
RUN pip install matplotlib
RUN pip install pystan
RUN pip install fbprophet
RUN pip install fastapi
RUN pip install pydanticOnce 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.
/**
* Forecast data using Facebook Prophet. Upper and Lower bounds are only required when using the logistic regression model. More information can be found at https://facebook.github.io/prophet/
*
* @param {A2:A150} dates - Select the pre-forecast date range as a STRING
* @param {B2:B150} values - Select the pre-forecast metric values as a STRING
* @param {14} forecastLength - How many days would you like to forecast?
* @param {"linear"} model - "linear" or "logistic"
* @param {"True"} annual - Does data contain annual seasonality? true or false
* @param {"False"} weekly - Does data contain weekly seasonality? true or false
* @param {"False"} daily - Does data contain daily seasonality? true or false
* @param {""} upper - Select the pre-forecast date range (Only required for logistic regression model)
* @param {""} lower - Select the pre-forecast date range (Only required for logistic regression model)
* @param {"UK"} nationalHolidays - ISO of country you'd like to add national holidays for e.g. "UK"
* @return Facebook Prophet Forecast.
* @customfunction
*/
function prophecise(dates, values, forecastLength, model, annual, weekly, daily, nationalHolidays, upper, lower) {
// API endpoint
var url = 'YOUR_CLOUD_RUN_URL';
// Format the date and values
var dates = SpreadsheetApp.getActiveSpreadsheet().getRange(dates).getDisplayValues().join().split(','),
dates = dates.filter(String),
values = SpreadsheetApp.getActiveSpreadsheet().getRange(values).getDisplayValues().join().split(','),
values = values.filter(String),
formattedDates = dates.join().split(','),
formattedMetrics = values.join().split(','),
preforecastData = {
"length": forecastLength,
"ds": formattedDates,
"y": formattedMetrics,
"model": model.toLowerCase(),
"annual": annual.toString().toLowerCase(),
"weekly": weekly.toString().toLowerCase(),
"daily": daily.toString().toLowerCase(),
"upper": upper,
"lower": lower,
"national_holidays": nationalHolidays
};
// Add the pre-forecast data to the payload
var options = {
'method': 'post',
'contentType': 'json',
'payload': JSON.stringify(preforecastData)
};
// Call prophecise API
var response = UrlFetchApp.fetch(url, options);
// Return forecast result
var json = JSON.parse(response)
var dates = Object.values(json[0].ds);
var forecast = Object.values(json[1].yhat);
var forecastresult = [];
dates.forEach(function(date) {
forecastresult.push([date]);
});
forecast.forEach(function(yhat, index) {
forecastresult[index].push(yhat);
})
forecastresult.splice(0, 0, ["Date", "Forecast"]);
return forecastresult;
}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:
=prophecise("A2:A","B2:B",30,"linear",true,false,false,"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!