Dynamic push of data into Excel from a Report

Francis Comtois
Francis Comtois ✭
edited 02/06/20 inFormulas and Functions

Hey guys, I need some help as I 'm trying to automatically import some data from a specific colum displayed in a Report into an Excel spreadsheet and I don't really know how to do it...


Here's the steps I've followed in order to have the data displayed dynamically in my Excel spreadsheet :


#1 - I've created a report in which all the needed data is being displayed.

#2 - I've published my report and got a link.

#3 - I've open Excel.

#4 - In the data tab, I've selected Get Data from Web.

#5 - I've pasted the URL in the pop up window and clicked on Import.

#6 - An error message appears in Excel.


2020-02-06_17-23-36.png
2020-02-06_17-25-08.png


When I do the same process with a Sheet (instead of a Report), it works perfecly... but I'm having too much data displayed in Excel. Plus, the data is not very well organised this way. I think the problem is Excel can't manage the Gantt charts and it's one of the only two options we can choose in Smartsheet for the Report (the other one is Agenda...), as opposed to the Sheet side in which we have way more publishing options (Read only - HTML seems to work perfectly).


2020-02-06_17-34-56.png


To sum up, I need to have some very specific data imported automatically in Excel, this data needs to been dynamically updated every 5 minutes and I need to autosave my Excel as a csv file every 5 minutes.


This .csv file will be used by another system to trigger some jobs (i.e. copy / move / delete video files present in a given folder).


Anybody has an idea how I could manage to do that ?


Many thanks,


Francis Comtois

Tags:
· Share on Facebook Share on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

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

 Watch video
=COUNT(DISTINCT([Employee]:[Employee]))<\/p>

Replace [Employee] if your column has a difference name. This will essentially generate a list of distinct entries and then count them.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":109307,"type":"question","name":"Show approval status based on # of approvals vs. denials","excerpt":"I'm looking for a formula that will show \"Yes\", \"Hold\", or \"No\" based on # of approvals. There are 6 total approvals needed, if all 6 approve, then the Approved column would show \"Yes\". If there is 1 or more denials, then the Approved column would show \"No\". If the total of the columns don't equal to 6, then the Approved…","snippet":"I'm looking for a formula that will show \"Yes\", \"Hold\", or \"No\" based on # of approvals. There are 6 total approvals needed, if all 6 approve, then the Approved column would show…","categoryID":322,"dateInserted":"2023-08-22T21:20:12+00:00","dateUpdated":null,"dateLastComment":"2023-08-22T22:04:40+00:00","insertUserID":96646,"insertUser":{"userID":96646,"name":"@SPark","url":"https:\/\/community.smartsheet.com\/profile\/%40SPark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!co_Wh_2S-n4!ZAPZGbmgZrw!TRZYwfjvFdi","dateLastActive":"2023-08-22T22:57:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":96646,"lastUser":{"userID":96646,"name":"@SPark","url":"https:\/\/community.smartsheet.com\/profile\/%40SPark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!co_Wh_2S-n4!ZAPZGbmgZrw!TRZYwfjvFdi","dateLastActive":"2023-08-22T22:57:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":28,"score":null,"hot":3385482292,"url":"https:\/\/community.smartsheet.com\/discussion\/109307\/show-approval-status-based-on-of-approvals-vs-denials","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109307\/show-approval-status-based-on-of-approvals-vs-denials","format":"Rich","lastPost":{"discussionID":109307,"commentID":392022,"name":"Re: Show approval status based on # of approvals vs. denials","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392022#Comment_392022","dateInserted":"2023-08-22T22:04:40+00:00","insertUserID":96646,"insertUser":{"userID":96646,"name":"@SPark","url":"https:\/\/community.smartsheet.com\/profile\/%40SPark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!co_Wh_2S-n4!ZAPZGbmgZrw!TRZYwfjvFdi","dateLastActive":"2023-08-22T22:57:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/AJ5OBZXXQRAU\/2820-29-intake-smartsheet-com-2023-08-22-14-16-38.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"(20) Intake - Smartsheet.com 2023-08-22 14-16-38.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-22T22:04:29+00:00","dateAnswered":"2023-08-22T21:36:22+00:00","acceptedAnswers":[{"commentID":392011,"body":"

Something like this: =IF([Approval Count]@row = 6, \"Yes\", IF([Denied Count]@row >= 1, \"No\", \"Hold\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109287,"type":"question","name":"Formula help","excerpt":"=IF([WD Schematic End]@row = \"n\/a\", 0, IF([WD Schematic End]@row > [STR Schematic End]@row, 1, 0)) Above formula meant to trigger a red flag if the WD & STR Schematic End dates do not match. The formula I wrote is pulling back 'invalid' ... I am missing something and cannot figure out what!","snippet":"=IF([WD Schematic End]@row = \"n\/a\", 0, IF([WD Schematic End]@row > [STR Schematic End]@row, 1, 0)) Above formula meant to trigger a red flag if the WD & STR Schematic End dates do…","categoryID":322,"dateInserted":"2023-08-22T17:45:32+00:00","dateUpdated":null,"dateLastComment":"2023-08-22T18:30:00+00:00","insertUserID":157887,"insertUser":{"userID":157887,"name":"holli.nunn","url":"https:\/\/community.smartsheet.com\/profile\/holli.nunn","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T18:29:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":157887,"lastUser":{"userID":157887,"name":"holli.nunn","url":"https:\/\/community.smartsheet.com\/profile\/holli.nunn","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T18:29:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3385456532,"url":"https:\/\/community.smartsheet.com\/discussion\/109287\/formula-help","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109287\/formula-help","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109287,"commentID":391947,"name":"Re: Formula help","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391947#Comment_391947","dateInserted":"2023-08-22T18:30:00+00:00","insertUserID":157887,"insertUser":{"userID":157887,"name":"holli.nunn","url":"https:\/\/community.smartsheet.com\/profile\/holli.nunn","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T18:29:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/DLAV2H53BW86\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-22T18:29:34+00:00","dateAnswered":"2023-08-22T18:25:02+00:00","acceptedAnswers":[{"commentID":391943,"body":"

Give this a try:<\/p>

=IF([WD Schematic End]@row = \"n\/a\", 0, IF([WD Schematic End]@row <> [STR Schematic End]@row, 1, 0))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Counting people

    Hi Smartsheet Community I use smartsheet to manage volunteer opportunities for my organization. One sheet details the employee, event, date, etc. for the whole year. I want to find out how many total employees volunteer without having to go through the entire list and cross out duplicates. Any tips on how to do that? thank…

  • Show approval status based on # of approvals vs. denials

    I'm looking for a formula that will show "Yes", "Hold", or "No" based on # of approvals. There are 6 total approvals needed, if all 6 approve, then the Approved column would show "Yes". If there is 1 or more denials, then the Approved column would show "No". If the total of the columns don't equal to 6, then the Approved…

  • Formula help

    =IF([WD Schematic End]@row = "n/a", 0, IF([WD Schematic End]@row > [STR Schematic End]@row, 1, 0)) Above formula meant to trigger a red flag if the WD & STR Schematic End dates do not match. The formula I wrote is pulling back 'invalid' ... I am missing something and cannot figure out what!