Tech – Page 3 – My blog

I was working today on a Google Spreadsheet with more than 5,000 lines and I had to filter some columns. Google already have a pretty good filter by value, but you can also use conditions or custom functions.

I was looking for a way to filter every cell that doesn’t begins with exact 2 numbers. That one was pretty simple and fast (please note that I use Google Spreadsheets with my locale in Brazil, so you may have to adapt the function):

=NO(REGEXMATCH(B2;"^[0-9]{2}"))Code language: JavaScript (javascript)

Next, I would like to filter other column for avoiding cells that begins with “+”, AND is not blank, AND doesn’t contains 8 or 9 numbers. The 1st and 2nd rules were easy, but I had some trouble with the 3rd.

Searching on the internet, I found this Stackoverflow post that solved my problem. This is my function:

=NOT(OR(REGEXMATCH(AO2;"^\+");REGEXMATCH(AO2;"^(?:[^0-9]*[0-9]){8,9}[^0-9]*$");ISBLANK(AO2)))Code language: JavaScript (javascript)

What I learned today was how to use (?:…). “(?:…) is a non-capturing group that allows quantifying a sequence of subpatterns and is thus easily adjustable to match 3, 4 or more specific sequences in a string” as Mr. Wiktor Stribiżew explained on his answer.

I had already used (?:…) for the purpose of only non-capturing group sometime ago, but for “quantifying a sequence of subpatterns and is thus easily adjustable to match 3, 4 or more specific sequences” was new to me.

You can try his demo at https://regex101.com/r/DyP5e7/1.

I hope it may help somebody else too.

My Gmail account is my main email. I get dozen (if not hundreds) of e-mails everyday. Most of them are already filtered with dozen of rules that I’ve been creating since 2006. But there are a functionality that was missing: alert me whenever an important e-mail arrives.

I tried using filters combined with notifications alerts on Android or IOS, but after struggling for sometime, I gave up.

Years later, on my “automation my life” mission, I decided to search for some Google Scripts that could ease my life. That’s when I found Andreas Hochsteger’s Gmail2GDrive. I gave a try and it worked flawless, nowadays many of my bills are saved automatically on my Google Drive making my life much easier.

Then yesterday I remembered of my long lasting issue of not being able to be notified of my important e-mails. As my studies on Python have increased, also my knowledge and use of Telegram API. So I thought that maybe I could join Gmail2GDrive and Telegram API and personalize a solution, that’s when I adapted Gmail2Gdrive.

I got the project and made it much simpler (not better, simpler in functionalities) and adapted it to work with Telegram. I set the script to run every 5 minutes and voilà, now I have my notification system working.

If you want to check it, go to Gmail2Telegram on Github.