Is It Possible To Populate A Sheet From An External Database (NetBox) Every Time The Sheet Is Loaded?

[Bonislaw]

So I have been contacting Grist support via Email in the past week to address this problem, and kudos to @Natalie and @Dmitry for the kind support. As Dmitry suggested, I moved my question here for better discussion.

Ultimately, I’m trying to: making a column in Sheet1 a dropdown, whose choices are from a NetBox database (like the circuit types are limited to ASE, DDN, and Ethernet). The choices may vary from time to time, so it needs to be updated timely.

My thinking process:

1. Create a lookup column that reads data from another sheet (Sheet2):
2. Connect Sheet2 to an external PostgreSQL database
3. Populate (Replace) the rows in ‘Sheet2’ in my document every time the sheet page is loaded

As of now, with the help of Grist support, I’ve set up n8n and connected it with Grist via Rest API. I was able to, upon manually execution, remove all the rows and re-populate them with data from NetBox’s PostgreSQL database.

Now the catch is, how do I automate this process, triggering this event every time the sheet page is loaded (or when a button is clicked, the drop down menu is selected in Sheet1)?

Failed attempts:

1. By adding an automation column containing the following script:

import urllib.request
import json

webhook_url = 'http://<SERVER-IP>:5678/webhook/4e562645-e733-4dea-a4d0-241966253e53'
payload = {
'event': 'example_event',
'data': {
'message': 'Hello, webhook!'
}
}

headers = {'Content-Type': 'application/json'}
json_data = json.dumps(payload).encode('utf-8')

req = urllib.request.Request(webhook_url)
response = urllib.request.urlopen(req)

return('Webhook signal sent.')

Result: Feedback loop. The first loop is executed perfectly. However after the execution, the script in each new row is run yet again, causing a flood of data.

2. By embedding the script inside a toggle column:
   Results: Basically the same.

I’m really at my wit’s end at this point. Could you guys suggest a more economic way of solving this problem? Or should I ask for NetBox integration?

[Bonislaw]

My n8n workflow:

[dmitry-grist]

So am I right that this is working already if you trigger the process manually (by clicking “Execute Workflow”)? The issue is in making it automatic?

Triggering data changes on page load is indeed unsupported, but it wouldn’t be a good idea anyway. It may seem fine if you are the only user, opening the document only occasionally, but in general when you imagine that many people might open the document, it would be bad to replace a table’s data every time anyone opens it.

What do you think of these alternatives?

1. Trigger your workflow when relevant data changes on NetBox side (most efficient, what I would recommend if it’s possible), or when anything changes on NetBox side.
2. Time-based: do the update nightly, or hourly.
   • If there is a way to tell whether something changed on NetBox side (e.g timestamp of last change), then more frequent updates could be reasonable, as data wouldn’t get touched when there are no changes.
3. Manual: have a button in Grist that triggers the workflow.

[Bonislaw]

Yes, the manual execution works fine. And I think your first proposal sounds the most viable, but that’ll be another webhook rabbit hole to dig into. But I do believe having an ‘update’ button would be good. Will we be getting an action button update anytime soon?

[dmitry-grist]

There is already a way to manually trigger a webhook, just without a button-looking UI. But here is an example:

https://public.getgrist.com/k2pCRDfjYEg2/Webhook-Button-Trigger/p/1

This document has a Click to Trigger column, with a Toggle cell. Clicking this toggle doesn’t actually toggle the value (a trigger formula on this column resets the value back each time), but it nevertheless works as a record change that triggers a webhook.

The webhook it triggers is configured in webhook config, but you can’t view it because it’s only available to the document owner. Save a copy of the document to see the config; or here’s a screenshot:

This uses a “webhook.site”, a convenient place for testing webhooks. You can see the webhook calls showing up at https://webhook.site/#!/a16d76b9-09c6-4494-9416-b6345075cfd0/8c1be7dc-6662-402d-9abd-05b7e9828adb/1 every time you click the Click to Trigger toggle. In addition, I added a Last Timestamp (using Updated At pattern) to make it clear when the last click happened.

[Bonislaw]

I have basically implemented everything with your help! Now just one more thing. How do I add a ‘cooldown’ feature to the webhook trigger toggle? If the user keeps turning that toggle, it’ll add heavy traffic to the server.

I’ve fiddled with the readiness column box in Webhook settings but to no avail. I was trying to record the time when the toggle is clicked, then using current time to subtract that time. If the delta is less than a certain value (say, 30 seconds), then the readiness would be False.

The catch is, the timestamp column updates as soon as I toggle the trigger, which means the toggle time is literally NOW(). So the readiness is always false.

Is there a way to ‘async-ly’ record the time the trigger is toggled? Or is there some other ways to prevent Webhook spamming?

[dmitry-grist]

I meant to try this, sorry for not getting to it earlier. I added my attempt now here:

https://public.getgrist.com/k2pCRDfjYEg2/Webhook-Button-Trigger/p/2

Here are the salient parts:

1. The Click to Trigger column is still a trigger formula setting the value simply to True, triggered on any change to itself. (So clicking it has no effect except for triggering other cells.)
2. The Last Timestamp column is simply NOW(), triggered on any change to the Click to Trigger. It shows the last time that Click to Trigger was clicked.
3. The Last Trigger column is what’s doing the throttling, with this trigger formula:

   if not value or $Last_Timestamp > value + datetime.timedelta(seconds=5):
     return $Last_Timestamp
   return value
   

   It’s copying the value of Last Timestamp but only if it’s at least 5 seconds later than the previous value of Last Trigger. If clicked multiple times quickly, Last Trigger keeps it old value.
4. The Ready column is a regular formula $Last_Trigger == $Last_Timestamp. If Last Trigger updated, it turns to true. If Clicked to Trigger is clicked multiple times quickly, Last Trigger will lag behind, and Ready will be false.

The webhook is configured using Ready column as the “Ready” column (so it does not trigger if Ready is false). AND, importantly, it’s set to react both to the “update” and “add” events. That’s because when Ready turns from false to true, webhooks interpret that as the appearance of a new record, and only report it if “add” events are included: