Importing billing data using Google Sheets

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

Add Google Sheets as a 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 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 source

Add Google Sheets as a source. In the app, enter a unique name (such as “Google Sheets”), and click Create a 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 source.

View all sources by navigating to Data Platform > 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 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.

To set a specific subscription ID for a customer (for example, to update an existing customer’s metrics) you can change this field to Subscription ID in the Google Sheets app by navigating to Settings > enable Use subscription ID, not subscription number > and clicking 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.

Within the Google Sheets app, navigate to Billing Data > Invoices > Insert template. Here, we outline how to add common billing scenarios.

Add a new row for each invoice.

Subscribe a customer to a plan

Complete the following fields: Email, Type (should be ‘subscription’), Amount (in cents), Date, Plan name, 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 invoices to your customers.

Update a customer’s subscription

To update a customer’s subscription, add a new row to the Google Sheet and update any required columns, such as the new amount paid and service period dates. If the change is prorated, add TRUE under the Proration column.

Renew a customer’s subscription

To renew a customer’s subscription for another billing period, add a new row to the Google Sheet and specify the Subscription ID. Add updated information such as Amount, Plan name, Service period start, and Service period end. If the change is prorated, add TRUE under the Proration header.

Update an invoice

To update a customer’s invoice, for example to change the amount paid, simply send invoice to ChartMogul again. Please note that you must specify the same Invoice External ID for the invoice to be updated. If you do not specify an Invoice External ID, it's not 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, add them 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 seat

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 Silver Plan, which costs $60 a month, on August 16th.
  • You invoice Adam a prorated amount of $30 for the Silver 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 source (and all imported data) by navigating to Sources. Then, from the settings of the selected source, click DELETE THIS SOURCE.

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

Was this article helpful?

We’re sorry to hear that. Would you like to share more feedback?


Thanks for your feedback!