Importing billing data using Google Sheets

You’ll need to be an Admin or Owner in ChartMogul to add and manage data sources. Read more about user roles and permissions.

Add Google Sheets as a data source to import data from a billing system not supported by ChartMogul, for customers who are billed outside of a supported billing system, or to import historical billing data.

Billing data can also be imported using CSV, Data Platform, or our Import API.

Here’s what we cover in this article:

  1. Installing the Google Sheets app
  2. Setting up the Google Sheets app
  3. Creating a data source
  4. Importing plans
  5. Importing customers
  6. Importing invoices
  7. Tutorials
  8. Troubleshooting

Best practices

Before getting started with the Google Sheets app, familiarize yourself with the following best practices to import data successfully and accurately:

  • Before sending new data, always create a new Google Sheet. Re-using sheets will re-send the existing data and cause duplicates, and also impact the speed of the export (as each row will need to be processed again).
  • Keep the app open and active while data is exporting. If you close a browser tab that is running Google Sheets, it may interrupt the export.
  • Inserting a template will erase any existing data in the current sheet.
  • When importing invoices, plan names must be written exactly as when they were imported. For example, if you have a plan called “Gold Plan”, you cannot use “gold plan” or “Gold-Plan”.
  • It can take some time for data to be reported in ChartMogul. Check if data has been successfully imported by exporting data from ChartMogul to Google Sheets.
  • Keep a master list of uploads for your records.
  • There are daily limits to the number of calls that you can make, depending on your Google account:
    • Consumer (gmail.com): 20,000 / day
    • Google Apps free edition (legacy): 50,000 / day
    • G Suite: 100,000 / day

Before you begin

Installing the Google Sheets app

Install the app from the Google Workspace Marketplace or open a new Google Sheet and navigate to Add-ons > Get Add-ons and search for ChartMogul.

From a sheet, navigate to Add-ons > ChartMogul Google Sheets integration > Open app. The app will open in the sidebar of the Google Sheet.

Before importing any data, check the time zone of your Google Sheet by navigating to File > Spreadsheet settings.

Dates will be imported using the spreadsheet time zone, not your ChartMogul account time zone. If there is a timezone difference, the date and time for customers and invoices will be adjusted automatically in ChartMogul and could result in inaccurate dates.

 

Setting up the Google Sheets app

  1. Open the ChartMogul Google Sheets app.
  2. Copy your API key and paste it into the ChartMogul API Account Token and ChartMogul Secret Key fields. Click Save keys.
  3. Select Billing Data.

Creating a Data Source

Add Google Sheets as a data source. In the app, enter a unique name (such as “Google Sheets”), and click Create Data Source.

You will only need to do this once. Next time you open the Import section of the app, you will be prompted to select an existing data source.

View all data sources from Data Sources.

Importing Customers

Customers must be imported before any invoices or subscription data.

Customer data must be in a specific format. Follow these steps to format and import your customer data:

  1. Open a new sheet.
  2. Click Customers from the Billing Data sub-menu.
  3. Click Insert template to insert sample data in the required format into the current sheet.
  4. Use the template to add your customer data. The only required fields are Name and either Email or External Id. All other fields are optional.
  5. Click Send customers to ChartMogul. Keep the sheet open while the import is in progress.

Check if your customers have been imported successfully by clicking Import customers to Google Sheet in the Google Sheets app or by navigating to Customers.

Importing Plans

Before adding invoices to your customers, you must import your subscription plans.

Follow the steps below to format and import your plans:

  1. Open a new sheet.
  2. Click Plans from the Billing Data sub-menu.
  3. Click Insert template to insert sample data in the required format into the current sheet.
  4. Use the template to add your subscription plan data. All fields are required.
  5. Finally, click Send plans to ChartMogul. Keep the sheet open while the import is in progress.

Check if your plans have been imported successfully by clicking Import plans to Google Sheet in the Google Sheets app or by navigating to Profile > Admin > Plans.

Importing Invoices

Once you have imported your plans and customers into ChartMogul, add invoices to your customers. Every time you charge your customer, you should submit a new invoice in Google Sheets.

Invoices are used to generate subscriptions, transactions, refunds, and MRR movements in ChartMogul.

If you have invoices in the current sheet that have already been sent to ChartMogul, and click Send Invoices to ChartMogul this will create duplicates, especially when there is no Invoice ID. To avoid duplication, add a new sheet and use a unique Invoice External ID.

Follow the steps below to format and import your customer invoices:

  1. Open a new sheet.
  2. Click Invoices from the Billing Data sub-menu.
  3. Click Insert template to insert sample data in the required format into the currently sheet.
  4. Use the template to add your invoice data. The fields Email, Type, and Date are all required. Additional fields are required depending on the Type of invoice selected. See the table below for more information.
  5. Finally, click Send invoices to ChartMogul. Keep the sheet open while the import is in progress.

Check if your invoices have imported successfully by navigating to Customers and viewing a customer profile.

Formatting invoices for import

Column name Value
Email or External id

REQUIRED

Customer’s email address or unique identifier specified by you (e.g., the customer’s id in your database).

Type

REQUIRED


The type of invoice.

Options:

  • one_time
  • subscription
  • subscription_cancellation
  • refund
Amount

REQUIRED FOR ONE_TIME AND SUBSCRIPTION TYPE INVOICES

The amount charged towards this line item for the specified service period after discounts and taxes have been applied. Expected in cents (or pence for GBP, etc.).

Date REQUIRED FOR ALL INVOICE TYPES

The date on which this invoice was raised. Must be a date in the past. Must be a properly formatted Google Sheets date.

If a time is not specified in a date (e.g., 2017-04-04 15:21), a date will be imported with the time set at midnight (e.g., 2017-04-04 00:00).

Plan name REQUIRED FOR SUBSCRIPTION TYPE INVOICES

Display name of the plan. Accepts alphanumeric characters. Irrelevant for one_time, refund, or subscription_cancellation type invoices.

MUST ALREADY BE CREATED USING THE CHARTMOGUL GOOGLE SHEETS APP.

Subscription number

REQUIRED FOR SUBSCRIPTION AND SUBSCRIPTION_CANCELLATION TYPE INVOICES

The ID of the subscription. Can be 1, 2, 3, etc. If the customer already has a subscription with the number ‘1’, this will update the subscription. If left blank, ChartMogul will generate a number. Irrelevant for one_time and refund type invoices.

If you would like to set a specific subscription ID for the customer, for example, to update an existing customer’s metrics, you can change this field to subscription ID in the Google Sheets app Settings > enable “Use subscription ID, not subscription number”> click Save settings.

Currency

REQUIRED FOR ONE_TIME AND SUBSCRIPTION TYPE INVOICES

The 3-letter currency code of the currency in which this invoice is being billed, e.g., USD, EUR, GBP. Irrelevant for refund or subscription_cancellation type invoices.

Service period start

REQUIRED FOR SUBSCRIPTION TYPE INVOICES

The start of the service period for which this subscription is being charged. Irrelevant for one_time, refund, or subscription_cancellation type invoices.

Service period end

REQUIRED FOR SUBSCRIPTION TYPE INVOICES

The end of the service period for which this subscription is being charged. Irrelevant for one_time, refund, or subscription_cancellation type invoices.

Quantity

Only relevant for subscription invoices.

The quantity of the line item being billed. Defaults to 1. Irrelevant for one_time, refund, or subscription_cancellation invoices.

Proration

Only relevant for subscription invoices.

If this is a prorated charge, then set this attribute to true. Irrelevant for one_time, refund, or subscription_cancellation invoices.

Discount code If any discount applies to this invoice, then a reference code for the discount. Only relevant for subscription and one_time invoice types.
Discount amount If any discount has been applied to this line item, then the discount amount in cents. Only relevant for subscription and one_time invoice types. Defaults to 0.
Tax amount The tax that has been applied to this line item (in cents). Only relevant for subscription and one_time invoice types. Defaults to 0.
Description

Only relevant for one_time invoice types.

A short description of the non-recurring item being charged to the customer. Irrelevant for subscription, refund, or subscription_cancellation type invoices.

External ID

A unique identifier specified by you for the invoice. Typically the Invoice Number in your system.

 

Tutorials

Import customers and plans into ChartMogul before importing invoices. Review the steps above to get started.

Subscribe a customer to a plan

Once you have imported a customer and the necessary plans, navigate to the Billing Data section of the Google Sheets app, click Invoices and then click Insert template. You can add the customer’s subscription invoices by adding new rows to the Google Sheet for each invoice and filling in the following fields: Email, Type (should be ‘subscription’), Amount (in cents), Date, Subscription number<, Currency, Service period start, and Service period end. Optional fields include Quantity, Proration, Discount code, Discount amount, and Tax amount.

Once you have filled in the necessary fields, click Send invoices to ChartMogul to add subscription invoices to your customers.

Making changes to a customer’s subscription

To update a customer’s subscription, add a new row to the Google Sheet indicating the new amount paid each service period. If the change is prorated, add “true” under the Proration header.

Renew a customer’s subscription

To renew a customer’s subscription for another billing period, add a new row to the Google Sheet and fill in the fields. If the change is prorated, add “true” under the Proration header.

Update an invoice

To update a customer’s invoice, perhaps to change the amount paid, simply export the invoice again using the Sheets app. Please note that you must specify the same Invoice External ID for the invoice to be updated. If you did not specify an Invoice External ID, it would not be possible to update the invoice from Google Sheets. You can delete an invoice using the API.

Adding a non-recurring payment to a customer

To add a one-time payment to a customer (e.g., a setup fee), add a new row to your Google Sheet and add the customer’s email address to the Email field, enter “one_time” in the Type field, and then fill in the Amount and Date fields. If required, you can also fill in the Discount Amount, Discount Code, Tax, and Description fields.

Adding additional subscriptions to a customer

If a customer has multiple subscriptions, you can add additional subscriptions by specifying a Subscription number. If this field is left blank, ChartMogul will autogenerate a number. You can add a completely new subscription to a customer by entering a different value in the Subscription number field, for example, “2”. Remember to keep track of a customer’s subscription numbers, as if you add multiple subscriptions to a customer you will need them when canceling a customer’s subscriptions.

Canceling a customer’s subscriptions

To cancel a customer’s subscription, use the customer’s email address in the Email field, enter “subscription_cancellation” in the Type field, and add the Date and the Subscription number. If the customer only has one subscription, or you left this field blank when creating a customer’s subscription, enter “1” for the Subscription number field. Otherwise, enter the corresponding subscription number that you would like to cancel.

Refunding a customer’s invoice

To refund a customer’s payment, use the customer’s email address in the Email field, enter “refund” in the Type field, and add the date of the invoice paid as reported in ChartMogul in the Date field. An invoice can be refunded twice.

Example: Prorated charge for an additional subscription

Let’s imagine that:

  • Your customer Adam Smith bought a monthly subscription to the Bronze Plan on September 1st, 2018, and paid $50.
  • Adam then upgraded his subscription by adding another seat on the Bronze Plan on September 16th.
  • You charged him a prorated amount of $25 for the additional seat for the remaining service period until the end of September 30th.

To add this prorated charge, you must add a row in your Google Sheet with the field prorated set to true. Use the same subscription id or subscription number (depending on your Google Sheets app setting). Add the prorated service_period_start and service_period_end timestamps, the amount_in_cents, and the quantity set to 1.

Here’s how the initial subscription and the prorated entry should look like:

Prorations specified correctly: If specified correctly as demonstrated, this example prorated charge will result in the MRR for Adam Smith expanding by $50, from $50 to $100 MRR on September 16th.

Example: Prorated charge with plan change

Let’s imagine that:

  • Your customer Adam Smith bought a monthly subscription to the Bronze Plan on August 1st, 2018, and paid $50.
  • Adam then changed his plan to the Copper Plan, which costs $60 a month, on August 16th.
  • You invoice Adam a prorated amount of $30 for the Copper Plan until the end of August and credit him a prorated amount of $25 for the unused time on the Bronze Plan.
  • He pays the difference of $5 for the remaining service period ending August 31st.

To add this prorated charge, you must add two rows in your Google Sheet with the field prorated set to true. Use the same subscription id or subscription number (depending on your Google Sheets app setting) and the same service_period_start and service_period_end timestamps. One entry will have the new plan (Silver Plan) with a positive amount_in_cents indicating the additional payment. The other one will have the old plan (Bronze Plan) with a negative amount_in_cents indicating the credit amount.

Here’s how the initial subscription and the two prorated entries should look like:

Prorations specified correctly: If specified correctly as demonstrated, this example prorated charge will result in the MRR for Adam Smith expanding by $10, from $50 to $60 MRR on August 16th. The transaction will contribute $5 to the Gross Cash Flow and Net Cash Flow on August 17th.

Example: Prorated downgrade with a reduction in subscription quantity

Let’s imagine that:

  • Your customer Adam Smith bought an annual subscription to the Gold Plan on January 1st, 2018, and paid $200 for 20 seats.
  • Adam decided to reduce the number of seats midway through his annual subscription to 10 on July 1st.
  • You gave Adam a prorated amount of $50 for the unused time on the Gold Plan for ten seats as credit.

To add this prorated charge, you must add a row in your Google Sheet with the field prorated set to true. Use the same subscription id or subscription number (depending on your Google Sheets app setting). Add the prorated service_period_start and service_period_end timestamps, a negative amount_in_cents indicating the credit amount, a negative quantity of -10 indicating the reduction in seats.

Here’s how the initial subscription and the prorated entry should look like:

Prorations specified correctly: If specified correctly as demonstrated, this example prorated credit will result in the MRR for Adam Smith contracting by $8.24, from $16.67 to $8.43 MRR on July 1st.

Troubleshooting

Rows that have not been successfully imported will be highlighted in red. Learn more about common error messages.

Delete a customer by navigating to their customer profile in ChartMogul and clicking Delete.

Delete a plan by navigating to Admin > Plans and clicking Delete.

Delete a data source (and all imported data) by navigating to Data Sources. Then, from the settings of the selected data source, click DELETE THIS DATA SOURCE.

Get answers to common questions with our Google Sheets troubleshooting guide or contact support.

Was this article helpful?