list of duplicated contacts

Waze – Remove duplicate contacts on Android

If you have recently installed the Waze app on your Android phone like I have, it works great. But, one odd thing that happens is that you have a lot more contacts. If you look at the contact list, a lot of them are just duplicates of existing contacts.

list of duplicated contacts
List of duplicated contacts

When you open up the contact, you see the little icon down at the bottom that shows you where the contacts are coming from.

icon showing that contact is from Waze
Contact details showing Waze icon

Video Explanation

They are coming from the Waze app. So, I got curious and I uninstalled and reinstalled the app. Lo and behold, it does ask to access your contacts.

Waze asking for access
Waze asking for access

Why you would ever want a GPS navigation app to access your contacts is beyond me but it asked me and technically I said yes. I never pay attention to these permissions but I probably should, as should all of us. The way to solve this without removing Waze from you phone is to go into your settings, go to accounts, and then select the Waze account.

List of accounts including Waze
List of accounts including Waze

Go ahead and uncheck sync contacts.

Box to uncheck to stop Waze from synching contacts
Stop Waze from syncing your contacts

You don’t need to do that anymore. Then hit the button that brings up the extra menu.  On my phone, Galaxy S4, it’s in the lower left hand corner.  It’s one of the hardware buttons. Then it brings up the option where you can remove the account.  

option to remove account
Option to remove account
android accounts
Confirmation to remove account

Go ahead and remove the Waze account from your phone and then all of these extra contacts will go away. This should solve it. It should shrink your address book down to the way it should be and you won’t have to sort through all these duplicates to find what you are looking for.

Gmail message with multiple attachments

Gmail – Download multiple attachments from an email at once

If you’re using Gmail and you’ve received an email with more than one file attached to it, it can time consuming to download them. This article will show an easy way to download them all at once, saving you all of the extra steps.

Gmail message with multiple attachments
Gmail message with multiple attachments

You can download each attachment separately by hovering over the thumbnail and clicking the download icon but this can take a long time. There is a lot of repeated steps if you do it this way.

Download all attachments at once
Download them one at a time

However, if you move your cursor to the upper right hand corner of the attachments and hover over the icon as shown in the next picture below, instead of downloading one attachment, this gives you the option to download all of the attachments at once.

Download all attachments at once
Download all attachments at once

Video explanation

The file that is downloaded will be a Zip file which is one compressed file with all of the downloads compressed inside of it.

Zipped file in file explorer
The zip file with the attachments in it

To open the Zip file using Windows 10, double click on the file then click the button that says “extract” on it. Click it and do extract all.

Extract all
Extract all of the files

This will extract all of the attachments whether there were two or twenty saving you time for other things in your life!

Snipping Tool in the Start menu

How to insert a YouTube video link into a Google Doc with a thumbnail

They are many reasons why you may want to inserts a YouTube Video link into your Google Doc but there is no menu option for doing this. Following is a series of steps that will allow you to create a link to a YouTube video in your document complete with a clickable thumbnail. To the reader of the document, this will be an obvious YouTube link that looks like one from the web. When clicked, it will bring the reader to the video.

These steps give you a clickable thumbnail, but they will not embed the player into your document.

Screen clip

Start by finding the video that you want on YouTube. Once you have found the video, use the Snipping Tool built into windows and grab a screen clip of the video thumbnail.

Snipping Tool in the Start menu
Snipping Tool in the Start menu

To find the Snipping Tool hit your Start button and type “Snip”. Press enter once it is highlighted.

Save the snip to your computer.

Video explanation which is actually a video!

Insert the images

Go back to your Google Doc and place your cursor where you would like to insert the video. Go to Insert and then Drawing.

Insert drawing option
Insert drawing option

While you are in the insert drawing window, you will be inserting two images.

Insert image option
Insert image option

For the first image, use the upload option and choose the screenshot that you just took with the Snipping Tool.

Upload image option
Upload an image

After inserting your screen shot file, go back to Insert image, then More, and choose search. Search for “YouTube Play Button” and choose which one you would like.

Search YouTube button
Search for a YouTube button

All ready

Shrink this button and move it over the middle of the screen shot. Your drawing is now ready. You have a thumbnail pictures of your YouTube video that will take you directly to the YouTube video page with it ready to play.

Finished YouTube thumbnail image
Finished YouTube thumbnail image

Follow image below for the live Google Doc with a YouTube link

docs share icon

Cells being summed and counted

Google Sheets – Sum or count values based on cell color

Overview

If you’re using Google sheets and you have a list of amounts that you want to sum or count based on the background color of the cells, there’s no built-in function to do it. However, there are a relatively easy set of steps to make your own functions to get it done. You’ll be able to count based on cell color and you’ll be able to sum as well.

Custom formulas in action

Cells being summed and counted
Light blue cells being summed and counted by color

Count by cell color

If you look in the live spreadsheet, you will see the custom formulas being used for summing and counting by cell color. The counting is done by a formula with the nice little name of countColoredCells which takes the range and cell with the background color that you want as its two inputs. You want to count B4 through B9 and you want to count the number of cells that have the background color used in cell B8, which is the light blue cell with the dotted line around it.

Formula for count by cell color
Formula for count by cell color

If you change the second parameter above from B8, a light blue cell, to B5, a yellow cell, the result would be 3 because there’s three yellow cells.

Sum by cell color

sumColoredCells is returning a 24 which is the sum of all the light blue cells. It is similar to the count function – it is summing the colored cells in B4 through B9 that are formatted like B8.

Formula for sum by cell color
Formula for sum by cell color

Creating custom formulas

How do we make these functions and any other custom function that you’re so inclined to write? First, go to Tools and you go to Script editor... Don’t worry, we’re just going to copy and paste code below. The code for countColoredCells was obtained from this page at iGoogleDrive.

 /**
* @param {range} countRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in countRange
* @return {number}
* @customfunction
*/

function countColoredCells(countRange,colorRef) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();
  
  var rangeA1Notation = formula.match(/\((.*)\,/).pop();
  var range = activeSheet.getRange(rangeA1Notation);
  var bg = range.getBackgrounds();
  var values = range.getValues();
  
  var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
  var colorCell = activeSheet.getRange(colorCellA1Notation);
  var color = colorCell.getBackground();
  
  var count = 0;
  
  for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
        count=count+1;
  return count;
};
   

And this code for sumColoredCells was obtained from the same blog, but a different page.

 /**
* @param {range} sumRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in sumRange
* @return {number}
* @customfunction
*/

function sumColoredCells(sumRange,colorRef) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula().toString();
  formula = formula.replace(new RegExp(';','g'),',');
  
  
  var rangeA1Notation = formula.match(/\((.*)\,/).pop();
  var range = activeSheet.getRange(rangeA1Notation);
  var bg = range.getBackgrounds();
  var values = range.getValues();
  
  var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
  var colorCell = activeSheet.getRange(colorCellA1Notation);
  var color = colorCell.getBackground();
  
  var total = 0;
  
  for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
        total=total+(values[i][j]*1);
  return total;
};   

Script editor

After you go to Tools then Script editor, you come up with a blank screen. But if you don’t, just do a new script file. Paste the code into the blank window. Repeat for each code section above and name them countColoredCells and sumColoredCells. For each file, the script editor puts the “.gs” at the end of the file name which indicates that it is a Google Script. After you make these two, save them, come back to your spreadsheet, type in the formulas, and it should work for you. See the video and live spreadsheet for further clarification.

Watch the video

This site has a companion YouTube channel that has pretty much, well almost exactly, the same content. If you like this, you’ll like that.

 Live examples in Sheets

Go to this spreadsheet for examples of counting cells by cell color that you can study and use anywhere you would like.

pivot table parameters

Google Sheets – Calculated Fields in Pivot Tables

Google Sheets allows you to build pivot tables to summarize large sets of data. When building the pivot tables, you can also add fields that perform calculations on the data once it is in the pivot tables as shown in this live Google Sheet. These calculated fields are a must-have in certain situations as you may want to add/subtract/multiply/etc summarized data from the pivot table that doesn’t exist in the original data being pivoted. For example, if you have a table of salaries and years of college each employee attended college, you may want to calculate the return for each year of college. To do this, you would first summarize the data by average salary for each group, then perform the division to arrive at the average after the data is summarized.

This tutorial assumes that you have already completed your Pivot Table and you have a basic knowledge of how to use them. See this video if you need some basic help on Pivot Tables or take a deep-dive in our comprehensive training course which includes full access to all of the spreadsheet files used including source data and Pivot Tables.

If you need a primer on Pivot Tables, this video will walk you through them.