Showing posts with label tutorial. Show all posts
Showing posts with label tutorial. Show all posts

Sunday, August 18, 2013

Google Apps Script demo – Send SMS on new important mail in GMail

Google Drive allows you to easily create and edit Documents, Presentations, Spreadsheets, Forms and Drawings. But not everyone might be familiar with the Google App Script integration functionalities similar to the Visual Basic scripting integration within Microsoft Office macro’s.

Recently, I found this tutorial to link GMail to Google Calendar written by Romain Vialard.

Based on this tutorial, I made the custom script below in order to get a free SMS’s from Google on every new unread e-mail marked as ‘Important’ within GMail. Setting up this little script will give you a brief indication of the power and possibilities of the Google App Script integration.

  1. In Google Calendar, register your mobile phone within the ‘Settings’ > ‘Mobile Setup’, to enable SMS notifications. Sending SMS’s from within Google is free of charges, but it might be limited to 50 messages per day.
  2. In Gmail, create a new label named 'NbSMS' (Notified by SMS). Once a new mail has been notified by SMS, this label will be assigned to the mail in order to make sure the notification is send out only once and the mail will be ignored by the script once this label is assigned.
  3. In Google Drive, create a new Spreadsheet, give it any name.
  4. Choose the menu ‘Tools’ > ‘Script Editor’.
  5. Choose ‘Create script for: Blank Project’.
  6. Copy and paste the following script:
    function sendText() {
    Logger.log('Start of sendText script');
    var today = new Date();
    var nowHour = today.getHours();
    var startTime = 8;
    var endTime = 24;

    Logger.log('The SMS notification will only run between: ' + startTime + ' and ' + endTime + ', current hour: ' + nowHour);
    if (nowHour <= startTime || nowHour >= endTime) {
    Logger.log('Quite time, no SMS notification will be sent between: ' + startTime + ' and ' + endTime + ', current hour: ' + nowHour);
    } else {
    // var events = cal.getEvents(new Date(startDateAndTime), new Date(endDateAndTime));
    //based on https://developers.google.com/apps-script/articles/gmail_filter_sms
    var notifiedBySmsLabel = 'NbSMS';
    var unreadPriority = GmailApp.getPriorityInboxUnreadCount();
    var unreadsFound = 0;
    Logger.log("Number of unread emails in your Priority Inbox : " +
    GmailApp.getPriorityInboxUnreadCount());
    if (unreadPriority > 0) {
    var threads = GmailApp.getPriorityInboxThreads();
    //threads.refresh();
    var now = new Date().getTime();
    var alreadyNotified = false;
    for(i in threads){
    if (!threads[i].isUnread()) {
    alreadyNotified = true;
    if(unreadsFound >= unreadPriority) {
    break;
    } else {
    continue;
    }
    } else {
    ++unreadsFound;
    }
    var threadLabels = threads[i].getLabels();
    for(y in threadLabels) {
    if(threadLabels[y].getName() == notifiedBySmsLabel) {
    alreadyNotified = true;
    break;
    }
    }
    if (!alreadyNotified) {
    var smsText = 'Mail: '+threads[i].getFirstMessageSubject() + ', from: ' + threads[i].getMessages()[0].getFrom();
    smsText = smsText + ' ' + threads[i].getMessages()[0].getPlainBody();
    Logger.log('Event with SMS will be created with content: ' + smsText);
    var event = CalendarApp.createEvent(smsText,
    new Date(now+60000),
    new Date(now+60000));
    event.setDescription(smsText);
    event.addSmsReminder(0);
    var label = GmailApp.getUserLabelByName(notifiedBySmsLabel);
    label.addToThread(threads[i]);
    }
    }
    //threads.refresh();
    Logger.log('All important messages treated and label ' + notifiedBySmsLabel + ' applied.');
    }
    }
    }



  7. Choose the menu ‘Resources’ > ‘Current project’s triggers…’ and add a new trigger.


  8. Select the function ‘sendText()’ > Events: ‘Time-driven’ > ‘Minutes timer’ > ‘Every 5 minutes’, and save the trigger.


  9. Save the script.


  10. Click the Run ► icon. A pop-up opens asking you for your authorization to access the Gmail and Google Calendar services.


  11. Click the Authorize button.


  12. Click the Run ► icon again.


  13. Choose the menu ‘View’ > ‘Logs’ to see the log output.


  14. Debugging is possible using the bug icon. Set some breakpoints by clicking on the line number next to the script.


  15. After one minute, you should receive a text on your mobile device, containing the subject of the important unread emails within GMail.


  16. To see what other functionalities are available within GMail, Google Calendar en other Google services, see the Google App Script reference guide.


  17. Some other interesting tutorial to work with Google Calendar events is available in this blog post.



Update 09/04/2014: optimized the labeling to only apply the label to messages which were not yet labeled, instead of all messages in priority inbox.



Update 21/10/2015: Google no longer supports to get notified by SMS for a calendar item. So the approach above won’t work any longer (calendar item will be created, but no sms will be received).

Saturday, September 29, 2012

My Excel macro selection

Some easy to use Excel macro’s. All explained below are available in one Excel Macro Example.

All macro’s are combined in one example Excel sheet template to test the usage of it.

Password Generation

Function RndPassword(vLength)
'This function will generate a random strong password of variable
'length.
'This script is provided under the Creative Commons license located
'at http://creativecommons.org/licenses/by-nc/2.5/ . It may not
'be used for commercial purposes with out the expressed written consent
'of NateRice.com

For x = 1 To vLength
Randomize
vChar = Int(89 * Rnd) + 33
If vChar = 34 Then 'this is quote character, replace it with a single quote
vChar = 39
End If
RndPassword = RndPassword & Chr(vChar)
Next
End Function








An example usage of this Password generation function: I have a list with on each row a button to generate a password in the cell next to the button. Each button is linked to the same macro (for easy copy paste of the row). The cell to fill with the new password is automatically detected based on the cell in which the button is drawn. Before setting the new generated password, a warning is shown in a message box.









ExcelMacroPasswordGeenration









Sub Process_GenNewPassword()
Dim LRange As String
Dim RowOffset As Integer
Dim ColumnOffset As Integer
Dim newGenPassword As String
'Find cell that button resides in
LName = Application.Caller
Set bBox = ActiveSheet.Buttons(LName)
LRange = bBox.TopLeftCell.Address
RowOffset = 0 ' relative location of the row in which to work, same row used
FirstNameColumnOffset = -5 ' relative location of firstname column in row to show in warning message box
LastNameColumnOffset = -4 ' relative location of lastname column in row to show in warning message box
PasswordColumnOffset = 2 ' relative location of column in row where the generated password should be set
sResult = MsgBox("The password for user '" & Range(LRange).Offset(RowOffset, FirstNameColumnOffset).Value & " " & Range(LRange).Offset(RowOffset, LastNameColumnOffset).Value & "' will be replaced with a new random password!", vbExclamation + vbOKCancel, "New password generation")
If (sResult <> 1) Then
Exit Sub
End If
newGenPassword = RndPassword(10)
Range(LRange).Offset(RowOffset, PasswordColumnOffset).Value = newGenPassword
End Sub








Checkboxes









Checkbox LinkedCell









Whenever you need many checkboxes in your sheet, you'll probably need each checkbox to be linked to the cell it is residing on.




When copy-pasting rows or columns, the checkboxes will be copied as well, but they will still be linked to the same cell as the checkbox you started to copy-paste. The little macro shown below, will update each Checkbox on the active sheet and set it's linked cell to the cell on which the Checkbox is drawn.









Sub setCheckBoxLinkedCell()
'Loop through all Checkboxes in the active sheet
'Set for each Checkbox it's "LinkedCell" value to the cell on which the Checkbox is drawn
Application.ScreenUpdating = False
' turns off screen updating
Application.DisplayStatusBar = True
' makes sure that the statusbar is visible
Application.StatusBar = "Updating linked cells of all Checkboxes in this sheet."
For Each chk In ActiveSheet.Checkboxes
LRow = ActiveSheet.Checkboxes(chk.Name).TopLeftCell.Row
LColumn = ActiveSheet.Checkboxes(chk.Name).TopLeftCell.Column
cA1 = Application.ConvertFormula("R" & LRow & "C" & LColumn, xlR1C1, xlA1)

ActiveSheet.Shapes.Range(Array(chk.Name)).Select
With Selection
.Value = xlOff
.LinkedCell = cA1
.Display3DShading = False
End With
Next chk
Application.StatusBar = "Linked cells of all Checkboxes in this sheet are updated."
Application.ScreenUpdating = True
End Sub








Save date on Checkbox check









The macro below will set the date in the cell next to the Checkbox whenever the checkbox is checked. All checkboxes are linked to the same macro, for easy copy-pasting. The cell in which to save the date will be detected based on the checkbox location.









ExcelMacroCheckboxDate









Sub Process_CheckBox()
Dim cBox As CheckBox
Dim LRow As Integer
Dim LRange As String

LName = Application.Caller
Set cBox = ActiveSheet.Checkboxes(LName)

'Find address that checkbox resides in
LRange = cBox.TopLeftCell.Address
DateRowOffset = 0 ' row offset (relative to the checkbox location) in which the date should be set
DateColumnOffset = 1 ' column offset (relative to the checkbox location) in which the date should be set

'Change date if checkbox is checked
If cBox.Value > 0 Then
Range(LRange).Offset(DateRowOffset, DateColumnOffset).Value = Date

'Clear date if checkbox is unchecked
Else
Range(LRange).Offset(DateRowOffset, DateColumnOffset).Value = Null
End If
End Sub








Create Folder Structure









I copied my previously explained 'Create Folder Structure' in this Excel Macros Example. For all details, see my previous blog post. An import of an existing system folder structure is now added in there as well.









ExcelMacroCreateFolderStructure









Run macro upon opening a workbook




To run a macro whenever the workbook Excel sheet is opened, the Sub Workbook_Open in 'ThisWorkbook' can be used. This is needed if you want to link some keyboard keys to specific macro's.







Link keys to macro




To link a keyboard key to a specific macro action, the Application.OnKey "..." can be used in the auto startup macro. The keys must be unlinked before closing the workbook. I used this functionality in my Exam Point Counter workbook, described in this previous blog post. In 'ThisWorkbook':







'startup macro
Private Sub Workbook_Open()
Application.OnKey "t", "tKeyPressed"
Application.OnKey "e", "eKeyPressed"
Application.OnKey "s", "sKeyPressed"
Application.OnKey "t", "tKeyPressed"
End Sub
Private Sub Workbook_BeforeClose()
Application.OnKey "t"
Application.OnKey "e"
Application.OnKey "s"
Application.OnKey "t"
End Sub



In any module, for example KeyboardAction:







Sub tKeyPressed()
Application.DisplayStatusBar = True
Application.StatusBar = "t key linked to special action in KeyboardAction module"
End Sub
Sub eKeyPressed()
Application.DisplayStatusBar = True
Application.StatusBar = "e key linked to special action in KeyboardAction module"
End Sub
Sub sKeyPressed()
Application.DisplayStatusBar = True
Application.StatusBar = "s key linked to special action in KeyboardAction module"
End Sub








Search Lookup









The search lookup functionality has also been described in details in this previous blog post. The macro is included in this Excel Macro Example as well.









The formula for E9 below looks like:









 ExcelMacroSearchLookupFomula









ExcelMacroSearchLookup









Open Save









A button with linked macro to navigate to a folder is shown in the module 'OpenSave'.








A basic example of the resource exporter I showed in a previous post, is included as well. It will export the example data set into a text file, and while doing so, all special characters will be converted (eg &#233). For rows in the list of AlternativeEncoding, an alternative encoding for special characters will be used (eg \u00E9). To make the sheet readable, the special characters can be converted back into readable special characters. The conversion is based on a hex2ascii converter, unicode encoding, html encoding and decoding.









When you would reuse this code, please note, in order to support FileSystemObject, you’ll need to add reference to Microsoft scripting runtime in your Excel workbook VBA. In order to do so, open the Visual Basic environment in Excel (ALT+F11) > Menu ‘Tools’ > ‘Reference’ > Enable ‘Microsoft Scripting Runtime’. If this isn’t done in the workbook, the error ‘User defined type not defined’ will appear when writing an export file on ‘Dim fso As New FileSystemObject’.









ExcelMacroOpenSave









When the special characters are encoded using the macro, they will look like this:









ExcelMacroOpenSaveEncoded









PhoneBook vCard export



A sheet in which all phonebook data can be added (can be exported from any other application) is now available. Once all contact information is set, it can be exported into a vCard format which is supported by many address and contact management applications. More details can be found in this specific blog post.



image



All code is available in the Excel Macro Example.

Friday, January 21, 2011

Salling Clicker - Windows Mobile

Salling Clicker is a remote control software. It lets you control popular applications from a mobile phone or handheld computer through a user interface similar to a portable media player.
You can choose to have the computer take action when you make or receive a phone call or get close to your pc. For instance, Salling Clicker can automatically mute the system volume while you're on the phone or automatically lock the pc while your gone.

  • Control PowerPoint, iTunes, Windows Media Player, and more with your mobile device.
  • Works with all major Bluetooth stacks (no configuration required).
  • Amazingly easy-to-use WiFi connectivity for long-range control.
  • Works with over 300 devices.
  • Easily extend support for other applications using JavaScript or VBScript

Scripts

Salling Clicker is installed with a set of default scripts (JavaScript for Windows) to support many different applications (PowerPoint, iTunes, MediaPlayer, etc.). On the forums of Salling Clicker many other custom scripts can be found, created and shared by the community.
I've created a set of personal scripts, some are base on the official scripts, some are based on the scripts found in the forums and some are completely new. I just list all the scripts I use:
  • De Slimste Mens: as explain in this separate blog post, I've created a complete interface to control the Flemish game "De Slimste Mens".
  • myT Mouse Keyboard: full control for mouse and keyboard. Specially made for Windows Mobile devices with hardware keyboard (tested on Samsung Omnia B7310 and HTC Wizard (Qtek9100)). Use the screen to move mouse, use 'Menu' button to get context menu (mouse right click), use 'Help' button for extra options. To get a good overview of all options, I’ve put some screenshot online.
    Use hardware keyboard to send all keys. The used keys will be displayed on the main screen for easy action tracking.
    The extra option are:
    • switching of Shift/Ctrl/Alt/Win keys
    • sending special characters
    • sending function keys (eg F1-F12, pause, insert, delete, etc)
    • sending media keys (play next song, webbrowser control, etc)
    • sending long text: text input on device
    • open a file on pc: navigate to folder and open a file, recent file history, easy access to favorite locations (eg start menu, 'my documents', etc)
    • Zoom screen using freeware ZoomIt application, all ZoomIt options supported (pen, break, etc)
    • Show a screenshot of the pc on the main screen.
    • Exchange data from pc clipboard to device and/or put text into the pc clipboard
  • PowerPoint: distribuated by Salling Clicker
  • MediaPlayer: distribuated by Salling Clicker
  • MediaPortal: from forum
  • MediaCenter: from forum
  • iTunes: distribuated by Salling Clicker
  • BSPlayer: from forum
  • VLC: from forum
  • WinDVD: from forum
  • MSN Online status: from forum
  • uTorrent: from forum
  • ShutdownControls: shutodown/hibernate/standby/screen on-off/lock
  • Device Info: distiribuated by Salling Clicker
  • Disconnect: distiribuated by Salling Clicker
  • System volume: control pc volume
  • MuteWhileOnPhone: distribuated by Salling Clicker
  • CallNotifier: distribuated by Salling Clicker
  • LockComputer: distribuated by Salling Clicker
  • MonitorAway: distribuated by Salling Clicker
  • Show Message: show message on pc

Wednesday, December 1, 2010

De Slimste Mens Ter Wereld

(Nederlandse versie: zie onder)

Jonathan Huyghe has made a nice little flash tool to let you play the popular Flemish TV show ‘De slimste mens ter wereld’ (The smartest person of the world) at home.
Because I found it quite complicated to prepare the game, I made a little Excel template that should make it more straightforward to make all preparations for it. It can easily generate the required ‘antwoorden.txt’ file with the correct syntax and it can print out cards with instructions to be used by the host during the game play.

Update 01/2016: An alternative version (independent of the version discussed below) is now available with full online management of the quiz. It works very well and very easy to create your own quiz's! See: http://deslimstemens.nu/

User Manual

  1. Download this package containing the original sources of Jonathan Huyghe + Excel template ‘DSM-Voorbereiding.xlt’
  2. Open the file ‘DSM-Voorbereiding’ with Excel and allow macros to run.EnableMacros
  3. Fill in all grey marked fields (names of players, questions, answers). The other fields are protected so no mistakes can be made by accident.
  4. Some questions require .jpg images or .flv flash movies. The names and resolution of the files are put next to the questions. These files should be saved in the same folder next to the DSM .swf flash file manually.
  5. Save the Excel sheet, it is advised to save it in the same folder as the DSM .swf file
  6. Click on the top button ‘Exporteer antwoorden.txt’ to generate (or overwrite) the ‘antwoorden.txt’ file (in the same folder as the Excel file) based on the data provided in the sheet. This file is required by the DSM .swf flash tool.
  7. Click on the top button ‘Print steekkaarten’ to get a print preview of the instruction cards that can be used by the game host. All instructions, questions and answers are clearly put together to print out and use during the play. The cards can be printed out 2 or 4 per page to make it easier to hold them during the game.


Examples


I've made two quizzes using the Excel file with questions, images and movies:


Extra



  • If some changes are required to customize the layout etc, the sheets can be unprotected (no password is used) using the Excel menus.
  • To convert movie files the freeware tools Format Factory and / or Riva FLV encoder can be used.
  • To convert / edit the picture files, the freeware tool Paint.NET can be used.
  • While exporting the 'antwoorden.txt' file, another file 'DSMData.txt' will be created (since v2.0). This 'DSMData.txt' file can be imported on a Windows Mobile pocket pc to get a very user friendly interface to control the flash quiz on a pc. For this, it will be required to install Salling Clicker on the PC and Windows Mobile device. Next this Salling Clicker 'De Slimste Mens' script needs to be added in Salling Clicker. A new item will be available in Salling Clicker on the Windows Mobile device. I've put some screenshots online.



(Dutch – Nederlands)

Jonathan Huyghe heeft een mooi flash programmaatje gemaakt om je thuis het populaire Vlaamse TV spel ‘De slimste mens ter wereld’ te laten spelen.
Omdat ik het nogal omslachtig vond om het spel op te zetten, heb ik een Excel template gemaakt om alle voorbereidingen te vergemakkelijken en wat duidelijker te maken. Deze Excel laat toe om het bestand ‘antwoorden.txt’ te genereren met de juiste syntax. Ook kunnen steekkaarten afgeprint worden met alle instructies, vragen en antwoorden die nodig zijn tijdens het spelen van het spel.

Update 01/2016: Een alternatieve versie voor het opzetten en spelen van eigen 'De Slimste Mens' (volledig losstaand van deze hierboven beschreven) is nu beschikbaar. Deze is volledig online beschikbaar. Deze kan volledig via de website http://deslimstemens.nu opgesteld en gespeeld worden. Dit werkt zeer vlot en hiervoor dienen dus ook de onderstaande instructies en Excel bewerkingen niet langer voor opgezet worden, volg gewoon de eenvoudige instructies op de website.

Handleiding:

  1. Download dit zip bestand met alle nodige bestanden van Jonathan Huyghe en de Excel template ‘DSM-Voorbereiding.xlt’.
  2. Open het bestand ‘DSM-Voorbereiding.xlt’ met Excel en sta het uitvoeren van macro’s toe.EnableMacros
  3. Vul al de grijze velden in (namen spelers, vragen, antwoorden). De andere velden zijn normaal beschermd zodat deze niet per ongeluk kunnen gewijzigd worden.
  4. Voor sommige vragen is het nodig om .jpg afbeeldingen en .flv flash filmpjes beschikbaar te maken. De namen en de resoluties die hiervoor moeten gebruikt worden, staan aangegeven naast de vragen. Deze bestanden moeten manueel in de zelfde map als het DSM .swf bestand geplaatst worden.
  5. Sla het Excel bestand op. Belangrijk hierbij: sla het bestand op als een Excel Macro-enabled bestand .xlsm, en niet als een standaard Excel .xlsx bestand, anders zullen de macro’s voor het exporteren verloren gaan. Het is aangeraden om het bestand in dezelfde map als het DSM .swf bestand op te slaan.
  6. Klik op de bovenste knop ‘Exporteer antwoorden.txt’ om het bestand ‘antwoorden.txt’ automatisch aan te maken (of te overschrijven). Dit bestand zal in dezelfde map als het Excel bestand geplaatst worden. Het bestand ‘antwoorden.txt’ is nodig voor de werking van het .swf programma DSM.
  7. Klik op de bovenste knop ‘Print steekkaarten’ om een print voorbeeld te krijgen van de steekkaarten. Deze steekkaarten kunnen tijdens het spel gebruikt worden en geven duidelijk de instructies, vragen en antwoorden aan. De steekkaarten kunnen per 2 of per 4 per blad afgedrukt worden om ze gemakkelijker vast te houden tijdens het spel. Eventueel kan hiervoor de gratis CutePDF printer gebruikt worden om naar PDF te printen met 2 of 4 per blad.
  8. Open het bestand ‘DSM3x4.swf’ met Internet Explorer om het spel te starten, volg vervolgens de instructies op de steekkaarten.

Voorbeelden


Zelf heb ik 2 quizzen gemaakt, met de Excel met vragen, afbeeldingen en filmpjes:

Extra




  • Als er wijzigen nodig zijn in de layout enz. kan het nodig zijn om de bescherming van de Excel tabbladen te verwijderen. Dit kan eenvoudig via de Excel menu’s (er is geen wachtwoord gebruikt in de beveiliging).
  • Voor het omzetten van de filmpjes kunnen de gratis applicaties Format Factory en / of Riva FLV encoder gebruikt worden.
  • Voor het omzetten / bewerken van afbeeldingen, kan de gratis applicatie Paint.NET gebruikt worden.
  • Bij het exporteren van het bestand 'antwoorden.txt', zal nu ook een bijkomend bestand 'DSMData.txt' aangemaakt worden. Dit bestand kan geïmporteerd worden op een Windows Mobile toestel om zo een zeer eenvoudige bediening van het volledige spel toe te laten. Hiervoor moet op de computer en de Windows Mobile PDA wel Salling Clicker geïnstalleerd worden en vervolgens moet dit Salling Clicker 'De Slimste Mens' script toegevoegd worden. Een nieuw item zal beschikbaar zijn in Salling Clicker op het Windows Mobile toestel. Enkele schermafbeeldingen.


Update 2/12/2010 v1.4: Extra validation added for field lengths. Status bar message for export added. Added import functionality.

Update 6/12/2010 v1.5: Added example quiz questions.

Update 17/01/2011 v2.0: Added export for Salling Clicker

Tuesday, November 2, 2010

Excel lookups in formulas

Different solutions exist to work with looked up data in Excel formulas: LOOKUP, VLOOKUP, HLOOKUP.

  • LOOKUP(value, lookup_range, result_range): searches for value in the lookup_range and returns the value in the result_range that is in the same position
  • VLOOKUP(value, table_array, index_number, not_exact_match ): searches for value in the left-most column of table_array and returns the value in the same row based on the index_number.
  • HLOOKUP(value, table_array, index_number, not_exact_match ): searches for value in the top row of table_array and returns the value in the same column based on the index_number.

But I needed yet some other lookup functionality, something like SEARCHHLOOKUP(search_in_text, lookup_range, result_range) where values from the lookup_range are searched in the text of search_in_text and if found the value of the result_range with the same column as were it was found is returned.

I wanted to be able to define some categories, with keywords linked. If a keyword occurs in a sentence, I wanted the category name as result. and I wanted to easily add new keywords for each category, without changing the formula. For example sheet CATEGORIES:

A B
Fruit Food
1 2
1 apple chocolate
2 banana milk






Next I have a cell with value: "Apple belongs to category" In another cell I want a formula (no VBS) that would result in the category name: "Fruit". For example sheet EXAMPLE:

A B
1 Apple belongs to category Fruit
2 Chocolate belongs to category Food




(Example SearchLookup.xlsm)

Since I need to search for the keyword in a sentence, I use the SEARCH(search_text, search_in_text, start_position ) function. If the result is bigger than 0, the keyword was found (not case sensitive). But if the keyword was not found, an error value #VALUE will be returned. To catch this error value, the function IFERROR(value, value_if_error) can be used. So I get this function: {=IFERROR(IF(SEARCH(CATEGORIES!$A$2:$A$5;EXAMPLE!$A1)>0;CATEGORIES!A$1);"")}

But this will only lookup keywords in my first column of Categories, while I want many more. I solved this by attaching a weight to each category and using the formula CHOOSE(position, value1, value2, ... value_n ) to select the category name corresponding it's weight. To ignore the error values when the keyword is not found, I return 0 if an error occurs and the category weight when the keyword was found, so a MAX on that array will result in the matching category weight. To make sure that 0 is returned, only when the keyword is not found, I add any single character (µ in this case) in front of the text to search in, else 0 could be returned if the text to search in starts with the text we are searching. Now the search has to be bigger than 1 when the keyword is found.

The result is this formula: {=CHOOSE(MAX(IFERROR(IF(SEARCH(Categories!$A$3:$A$6;"µ"&Example!$A1)>1;Categories!$A$2;0);0);IFERROR(IF(SEARCH(Categories!$B$3:$B$6;"µ"&Example!$A1)>1;Categories!$B$2;0);0))+1;"";Categories!$A$1;Categories!$B$1)}

Since we apply the SEARCH function on an array, the complete formula has to be an array formula, so don't forget to press CTRL+SHIFT+ENTER to save as an array function and get the "{=...}" signs around the formula.

So using a combination of CHOOSE, MAX, IFERROR, IF and SEARCH functions I can lookup category names base on keywords and the keywords can be added dynamically. The only "problem" left is that I need to change my formulas when a new category is added, but at least not when adding keywords in a category.

To solve this last problem, I ended up creating a 'User defined function' SEARCHHLOOKUP(search_in_text, lookup_range, result_range, (result_range_index)):

Function SearchHLookup(Search_in_text As Variant, Lookup_range As Range, Optional Result_range As Range, Optional Result_range_index As Integer)
'''''''''''''''''''''''''''''''''''''''
'Written by myT - http://myTselection.blogspot.com
'Values from the lookup_range are searched in the text of search_in_text
'If a match is found, the value of Result_range in the same column and top row (or result_range_index) is returned
'Example:
'A B
'1 2
'3 4
'if 2 or 4 is found in Search_in_text, B will be returned
'if 1 or 3 is found in Search_in_text, A will be returned
'if none is found, empty string will be returned
'''''''''''''''''''''''''''''''''''''''
Dim iRow, startRow As Integer
Dim iColumn, startColumn As Integer
If Result_range Is Nothing Then
startRow = 2
Else
startRow = 1
End If
startColumn = 1
For iColumn = startColumn To Lookup_range.Columns.Count
For iRow = startRow To Lookup_range.Rows.Count
If Not (Lookup_range(iRow, iColumn) = "") Then
If (InStr(1, Search_in_text, Lookup_range(iRow, iColumn), 1) > 0) Then
If Result_range Is Nothing Then
SearchHLookup = Lookup_range(1, iColumn)
ElseIf Not (Result_range_index = 0) Then
SearchHLookup = Result_range(Result_range_index, iColumn)
Else
SearchHLookup = Result_range(1, iColumn)
End If

Exit Function
End If
End If
Next iRow
Next iColumn
SearchHLookup = ""
End Function


So in my example, the function I use now has been simplified to:



=SearchHLookup(A1;Categories!$A$3:$B$4;Categories!$A$1:$B$1;1)



Of course, a SearchVLookup could be made easily as well:



Function SearchVLookup(Search_in_text As Variant, Lookup_range As Range, Optional Result_range As Range, Optional Result_range_index As Integer)
'''''''''''''''''''''''''''''''''''''''
'Written by myT - http://myTselection.blogspot.com
'Values from the lookup_range are searched in the text of search_in_text
'If a match is found, the value of Result_range in the same column and top row (or result_range_index) is returned
'Example:
'A 1 2
'B 3 4
'if 1 or 2 is found in Search_in_text, A will be returned
'if 3 or 4 is found in Search_in_text, B will be returned
'if none is found, empty string will be returned
'''''''''''''''''''''''''''''''''''''''
Dim iRow, startRow As Integer
Dim iColumn, startColumn As Integer
If Result_range Is Nothing Then
startColumn = 2
Else
startColumn = 1
End If
startRow = 1
For iRow = startRow To Lookup_range.Rows.Count
For iColumn = startColumn To Lookup_range.Columns.Count
If Not (Lookup_range(iRow, iColumn) = "") Then
If (InStr(1, Search_in_text, Lookup_range(iRow, iColumn), 1) > 0) Then
If Result_range Is Nothing Then
SearchVLookup = Lookup_range(iRow, 1)
ElseIf Not (Result_range_index = 0) Then
SearchVLookup = Result_range(iRow, Result_range_index)
Else
SearchVLookup = Result_range(iRow, 1)
End If

Exit Function
End If
End If
Next iColumn
Next iRow
SearchVLookup = ""
End Function


The user defined function needs to be defined in a module. Example: SearchLookup.xlsm)

Sunday, April 26, 2009

Find-Replace text in files from command line

In Linux one can easily find and replace some text in files by using the sed application. In Windows, I couldn’t find a decent build in equivalent. I ended by using the ported version of sed for Windows. I works quite well using the same syntax and doesn’t require to be installed. Just make sure sed.exe, libiconv2.dll and libintl3.dll are existing in the same folder or on the classpath.

One should know any file altered by sed for Windows will result in this file being also converted from Windows line ending (using 2 characters: Carriage Return + Line Feed) to Linux line endings (only 1 character: Line Feed). This can sometime give some strange results. To overcome this, one should make sure the last replacement in the file is set to replace the Line Feeds back to Carriage Return + Line Feed:

sed -i "s/$/\r/" "%DIR%\file.txt"


Some other useful sed commands are available from sed sourcefourge:



FILE SPACING:
 # double space a file
 sed G
 # double space a file which already has blank lines in it. Output file
 # should contain no more than one blank line between lines of text.
 sed '/^$/d;G'
 # triple space a file
 sed 'G;G'
 # undo double-spacing (assumes even-numbered lines are always blank)
 sed 'n;d'
 # insert a blank line above every line which matches "regex"
 sed '/regex/{x;p;x;}'
 # insert a blank line below every line which matches "regex"
 sed '/regex/G'
 # insert a blank line above and below every line which matches "regex"
 sed '/regex/{x;p;x;G;}'
NUMBERING:
 # number each line of a file (simple left alignment). Using a tab (see
 # note on '\t' at end of file) instead of space will preserve margins.
 sed = filename | sed 'N;s/\n/\t/'
 # number each line of a file (number on left, right-aligned)
 sed = filename | sed 'N; s/^/     /; s/ *\(.\{6,\}\)\n/\1  /'
 # number each line of file, but only print numbers if line is not blank
 sed '/./=' filename | sed '/./N; s/\n/ /'
 # count lines (emulates "wc -l")
 sed -n '$='
TEXT CONVERSION AND SUBSTITUTION:
 # IN UNIX ENVIRONMENT: convert DOS newlines (CR/LF) to Unix format.
 sed 's/.$//'               # assumes that all lines end with CR/LF
 sed 's/^M$//'              # in bash/tcsh, press Ctrl-V then Ctrl-M
 sed 's/\x0D$//'            # works on ssed, gsed 3.02.80 or higher
 # IN UNIX ENVIRONMENT: convert Unix newlines (LF) to DOS format.
 sed "s/$/`echo -e \\\r`/"            # command line under ksh
 sed 's/$'"/`echo \\\r`/"             # command line under bash
 sed "s/$/`echo \\\r`/"               # command line under zsh
 sed 's/$/\r/'                        # gsed 3.02.80 or higher
 # IN DOS ENVIRONMENT: convert Unix newlines (LF) to DOS format.
 sed "s/$//"                          # method 1
 sed -n p                             # method 2
 # IN DOS ENVIRONMENT: convert DOS newlines (CR/LF) to Unix format.
 # Can only be done with UnxUtils sed, version 4.0.7 or higher. The
 # UnxUtils version can be identified by the custom "--text" switch
 # which appears when you use the "--help" switch. Otherwise, changing
 # DOS newlines to Unix newlines cannot be done with sed in a DOS
 # environment. Use "tr" instead.
 sed "s/\r//" infile >outfile         # UnxUtils sed v4.0.7 or higher
 tr -d \r <infile >outfile            # GNU tr version 1.22 or higher
 # delete leading whitespace (spaces, tabs) from front of each line
 # aligns all text flush left
 sed 's/^[ \t]*//'                    # see note on '\t' at end of file
 # delete trailing whitespace (spaces, tabs) from end of each line
 sed 's/[ \t]*$//'                    # see note on '\t' at end of file
 # delete BOTH leading and trailing whitespace from each line
 sed 's/^[ \t]*//;s/[ \t]*$//'
 # insert 5 blank spaces at beginning of each line (make page offset)
 sed 's/^/     /'
 # align all text flush right on a 79-column width
 sed -e :a -e 's/^.\{1,78\}$/ &/;ta'  # set at 78 plus 1 space
 # center all text in the middle of 79-column width. In method 1,
 # spaces at the beginning of the line are significant, and trailing
 # spaces are appended at the end of the line. In method 2, spaces at
 # the beginning of the line are discarded in centering the line, and
 # no trailing spaces appear at the end of lines.
 sed  -e :a -e 's/^.\{1,77\}$/ & /;ta'                     # method 1
 sed  -e :a -e 's/^.\{1,77\}$/ &/;ta' -e 's/\( *\)\1/\1/'  # method 2
 # substitute (find and replace) "foo" with "bar" on each line
 sed 's/foo/bar/'             # replaces only 1st instance in a line
 sed 's/foo/bar/4'            # replaces only 4th instance in a line
 sed 's/foo/bar/g'            # replaces ALL instances in a line
 sed 's/\(.*\)foo\(.*foo\)/\1bar\2/' # replace the next-to-last case
 sed 's/\(.*\)foo/\1bar/'            # replace only the last case
 # substitute "foo" with "bar" ONLY for lines which contain "baz"
 sed '/baz/s/foo/bar/g'
 # substitute "foo" with "bar" EXCEPT for lines which contain "baz"
 sed '/baz/!s/foo/bar/g'
 # change "scarlet" or "ruby" or "puce" to "red"
 sed 's/scarlet/red/g;s/ruby/red/g;s/puce/red/g'   # most seds
 gsed 's/scarlet\|ruby\|puce/red/g'                # GNU sed only
 # reverse order of lines (emulates "tac")
 # bug/feature in HHsed v1.5 causes blank lines to be deleted
 sed '1!G;h;$!d'               # method 1
 sed -n '1!G;h;$p'             # method 2
 # reverse each character on the line (emulates "rev")
 sed '/\n/!G;s/\(.\)\(.*\n\)/&\2\1/;//D;s/.//'
 # join pairs of lines side-by-side (like "paste")
 sed '$!N;s/\n/ /'
 # if a line ends with a backslash, append the next line to it
 sed -e :a -e '/\\$/N; s/\\\n//; ta'
 # if a line begins with an equal sign, append it to the previous line
 # and replace the "=" with a single space
 sed -e :a -e '$!N;s/\n=/ /;ta' -e 'P;D'
 # add commas to numeric strings, changing "1234567" to "1,234,567"
 gsed ':a;s/\B[0-9]\{3\}\>/,&/;ta'                     # GNU sed
 sed -e :a -e 's/\(.*[0-9]\)\([0-9]\{3\}\)/\1,\2/;ta'  # other seds
 # add commas to numbers with decimal points and minus signs (GNU sed)
 gsed -r ':a;s/(^|[^0-9.])([0-9]+)([0-9]{3})/\1\2,\3/g;ta'
 # add a blank line every 5 lines (after lines 5, 10, 15, 20, etc.)
 gsed '0~5G'                  # GNU sed only
 sed 'n;n;n;n;G;'             # other seds
SELECTIVE PRINTING OF CERTAIN LINES:
 # print first 10 lines of file (emulates behavior of "head")
 sed 10q
 # print first line of file (emulates "head -1")
 sed q
 # print the last 10 lines of a file (emulates "tail")
 sed -e :a -e '$q;N;11,$D;ba'
 # print the last 2 lines of a file (emulates "tail -2")
 sed '$!N;$!D'
 # print the last line of a file (emulates "tail -1")
 sed '$!d'                    # method 1
 sed -n '$p'                  # method 2
 # print the next-to-the-last line of a file
 sed -e '$!{h;d;}' -e x              # for 1-line files, print blank line
 sed -e '1{$q;}' -e '$!{h;d;}' -e x  # for 1-line files, print the line
 sed -e '1{$d;}' -e '$!{h;d;}' -e x  # for 1-line files, print nothing
 # print only lines which match regular expression (emulates "grep")
 sed -n '/regexp/p'           # method 1
 sed '/regexp/!d'             # method 2
 # print only lines which do NOT match regexp (emulates "grep -v")
 sed -n '/regexp/!p'          # method 1, corresponds to above
 sed '/regexp/d'              # method 2, simpler syntax
 # print the line immediately before a regexp, but not the line
 # containing the regexp
 sed -n '/regexp/{g;1!p;};h'
 # print the line immediately after a regexp, but not the line
 # containing the regexp
 sed -n '/regexp/{n;p;}'
 # print 1 line of context before and after regexp, with line number
 # indicating where the regexp occurred (similar to "grep -A1 -B1")
 sed -n -e '/regexp/{=;x;1!p;g;$!N;p;D;}' -e h
 # grep for AAA and BBB and CCC (in any order)
 sed '/AAA/!d; /BBB/!d; /CCC/!d'
 # grep for AAA and BBB and CCC (in that order)
 sed '/AAA.*BBB.*CCC/!d'
 # grep for AAA or BBB or CCC (emulates "egrep")
 sed -e '/AAA/b' -e '/BBB/b' -e '/CCC/b' -e d    # most seds
 gsed '/AAA\|BBB\|CCC/!d'                        # GNU sed only
 # print paragraph if it contains AAA (blank lines separate paragraphs)
 # HHsed v1.5 must insert a 'G;' after 'x;' in the next 3 scripts below
 sed -e '/./{H;$!d;}' -e 'x;/AAA/!d;'
 # print paragraph if it contains AAA and BBB and CCC (in any order)
 sed -e '/./{H;$!d;}' -e 'x;/AAA/!d;/BBB/!d;/CCC/!d'
 # print paragraph if it contains AAA or BBB or CCC
 sed -e '/./{H;$!d;}' -e 'x;/AAA/b' -e '/BBB/b' -e '/CCC/b' -e d
 gsed '/./{H;$!d;};x;/AAA\|BBB\|CCC/b;d'         # GNU sed only
 # print only lines of 65 characters or longer
 sed -n '/^.\{65\}/p'
 # print only lines of less than 65 characters
 sed -n '/^.\{65\}/!p'        # method 1, corresponds to above
 sed '/^.\{65\}/d'            # method 2, simpler syntax
 # print section of file from regular expression to end of file
 sed -n '/regexp/,$p'
 # print section of file based on line numbers (lines 8-12, inclusive)
 sed -n '8,12p'               # method 1
 sed '8,12!d'                 # method 2
 # print line number 52
 sed -n '52p'                 # method 1
 sed '52!d'                   # method 2
 sed '52q;d'                  # method 3, efficient on large files
 # beginning at line 3, print every 7th line
 gsed -n '3~7p'               # GNU sed only
 sed -n '3,${p;n;n;n;n;n;n;}' # other seds
 # print section of file between two regular expressions (inclusive)
 sed -n '/Iowa/,/Montana/p'             # case sensitive
SELECTIVE DELETION OF CERTAIN LINES:
 # print all of file EXCEPT section between 2 regular expressions
 sed '/Iowa/,/Montana/d'
 # delete duplicate, consecutive lines from a file (emulates "uniq").
 # First line in a set of duplicate lines is kept, rest are deleted.
 sed '$!N; /^\(.*\)\n\1$/!P; D'
 # delete duplicate, nonconsecutive lines from a file. Beware not to
 # overflow the buffer size of the hold space, or else use GNU sed.
 sed -n 'G; s/\n/&&/; /^\([ -~]*\n\).*\n\1/d; s/\n//; h; P'
 # delete all lines except duplicate lines (emulates "uniq -d").
 sed '$!N; s/^\(.*\)\n\1$/\1/; t; D'
 # delete the first 10 lines of a file
 sed '1,10d'
 # delete the last line of a file
 sed '$d'
 # delete the last 2 lines of a file
 sed 'N;$!P;$!D;$d'
 # delete the last 10 lines of a file
 sed -e :a -e '$d;N;2,10ba' -e 'P;D'   # method 1
 sed -n -e :a -e '1,10!{P;N;D;};N;ba'  # method 2
 # delete every 8th line
 gsed '0~8d'                           # GNU sed only
 sed 'n;n;n;n;n;n;n;d;'                # other seds
 # delete lines matching pattern
 sed '/pattern/d'
 # delete ALL blank lines from a file (same as "grep '.' ")
 sed '/^$/d'                           # method 1
 sed '/./!d'                           # method 2
 # delete all CONSECUTIVE blank lines from file except the first; also
 # deletes all blank lines from top and end of file (emulates "cat -s")
 sed '/./,/^$/!d'          # method 1, allows 0 blanks at top, 1 at EOF
 sed '/^$/N;/\n$/D'        # method 2, allows 1 blank at top, 0 at EOF
 # delete all CONSECUTIVE blank lines from file except the first 2:
 sed '/^$/N;/\n$/N;//D'
 # delete all leading blank lines at top of file
 sed '/./,$!d'
 # delete all trailing blank lines at end of file
 sed -e :a -e '/^\n*$/{$d;N;ba' -e '}'  # works on all seds
 sed -e :a -e '/^\n*$/N;/\n$/ba'        # ditto, except for gsed 3.02.*
 # delete the last line of each paragraph
 sed -n '/^$/{p;h;};/./{x;/./p;}'
SPECIAL APPLICATIONS:
 # remove nroff overstrikes (char, backspace) from man pages. The 'echo'
 # command may need an -e switch if you use Unix System V or bash shell.
 sed "s/.`echo \\\b`//g"    # double quotes required for Unix environment
 sed 's/.^H//g'             # in bash/tcsh, press Ctrl-V and then Ctrl-H
 sed 's/.\x08//g'           # hex expression for sed 1.5, GNU sed, ssed
 # get Usenet/e-mail message header
 sed '/^$/q'                # deletes everything after first blank line
 # get Usenet/e-mail message body
 sed '1,/^$/d'              # deletes everything up to first blank line
 # get Subject header, but remove initial "Subject: " portion
 sed '/^Subject: */!d; s///;q'
 # get return address header
 sed '/^Reply-To:/q; /^From:/h; /./d;g;q'
 # parse out the address proper. Pulls out the e-mail address by itself
 # from the 1-line return address header (see preceding script)
 sed 's/ *(.*)//; s/>.*//; s/.*[:<] *//'
 # add a leading angle bracket and space to each line (quote a message)
 sed 's/^/> /'
 # delete leading angle bracket & space from each line (unquote a message)
 sed 's/^> //'
 # remove most HTML tags (accommodates multiple-line tags)
 sed -e :a -e 's/<[^>]*>//g;/</N;//ba'
 # extract multi-part uuencoded binaries, removing extraneous header
 # info, so that only the uuencoded portion remains. Files passed to
 # sed must be passed in the proper order. Version 1 can be entered
 # from the command line; version 2 can be made into an executable
 # Unix shell script. (Modified from a script by Rahul Dhesi.)
 sed '/^end/,/^begin/d' file1 file2 ... fileX | uudecode   # vers. 1
 sed '/^end/,/^begin/d' "$@" | uudecode                    # vers. 2
 # sort paragraphs of file alphabetically. Paragraphs are separated by blank
 # lines. GNU sed uses \v for vertical tab, or any unique char will do.
 sed '/./{H;d;};x;s/\n/={NL}=/g' file | sort | sed '1s/={NL}=//;s/={NL}=/\n/g'
 gsed '/./{H;d};x;y/\n/\v/' file | sort | sed '1s/\v//;y/\v/\n/'
 # zip up each .TXT file individually, deleting the source file and
 # setting the name of each .ZIP file to the basename of the .TXT file
 # (under DOS: the "dir /b" switch returns bare filenames in all caps).
 echo @echo off >zipup.bat
 dir /b *.txt | sed "s/^\(.*\)\.TXT/pkzip -mo \1 \1.TXT/" >>zipup.bat