CxReports empowers users to generate dynamic reports by seamlessly integrating with various data sources, including Google Sheets. In this guide, we will walk you through the steps to set up and use Google Sheets as a data source in CxReports. By following these instructions, you can streamline your reporting process and take full advantage of CxReports' capabilities.

Step 1: Setting Up a Google Service Account

To enable the integration, you’ll need a Google Service Account. Follow these steps to create and configure it:

1. Create a Google Cloud Project:
   • Log in to the Google Cloud Console.
   • If you don’t already have a project, click on Select a project and choose New Project to create one.
     
2. Enable APIs:
   • In the sidebar, navigate to APIs & Services.
     
   • Search for and enable both Google Sheets API and Google Drive API.
     
3. Create a Service Account:
   • Under IAM & Admin in the sidebar, select Service Accounts.
     
   • Click on + Create Service Account and complete the form. Assign appropriate roles for the account.
     
4. Generate and Download JSON Key:
   • Once the account is created, locate it in the Service Accounts dashboard.
   • Navigate to the Keys tab, click on Add Key, and select Create new key.
   • Choose JSON as the key type and download the key file. This file will be needed later.
     
5. Share Google Sheets with Service Account:
   • Open the Google Sheet you want to use as a data source.
   • Share the sheet with the email address of your service account.
   • Ensure the first row of the sheet contains header names for the data table in CxReports.
     

Step 2: Configuring the Service Account in CxReports

1. Log in to CxReports.
2. Navigate to Workspace Configuration > Google Drive.
3. Click on + Add New Service Account.
4. Fill out the form with the following details:
   • Name: Provide a name for the service account within CxReports.
   • Email: Enter the email address of the Google Service Account.
   • Scopes: Enable both Google Sheets and Google Drive scopes.
   • Key (JSON): Paste the contents of the JSON file downloaded in Step 1.
5. Click Save to complete the configuration.
   

Step 3: Creating a Google Sheets Data Source in CxReports

1. Open Your Report:

   • Log in to CxReports and select the report where you’d like to use Google Sheets as a data source.

2. Add a New Data Source:

   • In the top navigation bar, go to the Report tab.
   • Navigate to Data Sources and click + Add new data source.

3. Fill Out Data Source Details:

   • Name: Assign a name to the data source.
   • Display Name: Provide a display name for easy identification.
   • Type: Select Google Sheets from the dropdown menu.
   • Google Account: Choose the service account you created earlier.
   • Sheet ID: Enter the ID of the Google Sheet. The Sheet ID can be found in the URL of the sheet, for example: https://docs.google.com/spreadsheets/d/3p7m1L034M8-GgKQzcOtinkpBreia1gIbBR9nKHTh3vL/edit – the ID is 3p7m1L034M8-GgKQzcOtinkpBreia1gIbBR9nKHTh3vL.
   • Range: Define the data range using the format sheet_name!columns_range, such as Sheet1!A:Z.

4. Click Save to finalize the data source.
   

Step 4: Using the Google Sheets Data Source in Your Report

1. Add a Data Table Component:

   • Drag and drop the Data Table component from the component bar onto the report page.

2. Configure the Data Table:

   • Select the data source you just created from the Data Source dropdown in the Configuration tab.
   • Click Generate columns to populate the table with data from your Google Sheet.
     

Benefits of Google Sheets Integration

Integrating Google Sheets with CxReports offers several advantages:

1. Real-Time Data Updates:

   • If the values in the Google Sheet change, the table in CxReports will automatically display the updated values upon reloading.

2. Fresh Data in Reports:

   • When generating reports, such as email reports that use a Google Sheets data source, the data will always be pulled directly from the latest version of the Google Sheet, ensuring up-to-date accuracy.

3. Improved Collaboration:

   • Multiple team members can collaborate on the same Google Sheet, and any updates made by one team member will reflect automatically in CxReports.

4. Ease of Access:

   • Since Google Sheets is cloud-based, users can update and manage data from anywhere, ensuring the reports are always up-to-date without being tethered to a specific device or location.

Conclusion

By following these steps, you can easily integrate Google Sheets with CxReports to create dynamic, data-driven reports. This integration enables you to use the flexibility of Google Sheets while benefiting from CxReports' powerful reporting features. Start optimizing your reporting process today by setting up this seamless integration.

Try our live demo to explore how CxReports can transform your reporting process. Have specific questions? Schedule a meeting with our team to learn how we can support your reporting needs.