Vlookup with split text by Google Appscript-CodePudding

I am new to Appscript hence any help on below will be really appreciated.

My query is similar to the one posted in the below link,however, in that question the job is done by custom function and it is working bit slow and runs on every edit. In place of custom function I want to design an Appscript for the same which runs on the change of dropdown.

Link to similar question:

Google Appscript partial vlookup

Link of sample spreadsheet.

https://docs.google.com/spreadsheets/d/1vI22QCmixKe3aoWMLODTFzt7pNXIKO3pjXS4mT6GHT0/edit#gid=0

Any help on above will really be appreciated.

CodePudding user response：

I believe your goal is as follows.

You want to run the script when the dropdown list of cell "A1" of "Sheet3" is changed to "Refresh".

You want to obtain the same result with your following script.

  function MYLOOKUP(data1, data2) {
    return data1
      .map(([rollNo_Name, value]) => {
        return (rollNo_Name !== '' && value === '') ?
          data2.find(([rollNo,]) => rollNo_Name.split('*')[0] == rollNo)[1] :
          ''
      });
  }

In this case, how about using the OnEdit trigger of the simple trigger? When this is reflected in your sample Spreadsheet, the sample script is as follows.

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and save the script. When you run the script, please change the dropdown list of cell "A1" of "Sheet3" to "Refresh". By this, the script is run.

function onEdit(e) {
  const sheetName = "Sheet3"; // This sheet name is from your Spreadsheet.
  const { range, value, source } = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != sheetName || range.getA1Notation() != "A1" || value != "Refresh") return;
  const sheet1 = source.getSheetByName("Sheet1"); // This sheet name is from your Spreadsheet.
  const sheet2 = source.getSheetByName("Sheet2"); // This sheet name is from your Spreadsheet.
  const range1 = sheet1.getRange("A2:B"   sheet1.getLastRow());
  const obj = sheet2.getRange("A2:B"   sheet2.getLastRow()).getValues().reduce((o, [a, b]) => (o[a] = b, o), {});
  const values = range1.getValues().map(([a, b]) => {
    const temp = obj[a.split("*")[0]];
    return [temp && !b.toString() ? temp : null];
  });
  range1.offset(0, 2, values.length, 1).setValues(values);
  range.setValue(null);
}

In this script, when the dropdown list of cell "A1" of "Sheet3" is changed to "Refresh", the script is run. And, the same result with your script is obtained. And, the value of the dropdown list is changed to null.
The result values are put to column "C" of "Sheet1". If you want to change this, please modify the above script.

Note:

In this script, when you directly run the function onEdit with the script editor, an error occurs. Please be careful about this.
In this script, in order to search the values, I used an object. By this, the process cost might be able to be reduced a little.
Updated: I reflected value in to be pulled only when there is no value in Column B.

Sample script:

Note:

References: