Triggering Airflow DAG From Google Sheets

Wait!
Why should one even trigger Airflow DAG from Google Sheets?

At Affinity Answers, we are looking into providing programmatic audiences using which advertisers can target their audiences better. We cover the most frequently used audience segmentation in our taxonomy. But there are some cases in which our clients need custom build audience segmentation for their campaign. In those cases, they need to know the number of people they can reach with the custom-defined segmentation.
In that case, the research analysts will come to the developers with the audience definitions in an excel sheet (because they are familiar with excel sheets), and developers will run some scripts to determine the estimated number of audiences.
In this approach, the developer’s valuable time is wasted doing some repetitive tasks. So, we decided to automate this task. One solution that came into our mind was to provide a UI so that research analysts could enter custom audience definitions and wait for the result. Implementing this solution involves some engineering effort, maintenance efforts, and research analysts to learn the new UI. We wanted some simple solutions and decided to extend one tool familiar to the research analysts, Google Sheets. So, once research analysts enter the custom audience definitions into the sheet, they need to click on a custom-made button on Google Sheets to trigger the processing task. Then they will get the result inside Google Sheets itself.
Let’s see how we did that.

Enabling Airflow’s experimental API

Airflow exposes an experimental REST API, which is available through /api/experimental. This will provide us with the Airflow’s functionalities via REST API. We could make use of this to trigger our DAGs by calling this API.
From version 2.0 onward, this is disabled by default. So, we need to enable this first. We can do that by setting enable_experimental_api option in [api] section of the config to True

Authentication for Airflow’s experimental REST API

Once you enable the experimental REST API, anyone can access Airflow via the API. Which is dangerous! We need to secure our Airflow APIs.
The Authentication to API is handled separately from the web application.
To use the password-based authentication, we need to set the following in the configuration:

[api]
auth_backend = airflow.contrib.auth.backends.password_auth

After this is enabled, you need to pass username and password in the Authorization header of the API calls:

Authorization: Basic base64(username:password)

Now, since the API for Airflow is ready, we can make use of this to trigger the DAG of our choice just by making a call to this API

curl "https://${AIRFLOW_HOST}/api/experimental/dags/${DAG_NAME}/dag_runs" \
  -X POST \
  -H "Authorization: ${BASE64_USERNAME_AND_PASSWORD}" \
  -d '{}'

Extending Google Sheets’ functionality using AppScript

Google provides a mechanism to extend the functionalities of Google apps via AppScript. Using AppScript, we can add custom menus, dialogs, etc.

Extending Google Sheets | Apps Script | Google Developers

We can access the AppScript editor from Tools > Script Editor
For our use case, we just need to add one menu item to trigger our Airflow script. We can create that using the following script:

// Adds Custom menu and make it to call triggerDAG() function
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
    .addItem('Trigger DAG', 'triggerDAG')
    .addToUi();
}
function triggerDAG() {
  const options = {
    'method': 'post',
    'payload': '{}',
    'headers': {
      'Authorization': `Basic ${BASE64_USERNAME_AND_PASSWORD}`
    }
  }
  const response = UrlFetchApp.fetch(`https://${AIRFLOW_HOST}/api/experimental/dags/${DAG_NAME}/dag_runs`, options);
}

Custom Menu on Google Sheets

After the DAG is triggered, it will call the function to process the Google Sheets and update the values inside Google Sheets.

Thus, we have integrated AirFlow with Google Sheets — zero learning curve for those using it and low-maintenance for developers.