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
9. Deleting Google Sheets as a source

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

• Assign each customer a unique email address or External ID, if they do not already have one.
• Make note of the time zone in your Data Settings.
• Create a new read & write API key.

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.

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 record.

Formatting invoices for import

Tutorials

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:

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:

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:

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 record in ChartMogul and clicking Delete.

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

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

Deleting Google Sheets as a source

To delete a Google Sheets source, navigate to Data Platform > Sources. Find the source in the list and click the Settings  icon. From there, click DELETE THIS SOURCE.