GSaaDB (Google Sheets as a Database)

Joins.gs

/*************
 *
 * The following code snippet was written by https://github.com/bestknighter
 * It's freely available at https://gist.github.com/bestknighter/ec075ee402176b886a3c51913ab8c08c.
 * You can copy, change and even make money with it
 * as long as credit is properly given.
 * 
 * You can use these functions as inside the sheet itself,
 * just like other functions such as with ADD() or COUNT().
 * 
 * Examples of use within any cell:
 * =innerJoin(A:C; E:H; 0; 1; TRUE)
 * =crossJoin(NAMED_TABLE01; NAMED_TABLE02; FALSE)
 * =rightJoin(A1:D7; E2:I15; [0, 2]; [0, 1])
 *
 * QUERY() may be your friend when sanitizing the output.
 * Great for removing unwanted columns and renaming the remaining ones
 * from the output when finished with all calculations. And much more.
 *
**************/

/**
 * Computes the cross join of two arrays.
 * BEWARE! The resulting table will contain arr1.rows * arr2.rows rows!
 * For example, a cross join of two 10-row tables will result in a 100-row table,
 * this function gets exponentially slow!
 *
 * @param {Any[][]} arr1
 *        Left table
 * @param {Any[][]} arr2
 *        Right table
 * @param {boolean=false} hasHeader
 *        Boolean to represent if tables have headers or not
 *
 * @return {Any[][]} A cartesian join/cross join of table 1 with table 2.
 * @customfunction
**/
function crossJoin( arr1, arr2, hasHeader = false ) {
  /* DATA VALIDATION */
  if( null == arr1 ) throw 'Missing left table: Argument 1 is required.';
  if( null == arr2 ) throw 'Missing right table: Argument 2 is required.';
  
  /* DATA PREPARATION */
  arr1 = tableTrimmer( arr1 );
  arr2 = tableTrimmer( arr2 );
  
  /* COMPUTATION */
  var output = [];
  
  // Dealing with headers
  if( hasHeader ) {
    var headers = arr1[0].concat(arr2[0]);
    output.push(headers);
  }
  
  // Cross join itself
  for( let i = hasHeader?1:0; i < arr1.length; i++ ) {
    for( let j = hasHeader?1:0; j < arr2.length; j++ ) {
      let r = arr1[i].concat(arr2[j]);
      output.push(r);
    }
  }
  
  return output;
}

/**
 * Returns an inner join of table 1 with table 2.
 *
 * @param {Any[][]} arr1
 *        Left table
 * @param {Any[][]} arr2
 *        Right table
 * @param {(number|number[])} columnToMatch1
 *        Column(s) from table 1 used for matching, "joining on"
 * @param {(number|number[])} columnToMatch2
 *        Column(s) from table 2 used for matching, "joining on"
 * @param {boolean=false} hasHeader
 *        Boolean to represent if tables have headers or not
 *
 * @return {Any[][]} An inner join of table 1 with table 2.
 * @customfunction
**/
function innerJoin( arr1, arr2, columnToMatch1, columnToMatch2, hasHeader = false ) {
  /* DATA VALIDATION */
  if( null == arr1 ) throw 'Missing left table: Argument 1 is required.';
  if( null == arr2 ) throw 'Missing right table: Argument 2 is required.';
  if( null == columnToMatch1 ) throw 'Missing left table column index: Argument 3 is required.';
  if( null == columnToMatch2 ) throw 'Missing right table column index: Argument 4 is required.';
  if( Array.isArray(columnToMatch1) != Array.isArray(columnToMatch2) ) throw 'Type mismatch: Argument 3 and 4 must have same type. They both need to be an integer or an array of integers';
  if( Array.isArray(columnToMatch1) && columnToMatch1.length != columnToMatch2.length ) throw 'Size mismatch: Argument 3 and 4 must have the same length';
  
  /* DATA PREPARATION */
  arr1 = tableTrimmer( arr1 );
  arr2 = tableTrimmer( arr2 );
  if( !Array.isArray(columnToMatch1) ) columnToMatch1 = Array.of(columnToMatch1);
  if( !Array.isArray(columnToMatch2) ) columnToMatch2 = Array.of(columnToMatch2);
  
  /* COMPUTATION */
  var output = [];
  
  // Dealing with headers
  if( hasHeader ) {
    var headers = arr1[0].concat(arr2[0]);
    output.push(headers);
  }
  
  // Inner join itself
  for( let i = hasHeader?1:0; i < arr1.length; i++ ) {
    for( let j = hasHeader?1:0; j < arr2.length; j++ ) {
      // First, let's check if these two rows matches according to the desired column(s)
      let isMatch = true;
      for( let k = 0; k < columnToMatch1.length && isMatch; k++ ) {
         isMatch = isMatch && ( arr1[i][columnToMatch1[k]] === arr2[j][columnToMatch2[k]] )
      }
      // If they match, add them to the output and go for the next pair
      if( isMatch ) {
        let r = arr1[i].concat(arr2[j]);
        output.push(r);
      }
    }
  }
  
  return output;
}

/**
 * Returns an right join of table 1 (left) with table 2 (right).
 *
 * @param {Any[][]} arr1
 *        Left table
 * @param {Any[][]} arr2
 *        Right table
 * @param {(number|number[])} columnToMatch1
 *        Column(s) from table 1 used for matching, "joining on"
 * @param {(number|number[])} columnToMatch2
 *        Column(s) from table 2 used for matching, "joining on"
 * @param {boolean=false} hasHeader
 *        Boolean to represent if tables have headers or not
 *
 * @return {Any[][]} A left join of table 1 with table 2.
 * @customfunction
**/
function leftJoin( arr1, arr2, columnToMatch1, columnToMatch2, hasHeader = false ) {
  /* DATA VALIDATION */
  if( null == arr1 ) throw 'Missing left table: Argument 1 is required.';
  if( null == arr2 ) throw 'Missing right table: Argument 2 is required.';
  if( null == columnToMatch1 ) throw 'Missing left table column index: Argument 3 is required.';
  if( null == columnToMatch2 ) throw 'Missing right table column index: Argument 4 is required.';
  if( Array.isArray(columnToMatch1) != Array.isArray(columnToMatch2) ) throw 'Type mismatch: Argument 3 and 4 must have same type. They both need to be an integer or an array of integers';
  if( Array.isArray(columnToMatch1) && columnToMatch1.length != columnToMatch2.length ) throw 'Size mismatch: Argument 3 and 4 must have the same length';
  
  /* DATA PREPARATION */
  arr1 = tableTrimmer( arr1 );
  arr2 = tableTrimmer( arr2 );
  if( !Array.isArray(columnToMatch1) ) columnToMatch1 = Array.of(columnToMatch1);
  if( !Array.isArray(columnToMatch2) ) columnToMatch2 = Array.of(columnToMatch2);
  
  /* COMPUTATION */
  var output = [];
  
  // Dealing with headers
  if( hasHeader ) {
    var headers = arr1[0].concat(arr2[0]);
    output.push(headers);
  }
  
  // Left join itself
  var arr2RowLength = arr2[0].length;
  for( let i = hasHeader?1:0; i < arr1.length; i++ ) {
    let r = null;
    for( let j = hasHeader?1:0; j < arr2.length; j++ ) {
      // First, let's check if these two rows matches according to the desired column(s)
      let isMatch = true;
      for( let k = 0; k < columnToMatch1.length && isMatch; k++ ) {
         isMatch = isMatch && ( arr1[i][columnToMatch1[k]] === arr2[j][columnToMatch2[k]] )
      }
      // If they match, add them to the output and go for the next pair
      if( isMatch ) {
        r = arr1[i].concat(arr2[j]);
        output.push(r);
      }
    }
    // If we couldn't find any match for the row, add it but with nulls for the other entries
    if( null == r ) {
      r = arr1[i].concat(Array(arr2RowLength).fill(null));
      output.push(r);
    }
  }
  
  return output;
}

/**
 * Returns an right join of table 1 (left) with table 2 (right).
 *
 * @param {Any[][]} arr1
 *        Left table
 * @param {Any[][]} arr2
 *        Right table
 * @param {(number|number[])} columnToMatch1
 *        Column(s) from table 1 used for matching, "joining on"
 * @param {(number|number[])} columnToMatch2
 *        Column(s) from table 2 used for matching, "joining on"
 * @param {boolean=false} hasHeader
 *        Boolean to represent if tables have headers or not
 *
 * @return {Any[][]} A right join of table 1 with table 2.
 * @customfunction
**/
function rightJoin( arr1, arr2, columnToMatch1, columnToMatch2, hasHeader = false ) {
  /* DATA VALIDATION */
  if( null == arr1 ) throw 'Missing left table: Argument 1 is required.';
  if( null == arr2 ) throw 'Missing right table: Argument 2 is required.';
  if( null == columnToMatch1 ) throw 'Missing left table column index: Argument 3 is required.';
  if( null == columnToMatch2 ) throw 'Missing right table column index: Argument 4 is required.';
  if( Array.isArray(columnToMatch1) != Array.isArray(columnToMatch2) ) throw 'Type mismatch: Argument 3 and 4 must have same type. They both need to be an integer or an array of integers';
  if( Array.isArray(columnToMatch1) && columnToMatch1.length != columnToMatch2.length ) throw 'Size mismatch: Argument 3 and 4 must have the same length';
  
  /* DATA PREPARATION */
  arr1 = tableTrimmer( arr1 );
  arr2 = tableTrimmer( arr2 );
  if( !Array.isArray(columnToMatch1) ) columnToMatch1 = Array.of(columnToMatch1);
  if( !Array.isArray(columnToMatch2) ) columnToMatch2 = Array.of(columnToMatch2);
  
  /* COMPUTATION */
  var output = [];
  
  // Dealing with headers
  if( hasHeader ) {
    var headers = arr1[0].concat(arr2[0]);
    output.push(headers);
  }
  
  // Right join itself
  var arr1RowLength = arr1[0].length;
  for( let i = hasHeader?1:0; i < arr2.length; i++ ) {
    let r = null;
    for( let j = hasHeader?1:0; j < arr1.length; j++ ) {
      // First, let's check if these two rows matches according to the desired column(s)
      let isMatch = true;
      for( let k = 0; k < columnToMatch1.length && isMatch; k++ ) {
         isMatch = isMatch && ( arr1[j][columnToMatch1[k]] === arr2[i][columnToMatch2[k]] )
      }
      // If they match, add them to the output and go for the next pair
      if( isMatch ) {
        r = arr1[j].concat(arr2[i]);
        output.push(r);
      }
    }
    // If we couldn't find any match for the row, add it but with nulls for the other entries
    if( null == r ) {
      r = Array(arr1RowLength).fill(null).concat(arr2[i]);
      output.push(r);
    }
  }
  
  return output;
}

/**
 * Returns an outer join of table 1 with table 2.
 *
 * @param {Any[][]} arr1
 *        Left table
 * @param {Any[][]} arr2
 *        Right table
 * @param {(number|number[])} columnToMatch1
 *        Column(s) from table 1 used for matching, "joining on"
 * @param {(number|number[])} columnToMatch2
 *        Column(s) from table 2 used for matching, "joining on"
 * @param {boolean=false} hasHeader
 *        Boolean to represent if tables have headers or not
 *
 * @return {Any[][]} An outer join of table 1 with table 2.
 * @customfunction
**/
function outerJoin( arr1, arr2, columnToMatch1, columnToMatch2, hasHeader = false ) {
  /* DATA VALIDATION */
  if( null == arr1 ) throw 'Missing left table: Argument 1 is required.';
  if( null == arr2 ) throw 'Missing right table: Argument 2 is required.';
  if( null == columnToMatch1 ) throw 'Missing left table column index: Argument 3 is required.';
  if( null == columnToMatch2 ) throw 'Missing right table column index: Argument 4 is required.';
  if( Array.isArray(columnToMatch1) != Array.isArray(columnToMatch2) ) throw 'Type mismatch: Argument 3 and 4 must have same type. They both need to be an integer or an array of integers';
  if( Array.isArray(columnToMatch1) && columnToMatch1.length != columnToMatch2.length ) throw 'Size mismatch: Argument 3 and 4 must have the same length';
  
  /* DATA PREPARATION */
  arr1 = tableTrimmer( arr1 );
  arr2 = tableTrimmer( arr2 );
  if( !Array.isArray(columnToMatch1) ) columnToMatch1 = Array.of(columnToMatch1);
  if( !Array.isArray(columnToMatch2) ) columnToMatch2 = Array.of(columnToMatch2);
  
  /* COMPUTATION */
  var output = [];
  
  // Dealing with headers
  if( hasHeader ) {
    var headers = arr1[0].concat(arr2[0]);
    output.push(headers);
  }
  
  // Outer join itself
  var arr1RowLength = arr1[0].length;
  var arr2RowLength = arr2[0].length;
  var visitedFromArr2 = Array(arr2.length).fill(false);
  
  for( let i = hasHeader?1:0; i < arr1.length; i++ ) {
    let r = null;
    for( let j = hasHeader?1:0; j < arr2.length; j++ ) {
      // First, let's check if these two rows matches according to the desired column(s)
      let isMatch = true;
      for( let k = 0; k < columnToMatch1.length && isMatch; k++ ) {
         isMatch = isMatch && ( arr1[i][columnToMatch1[k]] === arr2[j][columnToMatch2[k]] )
      }
      // If they match, add them to the output and go for the next pair
      if( isMatch ) {
        r = arr1[i].concat(arr2[j]);
        visitedFromArr2[j] = true;
        output.push(r);
      }
    }
    // If we couldn't find any match for the row of the left table, add it but with nulls for the other entries
    if( null == r ) {
      r = arr1[i].concat(Array(arr2RowLength).fill(null));
      output.push(r);
    }
  }
  // Now let's go back and deal with the unmatched rows of the right table
  for( let i = 1; i < arr2.length; i++ ) {
    if( visitedFromArr2[i] ) continue;
    let r = Array(arr1RowLength).fill(null).concat(arr2[i]);
    output.push(r);
  }
  
  return output;
}

function joins_tests() {
  var a1     = [[1, 'a', 'a1'], [2, 'a', 'b2'], [3, 'c', 'b2'], [4, 'c', 'a1']];
  var a2     = [[6, 'a', 'a1'], [7, 'b', 'a1'], [8, 'c', 'b1'], [9, 'c', 'a1']];
  var cross  = crossJoin(a1, a2, false);
  var left   = leftJoin (a1, a2, 1, 1, false);
  var right  = rightJoin(a1, a2, 1, 1, false);
  var inner  = innerJoin(a1, a2, 1, 1, false);
  var outer1 = outerJoin(a1, a2, 1, 1, false);
  var outer2 = outerJoin(a1, a2, 1, 1);
  var outer3 = outerJoin(a1, a2, [1], [1]);
  var outer4 = outerJoin(a1, a2, [1,2], [1,2]);
}

TableUtils.gs

/*************
 *
 * The following code snippet was written by https://github.com/bestknighter
 * It's freely available at https://gist.github.com/bestknighter/ec075ee402176b886a3c51913ab8c08c.
 * You can copy, change and even make money with it
 * as long as credit is properly given.
 * 
**************/

/**
 * Returns a 2-dimensional array similar to the input but with columns
 * and rows that are completely empty trimmed out.
 * 
 * Trim null columns and null rows from a 2-dimension array.
 *
 * @param {Any[][]} input
 *        The array to be trimmed
 *
 * @returns {Any[][]}
 *          A table with null columns and rows removed
 * @customfunction
**/
function tableTrimmer( input ) {
  // If there are 3 Consecutive null Rows, assumes that
  // the data has ended and trim to rest of the table altogether
  const CNR_LIMIT = 3;
  
  /* DATA PREPARATION */
  let arr = Array.from(input);
  let rowSize = Array.isArray(arr[0])? arr[0].length : 1;
  let colHasData = Array(rowSize).fill(false);
  let consecutiveNullRows = 0;
  
  /* COMPUTATION */
  // Null rows removal
  let i;
  for( i = 0; i < arr.length && consecutiveNullRows < CNR_LIMIT; i++ ) {
    let hasData = false;
    let row = Array.isArray(arr[i])? arr[i] : Array.of(arr[i]);
    // Does this row have data?
    for( let j = 0; j < row.length; j++ ) {
      if( !(row[j] == null || row[j] == '' ) ) {
        hasData = true;
        colHasData[j] = true; // This row has data in this column
      }
    }
    // If the row has data, ignore it and go to the next
    if( hasData ) {
      consecutiveNullRows = 0;
    } else { // If it doesn't, remove it
      arr.splice(i--,1);
      consecutiveNullRows++;
    }
  }
  // Removes remaining rows
  arr.splice(i);
  
  // Null columns removal
  // Is there any column with absolutely no values whatsoever?
  let hasEmptyCol = false;
  for( let j = 0; j<colHasData.length; j++ ) {
    hasEmptyCol = hasEmptyCol || !colHasData[j];
  }
  // Yep, let's remove it
  if( hasEmptyCol ) {
    for( let i = 0; i < arr.length; i++ ) {
      for( let j = colHasData.length-1; j>=0; j-- ) {
        if( !colHasData[j] ) {
          let aux = Array.from(arr[i])
          aux.splice(j,1);
          arr[i] = aux;
        }
      }
    }
  }
  
  return arr;
}

/**
 * Replace a value if it is null or empty by a given replacement.
 * 
 * This function can be used if replacing all null or empty values in an array
 * by a predefined value is desired. Like "---", "empty", "0" and so on.
 *
 * @param {(Any|Any[])} value
 *        The value to be evaluated (can be infinitely nested in arrays).
 * @param {string} replace
 *        The value to replace is case value is null or empty.
 *
 * @return {Any}
 *         replace if value is null or empty, value otherwise.
 * @customfunction
**/
function isNE( value, replace ) {
  if( Array.isArray(value) ) {
    for( let i = 0; i < value.length; i++ ) {
      value[i] = isNE(value[i], replace);
    }
    return value;
  } else {
    return myIsNE_(value, replace);
  }
}

function myIsNE_( value, replace ) {
  return ( value === null || value == '' ) ? replace : value;
}

/**
 * Returns an array with all indices were 'value' was found at column 'column'.
 * 
 * Find all rows that have a given value in a specific column.
 *
 * @param {Any[][]} table
 *        Table to look for a row.
 * @param {number} column
 *        Column at which to look for the value.
 * @param {Any} value
 *        Value being searched.
 *
 * @returns {number[]}
 *          Array with indices of all rows containing 'value'.
**/
function valueSearch( table, column, value ) {
  var result = [];
  for( var r = 0; r < table.length; r++ ) {
    if( table[r][column] === value ) result.push(r);
  }
  return result;
}

/**
 * Returns the index of the row in which the given UID is located, -1 if not found. 0-indexed.
 * 
 * Find a row with a given UID. Bootstraped mixed binary search. UID in a
 * table with no deleted entries will always be at index = UID-1, if it exists.
 * 
 * @param {Any[][]} table
 *        Table to search into.
 * @param {number} uid
 *        UID being searched.
 *
 * @return {number}
 *         Row index of given UID. -1 if not found.
**/
function uidSearch( table, uid ) {
  if( uid <= 0 ) return -1;

  var searchPos = Math.min(uid-1, table.length-1);

  if( table[searchPos][0] === uid ) return searchPos;
  else if( table[searchPos][0] > uid ) {
    if( searchPos === 0 ) return -1;
    else if( table[searchPos-1][0] === uid ) return searchPos-1;
    else if( table[searchPos-1][0] < uid ) return -1;
    else return bsearch_(table, uid, 0, searchPos-2);
  }
  else {
    if( searchPos+1 === table.length ) return -1;
    else if( table[searchPos+1][0] === uid ) return searchPos+1;
    else if( table[searchPos+1][0] > uid ) return -1;
    else return bsearch_(table, uid, searchPos+2, table.length-1);
  }
}

/**
 * Returns the index of the row in which the given UID is located, -1 if not found. 0-indexed.
 * 
 * Recursive binary search on table looking for a unique identifier (integer)
 * with partial sequential search as UIDs are also sequential and rarely skips.
 *
 * @param {Any[][]} table
 *        Table to search into.
 * @param {number} uid
 *        UID being searched.
 * @param {number} firstIdx
 *        Index of first element inside searching scope.
 * @param {number} lastIdx
 *        Index of last element inside searching scope.
 *
 * @return {number}
 *         Row index of given UID. -1 if not found.
**/
function bsearch_( table, uid, firstIdx, lastIdx ) {
  if( lastIdx < firstIdx ) return -1;

  var mid = Math.floor((lastIdx+firstIdx)/2);
  if( table[mid][0] === uid ) return mid;
  else if( table[mid][0] > uid ) {
    if( mid-1 < 0 ) return -1;
    if( table[mid-1][0] == uid ) return mid-1;
    else if( table[mid-1][0] < uid ) return -1;
    else return bsearch_( table, uid, firstIdx, mid-2 );
  }
  else if( table[mid][0] < uid ) {
    if( mid+1 === table.length ) return -1;
    if( table[mid+1][0] == uid ) return mid+1;
    else if( table[mid+1][0] > uid ) return -1;
    return bsearch_( table, uid, mid+2, lastIdx );
  }
  else return -1;
}

/**
 * Returns the index of the column named 'value', -1 if not found.
 * 
 * Find the column of a specified header in table header.
 *
 * @param {string[]} headers
 *        Header row of table.
 * @param {string} value
 *        Value being searched.
 *
 * @return {number}
 *         Index of column named value. -1 if not found.
**/
function searchHeaders( headers, value ) {
  for( var c = 0; c < headers.length; c++ ) {
    if( headers[c] === value ) return c;
  }
  return -1;
}

/**
 * Returns an array with properly ordered values in accordance with the table.
 * 
 * Uses an array of headers to transform an object with
 * properties in an array with the correct order of values.
 *
 * @param {Object} obj
 *        Object containing the values that represent a table entry.
 * @param {string[]} headers
 *        Array containing the headers of a table.
 *
 * @return {Any[]}
 *         Array with values properly ordered.
**/
function objectToArray( obj, headers ) {
  var entries = Object.entries(obj);
  var result = [];
  headers.forEach( (h) => {
    var idx = entries.findIndex( (element) => {return element[0] === h} );
    if(idx > -1) result.push(entries[idx][1]);
    else result.push(null);
  })
  return result;
}

/**
 * Returns an object with named properties in accordance with the table.
 *
 * Uses an array of headers to transform an array with correctly
 * ordered values into an object with properties.
 *
 * @param {Any[]} arr
 *        Array containing the values that represent a table entry.
 * @param {string[]} headers
 *        Array containing the headers of a table.
 *
 * @return {Object}
 *         Object with named properties in accordance with the table.
**/
function arrayToObject( arr, headers ) {
  var result = {};
  arr.forEach( (elem, index) => {
    Object.defineProperty(result, headers[index], {value: elem});
  })
  return result;
}

/**
 * Returns the next free index to be used.
 * 
 * Use this function to get the index for a new entry and auto
 * increment the value for the next call.
 * 
 * @param {string} table
 *        Name of the table to look for next index.
 * 
 * @return {integer}
 *         The a free index that should be used.
 * 
 * @throws Table name needs to exist in Indexes table.
**/
function getNextIndex( table ) {
  var idxSheet = SpreadsheetApp.getActive().getSheetByName("Indexes");
  var idxRow = valueSearch(idxSheet.getDataRange().getValues(), 0, table)

  if( idxRow.length == 0 ) throw "This table entry does not exist in Indexes";
  else idxRow = idxRow[0];

  var idxRange = idxSheet.getRange(idxRow+1, 2, 1, 1);// +1 because Range is 1-indexed and array is 0-indexed
  var index = idxRange.getValue();
  idxRange.setValue(index+1);

  return index;
}

/**
 * Returns the index of the inserted element.
 * 
 * Inserts an Entry into a Table.
 * 
 * @param {string} table
 *        Name of the table to insert.
 * @param {Object} entry
 *        Object with values to be inserted.
 * 
 * @return {number}
 *         The index of the inserted element.
 * 
 * @throws Table name needs to exist in Indexes table.
**/
function insertEntry( table, entry ) {
  entry.UID = getNextIndex(table);
  var tabela = SpreadsheetApp.getActive().getSheetByName(table);
  var headers = tabela.getDataRange().getValues()[0];
  tabela.appendRow(objectToArray(entry, headers));
  return entry.UID;
}

function tableUtils_tests() {
  var tableToTrim = [[1, 'a', null], [2, 'b', null], [3, null, null], [null, null, null], [null, null, null], [null, 4, null], [null, null, null]];
  var trimmedTable = tableTrimmer(tableToTrim);

  var headers = ["UID", "Name", "Telephone"];
  var arr = [5, 'John Doe', '+1 999 999 9999'];
  var obj = {Name: 'John Doe', Telephone: '+1 999 999 9999', UID: 5};
  var objArr = objectToArray(obj, headers);
  var arrObj = arrayToObject(arr, headers);

  var bigTable = [[1],[2],[3],[4],[5],[6],[7],[8],[9],[11],[12],[13],[14],[15],[16],[17],[18],[19],[99]];
  var searchResult = uidSearch(bigTable, 10);
  var foundIdx = searchResult == -1 ? -1 : bigTable[searchResult][0];
}