Data mesh: Merging between sheets

I have 3 sheets: 2 of them have their own forms to collect data, and 1 master sheet. The 2 individual sheets have questions which are similar to each other and questions which are completely different (they are 2 forms of questionnaires). The master sheet is used to merge the answers of the similar questions and all other answer into one place (so it has the answers to both sheets).

I used DataMesh to update and overwrite the data for each person depending on which form is filled out. But I've run into a problem where when the data mesh updates, it updates everything into either one of the form instead of the most recent update for each row.

As in, each row is a different person. If person 1 filled in Form 1 first. The data from Form 1 is datamesh'ed to the master sheet for the corresponding columns of form 1 and the columns that are similar to form 2. Couple days later Person 1 filled in Form 2 and this is datamesh'ed to master sheet for the corresponding columns of form 2 + overwrite (if needed) the similar questions from form 1 columns (so the answers are the most up to date). The problem happens when, if Person 2 did Form 2 first and then Form 1, after updating through datamesh, both person will show the overwritten data (or updated data) from either form 1 or form 2. I want there to be a division in updating—as in, only update if there is a change, and update to the more recent entry for each individual row.

Any ideas?

Find more posts tagged with

Sheets

Automations

DataMesh

Cell linking

Accepted answers

All comments

Jason Tarpinian

So if I'm understanding, you have 2 forms where some questions overlap, and you want to take the most recent of those overlaps? If so, read on, if not, my apologies for not understanding.

So if I'm understanding, what you'd want to do first is create another sheet where you copy rows to automatically as they are added to your (2) form sheets. So you would have something that looks like below.

And now you have a list of all the data that you need to report on, you just want to pull the latest answer from each question? Knowing that the newest data will be on the bottom of the sheet, we can use an INDEX(COLLECT to pull the value that we want.

=INDEX(COLLECT([Q2]:[Q2], [Form user]:[Form user], [DataMesh Sheet - Create User Names]@row , [Q2]:[Q2], <>""), COUNTIFS([Form user]:[Form user], [DataMesh Sheet - Create User Names]@row , [Q2]:[Q2], <>""))

So what that formula says is we want to create a range of the Q2 column, where Form user is Jason, but only where the answer wasn't blank, giving us 2 values. The COUNTIFS just does that second part then to get us the latest answer, of those 2 fields that match our original criteria, return the 2nd value.

If this isn't exactly what you were looking for, hopefully it could at least give you some ideas on how to proceed. Good luck!

Essieoy

It sounds like the overwrite is happening because DataMesh is not checking which form entry is more recent. One workaround is to add a timestamp column to both Form 1 and Form 2, and then set your DataMesh update rule so that only the latest timestamp per person is merged into the master sheet. That way, if Person 2 fills Form 2 first and later Form 1, the system keeps the latest response rather than overwriting with the older one.

Babitha

Hi Essieoy, Thank you for replying!

I have a date column in both forms (filled in by the form), but there isn't an option in DataMesh to add a rule persay.

Would you be able to expand on how to do this please? Will it be an automation?

Below is a picture of how one of my DataMesh config looks like for reference.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.