Bot only queues records from Google Sheets
-
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.
