automate a download of a sheet

Dear Community: Please be gentle...I am new to the community. Our application uses forms to have people add row to various sheets. On one of the sheets we need to upload to one of 1000 systems as people continue to add to the sheets. I thought I could create a rule or action that would allow me to automatically download the master sheet so that I could send it out to the network servers to keep them all in sync.

What I am looking for is a way to download a sheet in a xcel .cvs file format to my dropbox when a item is added or changed.

I could stand to do it one time a day but would rather automate it to update as new items are added or changed. Any ideas?

Find more posts tagged with

Sheets

Services

Support

Importing and Exporting

Comments

Marcin P

Hi Tom,

If you'd like to have a master excel sheet that automatically pulls data down from your smartsheet sheets, you can do it by using Power Query add-on within excel and Smartsheet Live connector or with special code for PQ that not requires installation of that connector.

I have planty of excels that works this way.

It creates table that can be refreshed via right-click menu, but can also be automated with really simple VBA code to make it for you for example every hour. Then it can be source of information for your other files.

tom55166

So you just blew past most of my abilities...lol. If I understand your response correctly, excel has an option to use power query which can be automated. I will look into that...I assume you can direct the output file to a specific file like dropbox or some other place.

Thank you so much for your help. My IT partner suggested a linux/unix command line to pull the file down in mass and work with it in that way.....love the product.

Andrée Starå

Hi Tom,

Something could probably be set up with Zapier or similar. Rows could get added to a Google sheet for example and exported automatically.

Would that work?

I hope this helps you!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Marcin P

Yes, power query can be set up with code that automatically connects to your sheet and pull data from it, but I never tried to auto save into one drive. Of ourse Sheet ID and token has to be placed within. I can send it to you via email if you like.

Best regards

Marcin

Mike Wilday

How come you don't use Smartsheets as your mastersheet? Their mobile app could be used by your field workers to access the data. If you need to restrict or minimize the data to just those tasks a field worker is associated with you could use a report, that would always deliver the most current data to the people in the field. I might be misunderstanding your use here, but it seems like it would be best to keep all the data in Smartsheet and share it out, rather than create copies of the data that would go out to your workers.

Here is some more information on creating reports.

https://help.smartsheet.com/articles/522214-creating-reports

https://help.smartsheet.com/videos/smartsheet-reports

PhillyPepboy

Hello Marcin p

can you send over that query so I can test it? Any help would be appreciated.

Email at gaetanodisalvo@comcast.net

PhillyPepboy

Can you send query over to me?

email at gaetanodisalvo@comcast.net

Mike Wilday

Can you email that query to me as well? I'd love to play with it too!

PhillyPepboy

To All,

If you want to AUTOMATE the download of SmartSheet data into your EXCEL.

Follow these steps:

1) Create your smartsheet. - Save it

2) Use the little globe on the right hand side to publish the sheet.

3) When doing a PUBLISH, you are creating an HTML version. Get the LINK

4) Get the Link first OPTION, Read Only- HTML Set to ON. Then Click the Link to receive the Link to use. Example: https://publish.smartsheet.com/asduasiuerhdfh

5) Open EXCEL

6) Go to the data tab to get into POWER QUERY by selecting GET DATA dropdown then Get Data From WEB

7) A Pop up for the Data from WEB. Paste the URL of the HTML Smartsheet HERE

8) Generate it and it will show up in a NEW SHEET in EXCEL.

NOTE: This is one direction only, from smartsheet to EXCEL. But it will be real time. If you make a change in Smartsheet, then go to Excel and HIT DATA then REFRESH

Paul Rose

https://community.smartsheet.com/discussion/comment/112546#Comment_112546

Marcin P. Would like to see the power query excel code to connect to Smartsheet. Could you send me a example file at paulyprose@gmail.com? Thanks

Don Wilcox

I know these were a bit of an older post, but I just found it.

How can you adjust the Power Query when the sheet owner hides a column? the published URL does not show the hidden column, but PQ is, of course, picking it up.

Sign in to join the conversation!

Quick Links

Trending discussions

Set Up Links for Board View
In my grid view, I added a column that takes a hyperlink but when I mosey on over to Board View, only some of the columns appear as attributes displayed on the card. Can I make the hyperlink appear here?
Sorting not working?
Hi. I have a sheet where I am sorting dates by ascending order. But the sorting functionality doesn't seem to be working. Instead of bringing all items with date values to the top of the sheeting and sorting them in ascending order, it leaves a gap. Is it a result of the "Next Review Due" column containing a column formula…
Automatic Unalignment of Dashboard Widgets
Is anyone experiencing their dashboard widgets automatically being unaligned or they have moved a bit from your last save? I am the type where I like things clean and aligned. Some may say it's OCD. In Dashboard, this can mean the edges of my widget line up, each section is centered or same purposed widgets are the same…