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.

• Topic Participants

• Sasidaran K

  

• Luke

• Sasidaran Kumar

  

• 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

• filter on sheets mobile (iOS, iPadOS) does not work

  I have this issue on every spreadsheet, whether imported or created natively in Zoho Sheets. I can use on desktop without issue: desktop screenshot on iOS the filter dropdowns are not there and there is no way to add them mobile iPadOS looks the same

• 【Zoho CRM】商談タブへのデータインポート

  Zoho使用前にエクセルで管理していた商談情報を、Zoho一括管理のため、商談タブにインポートしたいのですが、お客さまの氏名だけが紐づけられませんでした。 「Zoho CRMにインポートする項目を関連付ける」のところが画像のようになっています。 （弊社では、「姓」を「★個人データ名」という項目名に変更し、フルネームを入れて使用しています。） どのようにしたら氏名をインポートできるかご存じの方がいらっしゃいましたら、ご教示いただきたく、よろしくお願いいたします。 （投稿先が間違っていましたらご指

• Canva Integration

  Hello! As many marketing departments are streamlining their teams, many have begun utilizing Canva for all design mockups and approvals prior to its integration into Marketing automation software. While Zoho Social has this integration already accomplished,

• Can't edit Segments

  Happening with 2 different Zoho One environments, in different browsers. Please fix.

• Error 554 Email Outgoing Error

  Our email address (info@tvbhouston) was recently migrated to a new domain host on WordPress, and our SSL certificate is still pending. We would like to continue using your service, but we are currently unable to send emails. Could you please re-enable

• Transfer ownership of files and folders in My Folders

  People work together as a team to achieve organizational goals and objectives. In an organization, there may be situations when someone leaves unexpectedly or is no longer available. This can put their team in a difficult position, especially if there

• Consumer Financing

  Does Zoho currently have a payment gateway (such as Stripe, Square, etc) which offers financing for customers? So, let's say the estimate we give the customer is greater than what they can afford at the time, but we can sell the service now, letting them

• Subform edits don't appear in parent record timeline?

  Is it possible to have subform edits (like add row/delete row) appear in the Timeline for parent records? A user can edit a record, only edit the subform, and it doesn't appear in the timeline. Is there a workaround or way that we can show when a user

• Zoho CRM - Option to create Follow-Up Task

  When completing a Zoho CRM Task, it would be very helpful if there was an option to "Complete and Create Follow-Up Task" in the pop-up which appears. It could clone the task you are closing and then show it on the screen in edit mode, all the user would

• Deluge function to copy parent record file upload field to child record file upload field

  I'm stuck trying to write a deluge function that is triggered via automation in child record "Appointments," confirms if a file is in file upload "Report" field of parent "Contacts" record via Contacts lookup field "Contact_Name". If no file is in parent

• Make panel configuration interface wider

  Hi there, The same way you changed the custom function editor's interface wider, it would be nice to be able to edit panels in pages using the full width of the screen rather than the currently max-width: 1368px. Is there a reason for having the configuration panel not taking the full width? Its impossible at this width to edit panels that have a lot of elements. Please change it to 100% so we can better edit the layouts. Thanks! B.

• Image Compression Options

  Much better if we have level of options to compress the image [20%, 40%...] We are dealing with service reports daily that has before and after photos (image field)- the file size too large and one thing, the current limit is 10mb or 15mb for report

• How to hide or archive a blog post temporarily in Zoho commerce website builder?

  I would like to temporarily hide or archive a blog post in zoho commerce website builder so that it doesnt appear on my website till I enable it again. I tried to look for this option but could not find it.  It only allows me to permanently delete a blog

• How to mix different types of inputs (such as dropdown list and textbox)

  Hi, I'm creating a form called "Room Reservations" for a company. I created a "table" using "Matrix Choice". I created "Room 1", "Room 2" and "Room 3" with the "Questions". I would then like to create two columns with the "Answers", one called "Department"

• How to Convert NSF to PST Format Effortlessly? - SYSessential

  It is highly recommended to obtain the error-free solution of the SYSessential NSF to PST converter to convert NSF files from Lotus Notes. Using this professional software, it becomes easier to convert all NSF database items, including emails, journals,

• Zoho Commerce - Poor Features Set for Blogging

  Hi Zoho Commerce team, I'm sure you will have noticed that I have been asking many questions about the Blogs feature in Commerce. I thought that it would be useful if I share my feedback in a constructive way, to highlight the areas which I feel need

• Subscription went to default (@zoho.com) address instead for custom domain

  Hello! So I bought a lite sub to test things out, wanting to use my own domain. However, after passing through all the verification steps (completed now), it seems that the sub I bought was assigned to the default email that I already had with Zoho and

• Pass shipping info to payment gateway Zoho Books to Authorize.net

  For some reason the integration from Zoho books to Authorize.net does not pass the shipping address. Authorize.net is ready to receive it, but zoho books does not send it

• Massive Zoho Books failure

  We have not received any communication or notification from Zoho, but we have detected that Zoho Books is not working for all our users. We cannot access or use Zoho Books. This is critical. We are trying to contact Zoho on the Spain telephone number,

• Does the Customer “Company Name” field appear anywhere in the Zoho Books UI outside of PDFs?

  Hi everyone, I’m trying to understand how the Company Name field is actually used in Zoho Books. There is a Company Name field on the customer record, but when viewing transactions like a Sales Order in the normal UI (non-PDF view), that field doesn’t

• Email outbox is now available in the sandbox

  Hello all! Testing emails without visibility has always been a blind spot in the sandbox. With the new Outbox, that gap is closed. You can now view and verify every email triggered from your sandbox, whether it’s through workflows, approvals, or mass

• Looking For Recruit Developer

  Hi everyone, I am looking for a Zoho Certified Developer to assist with a development project for MetalXpert. We are building a software system designed to bridge the gap between a candidate mobile app and an employer web portal using Zoho Recruit as

• sales IQ issue on website

  i integrated the zoho sales IQ code on the website but it is comming in distroted form i am sharing the screenshot below the website is bulit in wix platform

• Deprecation of the Zoho OAuth connector

  Hello everyone, At Zoho, we continuously evaluate our integrations to ensure they meet the highest standards of security, reliability, and compliance. As part of these ongoing efforts, we've made the decision to deprecate the Zoho OAuth default connector

• I need to know the IP address of ZOHO CRM.

  The link below is the IP address for Analytics, do you have CRM's? IP address for Analytics I would like to know the IP address of ZOHO CRM to allow communication as the API server I am developing is also run from CRM. Moderation Update: The post below

• Important Update: Google Ads & YouTube Ads API Migration

  To maintain platform performance and align with Google's newest requirements, we are updating the Google Ads and YouTube Ads integrations by migrating from API v19 to the newer v22, before the official deprecation of v19 on February 11, 2026. Reference:

• Zoho recruit's blueprint configuration is not functioning as mapped

  Current Status: Zoho Blueprint is not functioning as configured. Issue: We are moving a Candidate status in Zoho Recruit "for active file" but we encountered: "Status cannot be changed for records involved in Blueprint." This happens to various client

• Blocklist candidates in Zoho Recruit

  We’re introducing Block Candidate, which helps recruiters to permanently restrict a candidate from applying to current/future job openings. Once the candidate is blocked, they will no longer be able to participate in the recruitment process. This will

• Admin asked me for Backend Details when I wanted to verify my ZeptoMail Account

  Please provide the backend details where you will be adding the SMTP/API information of ZeptoMail Who knows what this means?

• Zoho Desk - Upsert Ticket

  Hi Desk Team, It is common to request more information from end-users. Using forms is a great way to ensure all the required information is collected. It would be great if there were an "upsert" option on the Zoho Form -> Zoho Desk integration which would

• All new Address Field in Zoho CRM: maintain structured and accurate address inputs

  The address field will be available exclusively for IN DC users. We'll keep you updated on the DC-specific rollout soon. It's currently available for all new sign-ups and for existing Zoho CRM orgs which are in the Professional edition. Latest update

• Client Side Scripts for Meetings Module

  Will zoho please add client side scripting support to the meetings module? Our workflow requires most meeting details have a specific format to work with other software we have. So we rely on a custom function to auto fill certain things. We currently

• Introducing Multiple Sandbox Types and Support for Module's Data Population

  Register here for the upcoming Focus Group webinar on Multiple Sandbox | Help documentation to learn more about the new enhancements Hello everyone,  Sandbox in CRM is a testing environment for users to create and test new configurations like workflow

• Creator Offline

  We had online access setup and working on our iphones. We have just set it up on an 'Android Tablet' and it is not downloading all the images? We use it to show customers our catalogue. Any ideas. Offline components all setup on both devices

• Updated font library: New font styles and custom font options in Zoho Sheet

  Zoho Sheet's font library now supports 500+ font styles in 60+ languages! The updated font library is stacked with new font styles, and some of the previously available font styles have been replaced with equivalent options. There are two ways you can

• Enable or disable any Field Rule!

  Hello Zoho Forms Community, We are excited to announce a powerful new enhancement to Field Rules that gives you greater control and flexibility in managing your form logic! Previously, if you wanted to temporarily deactivate a field rule, you had two

• Marketing Tip #20: Increase traffic with strong meta titles and descriptions

  Meta titles and descriptions are what people see first on search results before they ever click through to your website. If your pages use generic titles or basic descriptions, you miss the chance to stand out, and search engines may not know which page

• Kanban view on Zoho CRM mobile app!

  What is Kanban? The name doesn't sound English, right? Yes, Kanban is a Japanese word which means 'Card you can see'. As per the meaning, Kanban in CRM is a type of list view in which the records will be displayed in cards and categorized under the given

• Not able to delete a QC nor able to revert or create a cycle of manufacturing for QC failed Jobs

  Not able to delete a QC nor able to revert or create a cycle of manufacturing for QC failed Jobs

• Dheeraj Sudan and Meenu Hinduja-How do I customize Zoho apps to suit my needs?

  Hi Everyone, I'm Meenu Hinduja and my husband Dheeraj Sudan, run a business. I’m looking to tweak a few things to fit my needs, and I’d love to hear what customizations others have done. Any tips or examples would be super helpful! Regards Dheeraj Sudan

• Next Page