Ravi Shankar

Tweaking Apps

Disable highlighting of links when pressing tab in Google Chrome

By Leave a Comment

Google Chrome has a feature which when pressing tab highlights the links and form fields. If you are user who heavily uses keyboard tab for navigating between the form fields and you do not want the tab to be highlighted then you can disable this feature using Chrome Preferences.

Click the Chrome menu on the menu bar and select Preferences from the list of available menus.

201109180806.jpg

In the Preferences window, click the Under the hood link.

201109180806.jpg

Navigate to Web Content section available in Under the Hood page.

201109180808.jpg

Now un tick the check box with label as Pressing tab on a webpage highlights links, as well as form fields. This would only highlight the form fields when pressing tabs.

How to limit results returned by Query function in Google Spreadsheet

By Leave a Comment

We had already seen brief introduction about using Query function in Google Spreadsheet. Let us see how to limit the number of rows returned using Query Function with the following sample data of batsman with maximum runs in test cricket.

Now if you want to get the top 3 batsman with maximum number of runs scored, then you can use

=QUERY(C3:G13,”select C,G limit 3″)

The limit keyword will reduce the number of results returned to 3.

But say if you want to get the list of top 3 batsman with maximum averages then need to use the Order by clause.

=QUERY(C3:G13,”select C,F order by F desc limit 3″)

The above query would first sort the list using Order by clause and then uses the limit keyword to get the Top results.

Auto Replace feature in Google Docs

By Leave a Comment

This tutorial talks about the Auto Replace feature available in Google Docs. Using this feature, Google Docs users can add abbreviation and their corresponding words. For example, if you want to the entry da to be replaced with digianswers then you can do the following

Click the Tools menu and select Preferences from the displayed menu list.

In the Preferences Window, make sure to mark the check box with label as Automatic substitution.

And type “da” in Replace text box and the corresponding entry “digitalanswers” in With text box. Click OK button which is available at the bottom of the Preferences window to save the changes. Now when you type “da” in the document, it would be replaced with “digitalanswers”

How to validate an email addresses in Google Spreadsheet

By Leave a Comment

In Google Spreadsheet you can check whether the value entered is a proper email address by creating a data validation rule. This rule does a simple validation and checks for the @ symbol in the entered value. Let us say you have a column in your spreadsheet that needs to accept only email addresses, then you can do the following to add the validation.

Select the Column values, click the Data menu and select Validation from the menu list.

In the Data Validation window, select Text, Contains for the Criteria drop down and specify the value as @

And if you want to display a message to the user then type the message in Help text some thing like “Enter proper email address example [email protected]”. Click on the Save button to save the changes.

Now when you enter a value in the email address column without the @ symbol then it would display the message as shown below.

Always Keep headings visible in Google Spreadsheet

By 1 Comment

When you are working with lot of data in a Spreadsheet, the headings would disappear when you start scrolling down the rows. Google Docs Spreadsheet provides users with the option for keeping the headings always visible even when you scroll down the Sheet. This can be done by using the menu option available as part of the View menu. To explain, let us take the take following sample data

Now when the users scroll down below Row no 26, the heading rows would disappear as shown below.

And it will be difficult for the users to find out the column under which the values fall. This problem can be addressed using the Freeze rows feature. Navigate to View menu then Freeze rows and select Freeze 1 row menu option.

This would always Keep the heading rows always visible in a Google Docs Spreadsheet.

How to enable autocomplete in Google Docs Spreadsheet

By 5 Comments

Autocomplete feature helps users by automatically filling the content of cell when users starting entering the first couple of letters in the Cell. The automatic entry is picked from the previously entered values for the same column.

Let us say you have got the following list of entries in one of the column

Arizona
Alabama
California
Florida
Indiana

Now when you start typing Ar in the cell below Indiana, Spreadsheet will automatically display Arizona based on the entries done earlier for that column. And when you press enter this value will be automatically filled for that cell.

You can enable Autocomplete feature in Google Spreadsheet by clicking the Tools menu and selecting Enable AutoComplete from list of available menus.

Remember that if there is a empty cell before the active cell then Autocomplete feature will try to pick from the below column values.

Change alternate row background colors in Spreadsheet using Google Apps Script

By 8 Comments

Here is a Google Apps Script that would set the background color of alternate rows in Google Spreadsheet. This script would set the background color of of the selection, this may not be an efficient script but would do the job.

Let us take the following sample data which will be used by the script for setting the background color.

Install the below script and select the data by excluding the Header columns and execute the script.

function alternateColor() {
  var totalRows = SpreadsheetApp.getActiveRange().getNumRows();
  var totalColumns = SpreadsheetApp.getActiveRange().getNumColumns();
  var startRow = SpreadsheetApp.getActiveRange().getRow();
  var startColumn = SpreadsheetApp.getActiveRange().getColumn();
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = startRow;
  while (row < totalRows+startRow)
  {
    var column = startColumn
    while (column < totalColumns+startColumn){
      if(row%2 == 0){
           sheet.getRange(row, column).setBackgroundColor(“lightgrey”);
      }
      column++;
  }
    row++;
  }
}

After executing the script, the sample data would look as shown below.