Automation makes a whole new row?

Ty Werven

Some background:

So I have a sheet that has conditional formatting and cells get pulled to a dashboard. Here it is…

The user just types in each cell the number of visitors for each option and it turns different numbers in to different colors as you can see. They then save it and the numbers get sent to a dashboard.

Here is my question. I have this automation attached that will make all numbers changed to '0' at 6AM each morning.

It appears that this is working well, but the problem is sometimes it will run and create another row such as this. Why does it work sometimes, and other times it will duplicate the entire row when refreshing?

Ty Werven

@Matt Lynn-PCG @Adam Murphy

Thanks for the help.

Is this what you guys are meaning? I just added the middle condition. Not sure if I should set Where - Primary Column - is one of…. or something else? Let me know if I misunderstand. I think you guys are kind of getting at the same thing. Thanks.

Matt Lynn-PCG

@Ty Werven This is an interesting one… but thankfully I know why… at least I think.

So you have to think with two brains here… the UI and the backend of the software because they're not the same.

1.) You visually see 50 rows on your sheet… that's the UI (user interface)… you can't have less than 50. You can delete them but when you hit save or refresh it'll show 50. This is the problem… you think because your eyes see 50 that there are 50 there… there's not.

2.) The backend of the system only has or registers the rows that have or have had data in them. In your case it only has 1 row of data and in code only has 1 row on the sheet. The other 49 rows are fake UI rows.

The problem: When you delete those other rows and only have 1, you're good. But when a user goes in and clicks or does anything on those other rows accidentally or otherwise, it registers a new row(s). You can see this happen if you add a auto number column and save. You'll see one auto number populate. Then click and add/remove data on other rows.. any row like 4 or 10 or 50. Those rows will get auto numbers. So what's happening is not the automation, it's the back end. Somehow new rows are being registered throughout the day even though it looks like no data is there. Then when the automation runs it sees all the rows that are there and turns them to zeros.

FIX: Add a condition to your automation that only makes that change on rows where it says "Visitor Count" in the primary column. If you feel like that could change add a helper column with text (maybe "automation") and hide it, then add that as a condition. Now you'll still have new rows appear in the backend, but your UI won't show it and the automation won't do anything to them.

Adam Murphy

I don't think the automation is adding the row, but it is "seeing" it and populating it, if that makes sense. Is there an additional filter you can add at such as the Primary column is not blank? Then the "extra rows" won't be touched by the automation. Make sense?

Ty Werven

@Matt Lynn-PCG @Adam Murphy

Thanks for the help.

Is this what you guys are meaning? I just added the middle condition. Not sure if I should set Where - Primary Column - is one of…. or something else? Let me know if I misunderstand. I think you guys are kind of getting at the same thing. Thanks.

Matt Lynn-PCG

@Ty Werven Yes that should work.

Adam Murphy

@Ty Werven Agreed, that should work.