Use our Google Sheets integration to exchange data between Google Sheets and ChartMogul. Import customer data, invoice data, lead and trial data, or custom attributes and tags from Google Sheets to ChartMogul. Or, export customers and metrics from ChartMogul to Google Sheets.
Here’s what we cover in this article:
- Best practices
- Setting up the Google Sheets app
- Working with templates
- Exporting data from ChartMogul to Google Sheets
- Deleting Google Sheets as a source
Before you begin
- To import data for Subscription Analytics, you’ll need to import customers, plans, and invoices. Review ChartMogul’s other methods for importing data to generate subscription analytics to determine whether using Google Sheets is best for your business: API, flexible CSV import, structured CSV import, manual subscriptions, or using data tables.
- To import data for ChartMogul CRM, you’ll need to import customers and contacts. Google Sheets does not support adding contacts. Review ChartMogul’s other methods for importing data for CRM to determine whether using Google Sheets is best for your business: API, flexible CSV import, structured CSV import, using data tables, or adding a customer record.
- To set up the app, ensure your user role allows you to create an API key and add a source.
Best practices
Before getting started with the Google Sheets app, familiarize yourself with the following best practices to send accurate data.
Before sending data:
- Create a new Google Sheet each time you import data. Re-using a sheet re-sends any existing data, resulting in slow processing times, creating duplicates in ChartMogul, and causing metrics to fluctuate.
- Inserting a template erases data in the current sheet.
- Send data from any sheet, and not only the one you used when connecting to ChartMogul. The app is connected to ChartMogul using an API key and the source you select. The connection is only active when you click Send … to ChartMogul or Import … to Google Sheet.
- To accurately import invoice data, customer and invoice data must be in the same source.
- Plan names must be written exactly as when they were created. For example, if you have a plan called “Gold Plan”, using “gold plan” or “Gold-Plan” will result in inaccurate reports.
During data transfer:
- Keep the Google Sheets app open and active when sending data. Closing a browser tab that is running Google Sheets may interrupt the data transfer.
- Google sets daily limits to the number of URL fetch calls that you can make, and the quota depends on your account type:
- Consumer (e.g., gmail.com) and G Suite free edition (legacy): 20,000 / day
- Google Workspace accounts: 100,000 / day
After sending data:
- When importing 5,000 customers or more, we recommend waiting one hour for ChartMogul to process all data before importing the remaining datasets. To check if data has been successfully imported, export data from ChartMogul to Google Sheets. Then, import remaining datasets.
- Keep a record of the data you send, as it can’t be exported from ChartMogul in the same format. This is useful to reference when renewing or canceling invoices or if you need to re-import your data.
Setting up the Google Sheets app
1. Creating an API key
Create a new API key, setting its Access Level to:
- Read & Write — to import data from Google Sheets to ChartMogul or exchange it both ways
- Read-only — to export data from ChartMogul to Google Sheets
2. Installing the Google Sheets app
From a Google Sheet, navigate to Extensions > Add-ons > Get Add-ons and search for ChartMogul.
Or, install the app directly from the Google Workspace Marketplace.
3. Verifying the time zone setting
Before importing data to ChartMogul, check the time zone of your Google Sheet by navigating to File > Settings. If necessary, change it to match the time zone of your ChartMogul account and click Save and reload.
ChartMogul imports dates using the spreadsheet’s time zone. When there is a time zone difference, ChartMogul converts the date and time for customers and invoices to the account time zone, which could result in inaccurate reports.
4. Connecting your ChartMogul account
To link your ChartMogul account to the Google Sheets app:
- From a Google Sheet, navigate to Extensions > ChartMogul Google Sheets integration > Open app.
- Copy your API key found under Profile > View Profile > API keys in ChartMogul and paste it into the Enter your ChartMogul API field.
- Click Save key.
5. Creating a source
Create one Google Sheets source and use it for all imports with the Google Sheets app.
Creating a Google Sheets source is only required when importing invoice data using the Billing Data tab. Don’t add a new source when using the Google Sheets app to import lead data, trial data, custom attributes, tags, or to export data.
Add Google Sheets as a source using the app or within ChartMogul.
Creating a source using the app
To add Google Sheets as a source using the app:
- From a Google Sheet, navigate to Extensions > ChartMogul Google Sheets integration > Open app.
- Switch to the Billing Data tab in the Google Sheets app.
- Under Create a Data Source, enter a unique name (such as “Google Sheets app”).
- Click Create data source.
Creating a source within ChartMogul
To add Google Sheets as a source within ChartMogul:
- Navigate to Data & Config > Sources and click Add Source.
- Select Google Sheets.
- Enter a name and click Next.
View all sources by navigating to Data & Config > Sources in ChartMogul.
Working with templates
When importing data from a Google Sheet, ChartMogul requires the data to be in a specific format.
To achieve this, use one of the templates provided:
Replace the template data with your business’ data to import invoice data, lead and trial data, or custom attributes and tags.
Customer template
Customers are companies or consumers with a potential, current, or past relationship with your business. ChartMogul uses this data to create customer records. Review alternative methods to import customers.
Use this template to import customers:
- From a blank Google Sheet, navigate to Extensions > ChartMogul Google Sheets integration > Open app.
- In the Google Sheets app, navigate to Billing Data > Customers and click Insert template.
This will add a formatted template with the following columns:
| Column name | Description | Field type | Value |
|---|---|---|---|
| Name |
The customer’s name — typically the first and last name of the primary contact for this client. This is equivalent to the Full name (legacy) field in ChartMogul. We recommend adding contact details using data tables, flexible CSV import, structured CSV import, or the API. |
Required if Email and External id are blank. | Accepts up to 255 alphanumeric and special characters. If left blank, ChartMogul uses External id or Email as the name. |
|
The customer’s email address. This is equivalent to the Email (legacy) field in ChartMogul. We recommend adding contact details using data tables, flexible CSV import, structured CSV import, or the API. |
Required if External id is blank | A valid email address. | |
| Company | The customer’s company or organization. This is equivalent to the Company Name field in ChartMogul. | Optional | Accepts up to 255 alphanumeric and special characters. |
| Country | The customer’s country. | Optional | Select the region code from the drop-down. |
| State | For US customers, the state where the customer is located. | Optional | Specify the state code. |
| City | The customer’s city. | Optional | Accepts up to 255 alphanumeric and special characters. |
| Zip code | The customer’s postal code. | Optional | Accepts up to 255 alphanumeric and special characters. |
| External id | The customer’s unique identifier specified by you (e.g., the customer’s ID in your database). | Required if Email is blank | Accepts up to 255 alphanumeric and special characters. |
| Lead created at | The date when the lead was created. Learn more about tracking leads and trials. | Optional |
A date formatted as YYYY-MM-DD or DD-MM-YYYY and time formatted as HH:MM:SS. When the time is not specified, ChartMogul sets it to midnight (00:00:00). |
| Free trial started at | The date when the free trial started. Learn more about tracking leads and trials. | Optional |
A date formatted as YYYY-MM-DD or DD-MM-YYYY and time formatted as HH:MM:SS. When the time is not specified, ChartMogul sets it to midnight (00:00:00). |
Plan template
The name and frequency of the product/services (or sets thereof) you offer on a subscription (recurring) basis.
Use this template to import plans:
- From a blank Google Sheet, navigate to Extensions > ChartMogul Google Sheets integration > Open app.
- In the Google Sheets app, navigate to Billing Data > Plans and click Insert template.
This will add a formatted template with the following columns:
| Column name | Description | Field type | Value |
|---|---|---|---|
| plan_name | The name of the plan. | Required | Accepts up to 255 alphanumeric and special characters. |
| interval_count | The number of interval units in the plan. For example, a quarterly plan has the interval unit of month and the interval count of 3. Learn more. | Required | Must be an integer greater than zero. |
| interval | The plan’s interval unit (day, month, or year). Learn more. | Required | Select day, month, or year from the drop-down. |
Invoice template
Details of the product/services being billed. ChartMogul uses this data to calculate subscription analytics and cash flow reports. Review alternative methods to import invoices.
Use this template to import invoices:
- From a blank Google Sheet, navigate to Extensions > ChartMogul Google Sheets integration > Open app.
- To identify subscriptions by either Subscription ID or Subscription number, navigate to Settings and configure the Use subscription ID, not subscription number checkbox. Learn the difference in the table below. Click Save settings.
- In the Google Sheets app, navigate to Billing Data > Invoices and click Insert template.
This will add a formatted template with the following columns:
| Column name | Description | Field type | Value |
|---|---|---|---|
| Email or External id | The customer’s email address or unique identifier specified by you (e.g., the customer’s ID in your database). | Required | Accepts up to 255 alphanumeric and special characters. |
| Type | The type of invoice:
|
Required | Select from the drop-down:
|
| Amount in cents |
The invoice amount in cents (pence, centavos, etc.). ChartMogul uses this data to calculate MRR and cash flow. The Google Sheets app only allows you to refund invoices in full. To make a partial refund, use data tables, structured CSV import, or the API. |
Required for subscriptions and non-recurring (one-time) charges |
Accepts numeric characters only (no decimal point). To add a negative value, right-click the column header, select Data Validation, and remove the rule Value is greater than -1. |
| Date | The date and time when the invoice was raised. | Required |
A date formatted as YYYY-MM-DD or DD-MM-YYYY and time formatted as HH:MM:SS. When the time is not specified, ChartMogul sets it to midnight (00:00:00). |
| Plan name | The name of the plan for this subscription as it appears in ChartMogul. If the plan on your invoice is not present in ChartMogul, create it before importing invoices using the plan template or data tables. | Required for subscriptions | Accepts up to 255 alphanumeric and special characters. |
| Subscription number |
A sequential number of a customer’s subscription. ChartMogul adds this number after the customer’s UUID to generate a unique subscription ID. For example, “sub_cus_048d83b4-e759-11ee-9643-336123433156_1” or “sub_cus_048d83b4-e759-11ee-9643-336123433156_2”. |
Required for subscriptions and subscription cancellations |
Use “1” for a customer’s first subscription, “2” for their second subscription, etc. To use subscription IDs instead, check Use subscription ID, not subscription number in the app’s Settings. |
| Subscription ID | A unique identifier for the subscription. | Required for subscriptions and subscription cancellations |
Accepts up to 255 alphanumeric and special characters. To use subscription numbers instead, uncheck Use subscription ID, not subscription number in the app’s Settings. |
| Currency | The customer’s billing currency. | Required for subscriptions and non-recurring (one-time) charges | Select a three-letter ISO code of the currency from the drop-down. |
| Service period start | The date when the subscription starts. | Required for subscriptions |
A date formatted as YYYY-MM-DD or DD-MM-YYYY and time formatted as HH:MM:SS. When the time is not specified, ChartMogul sets it to midnight (00:00:00). |
| Service period end | The date when the subscription ends. | Required for subscriptions |
A date formatted as YYYY-MM-DD or DD-MM-YYYY and time formatted as HH:MM:SS. When the time is not specified, ChartMogul sets it to midnight (00:00:00). |
| Quantity | The quantity of the invoice line item being billed. | Required for subscriptions. Optional for other invoice types. | Must be an integer greater than zero. Defaults to 1. |
| Proration | Specifies whether this is a prorated invoice. | Required for subscriptions | Select True or False from the drop-down. |
| Discount code | A reference code for the discount. | Optional | Accepts up to 64 alphanumeric and special characters. |
| Discount amount | The discount amount in cents (pence, centavos, etc.). | Optional | Accepts numeric characters only (no decimal point). |
| Tax amount | The amount of tax applied to the invoice. | Optional | Accepts numbers with up to two decimal places. |
| Description | A short description of the non-recurring item being charged to the customer. Only relevant for one-time charges. | Optional | Accepts up to 255 alphanumeric and special characters. |
| Invoice External ID | A unique identifier specified by you for the invoice. Typically the invoice number in your billing system. | Required | Accepts up to 255 alphanumeric and special characters. |
Customer attribute template
ChartMogul uses attribute data for segmentation. Review alternative methods to import lead and trial data, custom attributes, and tags.
Use this template to import lead and trial data, custom attributes, and tags:
- From a blank Google Sheet, navigate to Extensions > ChartMogul Google Sheets integration > Open app.
- In the Google Sheets app, navigate to Enrichment and click Insert template.
This will add a formatted template with the following columns:
| Column name | Description | Field type | Value |
|---|---|---|---|
| Email or External ID | The customer’s email address or unique identifier specified by you (e.g., the customer’s ID in your database). | Required | Accepts up to 255 alphanumeric and special characters. |
| Attribute | The name of a custom attribute or the attribute type (for tags and lead and trial dates). | Required | A custom attribute name or one of the following:
|
| Value | The value of the attribute. | Required |
For strings, integers, tags, and custom attributes, use the attribute’s value. For booleans, use either True or False. For timestamps and lead and trial dates, use a date formatted as YYYY-MM-DD or DD-MM-YYYY and time formatted as HH:MM:SS. When the time is not specified, ChartMogul sets it to midnight (00:00:00). |
| Type | The data type of the attribute. | Required | Select the type from the drop-down:
|
Exporting data from ChartMogul to Google Sheets
To send customers, plans, or invoices from ChartMogul to Google Sheets:
- From a blank Google Sheet, navigate to Extensions > ChartMogul Google Sheets integration > Open app.
- In the Google Sheets app, select Billing Data.
- Under Select a Data Source, select the source that contains your data and click Select data source.
- Depending on the data you’re exporting, select the Customers, Plans, or Invoices tab.
- Click Import customers/plan/invoices to Google Sheet.
Exporting 500 customers, plans, or invoices takes about 5 minutes. Keep the Google Sheet open while the export is in progress.
Deleting Google Sheets as a source
Deleting a source is permanent and cannot be undone.
When a Google Sheets source is deleted, all data from this source will be immediately and irrevocably removed from ChartMogul. Customer records stored in another source are not impacted.
To delete a Google Sheets source, navigate to Data & Config > Sources in ChartMogul. Find the source in the list and click the Settings icon. From there, click Delete This Source.
Next Steps
Use the Google Sheets app to:
- Import invoice data
- Import lead and trial data
- Import custom attributes and tags
- Export customers and metrics
To learn about common scenarios you may encounter when using the app, review our Google Sheets troubleshooting guide.