Ready to visualize and track your Google Drive data? Get started by setting up a connection between PowerMetrics and your Google Drive account and adding one (or more) data feeds.
When you're finished adding Google Drive data feeds, you'll use them to power your custom metrics.
This article includes:
Here are a few things to note before creating your data feed:
- File size limits: The maximum raw source data file size is 10 MB. If your file exceeds this limit, try separating the data into more than one file, removing all external references (like VLOOKUPs), and/or removing hidden rows and columns.
- Pivot tables: Google Sheets with pivot tables are not supported.
- Google Sheet names: To ensure files get processed correctly, make sure your Google Sheet names don't exceed 31 characters.
- Number of columns: If your source data includes more than 100 columns, only the first 100 will be included in the data feed. We recommend using smaller data sets for your data feeds and metrics. If you need to create a data feed that includes 100 columns, please contact our Support Team to discuss increasing your limit.
- Modifying your spreadsheet data in PowerMetrics doesn’t change your original spreadsheet source data.
- The importance of column header names: PowerMetrics uses the column header names in your spreadsheet source data as unique identifiers. This lets us map to the columns in your source data, enabling your data feed and custom metrics to continue to point to the correct column, even if the content and order of the columns in the source data changes. For example, with this feature enabled, if a column is removed from the raw source data that a custom metric is using for its data, then the metric will stop updating. However, if you add the column back to the raw source data (using the original column header name), the metric will start working again and retroactively ingest all of the data that’s in the raw source data column.
To enable this feature, when creating your data feed and selecting the row to use as the “row with column headers”, you must choose a numbered row – not the “None. Use letters (A, B, C, D)” option.
To ensure your data feed (and custom metrics) continue to update correctly over time, after creating the data feed, don’t change any names in the column header row in your source data. This action would break the mapping to those columns.
Column header row names are case sensitive. For example, if you change a name from “Revenue” to “revenue” in your source data, PowerMetrics will identify “revenue” as a new column and custom metrics that were pointing to “Revenue” will break.
Each column header name should be unique. If your source spreadsheet data includes duplicated column header names, we recommend you modify it and replace duplicated names with unique ones before using it to create a data feed. If not, we’ll automatically assign new, unique names to duplicate column headers in the data feed editor. For example, if your source spreadsheet has 3 columns named “Profit”, they’ll be updated in the data feed to “Profit”, “Profit 1”, and “Profit 2”. If desired, you can rename these automatically-named columns in the data feed editor.
Creating a Google Drive data feed
When you create a Google Drive data feed, you:
Connecting PowerMetrics to your Google Drive account
The first step in creating a Google Drive data feed is to connect PowerMetrics to your Google Drive account.
Here are a couple things to note:
Your data is safe with us. The first time you connect your Google Drive account to PowerMetrics, you'll be prompted to enter your Google login credentials and give permission to allow access to the data within your Google account. We are serious about protecting your data privacy.
Managing your connections. By default, your connection name looks like this: yourname@Google<date and time created>. You can modify the default connection name when you create a new connection or from your list of connections, accessed by clicking the button in the left navigation sidebar and selecting Connections. Each time you connect to Google Drive, you can use the same account (or switch to a different account if needed). If you have trouble accessing an existing connection, your OAuth token may have expired. If that happens to you, go here for help.
To connect PowerMetrics to your Google Drive account:
- In the left navigation sidebar, click the + button beside Data Feeds.
- Click Select data.
- On the Where is your data? page, select Google Drive. (See below.)
- If this is your first time connecting to Google Drive:
- Click Connect new account. Enter your Google login credentials and click Next. If prompted, click Allow to enable Klipfolio to securely access your Google Drive data. Click Continue.
- If you've connected to Google Drive before:
- Select an existing connection from the drop-down list and click Continue.
Choosing data for the data feed
To choose data for the data feed:
- On the Configure data service page, click Choose file.
- Navigate through your Google Drive folders (choose from My Drive, Shared drives, or Shared with me) to locate your file.
- Click the file you want to use for your data feed. Then click Select.
- The name of the chosen file displays under File. If you want to select a different file or use a different Google account, click Change account connection or file.
- For multi-sheet files, under Sheet name, select the sheet you want to work with. The chosen sheet will display in the data preview.
To create a data feed that includes data from more than one sheet, create single data feeds and then merge them. Learn how here.
- To work with metrics, the measures and dimensions in your data feed need to be organized into columns, not rows. If applicable, under Settings, at Change rows to columns, use the toggle to switch row-oriented data to column-oriented data.
Note: This option does not apply to files with JSON or XML formats.
- At Row with column headers, we do our best to auto-select the correct row to use as your column header row, but, if desired, you can use the drop-down to manually select the column header row instead.
If your columns don’t have header names, select None to use letters, beginning with “A”, to refer to your columns.
Notes: All rows before the selected column header row will be excluded from the data feed. This setting does not apply to files with JSON or XML formats.
- When you’re done configuring settings, click Use this data.
- You're taken to the data feed editor, where you can either save the data as-is or modify it.
Note that the data feed is automatically named to match the source file. Typical editing actions here include changing the data feed’s name and entering a description, and changing the data feed’s refresh frequency rate.
If you want to make more extensive changes to the data feed, go here for detailed information.
- Click Save data feed.
If, before saving the data feed, you want to return to the Configure data service page, click in the field under Data service. (See below.)
The data feed is added to your account and is ready to use for custom metrics. To see a list of all your data feeds, click Data Feeds in the left navigation sidebar.
Note: If, after creating and saving your data feed, you open it and see an error message under Refresh status that says: “Disabled due to multiple refresh failures”, this likely means the Google account being used for the data feed either needs to be re-authenticated or it doesn’t have the appropriate permissions to access the data. In either case, click the file name under Data service to open the data feed. (See below.)
To re-authenticate or choose a different account connection, click Change account connection or file. (See below.)
Next steps - Creating Google Drive custom metrics
Now that you've created one or more Google Drive data feeds, you can return to them and use them to make custom metrics. A single data feed can be used to create a single or multiple custom metrics.
To create custom metrics using a data feed:
- In the left navigation sidebar, click Data Feeds to open your list of data feeds.
- Select the data feed you want to use for your custom metric.
- Click either the +Add metric button in the top right corner of the window or open the Metrics tab and click +Add metric. (See below.)
- Choose settings for your custom metric and click Save metric. If you need help, go here to learn more.
The metric opens in its homepage and is added to your list of metrics (accessed by clicking Metrics in the left navigation sidebar).