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

• Already have Zoho account. Not letting me log in

  I already have a Zoho account that is associated with my Google email and my phone number. Even though I'm already logged in to Zoho, when I click on the mail icon to access my email, it takes me to the pricing page. When I click on the free option, it

• ZOHO Mail App Not working

  There seems to be an issue with Zoho Mail App today. It is not connecting to server, internet is working fine, tried uninstalling app and reinstalling, loading circle keeps spinning round. Is there an update on the way?

• facing error 550 5.4.6 while sending emails

  Please help me fix this issue

• Allow Itemization for Recurring Expenses

  For whatever reason, one cannot itemize a Recurring Expense. This capability should be added. The use cases to support this are largely the same as what they were to allow for itemization in Expenses. Anything that would need to be itemized for a regular

• Zoho reply to not working. just reply to my self

  Hello. i using on my wordpress website a contact form from Wsform. i can set the reply to email there. normally it works. but since i am using your wordpress plugin zoho mail it doesn`t work. its not using the reply to (email from customer). I just can

• Painfully Slow Zoho mail

  Since yesterday Zoho Mail seems to have starting functioning very slowly and having a few bugs. It's slow to open mails, slow to send, slow to change between email accounts. Sometimes clicking on a particular folder (eg Sent folder) stops working and

• Can't receive any email from other platform

  Hello,everyone, i'm just join zoho and create two email accounts for my own business. I was using it to get a verified email from stripe, but can't receive it. and I use my private gmail account to send test email twice, first time show below reply, but

• Weekly Tips: Secure your attachment downloads with Zoho Mail

  Safety is one of our main concerns, whether it’s about device security or online protection. We use tools like fingerprint scanners, facial recognition, and two-factor authentication to keep our devices and email accounts secure. We use methods like OTP

• Your Incoming has been blocked and the emails will not be fetched in your Zoho account and POP Accounts

  Can some on help me regarding our account . thank you so much

• Zoho Creator integration with Sage 50

  Hi, Wondering if anyone has had any experience connecting Zoho to Sage 50 and could share any information on this matter. Thank you.

• Dropshipping Address - Does Not Show on Invoice Correctly

  When a dropshipping address is used for a customer, the correct ship-to address does not seem to show on the Invoice. It shows correctly on the Sales Order, Shipment Order, and Package, just not the Invoice. This is a problem, because the company being

• Conditional Email Forwarding

  How can I set conditional email forwarding of the users? For example: Mail should be forwarded to a address only if it comes from a particular sender. So, I want such email forwarding, which forwards mails based on particular conditions, like the incoming

• Incoming emails not appearing in Inbox

  Hello, I have an issue with incoming emails sent from my website (domain: h2ostop.si). Emails are visible in the Sent folder, which means they are successfully sent through Zoho SMTP, but they never appear in my Inbox. Nothing arrives in Inbox, Spam,

• Automatic Matching from Bank Statements / Feeds

  Is it possible to have transactions from a feed or bank statement automatically match when certain criteria are met? My use case, which is pretty broadly applicable, is e-commerce transactions for merchant services accounts (clearing accounts). In these

• Email Opt Out Question

  Has the problem where if a customer is emailed opt out prevents you sending standard emails? For me this feature is simply to stop any email marketing and should not block people from receiving emails via Zoho mobile, which makes no sense.

• Can No Longer Access Zoho Email Accounts from iPhone or iPad Apple Mail Apps ,.

  Keeps asking for password, Says ID or password incorrect. Tried creating a new app specific password. Same result. Is this possibly related to the server maintenance. Have verified all email settings, userid and password. This has worked for years until

• Latest update caused issue in using marathi typingzoho

  With latest update now marathi typing does Not work in zohonotebook. I preferred zoho over other because it was supporting marathi font without any distortion.. But after new update,keyborad simply does not work

• Login verification emails never received.

  I can't login to my account. You send a verification email, but it never arrives. This is a common problem, frequently caused by some relay point out there classifying the sender as a spammer. Is there anything I can do to bypass this? Maybe get a text

• Global lists for Multi select

  It would be great if I could select a global list to use for a multi select dropdown filed.

• Yahoo is rejecting e-mails sent from a Zoho server

  Diagnostic-Code: 4.7.0 [TSS04] Messages from 136.143.169.51 temporarily deferred due to unexpected volume or user complaints - 4.16.55.1; see https://postmaster.yahooinc.com/error-codes Remote-MTA: dns; mta5.am0.yahoodns.net

• Yahoo blocks e-mail sent from Zoho servers

  Getting this for a bunch of Yahoo addresses. Do you know if some of your servers got blacklisted? Diagnostic-Code: 4.7.0 [TSS04] Messages from 136.143.169.51 temporarily deferred due to unexpected volume or user complaints - 4.16.55.1; see https://postmaster.yahooinc.com/error-codes

• Working with dates and Function Field

  Hello friends! I'm trying to add days to a date, however the field function will always shows 00:00:00 after the resultant date. How can I display only the date, whithout the time? toDate(input.request_date.addDay(input.Prazo_acordado),"MM,d,yyyy") The code above will result something like "11-Feb-2020 00:00:00", but I want to display only "11-Feb-2020"

• What's New in Zoho Analytics - November 2025

  We're thrilled to announce a significant update focused on expanding your data connectivity, enhancing visualization capabilities, and delivering a more powerful, intuitive, and performant analytics experience. Here’s a look at what’s new. Explore What's

• Unable to send message;Reason:550 5.4.6 Unusual sending activity detected. Please try after sometime.

  Please help my account got blocked automatically, can you help me how to avoid it? Thanks so much

• Unusual activity detected from this IP. Please try again after some time

  When i try to create new addresses on my account i am getting this error, it has been 24 hours now and i am still getting this error can anyone help

• temporary system errorlouis

  J'essaye d'envoyer des mails avec mes 2 adresses mail qe nous avons sur le compte arthur@lepunch.fr et louis@lepunch.fr mais j'ai toujours le message temporaire system error, je reçois les mails mais impossible d'en envoyer a qui que ce soit

• How to Cancel/Delete Queued Mail Merge?

  Hi. I just tried to do a mail merge before realizing there's a limit on number of sends. I accidentally sent one of my lists twice, and all of those emails are currently queued. Is there any way to cancel or delete a queued mail merge? Would love to be

• Need to add a new admin for my domain

  Hello Zoho Support, I am the owner of the domain localeistanbul.com. The current super admin account (admin@localeistanbul.com) is not accessible. I do not want to reset or delete the existing account because I need to keep all existing emails. Please

• Possible Fraud Site.

  Hello. I received a text with the sender's name as zoho, claiming that my account was at risk and that I should sign in at https://verify.zohomails.ru/signin to verify my account. I signed in on the web address above, and a few days later someone hacked

• Zoho mail to Teaminbox

  Hello, We're searching for new mail program. Now I'm testing a bit with zoho mail and team inbox. My findings in the research: Pop mail throught zoho mail is almost instant. Any pop or imap via external provider takes a couple minutes to 15 minutes before

• Crear tarea CRM con recordatorio desde Zoho Flow

  Hola, estoy intentando crear desde Zoho Flow una tarea en CRM. Lo he logrado hacer pero sin recordatorio, ya que no se como se debe escribir el string adecuado. He probado varias alternativas, pero ninguna me funcionó hasta ahora. - FREQ=NONE;ACTION=EMAIL;TRIGGER=DATE-TIME:${FechaVto}

• Inquiry Regarding Automated Assignment of Zoho TeamInbox Messages using Zoho Flow and Deluge

  Hello, Our company is currently using Zoho TeamInbox, and we are interested in automating the assignment of responsible parties using tools such as ZOHO Flow and Deluge. Is it possible to achieve this? Allow me to provide more details. Currently, when

• Upgrade Zoho Desk Agent-Side Answer Bot to GenAI

  Hello Zoho Desk Team, We hope you're doing well. Following the recent announcements and rollout of the GenAI-based Answer Bot in Zoho SalesIQ (Nova '25), we’d like to formally request a similar upgrade for the Answer Bot used by agents inside Zoho Desk.

• Marketers' Space: The importance of warming up your sender domain

  Hello Marketers, Welcome back to yet another post! Today, we'll talk about why warming up your sender domain matters. Imagine you've recently started a business and want to share the news with your customers. You've designed a great email campaign using

• An Exclusive Session for Zoho Desk Users: AI in Zoho Desk

  A Zoho Community Learning Initiative Hello everyone! This is an announcement for Zoho Desk users and anyone exploring Zoho Desk. With every nook and corner buzzing, "AI's here, AI's there," it's the right time for us to take a closer look at how the AI

• Search Just Got Smarter in Notebook

  Hello there! Introducing Our New & Improved Search Experience! We heard your feedback! Many of you shared that our previous search had some challenges like • Inconsistent results across different clients • Limited accuracy in finding the right content

• Zoho Desk app update - AI Integration for IM Chats

  Hello everyone! We have now introduced AI integration for IM Chats within the Zoho Desk mobile app. To access the feature, please enable the 'Generative AI' settings on the desktop site(desk.zoho.com). Please refer to the help link attached below: Zoho

• Open A.I assistant Connect with Zoho Desk instant Message Conversations

  I would like to know how do I connect my instant messenger in Zoho desk with my Open A.I Gpt Assistant. this is very easy to setup using the Salesiq Zobot but when it comes to Zoho Desk i cannot figure how to make the connection. Ideal workflow Customers

• Cannot upgrade subscription plan due to payment error message

  Hi Zoho team, This is to request support on an issue I am facing during an upgrade I am trying to make to our company's yearly Zoho subscription. I am trying to add 3 more license to my plan and during the payment phase I get the below error as in the

• Enhancing Zia's service with better contextual responses and article generation

  Hello everyone, We are enhancing Zia's Generative AI service to make your support experience smarter. Here's how: Increased accuracy with Qwen One of the key challenges in AI is delivering responses that are both contextually accurate and empathetic while

• Next Page