Bot only queues records from Google Sheets

pbretz

Hi,

I’ve got 4 bot licenses and recently went through and made sure all systems were updated with the latest AE 2020 release, and BOT 2.9.5. I have a data sheet of about 8000 records that I’d like to process with the 4 BOT enabled systems. All 4 systems will queue records but never process them. The render-status column and bot column are in between other columns in the spreadsheet. I’ve trimmed off empty columns and rows from the sheet. Any suggestions what might be causing this behavior?

Thanks.

Pete

Jon

@pbretz There is a bug in the Google Sheets API where if there isn’t an empty column at the end of the worksheet, the Bot will not process the data correctly. We plan to correct this soon (it won’t require updating Templater), but the workaround at the moment is to just add an empty column back into the end of the data.

For further troubleshooting if necessary, do the bot names in the column exactly correspond (case sensitive) to the Bot names in the Templater Preferences on each machine, and does each “ready” row have a bot name assigned? How many rows have you set to queue at once? If you reduce the number to 5 or lower, does it make a difference? Are there any error messages in the Status area of the Templater panel or in the templater.log file located in the same folder as the After Effects project file? Is the column called “render-status” or something different like “Render-status”? How have you set Templater to handle missing footage? Are you rendering with the Render Queue or replicating to Media Encoder?

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?