automate a download of a sheet

tom55166
tom55166 ✭✭
edited 12/09/19 in Smartsheet Basics

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? 

Tags:
· Share on FacebookShare on Twitter

Comments

  • Marcin P
    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.

    · Share on FacebookShare on Twitter
  • tom55166
    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.

     

    · Share on FacebookShare on Twitter
  • Andrée Starå
    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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

    · Share on FacebookShare on Twitter
  • Marcin P
    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

     

     

    · Share on FacebookShare on Twitter
  • Mike Wilday
    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

    · Share on FacebookShare on Twitter
  • PhillyPepboy
    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

    · Share on FacebookShare on Twitter
  • PhillyPepboy
    PhillyPepboy ✭

    Can you send query over to me?

    email at gaetanodisalvo@comcast.net

    · Share on FacebookShare on Twitter
  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

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

    · Share on FacebookShare on Twitter
  • PhillyPepboy
    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

    · Share on FacebookShare on Twitter
  • Paul Rose
    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

    · Share on FacebookShare on Twitter
  • Don Wilcox
    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.

    · Share on FacebookShare on Twitter

Categories

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

 Watch video