Tip #8 : Implement metered billing with Google Sheet

Tip #8 : Implement metered billing with Google Sheet

Hello everyone,

We've been sharing a tip every week to address specific business scenarios which can be implemented for your subscription business. Last week, we shared a tip about integrating online form builders with Zoho Subscriptions. This week, we will be looking at how you can implement metered billing in Zoho Subscriptions with Google Sheet.

Business Scenario:
Metered Billing  is the concept of charging your customers based on their usage of a product of service. With Zoho Subscriptions, you can charge your customers for a base plan initially and then charge an additional fee based on their usage using metered billing.

Generally, when a business wants to automate the metered billing process, they should have a back-end server that handles the metered billing readings. Once the reading is recorded, the back-end server has to process the difference between the last recorded reading and the current closing reading. If the customer has exceeded the base limit, the back-end server makes an API request to Zoho Subscriptions to update the invoice and charge an additional fee.

Let's consider the following scenario to understand this better. Zylker Electro provides electricity services to residential areas. They have a meter installed in all the houses of the residential area to record the power consumption. Every month, a Zylker personnel visits the houses in the residential area to note down the meter reading. Then, the difference between last month's meter reading and the current month is calculated. If the customer has consumed more than 500 units of power, they will charge the customer an additional 12 cents per unit.

To automate the above process, Zylker has to build a back-end server that will:
  • store the meter readings;
  • calculate the power consumed for every month; and
  • charge the customers accordingly.
Instead of building a back-end server, you can automate the process using the Custom Functions in Zoho Subscriptions and Google Sheets. With the help of Google Sheets, you can note down all the metered readings every month. Later, you can use Google App Scripts to fetch the readings and update the pending invoice using the Custom Functions.

How can you do it?

You can use Google Sheets to note down the readings every month (similar to the image shown below).




Field
Description
Last Recorded Reading
The meter reading at the end of the previous month.
Closing Reading
The meter reading at the end of the current month.
Status
The field is used to specify whether the invoice for a customer has been updated in Zoho Subscriptions or not.
ID
Unique Ids that are used to map the customers between Google Sheets and Zoho Subscriptions.

Google allows us to access and edit the data in spreadsheet with the help of Google Scripts. Coming back to the scenario, every month a Zylker personnel will record the readings in the Google Sheet. With Zoho Subscriptions, you can configure a custom function to run for the invoices in the Pending status. You must configure the Custom Function in such a way that:
  1. it fetches the meter readings from Google Sheets using Google script;
  2. processes the data and adds an additional charge to the invoice, if needed (based on the consumption);
  3. updates the Status in Google Sheets as Updated; and
  4. updates the Last Recorded Reading with current month's closing reading.
Once the custom function executes successfully, the invoice will be updated with the additional charge. The value in the Status field of the Google sheet will also be updated.

Getting started with Google Apps Scripts:

 Google Apps Scripts provides you with feature-rich APIs that allow you to build you own web applications. For our scenario, we will be using the Spreadsheet APIs to fetch and edit the spreadsheet document. We will create two scripts:
  1. The first script will be used to fetch the last recorded reading and the closing reading of a customer.
  2. The second script for changing the Status field of the customer once the invoice is updated.
To create a script in Google App Scripts:
  1. Go to and click the New Script option on the top-left corner.
  2. Copy the scripts provided above and modify it to suit your needs.
  3. Once you are done, click the Publish option on the top and select the Deploy as web app option.
  4. Provide the project name.
  5. Choose Anyone, even anonymous in the Who has access to the app dropdown.
  6. Click the Deploy button.
  7. Save the Web app URL which will be displayed on the screen for future reference.
Once the Google app scripts are successfully deployed:
  1. Create a new Custom Function in Zoho Subscriptions.
  2. Choose the Module as Invoice and Predefined event as Invoice created.
  3. Copy the Custom function from this GitHub link and modify it as per your requirements.
  4. Save the Custom Function and you are good to go.
Found this useful? Try it out for your subscription business and let us know. If you have any queries, feel free to comment below. We are here to answer them.

Cheers,
Sasidaran K,
The Zoho Subscriptions Team.

    • Sticky Posts

    • Tip #4 : Refer and Earn Workflow system for your Subscription Business

      Hello everyone, We've been coming up with a tip every week to address specific business scenarios which can be implemented for your business. In continuation to the previous post, this week we will be seeing about how you can implement the Refer and Earn Workflow system to acquire more customers. Why are Referrals important? Customer Acquisition is crucial for any business to sustain itself in a constantly evolving market. It involves persuading new consumers to purchase your products. Customers

    • Tip #2 - Cancellation of Offline Subscriptions based on the Expected Payment Date

      Hello everyone, As you all know, we are coming up with a custom function workflow every week to address specific business scenarios. In continuation of this previous blog, this week we will be seeing about Cancellation of Offline Subscriptions based on the Expected Payment Date. Business Scenario: In general, for any business, the customers make payments via online as well as offline. In case of subscription where the automatic collection of charge is enabled (aka online subscription), the customer's

    • Tip #3 : Send SMS Reminders for your Subscription Business

      Hello everyone, As you all know, we are coming up with a tip every week to address specific business scenarios. In continuation to the previous blog, this week we will be seeing how you can use SMS reminders for your Subscription Business. Using SMS messages for your Subscription Business: While managing your subscription business, more often than not, you need to keep your customers informed about upcoming renewals, payment reminders and cancellations. By keeping them informed in advance, you can

    • Tips And Tricks - Announcement

      Hello everyone, Every business is unique and each of them follows a specific workflow. While managing your customers' subscriptions with Zoho, you might have some needs unique to your business. Custom Functions helps you address such needs. This is a start to a series of posts, where we will be coming up with a custom function workflow every week that addresses specific business scenarios. Watch this space for further updates! Also, if you have any specific business scenario which needs to be addressed,

    • Tip #6 : Accessing external URLs from Zoho Subscriptions

      Hello everyone, We have been sharing a tip every week to address specific business scenarios which you can implement for your subscription business. Last week, we shared a tip about implementing the Approval Workflow system. This week, let's look at how you can access external URLs with the help of Custom Link. Business Scenario: Every business functions differently and has its unique requirements. At times, businesses might want to access other websites or applications by appending specific information

    • Recent Topics

    • Introducing Zoho Sprints 3.0

      Zoho Sprints is consistently evolving in steady increments. The introduction of the latest version, with its enterprise level solutions, brings to you advanced capabilities that propel your agile efforts in the right direction. Here's a quick glimpse

    • Customize Sign-out Button

      Are there some url parameters I can use to make a form button sign-out the user from the app? The sign-out link on the top right is small. Here's and example of the url for the top right sign-out: https://creator.zoho.com/logoutpage.jsp?sharedBy=niskypto&appID=212085000006568003&appLinkName=MYAPP&signOutUrl=niskypto/MYAPP/view-login/SOMEPAGE Note: In my account, the sign-out is set to redirect users to my website. Can I also override this with some url parameters? John M. Whitney

    • Please can the open tasks be shown in each customer account at the top.

      Hi there This has happened before, where the open tasks are no longer visible at the top of the page for each customer in the CRM. They have gone missing previously and were reinstated when I asked so I think it's just after an update that this feature

    • Tip #65 - Exploring Technician Console: Short Keys - 'Insider Insights'

      Hello Zoho Assist Community! Have you ever been in the middle of a remote support session, trying to pass a key combination onto the remote machine, only to find it's reflecting on the technician's computer. The Short Keys feature in Zoho Assist is here

    • Reading from and writing to Zoho Projects Custom Module with Deluge

      Does anyone know if there is a way to read from and write to the Custom Modules that Zoho now supports. I would love to be able to loop through a set of data and create the entities I need to for this new custom module I'm looking to put together.

    • Disappointment with Zoho Payments

      Dear Gowdhaman, I am writing to inform you that I am removing Zoho Payments from my website. I cannot continue to disappoint my customers due to the lack of UPI support, as has been the case with my experience so far. Please note that the 0.5% transaction

    • Evolução do modelo de ambientes: Dev, Homologação e Produção com pacotes versionados

      Hoje o Zoho CRM já oferece Sandbox, o que é um avanço importante para organizações que trabalham com customizações mais complexas. No entanto, na prática, o modelo atual ainda apresenta limitações significativas quando múltiplas equipes ou consultorias

    • Permissões granulares por usuário além do modelo baseado exclusivamente em perfis

      Atualmente, o modelo de segurança do Zoho CRM é fortemente baseado em perfis. Embora funcional, esse modelo apresenta limitações quando equipes possuem variações individuais de acesso dentro do mesmo grupo operacional. Em cenários reais, é comum que usuários

    • Add or update lookup field values during Blueprint transition

      Hello everyone, During blueprint transition users can add or modify the value of a lookup field. For instance, if the Tickets module includes a lookup field that connects it to records in the Assets module, agents can link the ticket to the correct asset

    • Can you limit SEO penalties by delaying the appearance of a pop-up?

      Google is not keen on pop-ups and has got even less keen on them with the new Core Web Vitals updates. But I like using pop-ups. If you delay the pop-up so it only appears 10 seconds after the page loads, do you avoid Google’s penalties? 

    • Adding bank details to the contact through API

      How to add bank-related information to the contact while creating it using API? The account number needs to be encrypted before sending it through API but not sure how to encrypt and get those values. Please guide me in this.

    • Restrict Payment Methods

      Allow us to restrict certain payment methods specific for each customer.

    • Clone Banking Transaction

      Why is there no option to CLONE a Transaction in the Banking module?? I often clone Expenses (for similar expense transactions each month) so I would also like to clone Income transactions. But there is no option in Banking to clone an existing Income

    • PDF limit

      Hello everyone, We have received an e-mail that we have reached our PDF limit. (see screenshot) However, I cannot find any reference to a PDF limit in our tariff plan (Premium). (see screenshot). What is the maximum number of PDFs that can be generated

    • How can I see content of system generated mails from zBooks?

      System generated mails for offers or invices appear in the mail tab of the designated customer. How can I view the content? It also doesn't appear in zMail sent folder.

    • Credit Card Pre-Authorization with later Capture/Settlement

      We really enjoy the convenience of being able to pay off a customer's invoice using our Auth.Net integration with Zoho Books. Unfortunately, we can only take advantage of this feature with a small percentage of our customers as it leaves a gaping hole

    • Accessibility in Zoho CRM: Not just a feature—a way to empower

      For instructions on setting up these controls, please check this help document: Configuring accessibility controls. Hello everyone, Today (December 3, 2024), on the International Day of Persons with Disabilities, we begin our journey towards a CRM that

    • Automation Series #1: Round Robin vs Direct Assignment in Zoho Desk

      Direct Assignment vs Round Robin: Choosing the right routing method in Zoho Desk This post is part of the "Desk Automation Series," Chapter 1. Through this series, we will help you choose the right automation type in Zoho Desk by comparing commonly confused

    • Invalid tax authority ID.

      How do I correct this ?

    • Pay Pal Paylater button

      I am testing the paypal setup to have my customers pay invoices with paypal and credit cards. But it seems to have two options. Paypal and Pay later. I don't want my customers access to pay later feature. How can I turn that part off.. My other integration

    • Zoho Workshops are coming to the Netherlands - Join us on 14-16 April in Amsterdam!

      Dear Zoho Community Members, After succesful recent editions in the UK and Scandinavia, we’re pleased to invite you to the upcoming Zoho Benelux Workshop 2026, taking place 14–16 April 2026 in Amsterdam. This three-day, in-person event at the Park Plaza

    • How to install Widget in inventory module

      Hi， I am trying to install a app into Sales Order Module related list, however there is no button allow me to do that. May I ask how to install widget to inventory module related list?

    • Sub form auto field population based on parent form

      I have a parent form called "Sites" with a text field called "Site". I have a subform called "Design Comments" (actual form name "Review Comments") with a lookup field name "Sites1" that looks up from the Sites form. I want the Sites1 lookup field to

    • Building Toppings #7 - Using schedules and workflow functions

      Hello Biginners, In our previous forum post, we explored install and uninstall actions and learned how to trigger custom logic the moment a topping is added or removed from an organization. In this post, we'll look at how to automate actions during regular,

    • Simplify scripting with Zia assistant bot

      Hello everyone, Building automation using Deluge custom functions gives users flexibility and control. Traditionally, creating these functions required writing scripts, testing the logic, and validating the configuration before using it. With Zia assistant

    • Zoho Sprints iOS app update: Global view, screen capture control, file encryption, tags enhancement

      Hello everyone! We are excited to introduce new features in the latest version(v2.1) of the Zoho Sprints iOS app update. Let’s take a quick look at what’s new. 1. Global view Global view brings all your project items into one centralised space. You can

    • Resource Management System built using Zoho CRM, Creator, Projects, and People:

      In a Resource Management System built using Zoho CRM, Creator, Projects, and People: CRM Deal Closed → Creator Allocation Engine → Zoho Projects Task Assignment What is the recommended architecture to handle dynamic reassignment when: an employee goes

    • Request to Remove LinkedIn Verification from My Emai

      I would like to submit a complaint regarding my Zoho Mail account. I previously used this email address to verify a LinkedIn account, but that LinkedIn account has now been closed. I need to remove or cancel the verification associated with the closed

    • Pin multiple columns and adjust column widths in CRM subforms

      Hello all, Subforms act as secondary forms or tables in which you can associate multiple line items to a primary record and thereby ensure more structured and comprehensive data organization. We've made some recent enhancements to subforms. Here's what's

    • Removing To or CC Addresses from Desk Ticket

      I was hoping i could find a way to remove unnecessary email addresses from tickets submitted via email. For example, a customer may email the support address AND others who are in the helpdesk notification group, in either the TO or CC address. This results

    • From Zoho CRM to Paper : Design & Print Data Directly using Canvas Print View

      Hello Everyone, We are excited to announce a new addition to your Canvas in Zoho CRM - Print View. Canvas print view helps you transform your custom CRM layouts into print-ready documents, so you can bring your digital data to the physical world with

    • Announcing Kiosk 1.1 - Customize screen titles, configure new fields & actions, use values from your Kiosk to update fields, and more.

      Hello all We are back again with more enhancements to Kiosk. So what's new? Enhancements made to the Components Add titles for your Kiosk screens and adjust its width to suit your viewing preferences. Three new fields can be added to your screen: Percentage,

    • CRM Percent custom fields: When will it show the % symbol and behave like %?

      1. Actually Percent custom fields fail to show the % symbol. 2. When in formulas Percent fields work like number: 100 x 5% = 5 ideal world 100 x 5% = 500 what happens actually 3. When importing Percent fields the % symbol has to be removed and the data

    • Introducing Color Coding of Picklist Values

      Dear Everyone, Greetings!! Zoho CRM is uplifting the user experience. Recently, we had some notable aesthetic improvements in CRM like Kanban View UI enhancement, New List view UI enhancement, color coding of tags, and color coding of picklists in meetings.

    • Where can I find the best mail backup tool for Windows?

      Later this evening I found Mail Backup Tool in google. Actually I was looking for a solution to download/save emails to my local drive. As I had plenty of important data stored in my email account. So i was not in a mood to take this thing lightly. This made me curious to found any software which can help me to backup my data to hard drive. Then I found the above application which was like a gem. A complete email backup solution for Zoho Mail, Gmail, Yahoo Mail, Office 365 and more than 40+ email

    • Mailbox storage showing incorrect usage

      My mailbox shows 4.99 GB used out of 5 GB. However, actual mailbox usage is only around 394 MB. Trash and Spam are already empty. IMAP/POP is not enabled. WorkDrive is not in use. This appears to be a storage calculation issue. Please help to recalculate

    • Super Admin Logging in as another User

      How can a Super Admin login as another user. For example, I have a sales rep that is having issues with their Accounts and I want to view their Zoho Account with out having to do a GTM and sharing screens. Moderation Update (8th Aug 2025): We are working

    • Es posible cambiar el lenguaje de los modulos del ASAP?

      Es posible cambiar el lenguaje de estos textos? Tengo Zoho configurado en español pero aun así me muestra estos textos en ingles:

    • Option to Automatically Publish Job Openings to the Career Website via API or Workflow

      Currently, when creating Job Openings using the Zoho Recruit API, the records are successfully inserted into the system. However, there is no way to automatically publish these Job Openings to the Career Website. In the Job Opening field data, there are

    • Evaluate applicants faster: Profile Summary and Skill Sets now in Applications

      Evaluating applicants often requires switching between modules to understand their skills and background. With this update, we’ve extended two capabilities directly to the Applications module: Skill Sets and Profile Summary. You can now review applicants

    • Next Page