Tuesday, December 8, 2015

Fix missing country codes for all your Google contracts

When I recently moved from US to Sweden I realized that many of my beloved Google Contact phone numbers didn't have a country code specified, which has caused a number of issues.  From the obvious one of not being able to complete a call, to not being able to locate friends on WhatsApp.

Anyway, in google contacts you can set default country code, but that only works well going forward, so I wrote a simple script for Google Apps Script to solve the problem. The script is listed below.

Disclaimer: I have tested the script many ways, but please use at your own risk !

To run simply:
- Go to https://script.google.com,
- Start a blank project, and copy/paste the script below
- Run once with UNCOMMENT line commented out (as is)
- Press Ctrl-Enter to view logs to make sure everything looks fine.
- Uncomment the UNCOMMENT line, and run again to add country codes.

Nifty,
Thomas

// ============================================================
// What:  Find and fix all phones from your Google contacts without country codes
// How:   1) Update list of common area codes you want to fix. See phone_fixes. Otherwise it could add invalid country code to contacts
//        2) Run once to verify that correct phones will be auto fixed.
//           Review log file (press ctrl-enter)
//        2) Uncomment the line after UNCOMMENT to run and update all contact that will be auto-fixed.
//
// Dec 2015, Thomas Gemal
// ============================================================
function FindPhonesWithoutCountryCodes () {

  // Grab all contacts
  var myContacts = ContactsApp.getContacts();
  var num_contact_without_phones = 0;
  var num_phones_to_fix = 0;
  var num_phones_auto = 0;
  
  // Add all the starting areas codes to fix, and country code to pre-pend. Add as many as you like
  var phone_fixes = [
    ["206", "+1"],
    ["302", "+1"],
    ["312", "+1"],
    ["(206)", "+1"],
    ["(302)", "+1"],
    ["(312)", "+1"],
  ];
    
  // For each contact group
  for(var i = 0; i < myContacts.length; i++) { 

    // Get contact info
    var contact = myContacts[i];
    var phones = contact.getPhones();
    var num_phones = phones.length;
    var name = contact.getFullName();
    
    if (num_phones == 0) {
      num_contact_without_phones++;
      continue;
    }

    var printed = false;
    // For each phone per contact
    for(var p = 0; p < num_phones; p++) { 

      var phone = phones[p].getPhoneNumber();

      // Skip if already country code
      if (phone.substring(0,1) == "+" ) {
        continue;
      }
      
      if (!printed) {
        Logger.log(Utilities.formatString("%s: %d phone(s)",name,num_phones));
        printed = true;
      }
      
      num_phones_to_fix++;
      
      var auto = false;
      // Check if phone number matches to the ones we want to fix
      for(var pf = 0; pf < phone_fixes.length; pf++) { 

        if (phone.substring(0,phone_fixes[pf][0].length) == phone_fixes[pf][0]) {

          // matched. Now prepend the country code
          var phone_new = phone_fixes[pf][1] + " " + phone;
          Logger.log(Utilities.formatString(" Fix-auto    %s  -> %s",phone, phone_new));
        
          // UNCOMMENT: Updates the phone number. Uncomment next line when fully tested
          //phones[p].setPhoneNumber(phone_new);
          
          auto = true;
          num_phones_auto++;
          continue;
        }
      }
      if (!auto) {
        Logger.log(Utilities.formatString(" Fix-manual  %s",phone));
      }
    }
  }
  
  Logger.log(Utilities.formatString(" Contacts:         %s",i));
  Logger.log(Utilities.formatString("   No Phone:       %s",num_contact_without_phones));
  Logger.log(Utilities.formatString("   Phone to fix:   %s",num_phones_to_fix));
  Logger.log(Utilities.formatString("    Auto:          %s",num_phones_auto));
  Logger.log(Utilities.formatString("    Manual:        %s",num_phones_to_fix - num_phones_auto));
  
}

Friday, July 18, 2014

Converting XLS worksheets into separate (CSV) files

Today I had a large Excel file (workbook) with many tabs (worksheets), and I needed each tab in a separate file as they needed to be shared with different people (who should not be able to see the other tabs).

Yes, you could make X copies of the XLS file and then manually delete all sheets but one in each file, but hey, that is for people who enjoy pain. The much easier way is to do it with a simple macro. To split an XLS file into separate XLS files for each tab, simply open the XLS file you want to split. Then open the VBA editor (press ALT + F11) and copy the following VBA code into the a new module (go under "Insert" menu, and select "Module"). Close VBA Editor (no need to save), and back in XLS file, go under "View", select "Macros" and "View macros". Now simply run the new macro called ConvertTabsToFiles. No need to save the macro. Just run it.

Sub ConvertTabsToFiles()
  Dim currPath As String
  currPath = Application.ActiveWorkbook.Path
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  For Each xWs In ThisWorkbook.Sheets
    xWs.Copy
    Application.ActiveWorkbook.SaveAs Filename:=currPath & "\" & xWs.Name & ".xlsx"
, CreateBackup:=False
    Application.ActiveWorkbook.Close False
  Next
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
End Sub


You should now have a new XLS file for each worksheet in the original XLS file.  If your XLS file is of extension .xls and not .xlsx, simply change the xlsx extension to xls in the code above @ line 8. Otherwise your new files will be of the wrong format (but still readable)

If you want to save several sheets to CSV, simply use this line instead of the one saving to xlsx
Application.ActiveWorkbook.SaveAs Filename:=currPath & "\" & xWs.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False

And if you run into issues that your file "cannot be saved in macro-free workbooks", simply save to the file format called xlsm (XLS macro-enabled workbook).

Nifty,
Thomas

Tuesday, July 15, 2014

Random date generation

Today I needed to generate a large (bulk) set of random dates for a project at work.  Probably a million ways to do this, but I turned to Excel. I've used the RAND function before and was expecting to use something ugly like this for month:
=INT(RAND()*12)+1

But as I'm always looking for new cool ways to be more efficient, I stumbled upon this cool function in XLS. Not sure when it was introduced, but it is cool. Simply use RANDBETWEEN in combination with the DATE function.  To generate a random date between 2 dates, simply use:
=RANDBETWEEN(DATE(2014,1,1),DATE(2014,7,15))

Note that new random data is calculated every time calculations are performed, like when you add a new formula. RAND works the same way.

Nifty,
Thomas


Thursday, June 5, 2014

JSON file validator locally

JSON files are great for many things, but they can also be the root of very strange issues. Hence, you should always validate your JSON files whenever you update them to ensure you don't spend hours debugging issues that a really caused by a simple error in a JSON file (yes, I talk from experience).

Validating a JSON file is easy. There are many good online ones, like http://jsonlint.com/. However, if you want to validate many files, it is cumbersome, or if you have sensitive data in your JSON file, you may not want to copy/paste that to a public site.

The solution is simple. Simply install a JSON validator / checker locally. And they also allow for “pretty printing” of your JSON too.  There are a few ways to skin this cat.

Steps (jsonlint):
  1. Install NPM. Part of nodejs. Get it from http://nodejs.org/download/
  2. Get jsonlint from https://www.npmjs.org/package/jsonlint
  3. Reboot (if on Windows)
To run simply open a prompt and type “jsonlint FILE” or "jsonlint -h" to see options.
For multiple files, script it. E.g. on Windows use “for %i in (content\*.json) do jsonlint -q %i

Steps (Cygwin):
  1. Make sure that Python is installed.
To run simply open a Cygwin prompt and type “cat FILE | python -mjson.tool”.
For multiple files, script it. E.g. in BASH “for FILE in content/*.json \n do \n cat $FILE | python -mjson.tool \n done

Nifty,
Thomas

Saturday, February 22, 2014

Cygwin DOS path names issue on Windows 8

I finally upgraded to a Windows 8 machine and noticed that the Cygwin command prompt gave me issues with DOS style path names. This is very easily fixable by using an environment variable called CYGWIN.

However, I already had the environment variable named CYGWIN set, but it still didn't work. Turns out I was using the incorrect case. CYGWIN MUST BE UPPERCASE. Hopefully this quick post can solve your headache too.

Issue:
Every time you use a DOS style path (backwards slash) you get an error like this
cygwin warning:
  MS-DOS style path detected: C:\Intel
  Preferred POSIX equivalent is: /cygdrive/c/Intel
  CYGWIN environment variable option "nodosfilewarning" turns off this warning.
  Consult the user's guide for more details about POSIX paths:
    http://cygwin.com/cygwin-ug-net/using.html#using-pathnames

Fix:
In a DOS command prompt (cmd) type
set cygwin

I got the response
cygwin=nodosfilewarning

This is wrong. Not sure why the cygwin word is lowercase. Should be UPPERCASE CYGWIN. To fix, press WINDOWS keys, type "environment" to open the system environment variables window. Under system variables, create a new one called CYGWIN and set to nodosfilewarning. And delete the older "cygwin" (lowercase) one.

Next time you open a DOS prompt and type "set cygwin" you should see
CYGWIN=nodosfilewarning

Now all the DOS errors should go away.
Nifty,
Thomas


Saturday, January 25, 2014

Setting function keys back to normal on HP Envy Notebook

Just got a new HP ENVY 15 and so far I love it. Well, except for one little thing that drove my crazy.

Not sure what those pesky engineers at HP were thinking when they decided to change the default way the function (fn) keys work on the newer HP ENVY laptops. By default you do not need to press the (fn) key to get function key functionality.

Example: If you press F5 (even in a browser window) it turns the backlit keyboard on and off, rather than refreshing the browser. Really???

This "amazing" technology is called "Action Keys Mode", and the good news is that it is very easily turned off to get your laptop back to working the standard way of having to press the fn key.

Steps to fix:
- Shut Down your computer completely. Don't put in Sleep or Hibernate
- Press Power button to start
- Immediately press F10 key a few times
- This will start the BIOS
- Go to 3rd page (System Configuration). Press right arrow key to go to next page
- Select the "Action Keys Mode". Press ENTER. Select DISABLE
- Save and Exit

Nifty,
Thomas

Saturday, January 18, 2014

Useful RegEx expression for text manipulation

When doing text manipulation, there are a number of regular expressions (RegEx) that I tend to use over and over again. Maybe you'll find them useful too.

Problem Find Replace Example: Before Example: After
Move text in brackets
from end of line to beginning of line
^(.*) (\[.*\])$ \2: \1 Yada yada [BC4]
Yada yada [POS123]
[BC4]: Yada yada
[POS123]: Yada yada
Convert dates from
MM-DD-YYYY to YYYYMMDD
^(..)-(..)-(....) \3\1\2 11-30-1998
01-23-2004
19981130
20040123
Clean phone numbers
(remove space, paren, dash, period)
[ \-\.\(\)] N/A 999.888.7777
(555) 444-3333
9998887777
5554443333

Nifty,
/T

Friday, January 10, 2014

Deleting multiple empty Google contact groups

My beloved Google Contacts suddenly have ~300 empty "Starred in Android" groups. Not sure how or why they showed up, but many people who have the very same problem are pointing fingers at Nook, and yes, I was using a Nook at the time it occurred.

Anyway, there does not seem to be an easy way to delete all these empty contact groups except manually one by one, which I simply refuse to do. So I wrote a simple script for Google Apps Script to solve the problem. The script is listed below.

Disclaimer: I have tested the script many ways, but please use at your own risk !

To run simply:
- Go to https://script.google.com,
- Start a blank project, and copy/paste the script below
- Run once with ContactsApp.deleteContactGroup commented out (as is)
- Press Ctrl-Enter to view logs to make sure everything looks fine.
- Uncomment the ContactsApp.deleteContactGroup line, and run again to delete empty groups.
- If you want to remove all empty groups independent of name, uncomment the line referencing "Starred in Android"

Nifty,
Thomas

// ============================================================
// What:  Deletes all empty "Starred in Android' contact groups from your Google contacts
// How:   1) Run once to verify that correct groups will be deleted.
//           Review log file (press ctrl-enter)
//        2) Uncomment deleteContactGroup line below and rerun.
//
// Jan 2014, Thomas Gemal
// ============================================================
function DeleteAllEmptyGroups() {
  // Grab all contact groups
  var myContactGroups = ContactsApp.getContactGroups();

  // Use this to limit number of groups processed if performance is an issue
  var MAXCOUNT = 1000;
  var count = 0;
  
  // For each contact group
  for(var i = 0; i < myContactGroups.length; i++) { 

    // Only delete MAXCOUNT groups at a time (since execution can take long)
    if (count == MAXCOUNT) {
      break;
    }
    
    // Get name of a contact group
    var group = myContactGroups[i];
    var group_name = group.getName();

    // Keep this code to only deal with "Starred in Android". 
    // To delete all empty groups, simply comment this 'if' out
    if (group_name != "Starred in Android") {
      Logger.log(Utilities.formatString("%s: Skipped. Not SiA", group_name));
      continue;
    }

    // Get number of contacts for a group
    var num_contacts = ContactsApp.getContactsByGroup(group).length;

    // We are only looking for empty groups
    if (num_contacts == 0) {
      // Ignore special groups
      if (group_name.substring(0,13) != "System Group:") {
        // Log that we want to delete
        Logger.log(Utilities.formatString("%s (%d):  DELETE",group_name,num_contacts));
        count++;
      
        // !!!!! Uncomment line below to physically delete the groups  !!!!!
        // Make sure you have first verified that list to delete is OK
        // ContactsApp.deleteContactGroup(group);
      } else {
        Logger.log(Utilities.formatString("%s (%d):  Skipped. System group",group_name,num_contacts));
      }   
    } else {
      Logger.log(Utilities.formatString("%s (%d):  Skipped. Not empty",group_name,num_contacts));
    }   
  }
  Logger.log(Utilities.formatString("Empty groups processed: %d", count));
}

Wednesday, June 19, 2013

Fixing localhost email on CodeIgniter under Windows

Argh. After spending a LOT of agonizing hours trying to get localhost email to work under CodeIgniter (PHP) on my Windows 7 machine, I finally figured it out. And since this is such a non-intuitive fix, hopefully I can ease the pain for someone else by sharing this.

Setup:
  • Using XAMPP + CodeIgniter
  • I had my email.php configured as very standard for localhost
    • $config['protocol']  = 'smtp';
    • $config['smtp_host'] = 'localhost';
    • $config['smtp_port'] = '25';
    • $config['smtp_timeout']='10';

Issue:
  • My PHP code kept hanging in "$this->email->send()" giving me a "waiting for localhost".
  • In my php_error_log I had "Fatal error: Maximum execution time of 300 seconds exceeded in [stuff]\system\libraries\Email.php on line 1869


Resolution:
  • Turns out that you need to specify the newlines on Windows
  • Simply update your email.php to include:
    • $config['newline'] = "\r\n";
    • $config['crlf'] = "\r\n";
  • Some additional details at the end of this thread http://ellislab.com/forums/viewthread/184596/

That's it.
Nifty

Tuesday, May 28, 2013

Fixing Carbonite UI skin issues

If you use the Carbonite online backup you may have noticed that Carbonite InfoCenter UI skin at times looks very odd. You may experience javascript errors, and see HTML code referencing Starbucks or hotels. See picture below

The good news is that this is just a caching issue, and that your backup is working as planned. However, it is really annoying to look at. It apparently can happen after you visit public WiFi networks that redirects you to a login page. Anyway, this is how to fix it.

1) Remove the folder with the cached files.
On my Windows 7 system, remove "C:\ProgramData\Carbonite\Carbonite Backup\dyncache"

2) Reload the UI
Go to the system tray
Press the left (not the right) SHIFT + CTRL key while left clicking on the green Carbonite icon. Go under "Skin" and "Reload UI"



Nifty,
Thomas

Thursday, April 4, 2013

Missing leading zeroes in XLS

If you are not careful when importing data into Excel (XLS), you often see leading zeroes be removed as XLS tries to be smart about the data it imports. However, this causes issues in Zip codes, SSNs, account numbers, etc. E.g. east coast or Puerto Rico Zip codes start with one or two leading 0s, and suddenly ZIP code 00555 turns into 555. This of course creates all sorts of fun issues in comparison, or if data is used as input into other processes, etc.

To append leading zeroes to a field, simply add a new column with the following formula, and then copy/paste the new column as values to save it. The formula works on any number of leading zeroes missing. Note that “5” in the formula below is the number of digits you expect in the correct length of the field (e.g. 5 for ZIP)

 A       B
ZIP   Fixed_ZIP
555   =RIGHT(“00000” & A2,5)

To add extra protection in case there are blank ZIP codes, simply change the formula to:
=IF(TRIM(A2)="","",RIGHT("00000"&A2,5))

FYI, the simplest way to avoid the leading zeroes being removed is to import the field as text. This is done in the "Text Import Wizard" in XLS that launches automatically when you open a text file. In step 3, simply select set the ZIP fields as text.

Nifty,
Thomas

Monday, February 25, 2013

Grep for value in a column

When doing data analysis, you often need to find records with a given value in a given column, but you want to return the whole line. The grep command will be able to do some of that but has a number of drawbacks. See older post about that at
http://niftypctricks.blogspot.com/2008/08/greping-for-column-value.html

A much simpler (and cleaner) way is to use the awk or gawk command from the command line. Note that the awk/gawk command line may look slightly different depending on whether you are on Linux, Cygwin, MKS toolkit, DOS, etc.

Assuming we have a sample tab delimited text file named in.txt:
line1 joe smith ca 4085551212
line2 joe carlson az 3334445555
line3 carl smith ny 2049998888
line4 joe smith or 5035551234
line5 mike erwin ca 4159876543
line6 mike erwin CA 4159876543

To find all records when column 5 (phone number) contained the value 555, simply use
cat in.txt | gawk -v FS='\t' 'match($5,"555")' > out.txt

The awk / gawk command "match" returns true if pattern1 ($5, meaning column 5) contains pattern2 (555), and by default that prints the whole line, so no need to explicitly put "print $0" in there. So the code sample above will return line 1, 2, and 4.

Nifty #NerdTip,
/T



Monday, February 11, 2013

Pipe to tab

Converting a pipe delimited file to a tab delimited file should be extremely simple. And many times it is if you run under a pure UNIX environment. But if you are under Windows / DOS (using Cygwin) it can be a little tricky, but there is a simple solution.

To convert a pipe delimited file, simply type:
cat FILE | tr "|" '\t' 

The trick here is that the first argument to the tr command has to use double-quotes. The standard single quotes will not work under DOS if the character you are converting is a pipe.

Nifty,
/T


Wednesday, January 30, 2013

Text editor tab in browser

This is such a cool and simple trick. Creates a simple text editor / notepad in a browser tab. This is useful for storing various kinds of temporarily data, e.g. when copy / pasting multiple items, etc.

To instantiate, simply create a new tab (in an HTML 5 compatible browser), and type in the following in the URL address bar:
data:text/html, <title>Text Editor</title><body contenteditable>

Store as a bookmark so you have easy access in the future.

Or if you want a more permanent version (uses browser's LocalStorage), type:
a5.gg

Much more details, and many "interesting" incarnations at:
https://coderwall.com/p/lhsrcq

Nifty,
Thomas

Friday, October 19, 2012

DOS prompt output to clip board

This trick is so simple, yet so powerful if you dabble in the DOS Prompt a lot. You can pipe the output from any command to the clipboard simply by piping it to "clip". That;s it. No more copy paste from the line wrapped DOS prompt output.

Example:
dir | clip
will give you the output of the DIR command to the clipboard.

Or if you are running Cygwin or MKS toolkit, and want to get the counts of instances in column 3 of a file
cut -f3 myfile.txt | sort | uniq -c | clip

Nifty,