SmartSheet Data to Excel Macro OR Any Alternatives?

AliTaher

Hello, I am new to SmartSheet and have been working on a project. Currently, the data I am working with is entered through a form by users and inputted from there into a SmartSheet. The data I am working with is free-text text boxes (so things like comments, etc.) and I was tasked with coming up with a way to sort through them and categorize them. My solution to this was to export the SmartSheet data I had to a macro-enabled Excel file and use formulas and macros from there to search the column(s) that contained the comments, etc for key words and then designate categories for all of the comments. That part works. The problem I have now is that I want to automate this process in a way, so like I want the SmartSheet data to send any new rows it gets to that Excel file to get run through the filtering macros. I have a PowerBI connected to that Excel sheet to visualize my data. Is there a way for me to connect my SmartSheet to that Excel sheet so that it is consistently updating it with new data as the SmartSheet gets it? Or would I have to work around if that was not a possibility? Maybe something like converting my Excel formulas into SmartSheet formatting and trying to work solely on SmartSheet? What are my options and what are the capabilities of SmartSheet? I'm a bit lost right now and unfamiliar with this platform, but would like to learn more about what it can do? Any suggestions or ideas?

Paul Newcome

My first suggestion would be to try to convert formulas and just work in Smartsheet.

My second suggestion would be that you can publish a sheet, grab the published URL, and use that in a "Get Data" query in Excel. You can set the refresh rate so that it refreshes when you open the workbook and then auto-refreshes as frequent as every minute as well.

AliTaher

I wanted to try your second suggestion first, but I am having trouble getting Excel to access the SmartSheet. I currently am doing Get Data > Other Sources > From Web and then pasting the URL of my SmartSheet, but when i preview the data it only shows the Sign-In Screen for SmartSheet on my Excel. Am I doing that incorrectly?

Paul Newcome

You have to use a published URL. You cannot use a direct URL.

Danielle Wilson

I would definitely recommend Paul's suggestion of just working with formulas in Smartsheet. If you're a bit new to formulas in Smartsheet, you should be able to use the AI formula generator to get something pretty easily.

Paul Newcome

@Danielle Wilson I use the published URL —> Get Data when I have things I need to do that Smartsheet doesn't do. Typically it is formatting specific such as merged cells to allow for a certain data structure, but I have found in some instances that some functions are available in Excel that are not in SS or pulling multiple sheets together into a workbook to get around some scaling limitations.