CODEWITHSUNDEEP
performancegoogle-apps-scriptgoogle-sheetsquery-optimization
David McCheyne
David McCheyne

Reputation: 13

How do I improve the performance of this matrix search function?

I have data in the form of Sheets representing levels of taxonomy. Columns on each sheet are the members of a given (Phylum, Class, Order, etc.) the name of which is the column header.

Headers are the Order, and column contents are the Families in that Order. I have this search function that searches the Order sheet for a given Family, and returns the Order that Family is a member of. But it's very slow.

Can I improve this search algorithm, or my search approach to more quickly run this function in a number of cells in another sheet? This workbook has the reference sheets, and a Sample Foray sheet that shows the function in action I'm hoping to markedly improve the performance of.

https://docs.google.com/spreadsheets/d/1HvDpgWd6vhAF9UPiomwpmRLnzlduPU9c2ueWRwq3oZw/edit?usp=sharing

function findvalue(sheetName, searchVal) {
  if(searchVal.length === 0){
      throw 'Error. Incertae sedis.';
  };
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var rangeData = sheet.getDataRange();
  var lastColumn = rangeData.getLastColumn();
  var lastRow = rangeData.getLastRow();
  var searchRange = sheet.getRange(1,1, lastRow-1, lastColumn-1);
  // Get array of values in the search Range
  var rangeValues = searchRange.getValues();
  // Loop through array and if condition met
  for ( i = 0; i < lastColumn - 1; i++){
    for ( j = 0 ; j < lastRow - 1; j++){
      if(rangeValues[j][i] === searchVal){
        return rangeValues[0][i]
      };
    };
  };
};

Upvotes: 1

Views: 74

Answers (1)

Tanaike
Tanaike

Reputation: 201358

I believe your goal as follows.

  • You want to reduce the process cost of your script used as the custom function.

Modification points:

  • When I saw your shared Spreadsheet, it was found that a lot of custom functions of findvalue are used in the sheet of Example Foray. And, for example, when the cell "K3" is seen, the formula of =iferror(findvalue("Phylum", J28), iferror(findvalue("Phylum", I28), iferror(findvalue("Phylum", H28), iferror(findvalue("Phylum", G28), iferror(findvalue("Phylum", F28), iferror(findvalue("Phylum", E28), iferror(findvalue("Phylum", D28), ""))))))) is used. It seems that such formulas are used in the cells.
    • I think that this might be the reason of your issue.
  • In order to reduce the process cost of your script, I would like to propose to put all values using one custom function. In your shared Spreadsheet, the values for the cells "E3:K58" are put by one custom function. By this, I thought that the process cost will be able to be reduced.

When the values for the cells "E3:K58" are put by one custom function, the script is as follows.

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet, and save it.

function findvalues(sheetNames, searchValues) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ar = sheetNames[0].map(name => {
    const values = ss.getSheetByName(name).getDataRange().getValues();
    const obj = values[0].reduce((o, _, i) => {
      const [v, ...k] = values.reduce((ar, r) => {
        if (r[i]) ar.push(r[i]);
        return ar;
      }, []);
      k.forEach(kk => o[kk] = v);
      return o
    }, {});
    return searchValues.map(sv => {
      let temp = "";
      for (let j = 0; j < sv.length; j++) {
        if (obj[sv[j]]) {
          temp = obj[sv[j]];
          break;
        }
      }
      if (temp) sv.push(temp);
      return temp;
    });
  });
  return ar[0].map((_, i) => ar.map(r => r[i]));
}
  • In order to use this script, when your shared Spreadsheet is used, please do the following flow.
    1. Clear the cells of "E3:K58" of the sheet "Example Foray".
    2. Put a formula of =findvalues(E2:K2,D3:D58) to the cell "E3" of the sheet "Example Foray".
      • From your script and spreadsheet, I understood that the values of "E2:K2" and "D3:D58" are the sheet names and the search values, respectively.
      • In order to reduce the loop cost, I used E2:K2 and D3:D58 instead of E2:2 and D3:D, respectively.

By this, the values are put to the cells "E3:K58".

Result:

When above script is used as a custom function, it becomes as follows. These values are the same with your Spreadsheet using findvalue.

enter image description here

Note:

  • This sample script is for the sheet of "Example Foray" in your shared Spreadsheet. When the cell structure is changed, the script might not be able to be used. So, please be careful this.

References:

Upvotes: 1

Related Questions