How to run a Python script every time a spreadsheet is updated

Spreadsheet integration is essential to ops teams. It allies the convenience of still using trusty spreadsheets with powerful automation that ships that new inputted information wherever else it needs to go.

‍

In this tutorial we’ll learn how to run a Python script each time a new row is added to a spreadsheet.

‍

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 Cloud - 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://hooks.abstra.cloud/joaoworkspace/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

‍

After logging into Abstra Cloud, add your Apps Script environment variable to your workspace, on the Env Vars sidebar tab. Create a new Hook and start coding by importing the required libs, abstra.hooks and os. 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)

‍

Check out other 4 useful full-code examples of integrations for this step:

1. Use Pandas to clean up and arrange the data and send it straight to a database.
2. Let your team know of the new records on Slack.
3. Input new records into your CRM.
4. Send the new client a welcome email.

‍

Step 3 - Deploy your Hook

‍

With your code done, copy your Hook’s endpoint and paste that into Apps Script, so it knows where to send that request to.

‍

Just toggle the button on the top bar to activate your Hook and voilà! Your integration is up and running, ready to automate the heck out of your boring tasks.

‍

‍

Want a customized automation for free? Grab a 15-min chat with us today.

‍

‍

Full Hook code:

from abstra.hooks import get_request, send_json
import os

body, query, headers = get_request()


if headers["Api-Key"] != os.environ["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)

‍