Bot doesn't work, formula does

[Willem]

Hello,

Since a while I have a bot running that no longer works. This is a bot that checks whether a retention period has expired based on the stored date (timestamp) and then he deletes the row.

When I test the formula in the formula window it shows the correct rows.

OR(AND([date]<=TODAY()-30, [retention period] = "30 days"), AND([date]<=TODAY()-90, [retention period] = "90 days"))

When I test the bot, it does not show any rows (records). What am I doing wrong? I've already deleted and recreated the bot, but it didn't help.

There is a security filter on it and a number of shielded (PII) rows.

The strange thing is that the bot has always worked and hasn't worked for about 3 months

Thanks in advance for thinking along.

[1minManager]

Couple of suggestions:

1. Set the bot to ignore security filters
2. You'll need to change your formula if it or the securty filters is using UserEmail() as a bot doesn't login with an email

[Willem]

The bot ignores the security filter.
The security filter is indeed set to filter by email address, but because the ignore option is turned on, it bypasses the security filter?

[1minManager]

Sorry I meant to say that you're using UserEmail() in the trigger formula or anywhere else in the bot

[1minManager]

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Best-way-to-handle-table-security-settings-when...

[Willem]

Thanks for the solution, i'll try it!

[Willem]

Each user has all rights, only the records are filtered out by email address in the security filter. I have several other bots that work the same way and they don't experience any problems.

[1minManager]

So assmuing its not the security filter and you've checked the "ForEachRowInTable" in the trigger, then my hunch is its a <<START: command issue in the template.  

But its a bit hard to help when you're not showing us the formulas you're using...

[Willem]

This is my security filter: 

And the BOT:

With the formula: OR(AND([date]<=TODAY()-30, [retention period] = "30 days"), AND([date]<=TODAY()-90, [retention period] = "90 days"))

He will delete the found rows.

[1minManager]

So I'm guess that you feel that there are rows matching the filter formula, yet when you manually run this bot they are not being deleted?

In that case your going to have to do some troubleshooting to try and work out which bit isn't working.  Firstly try changing the security filter to:

OR(
   CONTEXT("Host")="Server",
   [adviseur]=UserEmail()
) 

This will allow the Bot full access to every row dispute the Bypass... option being turned on.

Test the bot

Next create a new row in the app and in the spreadsheet note its row number.  Then in the bot change the filter condition to be [_RowNumber]=XXXXX.  So the action is set to run specifically on this row.

Test the bot again

Finally also check the delete action that you haven't specified a "Only if this condition is true" formula

Run this and let me know what you find.

[Willem]

I modified the security filter and now it shows a lot more results when testing, including the correct ones. The same goes for [_ROWNUMBER]. If the condition matches, it returns true.

What do you mean by if the outcome is true, what else should I put in the action? Now he deletes the row.

I'll check tomorrow if the bot worked, my working day ends and I can pick up my kids 🙂

[1minManager]

This part of the actions settings

[Willem]

The action condition is true.
The bot did nothing last night, and the security filter no longer works. It no longer filters by email address. I have reset the security filter to the old settings.

What else can I do?

[1minManager]

So without actually logging into your app and diagnosing this myself, I can't think of anything else to suggest to directly fix the issue.  I have clients I work for so if you want this service, please DM me.

Other than that, I think you're only option is to recreate the entire bot from scratch adding one bit at a time and confirming at every stage that it's working as intended.  That way eventually you'll find it not working and be able to indentify the problem as the last thing you added.  Sometimes with problem solving, there is no quick solution 😕

[Willem]

Thanks for thinking along, I'll look into it again and if I find the problem I'll share it.

[Willem]

I have already found the problem, it contains 3000 rows and that is too heavy for the bot. If I delete 2800 rows, the bot works normally. All data is in an excel sheet. Does it make sense to split this up?

[1minManager]

Ahh yes, so you're hitting a 300secs timelimit.  If it goes past this it just sliently fails and does nothing.  So you have to either reduce the number of rows, make the columns more efficient by simplifying or removing virtual columns, or split the bot in 2 so maybe you do the odd rows then the even rows.

[Willem · Accepted solution]

I tried to filter them into even numbers and odd numbers (MOD()) but that didn't work either. I made the table into partitions and divided the data over several tables, this turned out to work in the end 🙂 Thanks for the help and thinking along!