Bot only queues records from Google Sheets

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.

Jon

@pbretz thanks for the update. That’s interesting that it could be related to formulas or Google app scripts. That gives us a potential direction to investigate.

pbretz

Latest Update: Running a Google Sheet with 25000 rows. Manipulated data and then copied entire sheet and pasted as values to an entirely new Google Sheet with 1 worksheet tab, and no scripts or calculations. Worksheet has a column labeled ‘bot’, and a column labeled ‘render-status’. I assigned 1st 4000 records to bot1, 2nd 4000 records to bot2, 3rd 4000 records to bot3 etc. Same behavior as before - render-status column changes to queued, but nothing ever gets processed.

I then copied the sheet to 5 worksheets for a total of 6 worksheets, each with a bot and render-status column. Worksheet 1’s bot column had the value bot1, worksheet 2’s bot column had the value bot2 etc. Same behavior

I finally deleted the bot column (and the bot name in the preferences of all 6 bots I’m running), and enabled all 6 bots. Records are processing as expected. So it looks to me like the bot naming mechanism is not working correctly. Is there a data type I should be assigning to the bot column? I typically leave it as ‘Automatic’. Should it be set to ‘Text’? Any suggestions?