Automate data flow from Source to Target sheet (based on New & Edit criteria)

Looking to automate data flow from Source to Target sheet based on below criteria

New row: If new row added in source sheet then new row should be added to target sheet. I should able to config what columns need to move target sheet.

Update/Edit: if any row get updated in source sheet then corresponding row in target sheet should get updated if the primary key of source and target sheet matches. I should able to config what columns need to get updated.

Is it possible to automate above criteria, I looked into "Data Shuttle" and sheet automation features but no luck.

Thank you in advance.

Answers

  • Amit Wadhwani
    Amit Wadhwani ✭✭✭✭✭✭

    HI @PKotha

    I should able to config what columns need to move target sheet.

    Unfortunately, when you apply copy/move automation, all the columns are moved to the destination. Here is a workaround that I would propose.

    1. Create an automation which copies all the new rows (including all the columns) into the destination sheet.
    2. All the columns in the destination sheet, except the primary key should be hidden.
    3. Create new columns with same name as the hidden columns (with some variation as Smartsheet does not allow duplicate column names). You can limit the new columns to only those which you desire to be shown on the second sheet.
    4. In these columns, apply INDEX/MATCH formula to reference data from the source sheet based on the primary key.

    I hope this helps.



    Best Regards

    Amit WadhwaniSmartsheet CoE, Ignatiuz Software, Exton, PA

    https://www.linkedin.com/in/amitinddr/

    Best Regards

    Amit Wadhwani, Smartsheet Community Champion

    Smartsheet CoE, Ignatiuz Software, Exton, PA

    https://www.linkedin.com/in/amitinddr/


    Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 07/28/22

    Hi @PKotha

    I hope you're well and safe!

    To add to Amit's excellent advice/answer.

    Another option (depending on your structure and specifics) would be to only use cross-sheet formulas (VLOOKUP or INDEX/MATCH), so there is no need to copy the rows.

    You'd use an auto number column in the source sheet and then add a so-called helper column in the destination sheet where you'd add the number manually, and we use that to connect the rows.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • PKotha
    PKotha ✭✭✭

    Thank you Amit and Andree. Let me try both options and I will update how it went.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @PKotha

    Happy to help!

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!