Connect to Google Sheets

To connect Google Sheets to Atlassian Analytics, you’ll need to ensure your spreadsheet has the required formatting, then allow Atlassian Analytics to access your Google Sheets. This quick setup makes connecting Google Sheets extremely helpful for ad hoc analyses.

A spreadsheet corresponds to a database, and worksheets correspond to tables. Any updates made to rows in your Sheets data are immediately queryable in Atlassian Analytics. If you add worksheets or columns, you’ll need to sync the schema for your Google Sheets data source to reflect the changes. Learn how to sync schemas.

Prepare your spreadsheet before connecting

1. You must be the owner of the spreadsheet to connect it to Atlassian Analytics.

2. To ensure your spreadsheet is properly imported, check the following format requirements before you connect them to Atlassian Analytics:

• Remove extra headers and footers. There should only be one header row per worksheet.

• Make sure each worksheet is in a tabular format, starting in the top-left cell. Arrange the data in columns and ensure there are no empty header cells for columns that contain data.

• Remove special characters from the header row (including newlines). Headers should contain only numbers, letters, underscores, and spaces.

• Use the Google Sheets Format menu to correctly specify the format of a column.

• Change the locale of the spreadsheet to United States so the decimal separator is a period. If your spreadsheet uses a comma as the decimal separator, the data may not be accurately imported.

• Use either of these date formats to ensure Atlassian Analytics recognizes them correctly:

  • yyyy-mm-dd (for example, 2023-03-20)

  • mm-dd-yyyy (for example, 03-20-2023)

Add Google Sheets to Atlassian Analytics

After ensuring your Google Sheets has the required formatting, you can connect it to Atlassian Analytics.

To add Google Sheets to Atlassian Analytics:

1. Select Data from the global navigation.

2. Select Add data source > Google Sheets.

3. Choose or sign in to your Google account.

4. Select Allow to grant access to your Google Drive.

5. Select the spreadsheet you want to connect. You can only connect spreadsheets that you’re the owner of.

6. Click Select.

Atlassian Analytics will connect to your spreadsheet to set up the schemas, then your data source will be ready to use.

Notes about Google Sheets schemas

Renaming spreadsheets, worksheets, or columns

If you rename an already connected spreadsheet, worksheet, or column, Atlassian Analytics treats these changes as a deletion and addition. This will cause errors for any charts or controls that query the renamed object. Note that moving a column or worksheet is supported and won’t cause breaking changes.

“Row number” columns

Each table will have a “Row number” column. If you don’t have your own ID columns, you can use these columns as primary keys for the tables, which help for “Count of unique” aggregations.

Syntax for SQL mode queries

Here are some quick tips for how to format your data when you create a SQL mode query that uses Google Sheets as the data source:

• Wrap strings in single quotes (for example, 'John Doe')

• Use the FORMAT function and Oracle's datetime patterns to format your dates and timestamps (for example, FORMAT("Table name"."Column name". 'MM-dd-YYYY'))

• Because of the JDBC driver we use to connect Google Sheets to Atlassian Analytics, you can only use inner, left, and right joins in SQL mode. If you want to use different types of joins, you can use multiple “Add query” steps then change the join type to merge their data. Learn more about how to merge queries.

Refer to the CData documentation for more comprehensive SQL syntax help.

Remove access to your Google account

If you no longer want to allow Atlassian Analytics to access your Google account, you’ll need to remove Atlassian’s access to it from your Google account settings. Refer to Google’s documentation for how to remove Atlassian’s access to your Google account.

After you remove access to your Google account, any Google Sheets data sources using this account as the authorizing account will stop working. Those data sources will work again if the owner of the spreadsheet reauthorizes their Google account in the “Connection” tab of the data source settings.