问Google脚本-做Vlookup的更好方法

EN

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}`);
    }
  })
}