Google Apps Script - Better way to do a Vlookup-CodePudding

I am doing a kind of VLOOKUP operation in a column with about 3K cells. I am using the following function to do it. I commented on what the code is doing in the function, but to summarize:

It creates a map from values to search for from a table with metadata
It iterates each value of a given range, and searches for coincidences in the previous map
If coincidences are found, it uses the index to capture the second column of the metadata table
Finally, sets the value captured in another cell

This is the code:

function questions_categories() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("data_processed");

  // get metadata. This will work as the table to look into
  // Column B contains the matching element
  // Column C contains the string to return
  var metadata = ss.getSheetByName("metadata").getRange('B2:C').getValues()

  // Just get the different values from the column B
  var dataList = metadata.map(x => x[0])

  // Used to define the last cell where to apply the vlookup
  var Avals = sheet.getRange("A1:A").getValues();
  var Alast = Avals.filter(String).length;

  // define the range to apply the "vlookup"
  const questions_range = sheet.getRange("Q2:Q"   Alast);
  
  forEachRangeCell(questions_range, (cell) => {
  
    var searchValue = cell.getValue();
    // is the value to search in the dataList we defined previously?
    var index = dataList.indexOf(searchValue);

    if (index === -1) {
      // if not, throw an error
      throw new Error('Value not found')
    } else {
      // if the value is there, use the index in which that appears to get the value of column C
      var foundValue = metadata[index][1]
      // set the value in two columns to the right
      cell.offset(0, 2).setValue(`${foundValue}`);
    }
  })
}

forEachRangeCell() is a helper function to iterate through the range.

This works very well, but it resolves 3-4 cells per second, which is not very efficient if I need to check thousands of data. I was wondering if there is a more performant way to achieve the same result.

CodePudding user response：

To improve performance, use Range.setValues() instead of Range.setValue(), like this:

function questions_categories() {
  const ss = SpreadsheetApp.getActive();
  const source = { values: ss.getRange('metadata!B2:C').getValues() };
  const target = { range: ss.getRange('data_processed!Q2:Q') };
  source.keys = source.values.map(row => row[0]);
  target.keys = target.range.getValues().flat();
  const result = target.keys.map(key => [source.values[source.keys.indexOf(key)]?.[1]]);
  target.range.offset(0, 2).setValues(result);
}

See Apps Script best practices.