Erratic behavior of Autocrat data

[Dennis McRitchie]

Hi all,

I've got a setup where I have 5 different Autocrat jobs in the same spreadsheet that are triggered by a form submit. Four of them work as expected but the 5th one is acting very strangely. The four that work reliably all report on rows in the same sheet within the spreadsheet. The fifth one reports on rows in another sheet in the same spreadsheet.

The setup is that a script runs and clears the Autocrat data (i.e., status and 3 other columns) for the rows  in the sheet I would like to have Autocrat report on. It then submits a form that triggers all 5 Autocrat jobs (I can see this in the hidden Autocrat Job Settings sheet).

In 4 of the jobs, I can see the correct rows being cleared in the Google sheet, then I see the jobs starting to run, and 10-20 seconds later I see the report files start to appear in the designated private Google Drive folder. And as the jobs go along, the Autocrat status data gets filled in until all the files have been created. So all is well, except...

The 5th job sometimes misbehaves as follows: it reports on the rows in the sheet whose Autocrat status information has been cleared, produces the correct file, and fills in the Autocrat data again ("Data merged and appended successfully; PDF created; Run via form trigger..."). Then within about a minute after this, the rows that were reported on have their data deleted again, by what process I don't know. The script that cleared them originally has completed running so should not be involved at this point.

I have also seen this job go through this same scenario for the same rows of the same sheet (Autocrat data cleared, Autocrat job runs, Autocrat data gets filled in again, and then gets mysteriously deleted) but NO report file is created at any point.

The weirdest is one where the script cleared 3 rows of Autocrat data, and when the Autocrat job ran, it created a report file on 15 rows (the 3 that were cleared and the 12 preceding rows whose Autocrat data was NOT cleared).  

I had previously created a report for those 15 rows, but in this job there were only 3 cleared rows. 

One explanation that could cover some of these cases is if the Autocrat job was using a cached version of the row data that was not up to date. But I don't know enough about how it or the Google sheets ecosystem propagates data under the hood to know if this is possible.

It would really help if I could see a debug log of what Autocrat is doing. Is there a way to turn on logging for Autocrat?

Of course, I tried the usual remedies of disabling triggering and re-enabling it. And also of deleting the Autocrat job and recreating it. I also have tons of logging in the script that clears the Autocrat data and submits the form to trigger the Autocrat jobs. But to no avail.

Anyway, I was a programmer for many years before retiring, but this has me stumped right now. Has anyone seen anything like this?

[Dennis McRitchie]

I figured it out. It *was* due to data propagation delay. Once I followed clearing the Autocrat data with a SpreadsheetApp.flush() and a 10 second delay *before* doing the form submit that triggers the Autocrat jobs, it started working reliably, like the other 4 Autocrat jobs.

I think the reason this 5th job was the only one failing was because it was the last one whose Autocrat data was being cleared before the form submit. So there was less time for the data to propagate.

[Joseph Schmidt]

I'm happy that you got it to work. I'm unsure what starts the script to run in the beginning. 

If it is a form trigger, autocrad is going to run twice once when the trigger signals and once again when the new form comes in. 

I was going and to recommend clearing the cells with a script prior to starting AutoCrat in a separate job.

I have helped several folks who wanted to the same report everyday. What we did was create a script that deleted autocrat cells to run at midnight 

Then use a separate script to automatically submit a form to trigger autocrat.

I'm interested in having more information as it might be useful for others

Joe Schmidt

--
You received this message because you are subscribed to the Google Groups "NV Autocrat Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/nv-autocrat-add-on/486eb41b-40c0-4a41-8ae1-c692d25d7cdbn%40googlegroups.com.

[Dennis McRitchie]

Hi Joe,

 

Sure, I’ll be glad to fill in the gaps. I am doing something analogous to your solution, but we needed the ability to run this in close to real time (start to finish in just a few minutes).

 

There are 2 spreadsheets: spreadsheet ‘A’ is a database of trouble tickets used by Facilities at the organization where I volunteer. I coded this system to allow anyone to create a trouble ticket, and for staff to update these tickets as work is done. This spreadsheet has (for the purposes of this Autocrat-related post) 3 sheets: the Active ticket sheet, the Completed ticket sheet, and the response sheet for the form submission that triggers our 5 Autocrat jobs. And it also includes (of course) the 5 Autocrat jobs that report on the Active and Completed tickets. (Note that the Active and Completed ticket sheets are not response sheets. The ticket creation and modification response sheets are part of a 3^rd spreadsheet and script, which pushes the processed data to spreadsheet ‘A’, thus avoiding triggering the Autocrat jobs.)

 

Spreadsheet ‘B’ is the one that has the response sheet for the form that allows staff to request ticket reports. Based on user input (specifying up to 5 different reports), its script clears the necessary columns and rows in spreadsheet ‘A’s Active and/or Completed sheets (e.g., rows associated with “To Be Assigned” tickets), does a SpreadsheetApp.flush() followed by a 10-second wait so that all write requests are propagated to spreadsheet ‘A’, then submits the form associated with the response sheet in Spreadsheet ‘A’ to trigger the 5 Autocrat jobs.

 

This script then monitors the Google Drive folder where the Autocrat jobs deposit the requested report files; when all the expected files are found, it attaches them to an email describing the reports, and sends it to the person that requested the reports. It then deletes the report files from the Drive folder so it is ready for the next report request.

 

The Autocrat jobs all create PDFs to be deposited in a specified private Drive folder, and all 5 are configured to respond to a form submit trigger; but I do not use their emailing ability because it is too limited (can’t specify the body contents or recipient at execution time).

 

And that’s about it. Let me know if you have any other questions.

 

Dennis

To view this discussion visit https://groups.google.com/d/msgid/nv-autocrat-add-on/CAPWPTORg0h83m8otO2uqY5kU2kwn1pavb11AVz%3DDq5G03-JaNA%40mail.gmail.com.

[Joseph Schmidt]

Thanks.  I always like to learn.

I have a video on this topic.I may review it and make some changes.

I helped one person who had to send a report at a specific time.

We have the script running every minute and checking for that time before it sends the fake form.

Joe Schmidt

To view this discussion visit https://groups.google.com/d/msgid/nv-autocrat-add-on/0de701dc5aa0%24ae2af3f0%240a80dbd0%24%40gmail.com.