|
Los tipos de archivo clásicos dejarán de estar disponibles a partir de enero de 2021. Puedes migrar tus archivos clásicos o descargar un PDF. Más información

Run Chain via Button withing Spreadsheet

Respondida
Solicitar demostración

I have several chains that run code to gather latest data from datbase, format files, upload files to table, then refresh queries and connections.

Can I set up a button or shortcut within my spreadsheets to run these chains? Trying to get more people on board with using chains but going through the whole chains menu and finding the execute button is a little too complicated. There has to be an easier way to run when not on a schedule. Not sure if there is some round a bout way where you set a run chain trigger as when a certain value is entered into a cell or something.

I know you can get the external link which brings you straigh to execute but this is still too much.

1

Comentarios

5 comentarios

Fecha Votos
  • Wade Hays

    Hi JP,

     

    I am actually working on a project that would provide this exact service. I'd be happy to share more when I get a little closer to launch.

     

    Wade

    --

    Wade Hays - https://towerturn.com/services/dedicated-expert 

    0
  • Andrew McKenzie

    JP Thomas - I would try something like the following:

    1. Put a Yes/No drop-down in a spreadsheet cell next to a field name like "Re-run [name of chain]?"
    2. Nearby, you could add a field to hold the timestamp of the last time this chain was run.
    3. Create a utility chain whose sole purpose is to retrieve a value from a particular sheet cell.
    4. Create another utility chain whose sole purpose is to update the value of a particular sheet cell.
    5. Add a node at the beginning of the chain you want users to be able to easily run where you will first call the utility chain to retrieve a value and get the value in the Yes/No "trigger" field returned from the sheet (i.e., step 1).
    6. Add nodes at the end of the chain to update the "trigger" field back to "No" (or whatever) and update the timestamp field in the sheet (the other field referenced in step 1).
    7. Add a conditional node following the first node to evaluate the value returned. If it's "Yes" then proceed with the rest of the chain. Otherwise, terminate the chain early.
    8. Schedule the chain you want to run to run every minute.

    I *think* that all of the above should work; I created a chain a while back that used a very similar pattern, although there were a few differences. For example, in my version of step 3, the data I wanted read in the sheet was highly structured, so I had actually used an SQL query to ID the values of interest for me. But I believe you can just use Get Sheet Data to get this.

    A few notes:

    • Depending on your approach, if you're worried about people mucking with your trigger field, you might have to lock down the sheet where this is located and/or have a second sheet (fully locked down) that references the field via a formula. This could reduce brittleness if people e.g. add rows to the sheet where your trigger is located since you may be using something as simple as a column:row ID to pull the trigger value.
    • For step #4 above, I did something a bit different, so I was writing values in a certain sheet that were referenced by a lookup on the sheet where the "trigger" occurred. The reason for this was that, depending on how the sheet where the trigger is located is being used, you have to be super careful writing data back to a sheet as you could easily overwrite existing data or user input. For me, it was easier (or necessary? I can't remember the details) to have the chain write back data in its very own sheet that users didn't have access to and then use lookups to pull relevant values back into other sheets.
    • Ideally, you could add further nodes at the start of the chain to check whether the spreadsheet has been updated since the last time the chain was run. This way, if the spreadsheet hasn't even been modified, you can instantly exit the chain; if the spreadsheet *has* been modified, you can then proceed to check if that particular "trigger" value has been updated.

    Hope the above is all clear. I know a similar approach has been used by other organizations in patterns I've seen, although I don't know details on the implementation. But I'm also interested because maybe there is a far better way of doing this....

    1
  • JP Thomas

    Andrew McKenzie Thanks for this detailed write up. I knew the best approach was probably something along these lines but I had  not even attempted to create the utility chains you mentioned. This gives a clear picture and I will try this approach. I agree that it is probably the best option we have right now.

    0
  • JP Thomas

    Just an updated in case anyone stumbles upon this. I implemented the above solution from Andrew McKenzie and it works pretty well. It will allow others to run chains straight from the spreadsheet which is pretty nice. Only complaint is that the "detector" chain - the one check whether Y/N has been changed - can only run once per minute so a chain that takes 15 seconds to run can take anywhere from 15 seconds to 1 minute 15 seconds based on when they changed the box.

    I added some other fun status update boxes that say exactly where the chain is at in the process as its happening. Also some conditional formatting helped show that something is actually happening when they flip the switch too. all the cells stay red until the chain is finished running. Then it drops in the date/time the chain was run so people know it actually worked.

    1
  • Andrew McKenzie

    Glad it's working for you JP Thomas! Sounds like you added some nice enhancements too.

    Unfortunately, I don't think there's any straightforward way to use an approach like this to kick off the chain the instant the user changes the drop-down. While I can think of a few off-the-wall ways it might be done, those seem overly complex and might still involve a delay anyway.

    If users really need that instant response, I think you'd have to set up permissions so that they could go into the chain execution interface themselves and execute the chain directly. (On the plus side, maybe that experience would give them enough curiosity that they'd want to start building their own chains, too.)

    One other thought (posting for anyone who stumbles across this) is that if your chain is primarily doing something with data, and you want the data updates to feel instant, you could potentially combine a regularly scheduled chain with a data connection to get the best of both worlds. For example, the chain could run every minute and update a staging table/sheet that is linked (via a connection) to the real table/sheet. When users want to update the real table, they refresh the connection, which pulls from the staging table. Technically the data would not be refreshed any faster (since it's still limited by the schedule frequency), but at least users would have instant gratification. Of course, that approach is probably only relevant for chains that are updating data.

    0

Iniciar sesión para dejar un comentario.