Get Email Alerts When COVID-19 Vaccines Become Available Near You

India is currently in the midst of a second wave of the Coronavirus and this one is far more devastating than what we have seen last year. The country is reporting close to 400,000+ new cases every day but the actual count of daily infections could be much higher.

The COVID-19 vaccination program in India was earlier available to people above 45 years of age but starting today (May 1), anyone above the age of 18 years is eligible for Covid-19 vaccination.

Vaccine Availability

COVID-19 Vaccines Near Me

The government’s official website - cowin.gov.in - has a useful search section that allows you see the nearby vaccination centers in your city or zip code. You’ll also know how many vaccine doses are available at a specific center and the earliest date when the next batch of vaccine stocks are due.

Based on this public data, I have developed an open-source vaccine tracker that will monitor the vaccine availability near you and will send email alerts as stocks become available. The source code of the project is available on Github.

Build your own Covid-19 Vaccine Tracker

Step 1: To get started, click here to make a copy of the Vaccine Tracker Google Sheet in your Google Drive.

Step 2: Click the Vaccine Tracker menu (near the Help menu) and choose Enable as shown in the screenshot.

Vaccine Tracker Google Sheet

Step 3: You may see an authorization window. If you get an “unverified app” message, click the Advanced link and choose “Go to Vaccine Alerts”. The app is 100% safe and open-source.

Step 4: Go to Step 2 now and choose the Enable menu again to launch the tracker. Enter one more pin codes, the email address where you wish to receive the alerts and the age group for which you need to monitor vaccine availability.

Click the Create Email Alert button and your system is up and running. Google Sheets will run this monitor every day and send an email at 8 am indicating the availability of vaccines in your specified areas.

Here’s a copy of the email sent by the vaccine tracker.

Email Alert - Vaccine Tracker

And if you ever wish to stop Google Sheets from tracking vaccine availability, go to the same sheet and choose Disable from the menu.

How to Print the Function Call Flow with Stack Trace in JavaScript

The printStackTrace method of Java is useful for handling exceptions and errors during development. It tells you the exact line number in your source code and the file name where the problem occurred.

If you are working in the JavaScript / Google Apps Script world, you can use the console.trace() method to output the complete stack inside the web console ( or StackDriver logs for Google Scripts).

A better alternative is that you parse the stack property of the Error object. This contains the entire stack trace along with line numbers, column position and the function names.

function printStackTrace() {
  const error = new Error();
  const stack = error.stack
    .split("\n")
    .slice(2)
    .map((line) => line.replace(/\s+at\s+/, ""))
    .join("\n");
  console.log(stack);
}

function three() {
  console.log("Function Three!");
  printStackTrace();
}

function two() {
  console.log("Function Two!");
  three();
}

function one() {
  console.log("Function One!");
  two();
}

one();

The output of the printStackTrace method looks something like this. The first few lines are the program output and as you move downwards, you’ll see a list of methods which invoked the previous method.

Function One!
index.js:16 Function Two!
index.js:11 Function Three!
index.js:7 three (index.js:12:3)
two (index.js:17:3)
one (index.js:22:3)
index.js:26:3
index.js:27:3

You can use the stack trace to know the exact location of the problematic code in your JavaScript app or if you simply want to print the function calling flow of your JavaScript program without even throwing an exception.

How to Perform IP Address Lookup with Google Sheets

Websites can determine the visitor’s geographic location using their IP address and serve more relevant content. For example, a weather website may use your IP address to estimate your approximate location and provide weather forecast for your current city automatically. A currency exchange website can determine your default currency based on your country which is detected from your IP address.

Google Sheets - IP 2 Location

There are free web IP lookup services, ip2c.org for example, that will reveal the country of your client’s IP address with a simple HTTP request. We internally use that service at Digital Inspiration to determine the payment service provider on the checkout page.

Bulk IP Lookup with Google Sheets

IP2Location is another good alternative that retrieves more detailed geolocation information for any IP address. The IP location lookup service can retrieve the client’s country, city name, region, the ISP name and more.

If you have a bulk list of IP addresses, you can use Google Sheets to estimate the corresponding geographic details for each of the addresses in few easy steps:

  1. Click here to make a copy of the Google Sheet for performing IP lookups in bulk.

  2. Paste the list of IP addresses in column A, one per row. The lookup service works for both IPv4 and IPv6 addresses.

  3. Enter your key in cell E1. If you have a small list of IP address, use demo as the key or get your own API key from ip2location.com.

  4. Click the Run button, authorize the script and watch as the geographic details and ISP names are populated in the sheet.

IP2Location Web Service Demo

How IP2Location Script Works

Internally, the Google Sheet uses the IP2location web service with Google Apps Script to transform IP addresses into geographic region.

It uses the UrlFetchApp service to perform multiple HTTP requests in a single batch for improved performance. Here’s the full source code:

const ip2location = () => {
  // Get all the input data from Google Sheet
  const ss = SpreadsheetApp.getActiveSheet();
  const data = ss.getDataRange().getDisplayValues();

  // Use your own API key or use demo key
  const apiKey = data[0][4] || "demo";

  // Generate API URL for IP address
  const getUri_ = (ipAddress) => {
    const API_URL = "https://api.ip2location.com/v2";
    return `${API_URL}/?ip=${ipAddress}&key=${apiKey}&package=ws4`;
  };

  const requests = [];

  for (let r = 2; r < data.length; r++) {
    const [ipAddress, countryName] = data[r];
    // Only process rows where the country is blank
    if (ipAddress && !countryName) {
      requests.push({ url: getUri_(ipAddress), rowNumber: r + 1 });
    }
  }

  // Make API calls in bulk using the UrlFetchApp service
  UrlFetchApp.fetchAll(requests).forEach((content, i) => {
    // Parse the JSON response
    const { city_name, country_name, isp, response } = JSON.parse(content);

    // If the response is populated, the API call failed
    if (response) throw new Error(response);

    // Write the response data to Google Sheet
    const values = [[country_name, region_name, city_name, isp]];
    ss.getRange(requests[i].rowNumber, 2, 1, 4).setValues(values);
  });

  // Flush all changes
  SpreadsheetApp.flush();
};

How Spammers Avoid the Gmail Spam Filter through Google Forms

Gmail is very effective at filtering spam emails but spammers seem to have figured out a new way to bypass the spam filters and send emails that land right in the user’s inbox. The emails are sent through Google Forms and because the messages originate from Google’s own email servers, they do not get caught in the spam filters.

Google Forms - Spam Emails

Here’s how spam emails are sent through Google Forms.

  1. A public form is created with Google Forms.
  2. The form creator uploads images for the various question fields and also adds links to spam websites in the form.
  3. Inside the Form settings, they turn on the option to “Collect Email Addresses” including the option to send “Response receipts” when a new form is submitted.

Google Form Email

Now the spammers can simply open the Google Form, fill in the recipient’s email address and hit the submit button.

Google Forms will automatically email a copy of the form response, including all the pictures and links contained in the original form, to the email address that was entered in the form.

Here’s a screenshot of one such email from Google Forms that easily tricked the spam filters.

Google Forms

How to Block Spam from Google Forms

If you would like to prevent spam emails from Google Forms from landing in your inbox, Gmail filters can help.

All pre-filled Google Forms emails have the sender’s email address as below:

forms-receipts-noreply@google.com

You can create a filter in Gmail that will automatically delete emails that have Google Forms as the sender.

Google Forms Filter

Alternatively, you may open the form link from the email and click the “Report Abuse” button to report the form to Google. That is not likely to be a very effective strategy though as spammers can always switch to a different Google account.

How to Learn Regular Expressions

Regular Expressions, or RegEx, are used for searching patterns in text. For instance, a RegEx like iP(hone|ad|od)s? will find mentions of any iOS device in a document. Knowledge of Regular Expressions is essential for programmers but they can be a great skill to have for non-developers as well - people who use Microsoft Word or spend hours inside Google Spreadsheets.

RegEx in Microsoft Word

Why Learn Regular Expressions?

Regular Expressions are extremely powerful, and no less intimidating, but even basic understanding of RegEx will save you time and make your everyday computing tasks easier.

For instance, you can quickly find & replace text that matches complex patterns in Word or Vim. You can easily extract phone numbers and emails in spreadsheet cells using regex formulas. If you are creating a form in Google Drive, RegEx can help you define validation rules for user input. You can use RegEx in Gmail and Google Analytics too.

How do you learn Regular Expressions? Or, if you are already familiar, how do you take your RegEx skills to the next level? You will obviously learn by doing but there are some excellent tools and learning resources on the Internet that will take make your journey to knowing Regular Expressions more pleasant.

The Best RegEx Tools & Resources

Lea Verou’s presentation will give you a good overview of what Regular Expressions are and what you can do with them. Jeffrey Friedl’s book - Mastering Regular Expressions - is still the best printed reference for RegEx newbies and masters. You can explore RegexOne, an interactive Codecademy-like online tutorial for learning RegEx or go here for learning the basics of pattern matching.

Highlighting all the non-English characters Highlighting all the non-English characters

RegExr is like a visual playground for Regular Expressions. You enter the text in one block and the RegEx in the other. As you edit the RegEx, the matching strings are highlighted in the input text. You can also hover over any character literal in the RegEx to know what it does. RegEx101 is a similar tool that also describes your RegEx in English as you write.

Regulex and RegExper are both open-source web apps that make it easy for you to understand and read Regular Expressions. You enter a RegEx and the tools will create a Railroad Diagram - for a string to match, it should be able to successfully move from left of the diagram all the way to the left along one of the available paths.

RegEx Visualizer

Windows users can download Expresso, a free program that will help beginners write both simple and complex regular expressions through a visual builder. Instead of coding the RegEx manually, you can select the components in a wizard. Reggy for Mac and RegEx Coach for Windows can also help you test regular expressions outside the browser.

Also see: How to Learn Coding

Once you understand the basics, head over to RegEx Golf or play this RegEx Crossword to test your skills. Like with everything else, you’ll only learn Regular Expressions by practicing and mere reading won’t be sufficient.

Find Who has Access to your Google Drive Files and Folders

The files and folders in your Google Drive are private by default until you decide to share them. You can share your documents with specific people or you can make them public and anyone on the Internet can view the shared files. Google Apps users have the option to share files and folders within the organization while restricting access to anyone outside the domain.

You can not only control who has access to your Google Drive files but can also assign the level of access they have on the shared files. You can set the access permissions to either view (read only) or edit (read & write). For instance, if you are to send a large file, you can upload the file to Google Drive and share it in view-mode with the recipient.

Who Can View or Edit your Drive Files

You may have a number of documents, spreadsheets and other files in your Google Drive that are accessible to other users. These users could be your contacts, someone within your Google Apps domain or some of the shared files could be public meaning they are available to anyone on the web who have the link (URL) to the file.

Would you like to know which files and folders in your Google Drive are shared with other users and what kind of access permissions they have on your files? Google Drive, unfortunately, doesn’t offer an easy option for you to figure out who you are sharing the files with either inside or outside your organization.

Meet Permissions Auditor for Google Drive, a new Google add-on that scans your entire Drive and then generates a comprehensive report revealing who has access to your shared files and what kind of permission they have on the files. If you have been collaborating with people for some time, the Drive Auditor is probably is the easiest way to find out what you’ve shared in Google Drive and sanitize it.

Here’s a sample audit report.

Google Drive - File Privacy Report

Google Drive - File Permissions Report

Getting started is easy. First, install the Google Drive Auditor add-on and authorize it. Internally, this is a Google Script that runs inside your Google Account, reads the files found in Google Drive and writes their access details in the spreadsheet. Not a single byte of data every leaves your Google Account.

Watch the video tutorial for a more detailed guide.

After the Drive Audit add-on is installed, go to the Add-ons menu inside the Google Spreadsheet, choose Drive Permissions Auditor and select Start Audit. It will open a sidebar where you need to specify a query and all matching files that match the query will be analyzed by the add-on.

Some sample Google Drive Search queries include:

  • “me” in owners and trashed = false (all files owned by except those in trash)
  • modifiedTime > ‘2016-01-01T12:00:00’ (file modified since Jan 2016 UTC)
  • mimeType = ‘application/vnd.google-apps.spreadsheet’ (scan the access permissions of only Google Spreadsheets in my Google Drive)

Once the audit is complete, the report will reveal detailed information of every file including:

  • When was a file created and last modified
  • What is the file size and MIME type (file extension)
  • Who is the owner of the file
  • Who has edit, view and comment permissions on the file
  • Where is the file located in Google Drive

You can click the File Name in the spreadsheet to directly open the corresponding file in Google Drive. Also, you can use the find function or even filters in Google Spreadsheets to display specific files that match a certain criteria. For instance, if you wish to know about all files that are public, you can apply a filter on the Access column in the spreadsheet.

The Drive Permissions Auditor add-on works for both Gmail and Google Apps accounts. If you are a domain administrator, you can install the Drive Audit add-on for all users in your domain through the Google Apps Marketplace.

The add-on is free and lets you audit up to 200 files in your Google Drive. If you have more files, please upgrade to the premium edition and analyze every single file and folder in your Google Drive.

Bonus tip: Did you know that you can set an auto-expiry date for your shared links in Google Drive. The shared link will automatically stop working after a certain date or time set by you.

Find Who has Access to your Google Drive Files and Folders

The files and folders in your Google Drive are private by default until you decide to share them. You can share your documents with specific people or you can make them public and anyone on the Internet can view the shared files. Google Apps users have the option to share files and folders within the organization while restricting access to anyone outside the domain.

You can not only control who has access to your Google Drive files but can also assign the level of access they have on the shared files. You can set the access permissions to either view (read only) or edit (read & write). For instance, if you are to send a large file, you can upload the file to Google Drive and share it in view-mode with the recipient.

Who Can View or Edit your Drive Files

You may have a number of documents, spreadsheets and other files in your Google Drive that are accessible to other users. These users could be your contacts, someone within your Google Apps domain or some of the shared files could be public meaning they are available to anyone on the web who have the link (URL) to the file.

Would you like to know which files and folders in your Google Drive are shared with other users and what kind of access permissions they have on your files? Google Drive, unfortunately, doesn’t offer an easy option for you to figure out who you are sharing the files with either inside or outside your organization.

Meet Permissions Auditor for Google Drive, a new Google add-on that scans your entire Drive and then generates a comprehensive report revealing who has access to your shared files and what kind of permission they have on the files. If you have been collaborating with people for some time, the Drive Auditor is probably is the easiest way to find out what you’ve shared in Google Drive and sanitize it.

Here’s a sample audit report.

Google Drive - File Privacy Report

Google Drive - File Permissions Report

Getting started is easy. First, install the Google Drive Auditor add-on and authorize it. Internally, this is a Google Script that runs inside your Google Account, reads the files found in Google Drive and writes their access details in the spreadsheet. Not a single byte of data every leaves your Google Account.

Watch the video tutorial for a more detailed guide.

After the Drive Audit add-on is installed, go to the Add-ons menu inside the Google Spreadsheet, choose Drive Permissions Auditor and select Start Audit. It will open a sidebar where you need to specify a query and all matching files that match the query will be analyzed by the add-on.

Some sample Google Drive Search queries include:

  • “me” in owners and trashed = false (all files owned by except those in trash)
  • modifiedTime > ‘2016-01-01T12:00:00’ (file modified since Jan 2016 UTC)
  • mimeType = ‘application/vnd.google-apps.spreadsheet’ (scan the access permissions of only Google Spreadsheets in my Google Drive)

Once the audit is complete, the report will reveal detailed information of every file including:

  • When was a file created and last modified
  • What is the file size and MIME type (file extension)
  • Who is the owner of the file
  • Who has edit, view and comment permissions on the file
  • Where is the file located in Google Drive

You can click the File Name in the spreadsheet to directly open the corresponding file in Google Drive. Also, you can use the find function or even filters in Google Spreadsheets to display specific files that match a certain criteria. For instance, if you wish to know about all files that are public, you can apply a filter on the Access column in the spreadsheet.

The Drive Permissions Auditor add-on works for both Gmail and Google Apps accounts. If you are a domain administrator, you can install the Drive Audit add-on for all users in your domain through the Google Apps Marketplace.

The add-on is free and lets you audit up to 200 files in your Google Drive. If you have more files, please upgrade to the premium edition and analyze every single file and folder in your Google Drive.

Bonus tip: Did you know that you can set an auto-expiry date for your shared links in Google Drive. The shared link will automatically stop working after a certain date or time set by you.

Learning Vim for Beginners

Vim, or Vi Improved, is a powerful text editor that lets you do almost everything using keyboard shortcuts. You can replace text in a document, move or delete lines, automate edit actions, and more, without ever reaching for the mouse. Vim is the favorite source code editor of programmers but there’s no reason why you cannot use this editor for writing documents or composing long emails.

Vim can be confusing for people who are used to working inside visual editors like Microsoft Word or Google Docs but spend some time with Vim and it will be difficult for you to go back. Entire books have been devoted to teaching Vim but if you can manage to learn the basic commands, you’ll find yourself more efficient and productive.

Vim Logo

How to Learn Vi

Here’s a list of online tutorials and other helpful resources to help you learn Vim.

1. OpenVim - An interactive tutorial for learning the basics of Vim. Switch to the Practice page to test your existing Vim skills.

2. Vim Adventures - An online puzzle game for learning and memorizing Vim commands. You play the character of a blinking cursor that has to navigate the maze with the letter keys. If you are stuck, you can always type :help for a hint.

3. Vim Genius - This a flashcard style game to help you learn the basics of Vim. There are dedicated lessons for learning the motion keys (h,j,k,l) and for mastering copy-paste in vim.

4. Learn to Love Vim - The Linux Voice magazine has put together a quick video tutorial to get you started with Vim.

5. Vim Basics - Derek Wyatt has produced a bunch of video tutorials (screencasts) around teaching Vim. A good resource for novice users who prefer learning Vim by watching than reading.

6. Learning Vim - Mike Coutermarsh covers getting up and running, and eventually productive with Vim.

7. Vim - Precision Editing - Drew Neil, author of the popular Practical Vim title and Vimcasts.org, walks you through Vim and how the text editor is optimized for mouseless operations.

8. Vim Tutorial - The official Vim documentation is excellent and includes a step-by-step tutorial. You can also access this tutorial inside the Vim program through the :vimtutor command.

9. Vim Cheat Sheet - Print this because you’ll need it later.

10. Vim Masterclass - This Udemy course will help you master all the concepts of Vim and how to ‘think’ in Vim.

If you spend a lot of time typing text, learning Vim will be totally worth the effort. I wrote this article inside Visual Studio Code with Vim key bindings.

How to Add QR Codes in Emails and Google Documents

With Document Studio, you can create employee badges, event tickets, school tags and other types of documents that contain QR Code images.

To get started, go to your source spreadsheet and create a column that will the QR Code. Give it a title, say QR Code Image, and add a formula QRCODE in the first empty cell of that column as shown below.

qrcode.png

This will add the QR Code image link to all the cells of your spreadsheet where the source cell is not empty.

Add QR Codes in Google Documents

Click anywhere in the Google document where you wish to insert the QR Code and add the variable field {{QR Code Image}} - the merge process will get the QR code image link from the source sheet and replace it with an image in the generated document.

google-document-qr-code.png

Add QR Codes in Google Sheets

To add QR Codes in Google Spreadsheet, we use the IMAGE function with the first parameter as the variable field that contains the QR Code image link inside double quotes.

=IMAGE("{{QR Code Image}}", 4, 100, 100)

Remember to merge a few adjacent cells else the QR code image won’t be visible in the generated document.

spreadsheet-qr-code.png

Add QR Codes in Google Slide Presentations

In the case of Google Slides, add a new text box and type the QR code variable field inside the text box surrounded by double curly braces.

You should also vertically align the text to the top of the enclosed box so that the entire QR code image is visible.

google-slide-qrcode.png

Add QR Codes in Email Notifications

To add QR Code images in your email templates, add the HTML <img> tag with source set to the variable field that contains the QR Code.

<img src="{{QR Code Image}}" alt="QR Code" />

email-qr-code.png