Smart management and organization are crucial elements for any business team to thrive. Smartsheet enables teams to collaborate effectively by allowing them to track projects, manage content, automate workflows, and rapidly build solutions at scale.
However, despite the platform’s vast capabilities, at times, you might need to extract your Smartsheet data – whether that be reports or sheets – to Excel for further analysis, visualization, or sharing with colleagues.Â
In this blog post, we’ll walk you through the steps on how to export Smartsheet data to Excel with ease and efficiency, and importantly, preserve vital aspects such as attachments, comments, and formulas. We’ll also discuss some external tools that can help you during the process and address potential limitations you may encounter when you export Smartsheet to Excel.
So, let’s get started!
How to export Smartsheet data to Excel – all options explained
Depending on what data you wish to export, you can use the following methods:
- Smartsheet’s built-in functionalities
- Smartsheet plugins
- ETL tools
- Smartsheet’s REST APIs
The built-in options enable you to export any sheet or report in the following formats: Microsoft Excel, PDF, PNG (Gantt Chart Only), Microsoft Project (XML), and Google Sheets.
In certain cases that will be described later, native options are limited in their ability to export certain data you desire. If data cannot be exported natively through the platform, you can use the plugins found in the Smartsheet Marketplace.
In addition, you can export Smartsheet to Excel with external ETL tools, like the one provided by Coupler.io. This allows you to load data from Smartsheet without requiring any coding knowledge. You will be walked through this export process in the following sections below.
Another way to export Smartsheet Smartsheet to Excel is through the REST API. Overall, the process involves using an HTTP GET or POST request to call Smartsheet’s REST endpoint. You can do this without coding using a JSON API connector.
How to prepare for Smartsheet data export
Before exporting your data, make sure your data is clean and properly formatted. Follow these steps for effective data exportation:
- Format your sheet by adding the appropriate fonts, colors, and cell styles
- Make sure you have a well-structured and organized column hierarchy
- Organize data in rows for better data analysis
- Validate data types as Excel might interpret them differently
- Make sure that you have administrator rights on both Smartsheet and Excel to run the export
Export Smartsheet data to Excel
There are three ways to export Smartsheet data – either sheets or reports – to Excel.
Method 1
- In the left navigation pane, click on Browse to view all available sheets and reports.
- Select the desired sheet or report by clicking on its title.
- Click the Actions button at the top and then click Export to Microsoft Excel
The data will be imported into an Excel sheet that will be downloaded to your computer. Once you open the downloaded Excel file, here is what the exported data will look like:
Method 2
- Right-click on the sheet or report you want to export
- Click Export to Microsoft Excel to download a file to your computer
Method 3
- Open the sheet or report you would like to export
- Click on File, hover over Export, and then click Export to Microsoft Excel to download a file to your computer
Limitations when exporting Smartsheet data through the UI
At its core, Smartsheet follows a spreadsheet format just like Excel. However, the exported data may not appear in Excel exactly as it does in Smartsheet. Here is a list of some of the key differences:
- Formulas are not preserved: When exporting your data to Excel, formulas will appear in the exported file in a textual format. This means that you will have to make some changes to the exported Excel file to ensure that the formulas function properly.
- Comments appear on a separate tab: You’ll find comments on the second tab of the exported workbook.
- Attachments are not included at all: To include attachments, and comments on one tab instead of a separate one, while exporting Smartsheet data to Excel, you can utilize several third-party integrations, such as Data Uploader. These tools should allow you to transfer this additional information during the export process. Keep in mind that you may need to follow the specific instructions of each integration to properly synchronize and access the attachments and comments.
- Not all column types are supported in Excel: Excel does not support Dropdown, Contact, Checkbox, or Symbols. When you export sheets that use these column types, only text values will be exported. Values in Boolean column types (Checkbox, for example) will be exported as true or false, according to the state of the item in the cell.
- Reports are limited to 20000 rows: If your report has more than 20000 rows, you can create multiple reports that pull in fewer rows and export each report as a separate Excel file.Â
- Certain Smartsheet features are lost: Some features like automated workflows are unique to Smartsheet and may be lost during export. Automated workflows refer to sending an alert to your team, setting up a reminder about upcoming deadlines, requesting updates or approvals, locking/unlocking rows.
Smartsheet – auto export to Excel
Smartsheet offers the opportunity to generate recurring exports. You can send the entire sheet through email as an Excel attachment or share a report as an attachment in an email. Alternatively, you can use third-party tools.
Auto export to Excel through the UI
Smartsheet offers a native option that automates data exports to Excel.
Sending a sheet or report as an email attachment
- In the menu bar, select File and click Send as an attachment
- Type the email addresses of the people who should receive the email.
- Separate multiple email addresses with a comma or a semicolon. To choose contacts from your Smartsheet Contact List who will receive the email, click Select Contacts.
- Users can select from other members of their team or groups created by their Group Admins. Edit the email subject and message as needed. By default, the sheet’s name is included in the Subject field.
- In the window, in the Attach as option, click on Microsoft Excel.
- In the window, next to Delivery, click on Schedule to send later or on a recurring basis. Once you have set the recurrences up, they will appear in the right panel of the Send as Attachment form. To edit or delete a recurrence, click the arrow next to the description of the recurrence.
You can use a filter to display only the rows you want to send. Filters work on one-time attachments only. You can’t use filters with attachments sent on a recurring basis.
Auto export to Excel using third-party tools
Smartsheet is a versatile online platform that facilitates project management and team collaboration. But if you choose Smartsheet auto export to Excel without using external tools and the API, the process could prove time-consuming and require more effort.
A convenient alternative to Smartsheet auto export to Excel through the platform is using an integration tool, such as one provided by Coupler.io. The integration tool provides Smartsheet as a premium source – this means that users can request the setup for an extra fee. Learn more about the Smartsheet premium integration.
With Coupler.io, you can set an automatic data refresh on the desired schedule so that your data remains up to date. The interval for the updates can vary from every month to every 15 minutes.
Optionally, you can set up the Smartsheet auto export to Excel integration yourself using Coupler.io’s JSON importer and the Smartsheet API integration.
Export versatile data from Smartsheet using Smartsheet’s REST API
Smartsheet’s REST API opens up a wealth of possibilities to interact with your data and export it to Excel, allowing you to perform sophisticated operations. Despite Smartsheet’s REST API potential, it requires knowledge of programming languages to utilize the API effectively. However, you can use Coupler.io’s JSON importer, which allows you to connect to the REST APIs without coding.
To start using the Smartsheet API, you’ll need to generate an API token.Â
Once you’ve generated an API token in Smartsheet, we will show you below how you can export your files (attachments and discussions) to Excel with the help of Coupler.io:
- Sign up to Coupler.io and click on Add new importer.
- Select JSON as your source app and Microsoft Excel as your destination app.
- Insert the URL of your Smartsheet endpoint. In our example, we’re exporting information about all the sheets we have in Smartsheet
https://api.smartsheet.com/2.0/sheets
- Select the GET request method
- Specify the header parameters for your request:
Authorization Bearer {api-key}
- You can also specify the URL query parameters if needed.
- Connect your Microsoft account. Then, you will need to select an existing workbook and a sheet to load exported data from Smartsheet.
- To automate data export, toggle the Automatic data refresh button and schedule the day, time, and timezone. Then click on Save and Run.
After the importer runs for a few seconds or minutes, you’ll get information about sheets in your Smartsheet account to the selected Excel workbook.
This is a very plain example of how you can export Smartsheet to Excel via the Smartsheet API without coding. However, you can get much more out of this integration. Try out Coupler.io for free.
How to export Smartsheet report to Excel with all attachments and comments
Smartsheet allows you to attach files in your sheet either from your PC or cloud storage providers. However, these attachments are not exported in Excel when exporting your Smartsheet data. What’s more, comments appear on a separate tab in the exported Excel file.
At the same time, you can export all Smartsheet files – attachments – either through the platform or through the REST API. The latter is quite a complex method, while exporting files through the UI is available for every user.
Export all Smartsheet files to Excel through the UI
To include attachments, you have to create a backup of the sheet. This automatic backup includes a folder containing any attachments that were uploaded directly to your sheets from a computer or mobile device.
To request a one-time backup:
- On the left navigation bar, click Browse.
- Right-click on the name of the sheet or report and select Request Backup.
- A pop-up window will appear. Follow the instructions and make sure you click on Include attachments.
If you want a backup copy of your data and you are on a Business or Enterprise plan, you can:
- Request a weekly recurring backup
- Request a sheet snapshot from a specific point in time from the sheet’s Activity Log.
How to export Smartsheet to Excel with formulas
Due to the differences between Excel and Smartsheet formula syntax, formulas are not preserved on export.
Once you import your data to the Excel file, you have to either modify the syntax to operate the formulas or create the formulas once again.
How to export Smartsheet Gantt chart to Excel
Excel does not support a Gantt chart format. Exporting a Gantt chart to Excel through Smartsheet will export only the task list it is based on. To work around this, you can export from Smartsheet to Excel, build a Gantt chart there, and then synchronize data.
How to export data from Smartsheets – summary
With Smartsheet giving you real-time access to your project data, and Excel offering top-notch analysis, combining the strengths of both platforms will elevate your project management experience. But while Smartsheet is powerful on its own, exporting from Smartsheet to Excel can unlock even more potential for your business team.
Whether you need to preserve attachments, comments, or formulas, following the steps detailed in this guide will make the export process hassle-free and more productive. Plus, with the help of external tools you can easily automate certain processes, while with Smartsheet’s REST API you can create customized solutions. So, in addition to Smartsheet’s built-in functionalities, why not take advantage of the best of both worlds and enhance productivity and streamline your workflow.
