Bot only queues records from Google Sheets
-
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
-
@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).
-
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
-
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.
-
@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.
-
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.
-
@pbretz Ok, that’s good to know. The last things to test would be whether it’s the
botcolumn itself, or the number of records that seem to be causing the issue. If you test with the full number of 8000 records but nobotcolumn, does it process correctly? If you do the reduced number of records, but have abotcolumn and Bot name in Templater’s Preferences, does that still work? -
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?
-
@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.
-
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.
-
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.
-
@pbretz Is the earlier version of AE running Templater 2.9.5, too?
-
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?
-
@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.
-
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.
-
@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.
-
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?
-
@pbretz Interesting. Thank you for testing so thoroughly and reporting back. That helps us narrow down things quite a bit. I don’t think that the data type of the “bot” column is having an effect. It’s possible that there is a bug in the way that Templater interfaces with the Google Sheets API, or it could be that there is an inefficiency in that querying code that is causing it to time out. If it’s a timeout issue, we have been working on optimizing that to speed up the process and eliminate that as a potential bottleneck. It’s possible that those optimizations could also tangentially address any bug problems, if there happens to be code that was causing an issue that gets rewritten. The good news is that these optimizations can happen on our end without you having to download and install an update to Templater. We’re firing on all cylinders to get an update to Templater out the door, soon, alongside some new supporting products, so we might not be able to get the optimizations published immediately. If separating the data into multiple worksheets is doable, at least temporarily, we’ll try to get a fix out and let you know as soon as possible.
-
Separating to different sheets is the approach I’ll take until I hear otherwise. Do you think using a JSON feed from MongoDB and having a bot field in a collection will also solve the issue?
-
@pbretz Yes, that would be an option. I’m pretty confident that it’s an issue specific to Google Sheets and not a “bot” value, generally.
