Tuesday, November 16, 2010

Performing a Find in Google Spreadsheet

You can write scripts for the various modules in Google Apps using JavaScript (just as you do scripts in Microsoft Office using VBScript).    I have not been able to perform a predefined method to perform a find.  This is a code that is provided by Henrique Abreu.  The discussion is found here.

The code is as follow:

function find(sheet,searchKey) {
  var data = sheet.getDataRange().getValues();
  for( var i in data )
    for( var j in data[i] )
      if( searchKey == data[i][j] )
        return sheet.getRange(parseInt(i)+1,parseInt(j)+1);
  return null;

To use the code, you can just call it as follow:

function usageExample() {
  //replace 1st occurence of "foo" with "bar"
  var range = find(SpreadsheetApp.getActiveSheet(),"foo");
  if( range != null )