Smoothing out update lag between metrics sheet and dashboard metrics widgets

Rutledge

I need some help gang...

I've created a dashboard to display a snapshot of some relevant customer specific values that are stored across several sheets, but I am having some trouble with lag between form submission and updating values on the metrics sheet that feeds the many metric widgets on the dashboard.

Essentially, you should just be able to pick a customer, hit submit, and then refresh the browser to see updated values based on your selection...and that does * "work" now, but not as intended.

THE CRUX: *When a new form submission happens, the metric sheet sometimes needs to be manually refreshed for the formulas to run again and bring in the correct results. Even once that is done, I sometimes need to manually hit the SAVE button on the metrics sheet before a refresh of the dashboard displays the expected metrics. WHY?!

THE NEED: Some way to ensure that when a selection is made, and the form is submitted, that users do not need to do anything other than refresh the browser in order to see the expected metrics display as intended.

CONTEXT: To help skip past the part of these forum discussions where folks go back and forth about how things have been set up, what formulas are being used, etc. I have documented the entire thing for reference below.

This is a walkthrough of the process of customer selection and the immediately following metric widget value updates based on the selection made are designed to function.

1. First, the user will (1) type-ahead or scroll through the dropdown list to select a customer by name, then they (2) press Submit

This is a Smartsheet form, that has been embedded into the dashboard using the "Web Content" widget

2. After a selection is made, the message below is displayed.

The intention is to allow the user to simply Refresh their browser at this moment and have all the appropriate metrics fill in based upon the customer selected.

3. The form results populate onto this sheet after submission.

4. The column to the right of the selected customer's name is filled with a formula that denotes which row is the most recent.

=IF([Created Date]@row = MAX([Created Date]:[Created Date]), 1, 0)

5. A separate sheet houses all the customer metrics to be displayed using metric widgets on the dashboard. The Customer name cell is populated with the most recent submission from the form results sheet.

6. All the metrics on this sheet key in on the customer name field and return relevant values from other sheets. The customer Name cell uses the formula below to always return the latest customer name from the form results sheet.

=INDEX({Name}, MATCH(1, {Latest}, 0))

7. Back on the Dashboard, the user will have seen the message telling them to refresh their browser and after it refreshes, they are presented with a dashboard full of metrics relevant to the customer selection they submitted using the form.

The name displayed by the customer name metric widget will now match exactly the name that was selected using the form.

Likewise, all other metric widgets will now display updated values relevant to the selected customer.

These values all come from the same metrics sheet shown in the previous screenshot.

Paul Newcome

How much lag are you experiencing? When you run into this lag... Do you have the dashboard and underlying sheets all open, or do you only have the dashboard open?

Some lag should be expected because it has to push everything through on the back end. It has to register a new form has been submitted. Then your form sheet has to run the formula to update the "most recent entry" column. Then your metrics sheet needs to run a formula to register that the customer has changed. Then your metrics sheet has to run multiple formulas to update the metrics (feeding from yet another sheet) based on the new customer. Then your dashboard needs to register that the metrics have changed. It is quite a few steps to process on the back-end, and the most likely bottle neck is the metrics sheet having to update the numerous metrics now that it is evaluating for a different value (customer).

Paul Newcome

How much lag are you experiencing? When you run into this lag... Do you have the dashboard and underlying sheets all open, or do you only have the dashboard open?

Some lag should be expected because it has to push everything through on the back end. It has to register a new form has been submitted. Then your form sheet has to run the formula to update the "most recent entry" column. Then your metrics sheet needs to run a formula to register that the customer has changed. Then your metrics sheet has to run multiple formulas to update the metrics (feeding from yet another sheet) based on the new customer. Then your dashboard needs to register that the metrics have changed. It is quite a few steps to process on the back-end, and the most likely bottle neck is the metrics sheet having to update the numerous metrics now that it is evaluating for a different value (customer).

Rutledge

Ok, go ahead Paul! 👏 I appreciate your response for its thoughtfulness.

Here is the thing, I fixed this issue before the community even allowed this post to be published....

BUT ALSO - you totally 100% nailed it with this part of your response...

 the most likely bottle neck is the metrics sheet

Now a few answers to your direct questions:

1)How much lag are you experiencing?

The lag was approximately 45-60 seconds, averaged over a maybe 8 dozen attempts over several weeks. (However, that is only on the 2 machines and 1 very good internet connection I have access to).

I had others report that the time was much longer than expected for something that appears to be a basic selection process. I think that was the main thing... it was only about a minute (not bad for all the backend works, because you're right they do make expecting some amount working time seem very reasonable)., but something about what you are seeing and what you are getting makes it feel like a 4 minute "alright already" sort of minute.

2)When you run into this lag... Do you have the dashboard and underlying sheets all open, or do you only have the dashboard open?

Same lag no matter if things were open or closed, the time still ran close to that average of 45-60 seconds. I suspected this too, so all combinations of possible states of open or closed for all parts of this solution were attempted and ruled out as a source of the issue. (Not always easy, because I sometimes needed to go back and open the metrics sheet and manually hit save, but only like 1 in 20 attempts, very odd)

HERE'S WHAT I DID TO FIX IT

I made copies of the form results sheet and dashboard and then just put enough rows into the form results sheet to hold the stuff that was happening in the metrics sheet also - copy/paste. Presto. Then I just redirected all the metric widgets on the copy I made of the dashboard to the new form results sheet (now with metrics also😎). That was it. Now you just follow the directions that pop up after the form submission and refresh the browser, and as quickly as it can refresh, the metrics widgets all update relative to the selection made with the form.

It seems like the added backend time to pass the form results to the metrics sheet was involved here. The metric sheet does need some process time, but the real issue was that it was not starting to fetch that stuff until another sheet updated, so it was losing the race because it was just not starting to run at the fire of the gun. It kind of hung around and tied its shoes, and then took off running.😅

Another thing that I noticed in the first version was that it sometimes just failed completely, rather than lagging. I am not positive why, but it seemed like the metric sheet was not being prompted to refresh and then SAVE without the occasionally manual save intervention. It was sporadic, but I did have to open the sheet and manually hit save before the metric widgets would read the updated results from the metric sheet.

The new combined sheet not only reduced the lag time by eliminating a backend hand-off completely, but it's possible it may have also sured up reliability by having the metrics on the form results sheet. I have never had a form results sheet require me to manually save before the updated results could be accessed by either metric widgets or formulas. Maybe they are designed to do a more frequent autosave or something? Not for me to say, I don't make software.

Anyway, it works now and no longer feels psychologically incongruent to my co-workers, so its 👌.

Thanks again for being an all-star Paul...you literally nailed it!

PS. Since the last post had screenshots, I wanted to throw in one more of the sheet with the combined form results & metrics

Paul Newcome

I am honestly kind of surprised that there wasn't more lag when you had everything opened at the same time. I generally get better results with only the dashboard open and a very noticeable difference when the underlying sheets, etc. are opened (if I don't manually refresh browser tabs).

For future reference... One thing that can help speed things up is doing away with the checkbox on the form sheet and having forms populate at the top of the sheet. You can automatically grab the top row with an INDEX function referencing the entire column and just using a hard-coded 1 for the row reference portion.

=INDEX({Range}, 1)

This takes the checkbox calculation off of the form sheet entirely and essentially "locks in" where the source data is pulling from instead of having to try to match on a checked box.

Rutledge