Dropbox Google Sheets

Posted onby admin

Login to Dropbox. Bring your photos, docs, and videos anywhere and keep your files safe. Gmail Watch emails Google Sheets Add a row Gmail Iterate attachments Router Facebook Create a post Archive Create an archive Dropbox Upload a file Documents only Images only When new email arrives Add a row to a sheet Process attachments one by one. Google sheets let us use this functionality with ease. With just a few clicks, you can create either a single-cell dropdown or populate a whole row or column with dropdown lists. However, you will notice that the default Google Sheets dropdown list allows the user to only select a single item from the list. Google Drive can quickly save and store Gmail attachments, twin stored images with Google Photos, and makes collaboration easier through Google Docs, Sheets, and Slides.

To keep track of files that are stored in another online resource (for example, Google Drive or OneDrive) and relate those files to items that you're tracking in Smartsheet, you'll use the Attachments capability. You can make file associations at the workspace, sheet, row, or comment level.

Here's how to get started:

  • Workspace: In the top right corner of the Workspace, click the Attachments icon.
  • Sheet: To open the Attachments pane, click Attachments on the right Feature Bar.
    At the bottom of the Attachments pane, click Attach Files to Sheet.
  • Row: Click the Attachments icon (in Card View, click a Card and click Add Attachment).
  • Comment: Open the comment for editing, click the Add Attachment icon to the right of the comment.

Who can use this capability

Owners, Admins, and Editors on a sheet can add, version, or delete attachments. Anyone with access to the sheet can view and download attachments. You can send an attachment in an email to anyone with a valid email address.

NOTE: Some online storage services require people to have additional permission settings before they can access or edit a file.


All plan types. System Admins for Enterprise and Premier accounts can disable any file attachment options. (See Manage Global Account Settings.)

Supported Third-Party Cloud Storage Services

The table below lists the online storages services that you can use with the Smartsheet Attachments capability. Use the information about the workflows for each service to effectively attach and access files stored by these services.

NOTE: You'll need to have the appropriate permissions in these programs, as well as access to the sheet or workspace in Smartsheet.

Storage service

Workflow for attaching the file

Workflow for accessing the file

Google Drive

If you use a Google address as your Smartsheet address, a pop-up window will appear requesting access to your Google Drive documents.


Click Grant Access to continue.


If you don’t use a Google address in Smartsheet, a pop-up window will appear enabling you to login to a Google account first. You’ll only need to enter this information in the first time you attach a file from Drive—it will be saved the next time you add an attachment.

When you open a Google Drive attachment that has been converted to Google Docs, Google Spreadsheet or Google Slides format, you can make changes to the file directly and Google will save your changes automatically.


The changes will be reflected in the Smartsheet attachment.


For more information, please see the related Google Drive support content.

OneDrive

A pop-up window will appear enabling you to login to OneDrive the first time you use this feature. The login information will be saved the next time you attach an item from OneDrive.

If you find that you're logged in to the wrong OneDrive account, click your email address in the upper-right corner and then select Switch account.

OneDrive attachments are view-only, unless you're the owner of the file or the file has been shared to you from OneDrive.

If you have editing permissions on the file, you'll see Edit Document at the top of the file.

Dropbox

A pop-up window will appear enabling you to login to Dropbox the first time you use this feature. The login information will be saved the next time you attach an item from Dropbox. Attaching an item from Dropbox creates a public link to it that is added to Smartsheet.

If you find that you're logged in to the wrong Dropbox account, you can click on your name in the upper-right corner to log out and then log back into the desired account.

Dropbox attachments are view-only when accessed from Smartsheet, unless they are Microsoft Word, PowerPoint, or Excel files.

Dropbox supports an integration with Microsoft Office Online that enables you to edit these files directly by clicking Open at the top. Changes you make will be synced back to the attachment in Smartsheet.

For more information, see the Dropbox help about editing Office files.

Box

A pop-up window will appear enabling you to login to Box the first time you use this feature. The login information will be saved the next time you attach an item from Box.

If you find you’re logged into the wrong Box account, click the Switch Box Account link in the lower-left corner of the window.

Box attachments are view-only when accessed from Smartsheet. (You must go to Box to download or edit the file.)

Evernote

A pop-up window will appear enabling you to log in to Evernote and authorize Smartsheet to access your files the first time you use this feature. The login information will be saved the next time you attach an item from Evernote.
If you find you’re logged into the wrong Evernote account, click the Switch Evernote Account link in the lower-left corner of the window.

Evernote notes will be read-only when opened from Smartsheet; however, any changes you make to your notes directly within Evernote will be synced back to the Smartsheet attachment.

Egnyte

A pop-up window will appear requesting your Egnyte domain, your Egnyte username and password, and your confirmation to Allow Access to Smartsheet. The login information will be saved the next time you attach an item from Egnyte.
If you find you’re logged into the wrong Egnyte account, click Change User in the upper-right corner to sign in to a different Egnyte account.

When you open an Egnyte attachment, you may see an option to edit it at the top. Editing Egnyte files requires you to download and install Egnyte's WebEdit tool. Learn more about this in Egnyte's support center. Changes made to the file from Egnyte's WebEdit tool will be synced back to the attachment in Smartsheet.
NOTE: The Egnyte folder containing the files (as opposed to the individual files themselves) will need to be shared out in order for other collaborators to open them in Smartsheet. Egnyte offers instructions for sharing folders in their Help Desk.

I have a file with CSV data stored in a cloud storage, OneDrive or Dropbox. Last year, I would have had to make two steps to get this file to Google Sheets:

  1. Download it to my device
  2. Upload it to Google Sheets

This workflow is a pain when you need to update the data recurrently. Fortunately, I can synchronize both files and automate data refresh on a schedule. How? Check out the life-saving solution below!

Export OneDrive CSV to Google Sheets
How to import a CSV file from a Dropbox to Google Sheets using IMPORTDATA
Coupler.io or IMPORTDATA – which option to choose?

Export OneDrive CSV to Google Sheets

Sheets

I tackled the issue with a Google Sheets add-on, Coupler.io. It provides several ready-to-use integrations to Google Sheets from third-party apps, such as Airtable, Xero, and others. For my case, there is a CSV importer. It is a solution to import data from online published CSVs. As a baseline, you need a URL to your CSV file to fetch data. Check out the entire flow to import data:

Install Coupler.io

Install Coupler.io from the G Suite Marketplace.

Once installed, open the Google Sheets doc you’re going to import CSV data to. Go to the following menus:

Add-ons => Coupler.io => Open dashboard

Click Add Importer and select CSV.

Set up the CSV importer

During this step you need to do the following:

1. Fill out the “Title” field

Enter the name of your importer.

2. Set up your data source

Enter the embedding code of your CSV file on OneDrive into the “CSV URL” field.

How do you get the embedding code of a OneDrive CSV file?

Right-click on your CSV file, select “Embed” and click “Generate”.

Example:

Note: Do not use a shareable link to your CSV file on OneDrive as the CSV URL. The importer will fail to fetch data.

3. Set up your data destination

Enter the name of the sheet that will be receiving data.

4. Configure importer’s settings

Enable the Automatic data refresh if you want to import CSV data on a specific schedule.

Click Save & Run to save your importer and initiate the first import. For more about the additional fields and settings available, refer to the Coupler.io knowledge base.

Export Excel files from OneDrive to Google Sheets

The CSV importer by Coupler.io supports the export of Excel files to Google Sheets. When you open a CSV file in OneDrive, it is converted into Microsoft Excel Workbook (.xlsx).

Meanwhile, you don’t have to edit the importer that has been set up to import CSV data to Google Sheets. It will work as-is.

If you want to import another Excel file from OneDrive, get its URL in the same manner as for the CSV files. Insert the downloadable URL of a .xlsx file into the CSV URL field and run the importer to retrieve data.

How to import a OneDrive CSV file to Google Sheets with IMPORTDATA

IMPORTDATA is a native Google Sheets function to import data from a given URL in either CSV or TSV format.

Check out more about the function and how it differs from the CSV importer by Coupler.io in our IMPORTDATA Tutorial.

However, the function doesn’t work smoothly for importing CSV data from OneDrive. First, you need to prepare a CSV URL as follows:

  • Right-click on your CSV file and Select “Embed”. Click “Generate” to get the embedding code of your CSV file. For example:
  • Get the URL from the embedding code:
  • In the URL, replace “embed” with “download

Here is what you’ll get if you use this CSV URL in the IMPORTDATA formula:

Dropbox google sheets tutorial

IMPORTDATA pulled the data from the CSV file but malformed it.

To fix the issue, you should nest IMPORTDATA with ARRAYFORMULA, IFERROR, and SPLIT functions as follows:

Read our blog post to learn more about How to Split Text, Date-Time And Other Data in Google Sheets.

CSV from Dropbox to Google Sheets

What about Dropbox users? What if they need a similar solution for CSV data import? Not to mention, the search interest in Dropbox is greater than OneDrive.

Coupler.io will help them as well. The setup flow is the same. The only difference is that you need to enter the shared link of a CSV file on Dropbox into the “CSV URL” field.

How to get a shared link of a Dropbox CSV file?
  1. Point your CSV file on Dropbox and click “Share”.
  2. Click “Create link”.
  3. Click “Copy link”.

Example of a Dropbox shared link:

So, insert the Dropbox shared link into the “CSV URL” field and welcome your CSV data in Google Sheets!

How to import a CSV file from a Dropbox to Google Sheets using IMPORTDATA

Unfortunately, you can’t just insert a shared link to a Dropbox CSV file in the IMPORTDATA formula – this won’t work:

Here is a workaround.

IMPORTDATA + SPLIT + ARRAYFORMULA to import a Dropbox CSV to Google Sheets

Step 1

First, you need to tweak your CSV URL. Replace “dl=0” with “raw=1” in the shared link of your Dropbox CSV file. So, instead of

you should have

Step 2

Dropbox Google Sheets

Use the edited CSV file URL in the IMPORTDATA formula, nested with ARRAYFORMULA, IFERROR, and SPLIT functions:

Here is how it works:

Read more about How to Split Text, Date-Time And Other Data in Google Sheets.

Coupler.io or IMPORTDATA – which option to choose?

The option to choose depends mostly on your workflow.

Choose IMPORTDATA

If it’s a one-time import. IMPORTDATA is a native Google Sheets function, which requires no installation.

Choose Coupler.io

If you’re planning to perform recurring imports, Coupler.io is quite the better choice because:

  • No manipulations with OneDrive or Dropbox links are needed. It’s very useful if you deal with multiple CSV files.
  • Automatic data refresh is available. You can customize the schedule to automate your CSV data import from either Dropbox or OneDrive or both
  • Import from multiple sources with one tool. Coupler.io is not only about CSV data import. You can set up integrations with Airtable, Pipedrive, Quickbooks and other sources.

Other data sources to import CSV to Google Sheets using Coupler.io

Besides the mentioned Dropbox and OneDrive, Coupler.io lets you fetch CSV data from any publicly available CSV file, as well as CSV from FTP to Google Sheets. Some of the use case have already been described on our blog:

If you need help to set up your CSV importer, feel free to contact us. Besides, you’re welcome to tell us about any integration with Google Sheets you’d like to have. Fill out this form, and your request may result in a ready-to-use importer. For example, a request from one of our users kickstarted the Clockify importer. Good luck!

Open Dropbox In Google Sheets

Back to Blog