Month: April 2021

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 May 1st, anyone above the age of 18 years can get vaccinated. That said, vaccine availability remains an issue countrywide and it is cumbersome to manually check slots every few hours.

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 vaccine slots are likely to open up.

Based on the public CoWIN API, I have developed an open-source vaccine tracker that will automatically monitor vaccine availability near your location and will send email alerts as stocks become available. If you have taken the first vaccine dose already, you can specify your vaccine preference - Covaxin or Covishield - and monitor availability of specific vaccine.

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. You should complete this step on a desktop computer as Google add-ons are not available on mobile devices yet.

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

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 (comma separated), the email address where you wish to receive the alerts and the age group for which you need to monitor vaccine availability.

You can specify the start date and vaccine availability will be checked only after that date. This is useful for people who have been monitored the first dose and need to find a slot after 4-6 weeks for the second vaccine dose.

The vaccine availability is checked every day by default but you can change the frequency to every 4 hours or every hour.

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.

Stop Vaccine Notifications

If you have been vaccinated and would like the vaccine tracker to stop sending you email alerts, here are the steps:

1. Go to Script Triggers
2. Click the 3-dot menu against Vaccine Alerts trigger
3. Click Delete Trigger from the menu as shown in the screenshot.

When a user submits your Google Form, the response can be either saved in the Google Form itself or it can be written as a new row in a Google Spreadsheet. Multiple Google Forms can be associated with a single spreadsheet and their responses will be stored in separate sheets of the same spreadsheet.

If you have multiple Google Forms in your Drive that are writing response data to the same Google Sheet, you can use Google Scripts to determine the name of the spreadsheet and the sheet where that form is storing their responses.

Open the Google Script editor, replace the formId with the Id of your Google Form and run the script to get the name of the associated sheet.

function getResponseSheetForGoogleForm() {
  const formId = "<<Google Form Id>>";

  // Open an existing Google Form by Id
  const form = FormApp.openById(formId);

  // Are the form responses stored in Google Sheets
  const destinationType = form.getDestinationType();

  if (destinationType !== FormApp.DestinationType.SPREADSHEET) {
    Logger.log("This form is not saving responses in Google Sheets");
  } else {
    // Get the Id of the response spreadsheet
    const destinationId = form.getDestinationId();

    // Open the Google Workbook and iterate through each sheet
    const formSpreadsheet = SpreadsheetApp.openById(destinationId);

    const [formSheet] = formSpreadsheet.getSheets().filter((sheet) => {
      // Returns the URL of the associated Google form
      // that is sending its user responses to this sheet
      const associatedFormUrl = sheet.getFormUrl();
      return associatedFormUrl && associatedFormUrl.indexOf(formId) !== -1;
    });

    Logger.log(`The form responses are stored in ${formSheet.getName()}`);
  }
}

Also see: Add Form Response URL in Google Sheets

An international school is building a Google Form where students can register their details. The form would have a drop-down list of countries, the names of class teachers as a multiple choice question and a checkbox style question where students can pick one or more of their favorite subjects.

Adding Bulk Questions in Google Forms

It is easy to create such a form inside Google Forms - here’s a sample form - but there are two issues:

1. There’s too much data to enter and type in the form. For instance, the country down-down alone has a list of 250 countries and it would take some work for the form editor to manually type each choice in the question.
2. The question choices in the form may change with time. In the previous example, some teachers may move out, new teachers may join in and the drop-down list in the form has to be updated manually every time there’s a change in the staff.

Auto-Populate Questions in Google Forms with Google Sheets

As with everything else in the world of Google Forms and Google Sheets, we can easily automate the process of adding question choices in Google Forms in bulk with the help of, you guessed it right, Google Apps Script.

The idea is simple. We’ll have a Google Sheet that will be the data source and have all the answer choices for various questions in the Google Form.

The app will read the data from this Google Sheet and auto-populate the choices in the form with one click. You can even create a time-trigger that runs every hour, day or month to dynamically update your form using the most current data available in the spreadsheet.

Add Options in Dropdown Lists & Multiple Choice Questions

Create a Google Spreadsheet and add the question titles in the first row of the sheet, one per column. Next, write down all the options or choices that should be available per question.

Here’s how your spreadsheet structure would look like:

The important thing to note here is that your column headings in the spreadsheet should exactly match the form field labels of the Google Form. The Google Script can bulk add answers in multiple-choice questions with a single answer, drop-down lists and checkbox with multiple options.

Bulk Add Question Choices in Google Forms

Open the Google Sheet that has the question choices, go to the Tools menu and choose Script Editor. Replace the default code in the script editor with the Google Script below. Please watch the video tutorial to know in more detail how this code works.

/**
 * Auto-populate Question options in Google Forms
 * from values in Google Spreadsheet
 *
 * Written by Amit Agarwal (MIT License)
 *
 **/

const populateGoogleForms = () => {
  const GOOGLE_SHEET_NAME = "<<Put the name of Google sheet here>>";
  const GOOGLE_FORM_ID = "<<Put your Google Form ID here>>";

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const [header, ...data] = ss
    .getSheetByName(GOOGLE_SHEET_NAME)
    .getDataRange()
    .getDisplayValues();

  const choices = {};
  header.forEach((title, i) => {
    choices[title] = data.map((d) => d[i]).filter((e) => e);
  });

  FormApp.openById(GOOGLE_FORM_ID)
    .getItems()
    .map((item) => ({
      item,
      values: choices[item.getTitle()],
    }))
    .filter(({ values }) => values)
    .forEach(({ item, values }) => {
      switch (item.getType()) {
        case FormApp.ItemType.CHECKBOX:
          item.asCheckboxItem().setChoiceValues(values);
          break;
        case FormApp.ItemType.LIST:
          item.asListItem().setChoiceValues(values);
          break;
        case FormApp.ItemType.MULTIPLE_CHOICE:
          item.asMultipleChoiceItem().setChoiceValues(values);
          break;
        default:
        // ignore item
      }
    });
  ss.toast("Google Form Updated !!");
};

You need to replace the GOOGLE_SHEET_NAME and the GOOGLE_FORM_ID with your own values. Go to the Run menu inside the Script editor, choose populateGoogleForms function and it should instantly choices for all the specified questions in the Google Form.

Dynamically Update Answers in Google Forms

Going forward, whenever you need to update the answer choices in Google Forms, you can simply update the values inside the spreadsheet and run the same auto-populate function from the Script editor.

Or, to make things even more simple, you can add a button on the spreadsheet page that, when clicked, will automatically update the answers in Google Forms for you.

Go to the Insert menu in Google Sheets, choose drawing and pick any shape. You can also add overlay text to the shape. Once the shape is placed on the spreadsheet canvas, click the menu, choose assign script and type populateGoogleForms.

That’s it. You can click this button to update your Google Forms right within Google sheets. Do note that the script will not append choices, it will replace all existing choices with the ones that are available in your Google Sheet.

Also see: Send Pre-filled Google Forms via Email

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.

You can bring the power of Google Maps to your Google Sheets using simple formulas with no coding. You don’t need to sign-up for the Google Maps API and all results from Google Maps are cached in the sheet so you are unlikely to hit any quota limits.

To give you a quick example, if you have the starting address in column A and the destination address in column B, a formula like =GOOGLEMAPS_DISTANCE(A1, B1, "driving") will quickly calculate the distance between the two points.

Or modify the formula slightly =GOOGLEMAPS_TIME(A1, B1, "walking") to know how long it will take for a person to walk from one point to another.

If you would like to try the Google Maps formulas without getting into the technical details, just make a copy of this Google Sheet and you are all set.

Using Google Maps inside Google Sheets

This tutorial explains how you can easily write custom Google Maps functions inside Google Sheets that will help you:

1. Calculate distances between two cities or any addresses.
2. Calculate the travel time (walking, driving or biking) between two points.
3. Get the latitude and longitude co-ordinates of any address on Google Maps.
4. Use reverse geocoding to find the postal address from GPS co-ordinates.
5. Print driving directions between any points on earth.
6. Get the address from the zip code itself.

1. Calculate Distances in Google Sheets

Specify the origin, the destination, the travel mode (walking or driving) and the function will return the distance between the two points in miles.

=GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "walking")

/**
 * Calculate the distance between two
 * locations on Google Maps.
 *
 * =GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "walking")
 *
 * @param {String} origin The address of starting point
 * @param {String} destination The address of destination
 * @param {String} mode The mode of travel (driving, walking, bicycling or transit)
 * @return {String} The distance in miles
 * @customFunction
 */
const GOOGLEMAPS_DISTANCE = (origin, destination, mode) => {
  const { routes: [data] = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();

  if (!data) {
    throw new Error("No route found!");
  }

  const { legs: [{ distance: { text: distance } } = {}] = [] } = data;
  return distance;
};

2. Reverse Geocoding in Google Sheets

Specify the latitude and longitude and get the full address of the point through reverse geocoding of coordinates.

=GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "walking")

/**
 * Use Reverse Geocoding to get the address of
 * a point location (latitude, longitude) on Google Maps.
 *
 * =GOOGLEMAPS_REVERSEGEOCODE(latitude, longitude)
 *
 * @param {String} latitude The latitude to lookup.
 * @param {String} longitude The longitude to lookup.
 * @return {String} The postal address of the point.
 * @customFunction
 */

const GOOGLEMAPS_REVERSEGEOCODE = (latitude, longitude) => {
  const { results: [data = {}] = [] } = Maps.newGeocoder().reverseGeocode(
    latitude,
    longitude
  );
  return data.formatted_address;
};

3. Get the GPS coordinates of an address

Get the latitude and longitude of any address on Google Maps.

=GOOGLEMAPS_LATLONG("10 Hanover Square, NY")

/**
 * Get the latitude and longitude of any
 * address on Google Maps.
 *
 * =GOOGLEMAPS_LATLONG("10 Hanover Square, NY")
 *
 * @param {String} address The address to lookup.
 * @return {String} The latitude and longitude of the address.
 * @customFunction
 */
const GOOGLEMAPS_LATLONG = (address) => {
  const { results: [data = null] = [] } = Maps.newGeocoder().geocode(address);
  if (data === null) {
    throw new Error("Address not found!");
  }
  const { geometry: { location: { lat, lng } } = {} } = data;
  return `${lat}, ${lng}`;
};

4. Print the driving directions between addresses

Specify the origin address, the destination address, the travel mode and the function will use the Google Maps API to print step-by-step driving directions.

=GOOGLEMAPS_DIRECTIONS("NY 10005", "Hoboken NJ", "walking")

/**
 * Find the driving direction between two
 * locations on Google Maps.
 *
 * =GOOGLEMAPS_DIRECTIONS("NY 10005", "Hoboken NJ", "walking")
 *
 * @param {String} origin The address of starting point
 * @param {String} destination The address of destination
 * @param {String} mode The mode of travel (driving, walking, bicycling or transit)
 * @return {String} The driving direction
 * @customFunction
 */
const GOOGLEMAPS_DIRECTIONS = (origin, destination, mode = "driving") => {
  const { routes = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();
  if (!routes.length) {
    throw new Error("No route found!");
  }
  return routes
    .map(({ legs }) => {
      return legs.map(({ steps }) => {
        return steps.map((step) => {
          return step.html_instructions.replace(/<[^>]+>/g, "");
        });
      });
    })
    .join(", ");
};

5. Measure the trip time with Google Maps

Specify the origin address, the destination address, the travel mode and the function will measure your approximate trip time between the specified addresses, provided a route exists.

=GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "walking")

/**
 * Calculate the travel time between two locations
 * on Google Maps.
 *
 * =GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "walking")
 *
 * @param {String} origin The address of starting point
 * @param {String} destination The address of destination
 * @param {String} mode The mode of travel (driving, walking, bicycling or transit)
 * @return {String} The time in minutes
 * @customFunction
 */
const GOOGLEMAPS_DURATION = (origin, destination, mode = "driving") => {
  const { routes: [data] = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();
  if (!data) {
    throw new Error("No route found!");
  }
  const { legs: [{ duration: { text: time } } = {}] = [] } = data;
  return time;
};

Tip: Improve Performance by Caching Results

All the above Google Sheets functions internally use the Google Maps API to calculate routes, distances and travel time. Google offers a limited quota for Maps operations and if your sheet performs too many queries in a short duration, you are likely to see errors like ""Service invoked too many times for one day” or something similar.

To get around this problem, it is recommended that you use Apps Script’s built-in cache to store results and, if the results of a function already exist in the case, you’ll make one less request to Google Maps The Maps functions inside this Google Sheet also use caching and here’s how you can implement it.

// The cache key for "New York" and "new york  " should be same
const md5 = (key = "") => {
  const code = key.toLowerCase().replace(/\s/g, "");
  return Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, key)
    .map((char) => (char + 256).toString(16).slice(-2))
    .join("");
};

const getCache = (key) => {
  return CacheService.getDocumentCache().get(md5(key));
};

// Store the results for 6 hours
const setCache = (key, value) => {
  const expirationInSeconds = 6 * 60 * 60;
  CacheService.getDocumentCache().put(md5(key), value, expirationInSeconds);
};

/**
 * Calculate the travel time between two locations
 * on Google Maps.
 *
 * =GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "walking")
 *
 * @param {String} origin The address of starting point
 * @param {String} destination The address of destination
 * @param {String} mode The mode of travel (driving, walking, bicycling or transit)
 * @return {String} The time in minutes
 * @customFunction
 */
const GOOGLEMAPS_DURATION = (origin, destination, mode = "driving") => {
  const key = ["duration", origin, destination, mode].join(",");
  // Is result in the internal cache?
  const value = getCache(key);
  // If yes, serve the cached result
  if (value !== null) return value;
  const { routes: [data] = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();
  if (!data) {
    throw new Error("No route found!");
  }
  const { legs: [{ duration: { text: time } } = {}] = [] } = data;
  // Store the result in internal cache for future
  setCache(key, time);
  return time;
};

Also see: Embed Google Maps in Emails and Documents

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.

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.

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();
};