Bot only queues records from Google Sheets

pbretz

Hi Jon,

Thanks for the info. I’ll add the extra column, and this should take care of the issue. I had originally processed a worksheet with 12 records, and had each bot process three records. This worked without issue. I’m currently trying to process 2000 records each. Columns are properly titled (case sensitive): render-status, and bot. Additionally the bot names are: boxx1, boxx2, boxx3, boxx4 in both the bot interface, and the spreadsheet. I think the additional column will make the difference.

Pete

pbretz

I added the extra blank column. I have one bot - boxx4 - that is setup now to render records 7501 - 8100 by setting the render-status column to ready. When I enable bot, It converts the first 20 records (7501 - 7520) render-status column to queued, however, the first record it begins processing is record 7001, who’s render-status was set to done. I let bot process a few more records, but it is processing records 7002, 7003, 7004, etc. I do have the Transport section set to start with record 7001 - 7500, but thought this was only used for Previewing records. Does this need to be set to the range of records I want bot to process?

Jon

@pbretz The transport values should be ignored for the purposes of the Bot, so we’ll need to figure out why it’s behaving that way. Can you start by reducing the number of items that are queued at once in Templater’s Preferences from 20 down to 5 or fewer? I don’t expect that to work necessarily, but it’s worth a shot. Can you also reset the log in the Templater panel, then try enabling the Bot again. Once it’s tried to process one or a few records, can you send the templater.log file from within the same folder as your After Effects project to support@dataclay.com (assuming that the data may be sensitive).

pbretz

Hi Jon,

I reduced the # of records to process from 20 to 5. When I restarted the job, it continued to process 20. I looked at the log file, and it indicated it was having trouble authenticating with Google. I signed out of the bot’s google connection. I also logged out of the browser that I had open on the same machine (Chrome) that was logged into the spreadsheet that was being processed. I then re-authenticated with Google and then re-enabled bot. It then queued 5 records at a time, but never processed them. I sent you the log file at support@dataclay.com.

Pete

Jon

@pbretz is the user account on each machine a full admin? If you run AE as an admin when you launch, does that resolve the issue?

pbretz

Hi Jon,

All machines are running from 1 online Microsoft Account with Administrator privileges. Should I create local admin accounts on each machine and run from those?

Pete

Jon

@pbretz It’s not unheard of that there may be differences between the “user” that runs AE and the account that is running when it launches. It’s difficult to know for certain how different environments are set up. Having a MS account instead of a local account shouldn’t matter, but with Windows it can still be a crapshoot to try to track down issues. If you right-click + “run as admin” is Templater able to continue processing with the Bot? If that doesn’t resolve the issue, then we might have to look at whether or not there is something about the project file itself that is interrupting the loop. If that doesn’t resolve things, you can reply to same support email with a log file that shows the preview process, as well as the aep file (the footage isn’t required, yet).

pbretz

So I’ve selected Run as Administrator when opening up AE. I then open up the project. Still no dice. Bot queues up 5 records, but never renders them. I’m going to send you the Log file. The first run I was getting a Google Authentication error in the log. I logged Templater Out of Google Drive, and then reauthenticated. After that authentication worked but still no rendering of records.

Pete

pbretz

Just to recap the items determined over the last couple of days:

Sheets need to have an extra blank column in order for bot to process rows.
Browser and Bot need to be logged out of any Google accounts, before authenticating Bot to work with sheets
4 Bots properly processed a sheet with less than 15 records. Bots have not been able to process a sheet with 8000+ records
Changing the quantity of records to batch in sheets from 20 to 5 has not corrected the issue.
Log indicates successful Google token obtained and sheet accessed. After queueing records, though log indicates there are no records to process.
Running AE 2020 as administrator and then opening the AE project does not correct the issue.

Jon

@pbretz The only thing I see with the AEP that you sent is that the audio track is throwing an expression error regardless of which expression engine I select for the project. That shouldn’t cause this issue with the Bot. I am curious if you were to split the records from your sheet into separate worksheet tabs for each Bot, point each one to its designated worksheet, then remove the “bot” column and Bot name from the Templater preferences, if that makes any difference. We might be able to narrow down whether this is a bug with the Sheets API or with Templater or with something else.

pbretz

This works. I duplicated the sheet and then removed all rows to be processed by other bots, and left only those for 1 bot. I then removed the bot column, and removed the bot name from the Preferences panel of the bot that would process them. Processing is working as planned.

Jon

@pbretz Ok, that’s good to know. The last things to test would be whether it’s the bot column itself, or the number of records that seem to be causing the issue. If you test with the full number of 8000 records but no bot column, does it process correctly? If you do the reduced number of records, but have a bot column and Bot name in Templater’s Preferences, does that still work?

pbretz

So to date, I’ve run an enabled bot with no bot column, just render-status, and successfully processed about 1500 records in the Google Sheet I’ve been working in. Before processing the large worksheet of about 8000 records, I had setup a worksheet with about 15 records, render-status and bot columns, and used 4 bots to successfully process all records. I haven’t tried to determine a maximum number of records before bot processing failed. Sounds like that’s my next step?

Jon

@pbretz Yeah, that seems like the best next step. It can help direct us where to look for any problems in the code. If it can do 1,500 with the Bot column (only one bot), vs. if it can do 8,000 with no bot column. It’s difficult for us to nail down these types of issues sometimes because each dataset is going to be different in the number of rows and columns, and also the volume of data that each cell contains.

pbretz

I have another project - AE 2019 - I just ran. One machine running bot on a different Sheet as previous project. render-status column set to ready. When I enabled the bot, record status changed immediately to processed, not queued. In fact, there was no queueing of 20 records. Processing ran fine, but then after processing all records, processing began processing records already marked as ‘done’. Oddly enough, the date / time stamp on the rendered files is 1 week from now. The computer’s date / time reflect the current date and time.

pbretz

Also, the records being processed are rows 7404 - 7741. The worksheet is all values, no formulas. Now that I have some time, I’ll try some smaller size worksheets for processing to see if there is a problem associated with larger worksheets.

Jon

@pbretz Is the earlier version of AE running Templater 2.9.5, too?

pbretz

Earlier version is running 2.9.5.

Our Sheets are created in our G-Suite for Business account. I was going to create a Sheet in my own personal gmail account and see if the behavior changes. Maybe this is a Sheets permission issue?

Jon

@pbretz I don’t think G Suite vs. personal Google account should matter, but I can’t totally rule it out, either. I’ve never run into an issue, myself, in using both types of accounts. My hunch is that there might be an unreported timeout happening when the API tries to grab the data. Technically, if you set your queue-at-once value to 5 or 10 or even fewer, it shouldn’t matter if you have 20,000 records in your Sheet.

The timestamp on the files being so off is very strange, and possibly separate behavior. I’m not even sure how that could happen, since that is an operating system function, and I don’t believe that Templater should have any ability to affect that.

pbretz

I tried a different setup of the Sheet on my end and looks like I’ve got a working sheet now. My goal was to have a sheet that did not include any calculations.

Typically the Sheet files I setup will have multiple worksheets with data, some worksheets with lookup tables, worksheets based off of the data and lookup tables, etc. I copied the manipulated data sheet I wanted to use as my feed and pasted values into a new Google Sheet with just 1 worksheet. It successfully ran with a bot column and a render-status column. It ran through about 135 records without any issue. One other item the Sheet did not have was a Google Sheet script I use to determine whether the rendered video file has been uploaded to AWS. While I don’t typically use the script in the worksheet Templater is processing, the script exists in the Google Sheet, and is used in other sheets in the Google Sheet. Maybe this has been causing timeout issues?

The standalone Sheet that successfully ran does not include that script. I’m going to try running a sheet with more records - ~1500 to see if there is any issue with length. I’ll also try with multiple bots.