Customising icons in Google Maps in Angular


Now that Google Maps has been rolled into its own Angular component that you can read more about here, I wanted to use it instead of going with a library. This comes with a little bit of overhead, especially with such a new component as there often isn’t as much documentation as we’d like.

I wanted to customise my own icons so that users could differentiate between the markers. One of the problems I was having with having two markers that needed to be displayed at the same point. So I wanted to use the anchor property of the Icon object that you can read more about here. But it took me a while to figure out that the marker [options] object takes either a string OR an instance of the Icon interface, so my anchor wasn’t working.

The problem: My code was like this:

<map-marker *ngFor="let ambulance of (ambulanceLocations$ | async)"
(mapClick)="openAmbulanceInfoWindow(ambulanceMarker, ambulance.rescues)"
[options]="{icon: 'assets/icons/ambulance_van.png', anchor: {x: 0, y: 55} , zIndex: 100}">


You’ll notice that the icon is just taking the ‘assets/icons/ambulance_van.png’ as its source and igoring everything else. That’s because we’re passing in a string and not an Icon instance.

Here’s the working code:

<map-marker *ngFor="let ambulance of (ambulanceLocations$ | async)"
(mapClick)="openAmbulanceInfoWindow(ambulanceMarker, ambulance.rescues)"
[options]="{icon: { url:'assets/icons/ambulance_van.png', anchor: {x: 0, y: 55} }, zIndex: 100}">


EDIT: strict type checking will fail when using anchor: {x: 0, y: 55}, becausae it is expecting a google.maps.Point. So it’s best to declare a variable in your component like so:

iconAnchor:google.maps.Point = new google.maps.Point(0, 55);

We can also add an InfoWindow too, for any marker on the map and add our own components to it too, all you need is a variable in your component to store the contents you want in the InfoWindow:


	<div class="ambulance-window" fxLayout="row wrap" fxLayoutGap="20px" fxLayoutAlign="start">

		<div *ngFor="let patient of infoContent">

			<search-result-card class="emergency-card" (onOpenEmergencyCase)="openCase($event)" [record]="patient"></search-result-card>



In the above instance the contents of infoContent is an array that we can iterate over. All we need to do is create a function to open the info window:

openAmbulanceInfoWindow(marker: MapMarker, rescues: OutstandingRescue[]){
//Get the content from our service:
  this.caseService.getContent().subscribe(result => {

//Populate the infoContent variable     
this.infoContent = result;

//Open the info window with the marker we want to attach it to.;


How to change Angular Platform using Chrome DevTools


Now, I’m only writing this post because I couldn’t find a great deal of info about the Platform utilities in Angular and my code was exhibiting strange behaviour.

I’ve got a button that I only want to show up on a mobile device so that the camera can capture and image, we don’t want to do this on desktop:

So I imported the platform utilities and set my button to only show if the platform is Android or iOS:

import { Platform } from '@angular/cdk/platform';

export class MediaDialog implements OnInit {

  constructor(public platform: Platform) { }
<button mat-mini-fab color="primary" *ngIf="platform.ANDROID || platform.IOS" aria-label="Upload new image">

Problem was when I was using developer tools the button was showing, even though I was on desktop.

By pressing the Toggle device toolbar button you can change the platform of the browser:

This way I was able to see my hide functionality working without having to load my site on a moble device. Noice.

Angular Proxy and the role of pathRewrite


My angular project is running in dev mode on the default port, i.e. http://localhost:4200, and I’m at the point in development where I want to start hitting my REST API in order to start getting and posting data to my database. The backend server is an Express server running on http://localhost:8080. So I followed the steps here to set up my Angular proxy, but was still facing the below errors:

My projects have changed a lot as I’m learning, and I’m using the /auth/ route in my front end for authentication API calls, however this needs to be able to use the existing back end route of /AttemptLogin. This is where the role of pathRewrite comes in. We can change the proxy set up to the below:

  "/auth/*": {
    "target": "http://localhost:8080",
    "secure": false,
    "logLevel": "debug",
    "changeOrigin": true,
    "pathRewrite": {
      "^/auth": "/AttemptLogin"

pathRewrite can be used to change the incoming path to the one you want to hit. So incoming is /auth and the above config changes proxies the request to /AttemptLogin

You can think of it like a ‘before’ and ‘after’:

Before: localhost:4200/auth >> after: localhost:<target>/pathRewrite i.e. localhost:8080/AttemptLogin

Angular Material Table with Reactive Forms as the data source


So I’ve got to say I was pretty blown away today when I figured out how to use a reactive form as the data source for a mat-table. This feature is really awesome as it means I don’t need one array for my table data source and then another for my form. The even better thing is my form is broken up into parent and child forms with the parent knowing nothing about the structure of the child forms and the child forms knowing nothing about the parent form. This means I can plug different components into my main form until the cows come home and neither the parent nor the children care. Angular modularity really at its finest!

Here’s how:

First define your reactive form using the FormBuilder:

this.parentForm ={
parentItem1: [''],
parentItem2: ['']
childArray: this.fb.array([]) //Notice how we didn't put any controls in here?

You can see in the above that the form has 2 parts, one group with some defined controls in it ‘parentGroup’, and another group that is just an array that hasn’t been defined yet. We didn’t define it because that’s the job of the child form. Now let’s create our template and show how we pass the parent form into the child. There’s a more in depth article about this here.

	<input formControlName="parentItem1" type="text" matInput>

	<input formControlName="parentItem2" type="text" matInput>
<child-component [parentForm]="parentForm"></<child-component>

You can see above that we’ve tied the 2 inputs to the parentGroup and passed the whole form down to the child component, the child component is where the mat-table lives.

In the child we’re going to take the parentForm as an input and then add the controls to it we need. This way the parent form doesn’t know about what’s in the child until the child creates the contents of the childGroup array. Bear in mind that childGroup doesn’t have to be an array, however the mat-table needs an array so it makes sense.

@Input() parentForm: FormGroup;


this.childArray = this.parentForm.get('childArray') as FormArray;

let row1 ={
column1: [''],
column2: ['']

let row2 ={
column1: [''],
column2: ['']


In the above we’re getting the childArray from the parentForm and the two are now bound together, so anything you do in the chil component will be reflected in the parent.

We define a new group and then add this to the child array. These are the records that will show up in our mat-table.

There’s abit of work to do to create the data source for the table and this is where some of the magic happens:

this.tableDataSource = new MatTableDataSource((this.recordForm.get('childArray') as FormArray).controls);

Here we’re setting the table to use the controls in the FormArray that has been passed in from the parent as it data source. The only piece of the puzzle left is to let our table know how to access the child array.

Now let’s define our table (this is just the columns) you can read more about defining a table here in the Angular docs.:

<ng-container matColumnDef="column1">
      <th mat-header-cell *matHeaderCellDef> Column1 </th>
      <td mat-cell *matCellDef="let element" (click)="toggleRow(element)"> {{element.get("column1").value}} </td>

<ng-container matColumnDef="column2">
      <th mat-header-cell *matHeaderCellDef> Column2 </th>
      <td mat-cell *matCellDef="let element" (click)="toggleRow(element)"> {{element.get("column2").value}} </td>

Here’s the really special part (or so I thought):


This binds the array to the table.

So now:

  • The parent passes its whole form to the child with parts yet to be defined, which is the job of the child.
  • The child defines the needed form components, which are automatically bound to the parent when they are created
  • The form array is used as the data source for the table
  • The table has its columns matched to those of the datasource i.e. the form array
  • Everything is all tied together

I know reactive forms can be a bit of a complicated pain sometimes, but when you start doing complicated things, the benefits really outweigh the costs. Having said that, this is the first time I’ve done anything like this with reactive forms, so it might all just be a steaming hot pile of garbage!

Also, if even one person asks I’ll create a stackblitz showing how it all works.

SSMS peek at your tables using SELECT keyboard shortcut


I often want to see a sample of the table I’m querying as i’m writing my queries or stored procedures. For instance if I want to know the name of a column or even get a sample from one of the cells. The old and slow way to do this is obviously to SELECT TOP 10 * FROM <your table name>, however when you’re in the middle of a large or complex join you don’t want to move to a new window to type that out. Basically, because every time you do that a fairy dies.

The way you should do it is by making use of SSMS’s inbuilt query shortcuts. You can find these in Tools > Options > Keyboard > Query Shortcuts

As you can see I’ve added ‘SELECT TOP 100 * FROM ‘ as my Ctrl + 3 shortcut. So now I can highlight the table I want to peek at and hit Ctrl + 3 and the results will be show in the results window without having to do any extra typing.

Please full enjoy radio button deselect


I believe there’s a small issue with the way radio buttons work in web where there is no way to deselect a selected radio button once it’s been checked. Which is a problem if you have a form with an optional question on it that gets answered by mistake. The only way to reverse the checked radio button is to reset the form or reload the page, which is a problem if you have alarge form to fill out.

You can use the below code to allow users to double click a radio button to deselect it.

function(ele) {;

    ele.addEventListener('dblclick', function(){

    //This is used to clear the selection. Otherwise we'd need to refresh the form
    //if the user doesn't want to pick an option before saving the case.
    //NB. Because the click method will fire each time as part of the MDC component
    //if you double-click on a blank field it will fire the click method twice.
    //Which you could argue is expected behaviour

        const currentRadio = document.getElementById(;

        const radio = new;

            radio.checked = false;            

}); web – chipset search


This may not be the greatest code in the world, but it works for my purpose. I’m building a web app that has chips all visible for use in an emergency call centre. I want to be able to tab into the chipset and then type in order to quickly go to the chip I want instead of tabbing all the way along.

I make use of an attribute of the chip set itself to store the currently searched value. You can add an attribute by using the attr jquery function

Here’s the code:

if($(".mdc-chip-set").find(':focus').length != 0 && event.keyCode > 59 && event.keyCode < 91)
    //Clear any active timers
    clearTimeout($.data(this, "timer"));

    var searchString = "";    

    //Check if we've already got a string that we're searching
    searchString += $(".mdc-chip-set").attr("searchString");

    //Add the current key to the search string
    $(".mdc-chip-set").attr("searchString", searchString += String.fromCharCode(event.which).toLowerCase());

    //Find the currently focussed chip set and go through each of its children to
    //see which one matches
    const items = $(".mdc-chip-set").find(':focus').parent().children();
      //See if the ID of the chip matches the search string
        return false;

    //Set the timer
    $(this).data("timer", setTimeout(function()
    }, 500));


I’m searching the ID of my chip for what the user is entering, my IDs are in the format –, e.g. er-animal-type-chip–Dog

So as the user types we look for IDs where the searchable section matches what has been typed so far (well in the last 500ms anyway). Then we focus on that chip. Notice that the each function is existed from as soon as a match is found. Web – Resetting Selected Chips


I couldn’t find this documented anywhere when I was looking and it could be a gap in my javascript knowledge (it’s a gap in my javascript knowledge), but I was struggling to figure out how to reset the selected chips in a web chipset.

Here’s the HTML for the chipset:

<label class="mdc-label">Main Problem(s)</label>
class="mdc-chip-set mdc-chip-set--filter" id="er-problem-chipset"> % for (var i = 0; i .length; i++) { %>
class="mdc-chip" id="er-chip-problem--">
class="mdc-chip__checkmark" > class="mdc-chip__checkmark-svg" viewBox="-2 -3 30 30"> class="mdc-chip__checkmark-path" fill="none" stroke="black" d="M1.73,12.91 8.1,19.28 22.79,4.59"/>
class="mdc-chip__text">%= problemTypeData[i].Problem%>
</div> <%}%> </div>

You can see the if for the chipset is:id="er-problem-chipset">

So we can use the below in a reset method:  

  //Make sure to use vanilla JavaScript here and not JQuery in order to
  //get the chipset
  const chipSetEl = document.querySelector('#er-problem-chipset');
  const chipSet = new mdc.chips.MDCChipSet(chipSetEl);

  //Go through all of the selected chips and set them to not selected
  chipSet.selectedChipIds.forEach(element => {

    const currentChip = new mdc.chips.MDCChip(document.getElementById(element));
    currentChip.selected = false;

We can take this one step further by going through all of the chipsets and resetting them all:  

  //Make sure to use vanilla JavaScript here and not JQuery in order to
  //get the chipset
  const allChipSets = document.querySelectorAll('.mdc-chip-set');

  //Go through all of the chip sets and reset them all
  allChipSets.forEach(currentChipSet => {

  const chipSet = new mdc.chips.MDCChipSet(currentChipSet);

  //Go through all of the selected chips and set them to not selected
  chipSet.selectedChipIds.forEach(element => {

    const currentChip = new mdc.chips.MDCChip(document.getElementById(element));
    currentChip.selected = false;


INR number to words with ARRAYFORMULA in Google Sheets


Amit Agarwal has put together a great little script for Google Sheets that turns INR (Indian Rupees) amounts into words. Like you’d need to use if you were writing a cheque.

You can find the original code here: INR Amount to Words

However in its current state it won’t work if you want to use it in an ARRAYFORMULA. You only need to make a small change though and the below code will help with that.

We just need to check if an array is being sent in the input and then either process each of the rows in the array, or process the single incoming row if appropriate.

function INR(input)
    return INR_(input);

function INR_(input) {........


As you can see I’ve renamed the original function to INR_ so that we can add the new functionality without breaking the original functionality.

The same logic can then be used to turn any function into one that will work in an ARRAYFORMULA. Because if you’re still coping VLOOKUPS all the way to the bottom of your sheet you’re wasting your time!

Tableau + Google Sheets -Line chart with missing dates


Recently I was tasked to created a line chart that showed movement of animals in an animal sanctuary over time. This data came in the form of survey counts taken each day for each area of the sanctuary like this:

Area Date Count
Kennel 1 03/01/2019 100
Kennel 1 04/01/2019 102
Kennel 1 05/01/2019 101
Kennel 1 08/01/2019 120
Kennel 1 09/01/2019 121

You’ll notice that there’s a gap in the data between the 5th of January and the 8th of January. Not a big problem when we’re looking at one single area as Tableau will auto fill our missing dates:


It’s pretty obvious we’re missing a day’s worth of data, and that’s fine here. We’re rightfully continuing on our data set. But what happens when we’ve got one set of records with every date recorded and one without? We take a big hit on the days we’re missing data:


How can we solve this problem? There are a bunch of different scenarios:

  • Areas that have all days
  • Areas that have all days up to a certain point x days ago
  • Areas that are missing some dates up to the current day
  • Areas that are missing some dates and the last record is y days ago

As you can see our data set becomes rapidly complex. Not particularly difficult to solve with SQL Server and a Tally table, but what happens when we’re restricted to the use of a Google Sheet that’s driven from a Google Form?

First create a calendar table with all of the dates in our data set:


Then we need to deal with any islands. Each record in our data set can be sorted by Area Name and Date. This allows us to find the date of the record previous to this one, we do this by sorting our data by Area Name and Date descending:

=sort('Form responses 1'!C2:K,1,true,2,false)

Then we need to add an ARRAYFORMULA to figure out the previous date:


The needs to go in the first row and runs for each column and simply does:

If the Area Name for the current record matches the Area Name from the previous date’s row (this is why we sorted descending), we use the previous date, else we use the current row’s date. So we get output that looks like this:

Area Date Count Previous Date
Kennel 1 09/01/2019 121 08/01/2019
Kennel 1 08/01/2019 120 05/01/2019
Kennel 1 05/01/2019 101 04/01/2019
Kennel 1 04/01/2019 102 03/01/2019
Kennel 1 03/01/2019 100 03/01/2019
Kennel 2 09/01/2019 76 08/01/2019
Kennel 2 08/01/2019 79 07/01/2019
Kennel 2 07/01/2019 72 06/01/2019
Kennel 2 06/01/2019 74 05/01/2019
Kennel 2 05/01/2019 74 04/01/2019
Kennel 2 04/01/2019 76 03/01/2019
Kennel 2 03/01/2019 76 03/01/2019

Now we have a date range that can be used to join to our calendar table in Tableau. If we join to our calendar table where the calendar date (in which we have every single date) is after the previous date, and less than or equal to the date of the record like below:


We now get a result that looks like this:


As you can see the Calendar Date fills in the gaps.

Dealing with data that’s not up to date uses the exact same technique, we just need to reverse the dates. So the Previous Date is now the date of the record and the Date is TODAY()-1, i.e. yesterday. There are a couple of steps to get this done in Sheets:

1: Get the all of the records where the last record we have is before yesterday:

=query(query('Form responses 1'!C:D,"select C,max(D) where C <> '' group by C"),"where Col2 < date '"&TEXT(DATEVALUE(today()-1),"yyyy-mm-dd")&"'")

So here we’re making a sub QUERY to get the max date for each Area Name, and then another QUERY to get just the records where the date is before TODAY()-1, i.e. yesterday. This would be easy in SQL Server by using a HAVING clause. We then need to get the count value for each of these records:

={"Heading";ArrayFormula(iferror(vlookup(A2:A&B2:B, {'Form responses 1'!C2:C&'Form responses 1'!D2:D, 'Form responses 1'!C2:L}, 9, 0 ), 0))}

I understand it’s difficult without an example, so when I get a chance I’ll put one together and provide a link. If you need one in the meantime, leave a comment and I’ll put something together.

The result is a line chart that has the latest count for each day and can show what would have been the count on any particular day chosen.