Run a Python script every time a spreadsheet is updated

Adding a script to Google Sheets is pivotal for teams who want to leverage automation while keeping trusty spreadsheets around. It not only amplifies productivity, but also opens new possibilities to the powerful work spreadsheet can already achieve.

‍

In this tutorial, discover how to add a script editor to Google Sheets and make it run a Python script every time you add a new row to your spreadsheet. Experience the convenience of having a button that triggers an app script on addition.

‍

Additionally, get into easy ways of integrating Python with Google Spreadsheet, as we guide you through running your first Python Google Spreadsheet automation.

‍

Step 1 - Paste this into Sheets’ Apps Script

‍

Sheets has it’s native Apps Script extension for running code with your spreadsheet. But it is Javascript only 😒 That means no superb libs for data analysis and manipulation, such as Pandas. Also, execution time is limited to 6 minutes, which can fall short to processing heavy spreadsheets.

‍

So let’s set up a simple JS snippet in Apps Script that, each time the spreadsheet is edited, triggers a Hook in Abstra - our Python script.

‍

Here’s how to do it. In your spreadsheet of choice, navigate to Extensions < Apps Script. In the services tab, add the Google Sheets API service.

‍

‍

Create a new file and paste the code below, just editing the cells you’d like to send and your API key.

‍

‍


function runOnEdit(e) {
  // Set a comment on the edited cell to indicate when it was changed.
  const range = e.range;

  var rowIndex = range.getRowIndex()
  var sheet = range.getSheet()
  var values = sheet.getSheetValues(rowIndex, 1, 1, -1)

  Logger.log(`Values: ${values}`)

  if (values[0].filter(a=>a).length < 3) {
    Logger.log("Incomplete row")
    return
  }

  Logger.log("Sending request")
  const url = 'https://subdomain.abstra.app/_hooks/cadastro-alunos';
  const data = { values: values[0] }

  const headers = {
      "Api-Key": "PASSWORD_API_KEY"
    };

  const options = {
    'method' : 'post',
    'contentType': 'application/json',
    'headers': headers,
    'payload': JSON.stringify(data)
  };

  const response = UrlFetchApp.fetch(url, options);

  Logger.log(response);
}

‍

Then, create a Trigger and select the created function and “On edit” as the event type. With Apps Script done, let’s move along to the Hook itself.

‍

‍

Step 2 - Create your Hook

‍

Next, we need to create a webhook to execute the Python code.

‍

We will create our webhook in Abstra, since it's very simple to set up – just a simple script, without any infrastructure or additional code configurations, and can be deployed in a click. Feel free to setup your webhook in other platforms, such as your cloud provider.

‍

To create the Hook, install and run the Abstra editor locally.

‍


pip install abstra # if you still haven't

abstra editor ./insert-your-project-directory-here # point to an empty folder

‍

Navigate to Env Vars page in the sidebar and select “Add Environment Variable”. Add your Apps Script environment variable with the name APPSSCRIPT_API_KEY.

‍

This will make the variable available to all scripts in your Abstra local project, for testing. You can also add the env var directly in your folder's .env file.

‍

Navigate back to the Stages page. Click on “Create new” and select “Hooks”. Let's name it “Sheets integration” and watch as Abstra creates a corresponding sheets_integration.py script.

‍

The script is very simple. First import the required libs, abstra.hooks and os. Then, use the get_request function from Abstra’s lib to get the request data.

‍


from abstra.hooks import get_request, send_json
import os

body, query, headers = get_request()

‍

With a simple if conditional, make sure your API key is correct.

‍


if headers["Api-Key"] != os.environ["API_KEY"]:
    send_json(data={'ok': False}, status_code=403)

‍

Here’s where Python’s power and creativity comes in. You can use the values we received to do pretty much anything. To exemplify, we can set the info up as variables and print them out on our log to keep track of every change made to the spreadsheet.

‍


else:
    values = body["values"]
    name = values[1]
    email = values[2]
    className = values[3]

    print(f"Name: {name}")
    print(f"Email: {email}")
    print(f"Class: {className}")

    send_json(data={'received': values}, status_code=200)

‍

You can use the information retrieved from your spreadsheet in various ways: send them in an email or Slack, record them in another database, process the data with Pandas, etc.

‍

Check out these other full-code examples of easy integrations:

‍

Step 3 - Deploy your Hook

‍

To deploy your Hook, login to Abstra's Cloud Console through the editor's navbar. After that, just press “Deploy” on the navbar and done! Your webhook will be live.

‍

Navigate to the Cloud Console simply to add the environment variable to your project in production. You can also choose a custom subdomain for your URL, if you'd like.

‍

Copy your Hook’s endpoint and paste that into Apps Script, so it knows where to send requests to. Voilà – your integration is up and running, ready to automate the heck out of your boring tasks.

‍

Go beyond simple tasks – automate entire processes with Abstra

‍

Although this spreadsheet and Python solution is efficient for putting out immediate fires, it is not the most robust. Spreadsheets are not known for reliability, slugging and crashing when data gets too heavy, and can be tough to govern in big teams with multiple users.

‍

Beyond being a simple solution for beginner Python automation, Abstra also offers full-scale features for business process automation and management.

‍

Our Python-based Workflows empowers teams to build and manage custom operations by leveraging code with UI and AI productivity boosts. Auditable management and infrastructure come out-of-box, giving teams autonomy from engineering squads.

‍

If you want to automate and get visibility into processes you're struggling to streamline, grab a quick chat with our founder.

‍

Full Hook code:


from abstra.hooks import get_request, send_json
import os

body, query, headers = get_request()


if headers["Api-Key"] != os.environ["APPS_SCRIPT_API_KEY"]:
    send_json(data={'ok': False}, status_code=403)
else:
    values = body["values"]
    name = values[1]
    email = values[2]
    className = values[3]

    print(f"Name: {name}")
    print(f"Email: {email}")
    print(f"Class: {className}")

    send_json(data={'received': values}, status_code=200)

‍

TUTORIAL