Take Report Data and Populate Sheet - potentially Data Shuttle

Rebecca Cunningham

Hi,

Problem I am trying to solve - explanation below.

Smartsheet Report -} Auto Email Export (.csv)-} One Drive -} Data Shuttle -} Smartsheet Sheet with column equations-}PowerBI Dashboard

I have created dashboards in PowerBI that look at the sheets/reports I have in Smartsheet.

I have a report that is currently compiling and summarizing all our active project schedules and filters out some of the unnecessary information. I also have a "data" sheet that then applies formulas to the data to tag it so it will merge with the PowerBi widget. (See photo below)

I recently tried to use the auto send (.csv) file via email and utilized a 3rd party software that takes that email and puts it in a file in my onedrive. I was hoping to use this export feature to take the report data and then be able to use Data Shuttle to upload the data into my sheet that has the equations.

The issue I ran into was that Data Shuttle has to point to an individual file and the service I was using just added the latest version of the file with a slightly different name.

Is there an easier way to get this data from this report into another sheet so when the data changes the flow of information is automated and the end product dashboards have the accurate information?

Paul Newcome

Have you looked into Data Mesh? It can pull data from a report and output it into a sheet. It would remove the need to export/email/onedrive and replace the DataShuttle step.

Rebecca Cunningham

@Paul Newcome you are a genius across the community. I feel like if anyone is going to be able to help it would be you. Would you mind taking a peek at my problem?

Trying to get Data from my Report to a Sheet in order to keep the filters and combined data.

Andrée Starå

Hi @Rebecca Cunningham

I hope you're well and safe!

What is the other service that you tried? Have you looked into using Microsoft Power Automate (Flow)?

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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

Haha. Thanks for the faith in my "genius", but I am afraid I may disappoint this time around. I can say that even if it is a different version, as long as the file on OneDrive has the same name, DataShuttle will continue to pull from it. Aside from that I can't offer much more on this one. Sorry.

I do see though that @Andrée Starå has chimed in. I would think that if anyone has a solution, it would be him. He definitely has a lot more knowledge than I do when it comes to 3rd party apps.

Paul Newcome

Hold on... I just re-read your post. You are basically starting with a Smartsheet report and ending with that same data in a sheet?

Edited to update: If that is the case, have you looked into Data Mesh? It can pull from a report and output into a sheet.

Rebecca Cunningham

@Paul Newcome thank you for taking the time to review! Big fan of your work on the community boards.

Yes, my goal is to take information from a smartsheet report and get it into a smartsheet sheet.

The report helps filter out all of the non essential items, and then the sheet allows me to apply column formulas to tag the items.

Unfortunately I cant add columns to a report with formulas so I am in a bit of a pickle.

Paul Newcome

Have you looked into Data Mesh? It can pull data from a report and output it into a sheet. It would remove the need to export/email/onedrive and replace the DataShuttle step.

Rebecca Cunningham

@Andrée Starå Thank you for reaching out as well!

I have been playing around with Zapier and was using Email It In to email it into onedrive.

I will check that out and see if they offer any workflows.

I really appreciate the comments and hope you are safe and well!

Rebecca Cunningham

@Paul Newcome I have not, I will reach out to my account manager to learn more. Thanks!

Paul Newcome

Sure thing! Hopefully it works for you. I was trying to figure out the whole naming convention/etc. to try to stick with your current process and DataShuttle and didn't even think of Data Mesh until I had already replied.

@Andrée Starå What are your thoughts on DataMesh as an option?

Jeana

Hi all @Andrée Starå @Paul Newcome ,

I'm trying to do something similar but in my case there is not data in the target sheet that I can match with a lookup value. Is there a way to set up the Data Mesh so it just dumps all that is in a report into a sheet without using Lookup Values?

Jeana