Copy worksheet from client environment to my environment

PeggyLang
PeggyLang ✭✭✭✭✭

I have 'viewer' access to a client worksheet. We have a number of project managers working on the projects/locations which are on the client worksheet and I would like to create some reports that would aid our PM's in their scope of work. The only unique 'anything' on the client worksheet is the 'Row #'.

Is there a way to use datamesh to create an automated duplicate of the client worksheet in our environment, i.e., using row #? or some such.

or if anyone has any other ideas . . . ??

Thank you.

Answers

  • Hamza1
    Hamza1 Moderator

    Hi @PeggyLang,

    You can use DataMesh which is a premium app that maps your Smartsheet data across multiple sheets and reports. It provides a way to automatically copy and paste data to ensure consistency among one-to-many relationships. With DataMesh you can:

    • Automatically find and remove duplicates
    • Create links between sheets based on lookup values that you specify
    • Automatically populate empty cells in one sheet based on a known lookup value in one or more sheets (for example, if you have a source sheet that contains phone numbers for a list of people, you could use DataMesh to update a different sheet with those same phone numbers)

    You can create a DataMesh Configuration. Configurations are the workflows that bring data from your source (a sheet or report you have Viewer permissions on or higher) to target (a sheet you have Admin permissions on or higher).

    You must be a Licensed User and have DataMesh Premium Application permissions enabled in User Management by a Smartsheet System Admin, to use DataMesh. You need Viewer permissions or higher on any source sheet and Admin permissions or higher on any target sheet to create a DataMesh.

    For more information on DataMesh see the following help articles:

    About DataMesh

    Create a new DataMesh Config


    Cheers,

    Hamza

  • PeggyLang
    PeggyLang ✭✭✭✭✭

    @Hamza1

    Thank you. DataMesh requires a 'Unique Identifier'. There is not a unique identifier in the Client SS, i.e., there are a number of instances where there are more than 1 row for location #123

  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @PeggyLang

    You can use INDEX, referencing a column with numbers, to bring back content based on Row Number:

    =INDEX({Column}, [Row Number]@row)

    This would require you to set up a column that pre-populates the total number of rows that will be in the other sheet. Keep in mind that if rows are Sorted or moved around in the source sheet, your formulas will update to display that shift - meaning that if your PMs have entered data on Row 1, but a new row is put into Row 1 in the source, their data may now be associated with the wrong task/information.

    It would be best if you could as your client to add in a unique identifier (Auto Number, perhaps?) or combine a few cells to create something unique so that you could use Data Mesh or Formulas in a consistent way.

    Cheers,

    Genevieve

  • PeggyLang
    PeggyLang ✭✭✭✭✭

    @Genevieve P.

    Yes, simplest answer would be for client to create an 'auto number' column that I could use as unique identifier. Sadly, client is not very Smartsheet savvy and unwilling to do this. It's quite frustrating actually.

    Thank you for the response.