App Script function failure due to AppSheet child records

[sckaruna]

Hello,

I have an AppSheet app with the following table schema:

1 Parent Table:
- Multiple Children tables referencing the "Parent" table
- There is 1 special "child (sub parent)" table referencing the "Parent" table with multiple children tables referencing the "child (sub parent)" table

I have an App Script function called createTemplate(row) that creates PDF letters when a record is created. When a record is created in the "Parent" table it always needs to have at least one child record created as well.

The App Script function createTemplate(row) works fine when I manually run the function through App Script by passing a "Parent" table row number as an argument. However, when I create a bot in AppSheet calling the createTemplate(row) function when a record is created in the "Parent" table, it always fails. What I have observed is that the createTemplate() functions runs right after a "Parent" record is created, but before the children records are created, thus leading to the failure. That 20 to 30 second delay between the creation of the parent record, and the creation of child record is the issue.

As a potential solution, I even injected a wait step with wait for "A period time" of 5 minutes in the AppSheet bot prior to createTemplate function event being trigged. However, the createTemplate function is still failing, or the bot times out. The rare occasion when the bot does work, it takes around 30 to 45 minutes to trigger the createTemplate function through AppSheet which is baffling me.

I even tried utilizing the sleep method in App Script, but what happens then is, AppSheet waits until the sleep method has expired before creating the children records. Then the same issue as above occurs.

I already have a workaround to make this function trigger properly right in App Script, but it is inefficient. Therefore, I would really like to make the function work directly through AppSheet when a parent/children records are created. How do I do that? Given there are ~20 different child tables, I don't want to check each child table for a new record before firing the createTemplate() event in AppSheet.

Any advice and/or suggestions would be greatly appreciated. Thank you.

[TeeSee1]

I do not if the following will completely satisfy your requirements but you might give it a try.

1. Create a column in the parent such as report_flag with initial value "INIT" for instance.

2. Create a bot or form save event to set the flag to "RUN" for instance.

3. Create a "scheduled" bot to look for parent records with the flag set to "RUN" and do your thing. As a second step, reset the flag to "INIT"

[scott192]

My thoughts on this is that you are passing a spreadsheet row number to your appscript function.
You have discovered that the bot trigger is fired as soon as the client side of appsheet updates rather than when the actual data is created and stored in the sheet, thus 'creating' the row you are passing...too late.
My honest advice here is to refactor your appscript so that it accepts data values rather than rows.

As you are using script to generate documents, I am guessing that the child rows contain line details of a Order, Line, Line details model?  Such as an Invoice perhaps?
The way I go about doing this is to get my triggered bot to generate a JSON file in Appsheet with the details of the master record and any child records needed to create the document/s.  Then I use Appsheet to SAVE that JSON file with a known filename into a known Drive folder.
After this, I pass the filename of the saved file as an argument to an appscript task.
The appscript task finds and reads the content of the file, parses it to a js object and then it can do all the functionality of creating a doc, inserting tables etc the way you are possibly already doing it in your script.
Finally it returns the id and url of the created Drive file back to Appsheet for storing in the parent record.
The hardest part for me was to create my json template, but it's something you do get used to after a while in the limited editor interface Appsheet provides - of course you could use other tools to help with that part.
Hope some of this helps you on your way to a solution 🙂

[sckaruna]

@scott192 @TeeSee1 Thank you both for proposing these two wonderful solutions. I really appreciate it. 🙂