Card Management : Google sheet : alert

[bengaltiger]

Hello,

Wanted to relax a bit,
so I am playing with google sheets and email notifications

1. it determines current due date , current payment
2. can send email notification 1 day before or on last date.

if interested let me know.

will finish by this weekend.

NB : You keep a copy of it and do whatever you want. so no data sharing at all with anyone.

MyCards_V0.2

Sheet1 BengalTiger,BT,Name,BT,Email Noti,Yes Test Date,16/03/2023,Email,youremail@gmail.com All Cards Total,6,000.00,Version,0.1 Bank Name,Card Name,Bill Date,Days2Pay,January,February,March,April,May,June,July,Augist,September,October,Novem,December,Payment,Current Amount,Paid 1,AMEX,Platinum,...

docs.google.com

Now full access. Make a copy.

BT

 

[bengaltiger]

see the attached images.

follow these steps then you will get notifications. You need to add a trigger, time bound, will be running every night
currently its payment day and day before email will come IF YOU HAVE NOT PAID

if you have paid already then make paid column to yes then you will not get email.
also once you get statement , add credit value to month

when the script will ask permission to access your file. give permission.

also add your email in the google sheet. Row 3, column E

 

[greenmail007]

It's copied.

Thanks alot.

 

[bengaltiger]

for email noti there was a bug.
change line no 54 as bellow in the script if you can or copy the google sheet and start fresh

to: emailaddr,

 

[meetprj]

Thanks for sharing

 

[bengaltiger]

Have a wish to have ny own android app very similar and much more. Will come back in few months. If all goes well

 

[bengaltiger]

Hello,

I made a mistake in Payment date calculation. I have fixed it. you can take in or just copy the formula in row 8, column R then drag for all colums in R

=IF(DAY(TODAY()) > D8,DATE(YEAR(TODAY()),MONTH(TODAY()),D8)+E8,DATE(YEAR(TODAY()),MONTH(TODAY())-1,D8+E8))

also I have added a function in script to reset the Paid to NO on bill generation day , you need to add trigger as well.

 

[Manoj Pant]

Hello,

I made a mistake in Payment date calculation. I have fixed it. you can take in or just copy the formula in row 8, column R then drag for all colums in R

=IF(DAY(TODAY()) > D8,DATE(YEAR(TODAY()),MONTH(TODAY()),D8)+E8,DATE(YEAR(TODAY()),MONTH(TODAY())-1,D8+E8))

also I have added a function in script to reset the Paid to NO on bill generation day , you need to add trigger as well.

Please upload revised/updated sheet we will copy it.

 

[bengaltiger]

Please upload revised/updated sheet we will copy it.

Please upload revised/updated sheet we will copy it.

done

MyCards_V0.2

Sheet1 BengalTiger,BT,Name,BT,Email Noti,Yes Test Date,16/03/2023,Email,youremail@gmail.com All Cards Total,6,000.00,Version,0.1 Bank Name,Card Name,Bill Date,Days2Pay,January,February,March,April,May,June,July,Augist,September,October,Novem,December,Payment,Current Amount,Paid 1,AMEX,Platinum,...

docs.google.com

added highlight (using script) and freeze as well.

when a column is selected , you will see yellow row and column

 

[bengaltiger]

I am using myself, looks like working nicely.

I have monthly yearly view
date calculated nicely.
alert working
payemnt "yes no" reset happening automatically.

 

[abby]

please copy the google sheet and play yourself.

if you need something, let me know I can try to add.
or if you found any bug do let me know

MyCards_V0.2

Sheet1 BengalTiger,BT,Name,BT,Email Noti,Yes Test Date,16/03/2023,Email,youremail@gmail.com All Cards Total,6,000.00,Version,0.1 Bank Name,Card Name,Bill Date,Days2Pay,January,February,March,April,May,June,July,Augist,September,October,Novem,December,Payment,Current Amount,Paid 1,AMEX,Platinum,...

docs.google.com

Thanks

 

[abhishek751]

Count me in.

 

[bengaltiger]

if you still need it , use the link above and make a copy. or dm me if you need help.

 

[mgforce]

Hey @bengaltiger - this is nice script & sheet combo. Just came across this while searching for the one made by @ChurningNoob

I know it's an old thread, but quite relevant.

When I try to run this script, giving me error

Error Exception: Cannot call SpreadsheetApp.getUi() from this context. onOpen @

Any pointers?

 

[cardio_guy]

Excellent 👍 congrats 🎉👏 for this effort.
Please can you also make a sheet for personal finance overall related document -
Like all bank accounts, insurance and other details - what my family needs to know document.
Also you can try making a investment tracker sheet as well.
All these integrated into an app later will make it useful for many people.