Google Sheet Subscription Tracker – An Overview

Today, there is a software application for almost everything, and we all subscribe to a few products and / or services for our different use cases.

I am not sure about you, but sometimes it happens with me that either I end up auto-renewing the subscription I wanted to cancel OR end up not renewing the one I wanted to.

For sure, there are a few applications available in the market which can help me track my subscriptions, remind me in advance whether I want to renew or not.

In fact these subscription management apps do much more that, and hence they come at their own paid “subscription”………..

wait…. what…. Should I subscribe to application which will help me manage my subscription????

Now, these subscription management or tracking applications might be good for some, but I personally just want a simple system which reminds me well in advance to renew (or not renew) my subscription.

I do not want to sign up for “one more” application, one more login, just for this simple system.

So I created this simple system for myself with Google Sheet (and a few lines of code), and yes, if you like, copy my Google sheet and use it for yourself.

I am calling it “Subscription Tracking with Google Sheet” but you can really use it to track anything, because essentially it is a reminder system where you enter the event date (e.g subscription renewal date, birthday, anniversary etc), and how many days in advance you want to be notified of these events.

Now with this context, let me first tell you a bit about how this Google sheet based subscription tracker works, and then i’ll show you how you can copy and use it.

‍

How does Subscription Tracker work?

How does Google Sheet Subscription Tracker Work?

Let me first help you understand how the script works, and then i’ll show you how to set it up in your own Google account.

1. You would have a Google Sheet where you can enter your product or service details (e.g vendor name, service name) along with renewal date, and when do you want to be notified to renew it.

2. Google Apps script would read the values from the sheet, loop through each row to see if the difference between renewal date and today’s date == your reminder request day, and send you an email notification if it finds one.

3. This script would leverage Google Apps Script’s time based trigger functionality to automatically run every 8 hours, so you can just set it up once and that is it.

Note:
By default, script is setup to send email notifications to the person who sets it up with his/her Google account, but I have provided comments in the script to change this behavior and rather send these reminders to someone else (e.g directly to your customer).

Features & Limitations of Google Sheet Subscription Tracker

Here are some of the features and limitations of this solution, that would help you decide whether or not you should use it to track your subscriptions.

Features :

1. Free to use, no need to invest in any additional software application unless you have complex subscription tracking needs.

2. No data sharing with 3rd party developers, as this script runs in your own Google account once you make a copy of the script sheet.

3. No learning curve  as you would simply enter your subscription details in Google sheet which I think almost all of us are familiar with.

4. Extensible as you can easily extend / enhance this solution based on your needs (e.g adding more functions to it, adding visualization / charts etc).

5. Automated as it run automatically once you are done with the first time setup.

Limitations :

1. This script leverages Google Apps script’s “MailApp” service to send email reminders, which comes with daily quota of 1500 emails, which means this tracker can max handle up to 1500 email reminders everyday.

Overcome Limitation :

Honestly, I do not think an individual like you or I would have 1500 subscriptions expiring in a day, it might be the case if you run a business and want to use this subscription tracker for thousands of customers, and if thats the case, you can leverage the same script but with 3rd party transactional email service like Sendgrid, Amazon SES, Mailgun etc.

Subscription Tracker Setup

Step 1 : Copy Google Sheet

Login to your Google account and then make a copy of this Google Sheet as it'll also include associated Google Apps Script.

https://docs.google.com/spreadsheets/d/1YWIxBWsCmvEfQMTwWrW3fEKgExDS6zFcdjKDJSugAqw/copy

‍

1. You should first make a copy of the Google Sheet by clicking on the “Copy Google Sheet” button above.

2. You would see a “make a copy” option like the one shown below, click on it to make a copy of subscription tracking sheet.

You copied sheet would like the one shown in screenshot below.

Please note that if you change the name of the sheet tab (as annotated below), then you would also need to change it in the script sheet (we’ll talk about it later in this post).

You may change the sheet title to anything you like (e.g My Subscription Tracker).

‍

Step 2 : Google Apps Script Trigger

You would need to set up an apps script time based trigger, so your script runs automatically every 8 hours.

We now need to setup a time driven trigger so our script automatically runs every 8 hours and notify us in case of any subscription is due for renewal.

From the Google Sheet main navigation menu, go to Tools, and then click on “Script Editor” as shown in the screenshot below.

You would notice that associated Google Apps Script also came over when you made a copy of my sheet.

Click on the “Clock” icon to setup trigger as shown in the screenshot below.

You won’t have any triggers here yet, click on the “Add Trigger” button from the bottom left as you see in below screenshot.

Setup your trigger settings like the way shown below, so it runs our “sendReminder” function every 8 hours, and notify us in case if our script fails.

Once done, make sure to click on save button to save your changes.

Script would need your permission to run, select the account in which you copied the script sheet.

Click Allow to provide permission to the script to interact with your Google sheet, and send you email reminders.

Now you would see that your trigger has been successfully setup.

Now our subscription tracking solution is all set, and it should send us emails when the difference between renewal date (you entered in the sheet) and today’s date is equal to reminder date.

Example :
1. Renewal date in sheet is 07-09-2020
2. Today’s date is 07-02-2020
3. You have entered 7 days in reminder1 column (which means you want script to notify you 7 days before subscription renewal date).

Here 07-02-2020 – 07-02-2020 = 7 days
7 days = 7 days advance notification requested, and hence you would get the email as shown in the screenshot below.

Of course, you can change the email content in the script.

Subscription Tracker - FAQs

CAN I SEND EMAIL REMINDER TO SOMEONE ELSE?

By default, this script is configured to send email reminders to the person who is running this script.

However, this can be changed by tweaking the script a bit.

You would do the following for it-:

1. Add a new column in your Google sheet between column F and G and name this column whatever you want (e.g Email Address to notify)
   
   
2. Then in your google apps script, change recipientEmail variable to read value from your new column like this:const recipientEmail = Session.getActiveUser().getEmail()
3. TO
   
   const recipientEmail = row[6]
4. Finally, change the script to read values from your new column by replacing:const data = trackingsheet.getRange(2, 1, trackingsheet.getLastRow() – 1, 6).getValues()
   
   TO
5. const data = trackingsheet.getRange(2, 1, trackingsheet.getLastRow() – 1, 7).getValues()

‍

HOW CAN I CHANGE THE EMAIL CONTENT?

Absolutely, you can change email subject and body of the email.

Email subject is defined in emailSubject variable, feel free the content between ` `.

Email body is defined in emailBody variable, feel free to change the email body between ` `.

WOULD THIS SOLUTION WORK ON MOBILE?

This subscription tracking solution reads information from your Google Sheet, and send email reminders to you, if you have your email setup on mobile device, then you can view the email reminder sent by the scipt.

You can also add subscription details on mobile device once you install and setup Google sheet app on your phone.

There is no mobile app for google apps script, so to change your script, you may use your mobile browser.

‍

I AM GETTING AN ERROR, WHAT SHOULD I DO?

1. Please make sure that you watched the video above and followed the directions as shown in it.

2. If you still face any issue or have any feedback, either put a comment below this post or contact me via the website menu’s “Ask Your Question” button.