DEV Community: nightwolfdev

Calculate Average Scores By Category using Google Sheets Pivot Table

nightwolfdev — Mon, 21 Apr 2025 21:32:49 +0000

During player evaluations for a sports organization, each player is evaluated across multiple categories. The scores must be averaged and totaled. This data is then used to make decisions for the following sports season. Let’s learn how to calculate the average scores by category using a pivot table in Google Sheets!

Spreadsheet Setup

Let’s say we have a sheet of data that has three columns:

Name
Category
Score

There are multiple rows of data with duplicate names and categories. We want to calculate the average score per player name and category. We can do this with a Pivot Table!

Pivot Table

To create a Pivot Table in Google Sheets, navigate to Insert > Pivot Table. At the Data range field, select all columns and rows of data, including the column headings. At the Insert to field, choose New sheet, which will create the pivot table in a new sheet. Select Create.

In the Pivot table editor, we need to select which pieces of data will go where. For the Rows, we want Name, which will create a row per unique player. You can also decide the sort order. For the Columns, we want Category, which will create a column per unique category. You can also decide the sort order. For the Values, we want Score, and choose AVERAGE under the Summarize by field.

The result is a nicely formatted report of average scores by player name and category, including grand totals!

Visit our website at https://nightwolf.dev and follow us on X!

Show/Hide Password Field In Angular Form

nightwolfdev — Thu, 30 Jan 2025 22:42:09 +0000

In modern web applications, user experience is key, especially when it comes to forms. One common feature that improves usability is the ability to toggle the visibility of password input fields. By allowing users to show or hide their password, you can reduce frustration caused by typos while maintaining security. In this article, we’ll implement a password visibility toggle in an Angular form. This simple feature can greatly enhance the user experience.

Password Field

In your form, you’ll have an input field for entering a password. The input type should be password to initially hide the value being typed in. Create a template variable on the input field called passwordField. We will reference this later in the component.

<input type="password" id="password" name="password" #passwordField>

Show Password Field

Let’s add a checkbox and label to the form to toggle the visibility of the password value. When the user clicks on the label/checkbox, it will call a method called togglePasswordVisibility.

<input type="checkbox" id="showPassword" (click)="togglePasswordVisibility()">
<label for="showPassword">Show Password</label>

Show/Hide Password Toggle

Within your form component, let’s import what we need.

import { ElementRef, ViewChild } from '@angular/core';

Create a variable called passwordField and decorate it with ViewChild, referencing the passwordField template variable we previously defined. Let’s also define its type as ElementRef, specifically an HTMLInputElement.

@ViewChild('passwordField') passwordField: ElementRef<HTMLInputElement>;

Now let’s create the togglePasswordVisibility method that gets called anytime someone clicks the Show Password label/checkbox. If the password field type is password, we’ll change it to text, allowing the user to see the actual value in the password field. If the password field type is text, we’ll change it back to password, hiding the password value.

togglePasswordVisibility() {
    const field = this.passwordField.nativeElement;

    if (field.type === 'password') {
        field.type = 'text';
    } else {
        field.type = 'password';
    }
}

Visit our website at https://nightwolf.dev and follow us on X!

Breweries App

nightwolfdev — Sat, 11 Jan 2025 18:28:37 +0000

Features

Search breweries in the USA by name, state, city, and type.
Find breweries near you using geolocation.
Save favorites to local storage.

Thank you to Open Brewery DB for the API and Beer Mug Vectors by Vecteezy for the logo!

Simplified Email Extraction with Javascript Bookmark

nightwolfdev — Mon, 02 Dec 2024 14:53:05 +0000

A scenario came up where there was a need to extract email addresses off of a web page. This was going to be a repetitive task, loading a similar page structure, but with different email addresses to extract from each page. The end goal was to copy and paste these email addresses into a spreadsheet. Creating a bookmark that ran Javascript was the simplest approach to extract the emails. Let’s learn how!

Extract Emails

In my particular example, the emails we wanted to extract were inside of a table that had a specific id. Again, the goal was to extract these emails, copy them to the clipboard, and manually paste them into a spreadsheet.

Let’s start by creating an immediately invoked function expression (IIFE). This will keep all the variables neatly scoped to the function itself. Because we want to use await later in the code, we also define the function as async.

(async () => {

})();

The first thing we should check is if we’re actually on the web page that this code will work on. If we’re not, we should show an alert and throw an error to stop the rest of the code from running.

if (!window.location.href.startsWith('URL_HERE')) {
  const hrefError = 'This bookmark cannot be used on this webpage.';
  alert(hrefError);
  throw hrefError;
}

Let’s find that table by its unique id.

const table = document.getElementById('ID_HERE');

If the table can’t be found, we should show an alert and throw an error to stop the rest of the code from running.

if (!table) {
  const tableError = 'Table not found';
  alert(tableError);
  throw tableError);
}

Now let’s look for all the links within the table.

const links = table.getElementsByTagName('a');

Let’s also create a variable called emails, which will be an empty array to start, so we can store all the emails we find.

const emails = [];

We need to loop through all the links we found in the table and see if the href attribute starts with mailto. If it does, then we’ve found an email! We’ll remove the mailto: prefix and be left with the email that we can add to the emails array.

for (const link of links) {
  const href = link.getAttribute('href');

  if (href && href.startsWith('mailto:')) {
    emails.push(href.substring(7));
  }
}

Now that we have an array of emails, let’s copy them to the clipboard as a string with a newline character in between each email. This better formats the data in preparation for pasting them into the spreadsheet. We use await because the clipboard api is an asynchronous function.

await navigator.clipboard.writeText(emails.join('\n'));

All that’s left to do is show how many emails were copied to the clipboard.

alert(`${emails.length} emails copied to clipboard`);

Here’s the final code:

(async () => {
  if (!window.location.href.startsWith('URL_HERE')) {
    const hrefError = 'This bookmark cannot be used on this webpage.';
    alert(hrefError);
    throw hrefError;
  }

  const table = document.getElementById('ID_HERE');

  if (!table) {
    const tableError = 'Table not found';
    alert(tableError);
    throw tableError;
  }

  const links = table.getElementsByTagName('a');
  const emails = [];

  for (const link of links) { 
    const href = link.getAttribute('href'); 

    if (href && href.startsWith('mailto:')) { 
      emails.push(href.substring(7)); 
    } 
  }

  await navigator.clipboard.writeText(emails.join('\n'));
  alert(`${emails.length} emails copied to clipboard`);
})();

Why A Bookmark?

When I first started working on this, I tried using a Google Chrome Snippet. When I got to the point of copying to the clipboard, it didn’t work. The reason is because the user must take action, like clicking a button, before allowing the clipboard to be used.

I injected a button into the page that when clicked would run the function to get emails and copy them to the clipboard. However, that meant the user had to open the browser’s developer tools, run the snippet, then click the button. Too many steps, especially for someone that might not be tech savvy or would be confused with using something like the developer tools.

I could also create a Google Chrome Extension, but this isn’t something I wanted to add to the store of course! This was for a very specific use case. I would have to develop the extension, package it up, and explain to the user how to manually load an unpacked extension! Again, not easy for someone that might not be tech savvy.

A bookmark was easier! You can run javascript in a bookmark url!

Create Bookmark

Within Google Chrome’s menu, navigate to Bookmarks and lists > Bookmark manager. Under the Bookmark manager menu, choose Add new bookmark.

At the Name field, give the bookmark a name like Extract Emails. At the URL field, begin by typing javascript: (yes, include the colon after the word javascript) and then paste in the code to extract emails from above. Save the bookmark.

If you don’t have the bookmarks bar visible, go to Google Chrome’s menu and choose Bookmarks and lists > Show bookmarks bar. The bookmark you just created should be visible.

Visit the url, click the bookmark, and you should get an alert showing you the number of emails copied to the clipboard!

Visit our website at https://nightwolf.dev and follow us on X!

Create A Sports Team Roster From Google Docs Template Using Google Sheets Data

nightwolfdev — Wed, 21 Aug 2024 00:12:50 +0000

In this article, you’ll learn how to get player information from a Google Sheet and create a roster with it based on a Google Doc template! You’ll also learn how to create a custom menu in the spreadsheet to make it easy to create the roster. As a bonus, you’ll also learn how to email the roster as an attached pdf!

Roster Template Setup

We need a template to base our roster on. Let’s start by creating a new Google Doc. You can make it look however you want. The important piece is where we want the table of player information to appear. Decide where that will be and type the following text in that location:

{{Players}}

Later, we will look for this specific text and replace it with the actual table of player information.

Spreadsheet Setup

Create a sheet called Players.
Create the following columns:
- Jersey
- First Name
- Last Name

Script Editor

Let’s start writing some code! Google Sheets has a handy script editor available.

Navigate to Extensions > Apps Script.
The script editor will include a starting function. You can remove all the code.
Navigate to File > Save. Give the script project a name select Ok.

Get Players

Let’s create a function to return all of our player information. Create a function called getPlayers.

function getPlayers() {

}

Within the getPlayers function, create a variable called rows and get all of the rows of data from the sheet called Players.

const rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Players').getDataRange().getValues();

The Jersey column has numbers. Even if you format the column to be just numbers, no decimal places, it will still return a decimal. We don’t want to see jersey numbers with decimals in our final roster! To keep it simple, we’ll just convert every column value in every row to a string. Create a variable called data and map every column value in every row to a string.

const data = rows.map(row => row.map(val => val.toString()));

All that is left to do is return the data.

return data;

Here’s the final code for getPlayers.

function getPlayers() {
  const rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Players').getDataRange().getValues();
  const data = rows.map(row => row.map(val => val.toString()));
  return data;
}

Style A Google Docs Table

We want a nice compact looking table with minimal padding. We’ll have to override the default table style with our own. Create a function called styleTable with an argument being passed in called table.

function styleTable(table) {

}

Within the styleTable function, create a variable called rows and get the number of rows for the passed in table.

const rows = table.getNumRows();

Styling involves defining an object where each property name is an attribue and the value is the new value to be applied. We want new styles for the column headings and each cell. Create two variables called headingStyle and cellStyle and assign empty objects to them.

const headingStyle = {};
const cellStyle = {};

We want the column headings to be bold and the top and bottom padding of each cell to be smaller. Add the following:

headingStyle[DocumentApp.Attribute.BOLD] = true;
cellStyle[DocumentApp.Attribute.PADDING_TOP] = 0.025;
cellStyle[DocumentApp.Attribute.PADDING_BOTTOM] = 0.025;

Apply the heading style to the first row of the table.

table.getRow(0).setAttributes(headingStyle);

Apply the cell style to every cell. We have to loop through each row and cell in the table to apply it.

for (let r = 0; r < rows; r++) {
  const cols = table.getRow(r).getNumChildren();
  for (let c = 0; c < cols; c++) {
    const cell = table.getRow(r).getCell(c);
    cell.setAttributes(cellStyle);
  }
}

Here’s the final code for styleTable.

function styleTable(table) {
  const rows = table.getNumRows();
  const headingStyle = {};
  const cellStyle = {};

  // Define custom styling
  headingStyle[DocumentApp.Attribute.BOLD] = true;
  cellStyle[DocumentApp.Attribute.PADDING_TOP] = 0.025;
  cellStyle[DocumentApp.Attribute.PADDING_BOTTOM] = 0.025;

  // Apply column heading style
  table.getRow(0).setAttributes(headingStyle);

  // Apply cell style
  for (let r = 0; r < rows; r++) {
    const cols = table.getRow(r).getNumChildren();
    for (let c = 0; c < cols; c++) {
      const cell = table.getRow(r).getCell(c);
      cell.setAttributes(cellStyle);
    }
  }
}

Create Roster

We are now ready to create the roster based on the template and player information! Create a function called createRoster.

function createRoster() {

}

Within the createRoster function, create a variable called ss, which will represent the spreadsheet. We will use a function provided by this object later.

const ss = SpreadsheetApp.getActiveSpreadsheet();

Remember the function we created earlier to get player information? We’re ready to use it! Create a variable called players and call the getPlayers function.

const players = getPlayers();

We need a folder to store the newly created roster. Pick a folder of your choice and get the folder’s id, which can be found in the url:

https://drive.google.com/drive/u/0/folders/FOLDER_ID_HERE

Create a variable called folder and get the folder by its id.

const folder = DriveApp.getFolderById('FOLDER_ID_HERE');

Remember the roster template we created earlier? We need that file’s id, which can be found in the url:

https://docs.google.com/document/d/FILE_ID_HERE/edit

Create a variable called template and get the file by its id.

const template = DriveApp.getFileById('FILE_ID_HERE');

Create a variable called templateCopy and make a copy of the template, give it a name, and store it in the folder we chose.

const templateCopy = template.makeCopy('Team Roster', folder);

Create a variable called doc to get the newly created doc by its id.

const doc = DocumentApp.openById(templateCopy.getId());

Create a variable called body to get the actual body of the document.

const body = doc.getBody();

Remember the text we typed in the template for the location of where we wanted the player information to appear? Let’s look for that in the body and call it playersPlaceholder.

const playersPlaceholder = body.findText('{{Players}}');

If we find the playersPlaceholder, we need to get its element, find where it’s located in the body and remove it, then insert the table of player information in its place. We also want to style the table using the styleTable function we created earlier.

if (playersPlaceholder) {
  const playersElement = playersPlaceholder.getElement();
  const playersIndex = body.getChildIndex(playersElement.getParent());

  body.getChild(playersIndex).removeFromParent();

  const playersTable = body.insertTable(playersIndex, players);
  styleTable(playersTable);
}

Let’s save and close the document.

doc.saveAndClose();

To show something happened in the spreadsheet, we can show a message to the user. We’ll show a different message depending on if there’s actually any players defined on the Players sheet. We check for a length greater than 1 because the first entry is the column headings.

if (players.length > 1) {
  ss.toast('Roster has been created!', 'Create Roster');
} else {
  ss.toast('No players defined.', 'Create Roster');
}

Here’s the final code for createRoster.

function createRoster() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const players = getPlayers();

  const folder = DriveApp.getFolderById('FOLDER_ID_HERE');
  const template = DriveApp.getFileById('FILE_ID_HERE');

  const templateCopy = template.makeCopy('Team Roster', folder);
  const doc = DocumentApp.openById(templateCopy.getId());
  const body = doc.getBody();

  const playersPlaceholder = body.findText('{{Players}}');

  if (playersPlaceholder) {
    const playersElement = playersPlaceholder.getElement();
    const playersIndex = body.getChildIndex(playersElement.getParent());

    body.getChild(playersIndex).removeFromParent();

    const playersTable = body.insertTable(playersIndex, players);
    styleTable(playersTable);
  }

  doc.saveAndClose();

  if (players.length > 1) {
    ss.toast('Roster has been created!', 'Create Roster');
  } else {
    ss.toast('No players defined.', 'Create Roster');
  }
}

Custom Menu

Let’s create a custom menu so we can create the roster from the spreadsheet’s menu toolbar instead!

The best time to create a custom menu is when the spreadsheet first opens. Use the onOpen trigger, which is executed when the spreadsheet is first opened.

Add a custom menu to the spreadsheet called Roster. Selecting the Roster menu will display a menu option called Create. Selecting Create will run the function called createRoster!

function onOpen() {
  const ui = SpreadsheetApp.getUi();

  ui.createMenu('Roster')
    .addItem('Create', 'createRoster')
    .addToUi();
}

Email Roster

If you’d like to email the roster as a pdf attachment, here’s a function for that. Replace EMAIL_ADDRESS_HERE with the email address you want it to be sent to. If you want to delete the copy of the roster that was created in the folder, that line of code is also included in the function. You can remove it if you don’t want that to happen.

function emailRoster(doc) {
  const htmlBody = '<p>Attached is your team roster!</p>';

  if (MailApp.getRemainingDailyQuota() > 0) {
    MailApp.sendEmail('EMAIL_ADDRESS_HERE', 'Team Roster', '', { attachments: [doc.getAs('application/pdf')], htmlBody: htmlBody });
  }

  // Delete the document
  DriveApp.getFileById(doc.getId()).setTrashed(true);
}

All that is left to do is add this function within the createRoster function right after saving and closing the doc.

doc.saveAndClose();

emailRoster(doc);

Have fun creating your sports team roster!

Visit our website at https://nightwolf.dev and follow us on X!

Warn And Log Out User Due To Inactivity In Angular App

nightwolfdev — Thu, 28 Mar 2024 22:34:32 +0000

Protecting sensitive data from unauthorized access is an important aspect to securing a web application. Implementing an automatic log out feature due to an inactive user can help with that. Let’s learn how!

Package Installation

Install the @ng-idle/core package by running the following command:

npm install @ng-idle/core

Package Setup

In order to use the package, you need to provide it at the root of the application. Depending on your Angular setup and version, you’ll need to do one of the following:

app.module.ts

import { NgIdleModule } from '@ng-idle/core';

@NgModule({
  providers: [
    NgIdleModule.forRoot()
  ]
})

app.config.ts

import { importProvidersFrom } from '@angular/core';
import { NgIdleModule } from '@ng-idle/core';

export const appConfig: ApplicationConfig = {
  providers: [
    importProvidersFrom(NgIdleModule.forRoot())
  ]
};

Warn User With App Alert

When the user becomes inactive, let’s provide a warning with a countdown. I’m using Clarity’s alert component, specifically an app level alert since it appears at the top of the app. Make sure to place it in a location that appears no matter what page you’re on.

@if (inactiveUser) {
  <clr-alert [clrAlertAppLevel]="true" [clrAlertClosable]="false" [clrAlertType]="'warning'">
    <clr-alert-item>
      <span class="alert-text">Logging out in {{ inactiveCountdown }} seconds due to inactivity.</span>
    </clr-alert-item>
  </clr-alert>
}

We wrap the alert in an if statement and only display it if inactiveUser is true. We’ll see how that variable is defined in a moment.

By the way, you can also use ngIf on clr-alert instead of @if if you’re not yet on an Angular version that supports @if.

Handle Inactive User

Within your app component, let’s set up everything to handle an inactive user. Let’s start by importing what we need from the @ng-idle/core package.

import { DEFAULT_INTERRUPTSOURCES, Idle } from '@ng-idle/core';

Create a variable called idle for the idle service in the component constructor.

constructor(private idle: Idle) {}

Within the app component class, create a variable called inactiveCountdown, which will be of type number. This will count down the number of seconds before a user times out.

inactiveCountdown: number;

Create a variable called inactiveUser, which will be of type boolean. This will track whether the user is considered inactive or not.

inactiveUser: boolean;

You’re probably keeping track of when the user is successfully authenticated. This is the right time to start watching for when the user becomes idle or not.

if (authenticated) {
  this.idle.watch();
} else {
  this.idle.stop();
}

Now let’s create a private method called handleInactiveUser.

private handleInactiveUser() {
  this.idle.setIdle(300);
  this.idle.setInterrupts(DEFAULT_INTERRUPTSOURCES);
  this.idle.setTimeout(15);

  this.handleIdleStart();
  this.handleIdleEnd();
  this.handleTimeoutWarning();
  this.handleTimeout();
}

setIdle defines the number of seconds before a user is considered idle. So 300 seconds would be 5 minutes.
setInterrupts defines what events will stop a user from being considered idle. We’ll use the default ones like mouse move, key down, etc.
setTimeout defines the number of seconds before a user times out. The user will have 15 seconds to take action before they time out.
handleIdleStart will listen for when the user is considered idle. We will set inactiveUser to true when this happens.
handleIdleEnd will listen for when the user is no longer considered idle. We will set inactiveUser to false when this happens.
handleTimeoutWarning will listen for the countdown. We will set inactiveCountdown to this countdown.
handleTimeout will listen for when the user is considered timed out. We will log the user out at this point.

Let’s create those 4 methods mentioned above.

private handleIdleStart() {
  this.idle.onIdleStart.subscribe(() => {
    this.inactiveUser = true;
  });
}

private handleIdleEnd() {
  this.idle.onIdleEnd.subscribe(() => {
    this.inactiveUser = false;
  });
}

private handleTimeout() {
  this.idle.onTimeout.subscribe(() => {
    this.inactiveUser = false;
    this.logOut();
  });
}

private handleTimeoutWarning() {
  this.idle.onTimeoutWarning.subscribe(countdown => {
    this.inactiveCountdown = countdown;
  });
}

All that’s left to do is call handleInactiveUser() in ngOnInit.

ngOnInit() {
  this.handleInactiveUser();
}

Access your application and remain inactive for the number of seconds you defined. You should see the alert appear with the countdown. If you take any action the countdown will stop and the alert will disappear. If you time out, you’ll be logged out automatically!

Visit our website at https://nightwolf.dev and follow us on Twitter!

Export CSV from Clarity Datagrid in Angular

nightwolfdev — Sat, 02 Mar 2024 00:59:55 +0000

In a recent project, I was using a Clarity Datagrid to display a table of data in an Angular application. The datagrid provides options to sort and filter the table, but it was missing an option to export to CSV. Let's learn how!

Access Clarity Datagrid Rows

Before we can export to CSV, we need access to the actual rows of data. To get that data, we need access to the datagrid class. Let’s import the ClrDatagrid class.

import { ClrDatagrid } from '@clr/angular';

Create a reference to the datagrid class using ViewChild.

import { ViewChild } from '@angular/core';

@ViewChild(ClrDatagrid) datagrid: ClrDatagrid;

With the reference created, we now have access to all datagrid items.

this.datagrid.items.all

However, we don’t want to export all items all the time. What if the user decides to filter a column and change the rows being displayed. We want to take that into account. You can use the following property instead:

this.datagrid.items['_filtered']

Hidden Reusable Anchor Element

When choosing to export to CSV, a file is created and downloaded. An anchor element provides that functionality. When clicked, it downloads a file containing the CSV content with a name of our choosing. However, we don’t want to create a new anchor element every time the user clicks on the Export CSV button. So we’ll create a hidden reusable anchor element.

Within your html template, create an anchor element like the following:

<a #exportLink href="#" style="display: none"></a>

Notice we defined a template variable called exportLink on the anchor element. Let’s create a reference to that next.

import { ElementRef, ViewChild } from '@angular/core';

@ViewChild('exportLink') exportLink: ElementRef<HTMLAnchorElement>;

We have everything in place now for the Export CSV button.

Export CSV

Let’s add an Export CSV button to the action bar of the Clarity Datagrid. Notice we also defined a click handler called exportCSV that will be called when the button is clicked.

<clr-datagrid>
  <clr-dg-action-bar>
    <button type="button" class="btn btn-sm" (click)="exportCSV()">Export CSV</button>
  </clr-dg-action-bar>
  ...
</clr-datagrid>

Create the exportCSV method next.

exportCSV() {

}

First, we need the row data. We already learned how to get that earlier. Let’s store it in a variable called data.

const data = this.datagrid.items['_filtered'];

The first row of a CSV file is usually the column headers. We can get that by extracting the keys of the first object in the data array.

const headers = Object.keys(data[0]);

The values in each row of data could potentially include commas. We need to wrap values that are of type string in double quotes to avoid any issues with commas.

const rows = data.map((entry: any) => headers.map(header => {
  let value = entry[header];
  if (typeof value === 'string') {
    // Enclose in double quotes
    value = `"${value}"`; 
  }
  return value;
}));

Now we can combine the headers and rows of data and convert them into CSV format. We need a comma to separate each value and a new line character at the end of each row.

const csvContent = [headers, ...rows].map(row => row.join(',')).join('\n');

Create a url using a blob with the correct format for CSV.

const blob = new Blob([csvContent], { type: 'text/csv; charset=utf-8;' });
const url = URL.createObjectURL(blob);

Remember that reusable anchor element we created earlier? We’re ready to use it now! We assign the url to the anchor’s href. The file name will be called data.csv. To trigger the actual download, we programmatically call the click method on the anchor element.

this.exportLink.nativeElement.href = url;
this.exportLink.nativeElement.download = 'data.csv';
this.exportLink.nativeElement.click();

Here’s the final code for the exportCSV method:

exportCSV() {
  const data = this.datagrid.items['_filtered'];
  const headers = Object.keys(data[0]);
  const rows = data.map((entry: any) => headers.map(header => {
    let value = entry[header];
    if (typeof value === 'string') {
      // Enclose in double quotes
      value = `"${value}"`; 
    }
    return value;
  }));
  const csvContent = [headers, ...rows].map(row => row.join(',')).join('\n');
  const blob = new Blob([csvContent], { type: 'text/csv; charset=utf-8;' });
  const url = URL.createObjectURL(blob);

  this.exportLink.nativeElement.href = url;
  this.exportLink.nativeElement.download = 'data.csv';
  this.exportLink.nativeElement.click();
}

Click the Export CSV button and a file called data.csv is downloaded with the data from your Clarity Datagrid in CSV format!

Visit our website at https://nightwolf.dev and follow us on Twitter!

Remove Unwanted Spaces from Angular Form Submission

nightwolfdev — Wed, 29 Nov 2023 21:12:28 +0000

When a user types a value into a form field, it’s a very common mistake to add spaces at the beginning and/or end of the value. We can clean those unwanted spaces up very easily using an Angular directive!

Trim Values Manually

When an Angular form is submitted, you can get all the values of the form and then use the Javascript trim method to remove unwanted spaces from each property.

const data = this.form.value;

data.firstName = data.firstName.trim();

Doesn’t look too difficult, but imagine if there are more fields. Instead of adding more code to the form submission logic, we can use a directive to make things easier!

Trim Directive

Let’s start by importing what we need to create the directive.

import { Directive, ElementRef, HostListener } from '@angular/core';

Within the directive’s constructor, create a parameter called element with the type ElementRef. This will be a reference to the host element, which is the form field the directive is added to.

constructor(private element: ElementRef) { }

We don’t want to remove spaces while the user is typing. Instead, let’s remove spaces once the form field loses focus. We can listen for the blur event using a HostListener.

@HostListener('blur') onBlur() {

}

Get the current value of the form field from its native element, trim any beginning and ending spaces, and then apply the cleaned up value to the form field.

const value = this.element.nativeElement.value;
this.element.nativeElement.value = value.trim();

Here’s the final code:

import { Directive, ElementRef, HostListener } from '@angular/core';

@Directive({
  selector: '[appTrim]'
})
export class TrimDirective {

  constructor(private element: ElementRef) { }

  @HostListener('blur') onBlur() {
    const value = this.element.nativeElement.value;
    this.element.nativeElement.value = value.trim();
  }

}

To make this directive usable across any module, let’s create it as its own module so it can be imported into any module where it’s needed.

import { NgModule } from '@angular/core';
import { CommonModule } from '@angular/common';

import { TrimDirective } from './trim.directive';

@NgModule({
  declarations: [TrimDirective],
  exports: [TrimDirective],
  imports: [CommonModule]
})
export class TrimModule { }

To use the directive, first import it into the module where you want to use it.

import { TrimModule } from 'path/to/trim.module';

@NgModule({
  ...
  imports: [
    ...
    TrimModule,
    ...
  ]
})
export class ContactFormModule { }

You can now add the directive attribute, which is called appTrim, to the form fields!

<input appTrim type="text" id="firstName" name="firstName" formControlName="firstName" />

Visit our website at https://nightwolf.dev and follow us on Twitter!

Software Development Resources

nightwolfdev — Mon, 20 Nov 2023 01:01:19 +0000

I have been posting software development resources on my website at https://nightwolf.dev. However, I decided to migrate them over to a repository instead.

Here is a curated list of Software Development Resources for categories such as Animations, CMS, Color, CSS, Databases, Date & Time, Drag & Drop, Front End Frameworks & Libraries, Games, Visualizations, and more!

Include Other Files In Angular Build

nightwolfdev — Thu, 29 Jun 2023 00:52:59 +0000

When building an Angular application, the assets folder and favicon.ico file are copied over by default to a dist/project-name folder. Did you know that you could include other files when building an Angular application? Let’s learn how!

Include Other Files Within Angular Src

By default, the assets folder and favicon.ico file are included in an Angular build, but why? When you run the ng build command, Angular looks to its angular.json file for instructions on what to include when building, specifically under the architect/build section. Within that section, there is an option called assets. It defines folders and files you want to include when building your application.

"architect": {
  "build": {
    "builder": "@angular-devkit/build-angular:browser",
    "options": {
      ...
      "assets": [
        "src/favicon.ico",
        "src/assets"
      ],
      ...
}

In a recent project, I needed to include a 404.html file in my Angular build. The 404.html file was located within the src folder of my Angular project.

node_modules
src
- app
- assets
- environments
- 404.html
- favicon.ico
- index.html

To include it in the build, all I had to do was add it as an asset in angular.json!

"architect": {
  "build": {
    "builder": "@angular-devkit/build-angular:browser",
    "options": {
      ...
      "assets": [
        "src/favicon.ico",
        "src/assets",
        "src/404.html"
      ],
      ...
}

After running the build, the 404.html file was included in the dist/project-name folder! By the way, the dist/project-name folder is also a default and can be changed in angular.json using the outputPath option!

"architect": {
  "build": {
    "builder": "@angular-devkit/build-angular:browser",
    "options": {
      "outputPath": "dist/project-name",
      "assets": [
        "src/favicon.ico",
        "src/assets",
        "src/404.html"
      ],
      ...
}

Include Other Files Outside Angular Src

In another recent project, I needed to include an app.yaml file in my Angular build. The app.yaml file was located outside of the src folder of my Angular project.

node_modules
src
- app
- assets
- environments
- favicon.ico
- index.html
app.yaml

I just learned this! I can add it as an asset in angular.json!

"architect": {
  "build": {
    "builder": "@angular-devkit/build-angular:browser",
    "options": {
      ...
      "assets": [
        "src/favicon.ico",
        "src/assets",
        "app.yaml"
      ],
      ...
}

After running the build, I get an error? What happened?

Asset Path Must Start With The Project Source Root

If you try to include folders or files located outside of the project source root, you’ll get an error that the asset path must start with the project source root. The solution is to define the asset as an object rather than a simple path.

"architect": {
  "build": {
    "builder": "@angular-devkit/build-angular:browser",
    "options": {
      ...
      "assets": [
        "src/favicon.ico",
        "src/assets",
        {
          "glob": "app.yaml",
          "input": ".", 
          "output": ".",  
        }
      ],
      ...
}

The glob is a file matching pattern. I want to look for the exact file named app.yaml.

The input is the path relative to the workspace root. Define where to find the file. It’s in the root of the workspace root so I defined it as a dot.

The output is the path relative to the outputPath (dist/project-name). Define where in the dist/project-name folder the file should go. I wanted it to go in the root of the dist/project-name folder so I defined it as a dot.

After running the build, the app.yaml file was included in the dist/project-name folder!

Visit our website at https://nightwolf.dev and follow us on Facebook and Twitter!

Create A Unique ID Using A Custom Function In Google Sheets

nightwolfdev — Mon, 24 Apr 2023 00:01:40 +0000

Have you ever needed to uniquely identify a row in Google Sheets? You could use the row number, but if users move or delete rows, the numbers would change. What you need is a unique id for each row. Learn how to create a custom function that returns a unique id in Google Sheets!

Create A Custom Function

Open your spreadsheet and navigate to Extensions > Apps Script.
The Apps Script Editor will open with a single script file called Code.gs.
An empty function will default in. You can remove it.
Give your untitled project a name.

In the Apps Script code editor, create a function called UUID.

function UUID() {

}

Apps Script provides a Utilities class with a getUuid method. All we have to do is return it in the function.

return Utilities.getUuid();

Here’s the final code for the UUID function.

function UUID() {
  return Utilities.getUuid();
}

Use A Custom Function

Using a custom function is no different than using the many functions Google Sheets provides. Within the cell, enter the function:

=UUID()

The result will be a unique id!

baceef3c-f633-4deb-94db-1c17a2105719

You can also use functions (and custom functions) when inserting rows. For example, when using the appendRow method of the Sheet class.

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Users');

sheet.appendRow([
  '=UUID()',
  'John',
  'Smith'
]);

You can now uniquely identify rows using the id! This can be useful when making an edit to a specific row!

Visit our website at https://nightwolf.dev and follow us on X!

Create Your Own APIs with Google Sheets and Google Apps Script (Part 2)

nightwolfdev — Sun, 02 Apr 2023 23:16:54 +0000

If you haven’t read Part 1 of this article series, make sure you check that out first! We created a spreadsheet (database) with sheets (tables). Now, let’s learn how to use Google Apps Script to get data from sheets we specify and restructure the data from an array of arrays to an array of objects!

Article Series

Spreadsheet Setup (Part 1)
Get Data from Sheets (Part 2)
Create and Deploy Apps Script Web App (Part 3)
Filter Data with Search Criteria (Part 4)
Post Data to Sheets (Part 5)

Apps Script Editor

Open your spreadsheet and navigate to Extensions > Apps Script.
The Apps Script Editor will open with a single script file called Code.gs.
An empty function will default in. You can remove it.
Give your untitled project a name.

Create A Quick Test Function

To familiarize ourselves with Apps Script, specifically with Google Sheets, let’s create a quick test function in the Code.gs script file.

function test() {

}

Apps Script provides APIs for various Google apps. For Google Sheets, we’re interested in SpreadsheetApp. We want to do the following:

Use the spreadsheet that this script is associated to
Select a specific sheet (divisions)
Get all the rows of data from it
Log it to the screen to see what it looks like.

Let’s update our test function with the following:

function test() {
  const rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('divisons').getDataRange().getValues();
  Logger.log(rows);
}

To run the function, select the Run option in the Apps Script Editor toolbar, making sure the function called test is selected. You’ll be prompted to authorize the script and its permissions. You can learn more at Authorization for Google Services.

The result is an array of arrays. You can see the column headings are also included.

[
  [id, active, name],
  [1.0, 1.0, Accounting],
  [2.0, 1.0, Customer Service],
  [3.0, 1.0, Human Resources],
  [4.0, 1.0, Legal],
  [5.0, 1.0, Manufacturing],
  [6.0, 1.0, Marketing],
  [7.0, 1.0, Operations],
  [8.0, 1.0, Research & Development],
  [9.0, 0.0, Inactive Division]
]

APIs usually return data in JSON format, which is structured data in the form of key/value pairs. This makes it easier to understand what the data represents. For example, I’d like to know that the first value in the array is an id, the second value represents whether or not the division is active, and the third value is the actual name of the division. The column headings are perfect for being a representation of what the data is. We just need to restructure it so we can return it that way for each row. Let’s work on that next.

Restructure The Data

Currently, the data being returned is an array of arrays with no understanding of what they represent.

[
  [1.0, 1.0, Accounting]
]

What we really want is an array of objects like the following:

[
  {
    id: 1,
    active: 1,
    name: "Accounting"
  }
]

We want to use the column headings as the keys for our restructured data. Let’s create a function in the Code.gs script file called buildData. It will accept columnHeadings and rows as arguments.

function buildData(columnHeadings, rows) {

}

Create a variable called data to store a new array, which will eventually hold an array of objects.

const data = [];

Loop through the rows of data we received as an argument.

rows.forEach(row => {

});

For each row of data, we want to start by creating an empty object. Let’s call it entry.

const entry = {};

Within each row, we want to loop through each column of data and build the entry object. The key will be the column heading, which should be at the same index as the column we’re currently on. The value will be the value in the column.

row.forEach((value, index) => {
  entry[columnHeadings[index]] = value;
});

Now that the entry has been built, let’s push the entry into the data array.

data.push(entry);

Finally, let’s return the new array of data.

return data;

Here’s the final code for the buildData function.

function buildData(columnHeadings, rows) {
  const data = [];

  rows.forEach(row => {
    const entry = {};

    row.forEach((value, index) => {
      entry[columnHeadings[index]] = value;
    });

    data.push(entry);
  });

  return data;
}

Let’s use this function to return an array of objects instead of an array of arrays.

Get Divisions

We can continue adding code to the Code.gs script file. However, it’s nice to split up your code to keep it organized. Code related to divisions can go in its own script file. At the top of the Apps Script Code Editor next to Files, select the Plus icon, and choose Script. Give it the name Divisions.

An empty function will default in, you can rename it to getDivisions.

function getDivisions() {

}

Let’s get all the rows of data from the divisions sheet.

const rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('divisions').getDataRange().getValues();

The column headings is the first array. We don’t want to return the column headings, but we do need them. We can use the Array shift method to remove them from the rows array, but store them in a separate variable. Let’s call it columnHeadings.

const columnHeadings = rows.shift();

All that’s left to do is return the rows as an array of objects. We already created a helpful function for that called buildData, where it accepts the column headings and rows as arguments!

return buildData(columnHeadings, rows);

You may be asking if calling buildData would cause an error because it lives in a separate file called Code.gs. Apps Script loads all the script files into the same global namespace. So it will not cause an issue, unless you use the same function name across script files!

Here’s the final code for the getDivisions function.

function getDivisions() {
  const rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('divisions').getDataRange().getValues();
  const columnHeadings = rows.shift();

  return buildData(columnHeadings, rows);
}

Let’s continue creating separate script files and functions for the other sheets as well.

Get Titles

At the top of the Apps Script Code Editor next to Files, select the Plus icon, and choose Script. Give it the name Titles. Add the following code.

function getTitles() {
  const rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('titles').getDataRange().getValues();
  const columnHeadings = rows.shift();

  return buildData(columnHeadings, rows);
}

Get Users

At the top of the Apps Script Code Editor next to Files, select the Plus icon, and choose Script. Give it the name Users. Add the following code.

function getTitles() {
  const rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('users').getDataRange().getValues();
  const columnHeadings = rows.shift();

  return buildData(columnHeadings, rows);
}

Next Steps?

We used Google Apps Script to get data from sheets we specify. We also restructured the data from an array of arrays to an array of objects. Next, let’s learn how to create and deploy a Google Apps Script web app that returns our spreadsheet data as JSON just like an API! Continue to Part 3.

Visit our website at https://nightwolf.dev and follow us on Twitter!

DEV Community: nightwolfdev

Calculate Average Scores By Category using Google Sheets Pivot Table

Spreadsheet Setup

Pivot Table

Show/Hide Password Field In Angular Form

Password Field

Show Password Field

Show/Hide Password Toggle

Breweries App

Features

Links

Simplified Email Extraction with Javascript Bookmark

Extract Emails

Why A Bookmark?

Create Bookmark

Create A Sports Team Roster From Google Docs Template Using Google Sheets Data

Roster Template Setup

Spreadsheet Setup

Script Editor

Get Players

Style A Google Docs Table

Create Roster

Custom Menu

Email Roster

Warn And Log Out User Due To Inactivity In Angular App

Package Installation

Package Setup

Warn User With App Alert

Handle Inactive User

Export CSV from Clarity Datagrid in Angular

Access Clarity Datagrid Rows

Hidden Reusable Anchor Element

Export CSV

Remove Unwanted Spaces from Angular Form Submission

Trim Values Manually

Trim Directive

Software Development Resources

Include Other Files In Angular Build

Include Other Files Within Angular Src

Include Other Files Outside Angular Src

Asset Path Must Start With The Project Source Root

Create A Unique ID Using A Custom Function In Google Sheets

Create A Custom Function

Use A Custom Function

Create Your Own APIs with Google Sheets and Google Apps Script (Part 2)

Article Series

Apps Script Editor

Create A Quick Test Function

Restructure The Data

Get Divisions

Get Titles

Get Users

Next Steps?