You can set up a connection between Klipfolio and your spreadsheet data to create custom data sources and data feeds. After creating data sources you can use them to power your custom metrics and custom Klips. Data feeds are used exclusively for custom metrics.
How do I upload my spreadsheet data to Klipfolio?
The following links will take you to detailed articles on uploading spreadsheet files to Klipfolio:
Note: Email attachment data sources are not currently supported in PowerMetrics.
- Uploaded files from your computer for data sources and for data feeds.
- FTP or SFTP for data sources and for data feeds.
- For Google Drive, Microsoft OneDrive, Microsoft SharePoint, Google BigQuery, Dropbox, and Box, see this section of the Knowledge Base.
Before connecting to your spreadsheet data
Here are some important things to note before connecting to your spreadsheet data:
- Google Sheets: Soon we'll be adding official support for Google Sheet formulas. Until then, some Google Sheet functions, such as ARRAYFORMULA, are not supported. If your Google Sheet includes unsupported formulas, and you import it into Klipfolio, the associated cells will be empty. If this happens, you can work around the issue by running your Google Sheet through Excel (which converts the data to use Excel functions) and then resaving it as a Google Sheet before importing it into Klipfolio.
- Sheet name: After you've successfully connected your spreadsheet with Klipfolio, do not change the sheet name. If you change your sheet name and re-upload the spreadsheet, the Klip or metric will continue to look for data using the old sheet name, and as a result, your formulas will break.
- Pivot tables: Klipfolio recommends using Excel without pivot tables. Excel pivot tables may not work and are not supported by Klipfolio.
- Unsupported formats: Financial and Scientific formats are not supported by Klipfolio. If your Excel file includes these formats, remove them before uploading your file.
- Unsupported Excel features: Excel software features such as worksheet links and macros are not supported. Some Excel functions are not supported and may be removed on import.
Best practices and tips
Here are a few best practices and tips to follow when creating data sources from your spreadsheets.
Selecting data to enable automatic updates
This tip applies to the data feed editor (for PowerMetrics), the modeller (for PowerMetrics and Klips), and the Klip Editor (for Klips only).
Selecting data using the column or row header allows you to assign an unlimited range of values to your custom metric or Klip (ex: A:A, or 1:1). This method tells the metric or Klip to use all values in that column or row. If new values are added to that range, they will automatically be included in your metric or Klip.
For example, you select column B, and the values in the spreadsheet are "Age, 4, 45, 84, 50, 23". Let's say the spreadsheet data is updated, and the values in A:A are now "Age, 4, 45, 84, 50, 23, 60", all values including the additional values will be used in your metric or Klip.
To make a range selection, click on the column or row header with your mouse. (See below.)
Removing column titles from your data
If you want to remove the column title from your data selection exclude that row in the data feed editor (for PowerMetrics) and in the modeller (for PowerMetrics and Klips) or, in the Klip Editor (for Klips only) by using the Filter action (located on the Properties panel) to Exclude the title from your list of data. (See below.)
Selecting multiple sheets
If your spreadsheet has multiple sheets associated with it, you can select those sheets when editing a data feed (for PowerMetrics), modelling a data source (for use in Klips or PowerMetrics), and in the Klip Editor (for Klips only) using the sheet selection menu.
Manipulating data using formulas and functions
As with all data sources and data feeds, you can manipulate your data using math operators, formulas and functions. You can do this in the data feed editor (for PowerMetrics), in the modeller (for PowerMetrics and Klips) and in the Klip Editor (for Klips only). Check out Tips for using the formula bar to learn more.