Data Mesh scheduling - new row in the target sheet

Phillip S
Phillip S ✭
edited 06/14/22 inAdd Ons and Integrations

I have a configuration with Execution Frequency = Immediate and Data Mapping Format = Create Cell Links. When I add a new row to the target sheet, mapped cells do not get their link in the new row. If I change the schedule to 1 Hour, links are added.

Q: If all my mapping is Create Cell Links, then I don't need a frequency of immediate since they are links, right? Meaning, data in the target sheet will always reflect what's in the source sheet whether the configuration is running or not.

Q: Is there any way for a new row to get data immediately from the configuration or is hourly the best available?

Tags:
· Share on Facebook Share on Twitter

Comments

  • Anthony M.
    Anthony M. Employee Admin

    Hello Phillip,

    Thank you for reaching out. I'm happy to help with your Data Mesh questions.

    Q: If all my mapping is Create Cell Links, then I don't need a frequency of immediate since they are links, right? Meaning, data in the target sheet will always reflect what's in the source sheet whether the configuration is running or not.

    A: The frequency in this particular situation would be for adding any new rows to your target sheet. The already created cell links, as you state, will always reflect what's in the source sheet once they have been created.

    Q: Is there any way for a new row to get data immediately from the configuration or is hourly the best available?

    A: Update immediately is available for configs built off a source sheet. If your config is built off a report, update immediately will not be an available option. If the config happens to miss the update immediately window, for any reason, it will update on the next hourly sync. If you are seeing any issues with update immediately not working as expected, please feel free to reach out to our support team directly. There will be some additional troubleshooting and information we will need to collect.

    Thank you for your time, Phillip. Please don't hesitate to open a ticket with us, for further troubleshooting of your Data Mesh configs.https://help.smartsheet.com/contact

    Kind Regards,

    Anthony

    Smartsheet Support

    · Share on Facebook Share on Twitter
  • PeggyLang
    PeggyLang ✭✭✭✭✭

    @Anthony M.

    Hello Anthony, there is a statement in one of your answers above that is confusing me;

    'The frequency in this particular situation would be for adding any new rows to your target sheet'


    In my data mesh situation my 'Source Sheet' contains a complete list of store locations. My 'Target Sheet' contains a list of store locations ONLY where a certain column has data.

    Data on the Source Sheet is updated as and when data is available - meaning that all columns are not populated at the onset.

    My issue seems to be when one of the store location in the Source Sheet receives new input in a column that is reflected on my Target Sheet that new store location is not added to the Target Sheet and therefore the data is not coming through. Data will come through if I manually add the new store location. Is there a way to get the new store locations added to the Target sheet automatically?

    Thank you!

    · Share on Facebook Share on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    Hey@PeggyLang

    Can you post a screen capture of your DataMesh settings?

    It sounds like you may want to have "Copy andAddData" as the option selected:

    Screenshot 2023-04-04 at 14.16.31.png

    This will mean that when you add a NEW Store location it will be brought over. Is that what you're looking for?

    Cheers,

    Genevieve

    · Share on Facebook Share on Twitter
  • PeggyLang
    PeggyLang ✭✭✭✭✭

    Sort of -

    The source sheet has ALL the store locations. The target sheet ONLY needs the store locations where Column 'X' has $'s greater than 0.

    I was previously using a 'copy row' when 'Column X' is updated AND when 'Column X' is greater than 0. What I found was that this was not always being triggered. As well the data in 'Column X' is sometimes being updated beyond the initial data entry (I did not anticipate this) so I pivoted to Data Mesh, but couldn't figure out how when 'Column X' on source sheet was initially completed would that specific row get added to the Target Sheet. Making any sense???

    我已经更新了数据网格的副本和添加数据' and will monitor if this works for me. My concern now is when 'Column X' is updated will the update transfer to Target sheet.

    Thank you for the prompt response!!

    · Share on Facebook Share on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    Hi@PeggyLang

    Ah thanks for clarifying. You're right, in this case I would also use the Copy Row action to initially bring over the filtered version of "Column X" (only the one with a value greater than 0) so your matching value exists in both sheets. Then use DataMesh to keep the rows updated and linked together.

    Can you explain a bit more about your original Copy Row automation and when it wasn't always being triggered? Is the trigger manual, or through a formula?

    · Share on Facebook Share on Twitter
  • PeggyLang
    PeggyLang ✭✭✭✭✭

    Sometimes my 'copy row' trigger in the source file does not trigger and I really don't understand why? Thoughts?

    · Share on Facebook Share on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    Hi@PeggyLang

    What action are you taking to trigger it? Are you using any formulas or cell-links, or is it a manual action?

    · Share on Facebook Share on Twitter
  • PeggyLang
    PeggyLang ✭✭✭✭✭

    My trigger is below;

    image.png

    It definitely does not always trigger and I do not understand why not.

    · Share on Facebook Share on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    Hi@PeggyLang

    Thanks for this screen capture!

    So I can see that the trigger is when "GC Cost" changes to something that'sgreater than 0.

    • Is it possible that content in your GC Cost column is sometimes entered as Text instead of Numerical? (Which would then be filtered out).
    • Are there any formulas in this column?
    · Share on Facebook Share on Twitter
  • PeggyLang
    PeggyLang ✭✭✭✭✭

    No formulas in this column

    This column isALWAYSpopulated by way of a Data Mesh. Value comes from individual Budget SS's.

    Column type is 'Text/Number' and column as been formatted as 'United States, Dollar (USD)

    image.png


    image.png


    · Share on Facebook Share on Twitter
  • PeggyLang
    PeggyLang ✭✭✭✭✭

    @Genevieve P.

    · Share on Facebook Share on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    Hey@PeggyLang

    My apologies for the delay!

    Thank you for this information, I'd like to test this myself but first can I check how your Data Shuttle workflow is set up?

    你澄清在工作流那吗t the values should be numerical?

    Screenshot 2023-04-12 at 19.19.06.png


    · Share on Facebook Share on Twitter
  • PeggyLang
    PeggyLang ✭✭✭✭✭

    @Genevieve P.I am using Data Mesh, not Data Shuttle. I don't see the option to clarify the 'column type' in Data Mesh??

    · Share on Facebook Share on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    Ah my apologies! Of course.

    Can you clarify if the values are showing up on the Right or the Left side of the cell?

    · Share on Facebook Share on Twitter

Categories

Sounds about right. Are you looking for some kind of guidance on a specific issue?<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":343,"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations","allowedDiscussionTypes":["discussion","question"]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":104380,"type":"question","name":"Questions about Bridge from a potential newbie","excerpt":"We are considering upgrading to Advance, and I have questions about Bridge capabilities: Our project blueprint includes multiple reports that pull entries from master logs based on the job number. This means that our admins have to add the job number to every single new report filter when they set up a project. Can Bridge…","snippet":"We are considering upgrading to Advance, and I have questions about Bridge capabilities: Our project blueprint includes multiple reports that pull entries from master logs based…","categoryID":343,"dateInserted":"2023-04-24T16:40:19+00:00","dateUpdated":"2023-04-24T17:41:23+00:00","dateLastComment":"2023-08-10T13:32:30+00:00","insertUserID":126351,"insertUser":{"userID":126351,"name":"Julie Fortney","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Julie%20Fortney","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hZje4WgcMyQ!en9eZ1QFHfo!MFtz_rjuwYj","dateLastActive":"2023-08-11T13:16:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":126351,"lastUserID":157551,"lastUser":{"userID":157551,"name":"joaolima","url":"https:\/\/community.smartsheet.com\/profile\/joaolima","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wjEmP-PDuHo!29cn_0Oa5bo!j4rsSRTRcJb","dateLastActive":"2023-08-13T22:58:07+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":265,"score":null,"hot":3374032369,"url":"https:\/\/community.smartsheet.com\/discussion\/104380\/questions-about-bridge-from-a-potential-newbie","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/104380\/questions-about-bridge-from-a-potential-newbie","format":"Rich","tagIDs":[369],"lastPost":{"discussionID":104380,"commentID":390004,"name":"Re: Questions about Bridge from a potential newbie","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390004#Comment_390004","dateInserted":"2023-08-10T13:32:30+00:00","insertUserID":157551,"insertUser":{"userID":157551,"name":"joaolima","url":"https:\/\/community.smartsheet.com\/profile\/joaolima","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wjEmP-PDuHo!29cn_0Oa5bo!j4rsSRTRcJb","dateLastActive":"2023-08-13T22:58:07+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-04-26T14:21:28+00:00","dateAnswered":"2023-04-26T02:30:08+00:00","acceptedAnswers":[{"commentID":373252,"body":"

Hey @Julie Fortney<\/a> <\/p>

I'll answer what I can and shout out to @Brian_Richardson<\/a> for back up. <\/p>

#1 Report Filters- To my knowledge there isn't a direct Report Filter update however I wondered if the column was a dropdown list - if so, Bridge can update dropdown lists and if the filter was set to select all it made me wonder if the filter would pick it up new selections. (Datashuttle also updates dropdown lists if this method works)<\/p>

#2 I've had problems with a slow sheet erroring out in Bridge. The sheet was massive and Bridge would time-out. Bridge did not help this sheet (it might have been too large for anything to help)<\/p>

#3 -I don't have any experience with Control Center, so sorry, I can't answer those questions<\/p>

My fav Bridge solutions?<\/p>

-Capturing Approver Names in approval workflows<\/p>

-Adding rows - same sheet, different sheets, Child rows to Parents, On a weekly cadence, Adding New rows of acquired data to a status update sheet which feeds into a dashboard report<\/p>

-Translating Employee Ideas from their native tongue to English<\/p>

-Checking FedEx Tracking Status on a sheet's worth of shipments<\/p>

I hope this helps<\/p>

Kelly<\/p>"},{"commentID":373322,"body":"

Hey @Julie Fortney<\/a> !<\/p>

  1. In general you cannot update reports with bridge or the API, you can only get report information and details. So unless you are able to come up with a different solution you may be out of luck. Although, if this is a control center program, you may be able to use \"Dynamic Reports\" which adds new sheets to reports every time a project is provisioned (and removes as projects are archived). <\/li>
  2. Maybe using JavaScript in bridge would help, hard to say. <\/li>
  3. Unfortunately not.. You can not programmatically retrieve a form link (Big functionality gap in my opinion). We have begged Smartsheet to dynamically update forms with control center. Also you can not update the contents of widgets in dashboards with bridge or the api.<\/li>
  4. You could definitely set up a solution to achieve this. It would involve using a summary sheet in your blueprint where the link to your template sheets is profile data on your summary sheet. From there you can get the sheet id for each project in bridge, loop through them and make updates.<\/li><\/ol>"},{"commentID":373373,"body":"

    @Kelly Moore<\/a> thanks for the shout out :-) but I'm not sure I can help more than what's already been posted. <\/p>

    @Julie Fortney<\/a> in general Bridge is an API tool. You can setup multi-branched workflows in Bridge with pre-set API calls and utilities. You can also do custom HTTP calls to any API that you have access to in the cloud. So anything you can do with API calls you can do with Bridge. It also, importantly, allows you to run Javascript code using the results of your API calls, and to trigger workflows on changes to Smartsheet content or on any other webhook that you can receive from other tools.<\/p>

    There's a fair amount of Smartsheet API methods that are prebuilt into Bridge, but not all of them. So if you're looking at the Bridge documentation for Smartsheet<\/a> integrations and not finding what you need, you can also look at the Smartsheet API documentation<\/a> to see if there's something there to help you out. If you find an API method to use that's not already pre-built as an integration, then you can use the Custom HTTP Call step to directly call the API. You just have to provide the Smartsheet API with an API token, which you can assign to yourself in your Personal Settings.<\/p>

    It looks like your specific questions were answered pretty comprehensively by Kelly and Samuel but here's my 2c:<\/p>

    1. Because you cannot adjust report filters using the Smartsheet API, you cannot do this with Bridge. Here's the Reports methods<\/a>. You might want to consider a workaround for the process of having reports hand-updated with new job numbers. Perhaps leverage a creation date or flag new items with a checkbox that's cleared once the conditions are met that would cause the project to fall back off the report? You can also explore the \"contact has any of current user\" filter to pick up specific jobs for the viewer?<\/li>
    2. You could definitely use Bridge to read one sheet on a schedule or trigger, perform calculations using Javascript or the included utilities, and post the results. It may be faster and more stable than a huge number of formulas in Smartsheet, but I'm not 100% sure it would be. I would probably first explore using Data Mesh, which is setup to do this in a much more friendly way. If you don't already use Data Mesh, it's basically a batch index\/match tool. <\/li>
    3. As Samuel said, there's no API for updating widgets on a dashboard or retrieving a form URL. Also, I tested copying a dashboard and sheet in a common folder, where the form link was embedded in the dash, and unfortunately it does not update the form link. Seems like a gap and would be a good enhancement request. You could potentially work around this by having a common sheet with the form to capture entries, have a dropdown in that form for the project name that you can keep updated with Bridge, do a lookup to the sheet ID that the entry should go to on a sheet that Bridge maintains, and then copy the form responses into the individual project sheets using Bridge. I know that's pretty roundabout.<\/li>
    4. As Samuel and Kelly said, you can push dropdown updates through Bridge, it works better than Data Shuttle. You wouldn't need to maintain a separate lookup sheet though, if you create a report that contains the dropdown columns across your various created sheets, and then read that report with Bridge, the return from the API includes the source sheet ID and column ID for each report row. You can then leverage that to point Bridge at the right columns across your sheets without having to maintain a separate lookup.<\/li><\/ol>

      At Iron Mountain we use Bridge for a variety of use cases, here's the most effective ones:<\/p>

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video