Vlookup function using appscript returns the specified value if the data found is empty-CodePudding

I found on internet a script allow me do Vlookup in app-script to lookup a value in other spreadsheet, but if value matching is blank it just copy "blank" cell into child spreadsheet. I want if matching values found is blank it will write "Pending" string to cell, not just empty please suggest me a script thank

 function getTracking() {   
  const sss = SpreadsheetApp.openById("1B2yhG1dhpqgtSfF4ngqRuH51kzwQ6VwxIvCmwZZ2yQU");
  const ssh = sss.getSheetByName("Tổng Hợp");   
  const mDB = ssh.getRange(2,1,ssh.getLastRow()-1,4).getValues();    
  const dss = SpreadsheetApp.openById("1slt0ExJK2X8xkRVpOzdD8NCsUmj3UeC3urfyZWsV0To");
  const dsh = dss.getSheetByName("test");

  const searchValues = dsh.getRange("B2:B").getValues();   
  const matchingID = searchValues.map(searchRow => {
  const matchRow = mDB.find(r => r[0] == searchRow[0])
  return matchRow ? [matchRow [3]]: ["Không tìm thấy dữ liệu"];   
  })   
  dsh.getRange("C2:C").setValues(matchingID);   
 }

CodePudding user response：

Modification points:

In your script, by const searchValues = dsh.getRange("B2:B").getValues();, all rows are used. In this case, getRange("B2:B" dsh.getLastRow()) might be suitable.
About if value matching is blank it just copy "blank" cell into child spreadsheet. I want if matching values found is blank it will write "Pending" string to cell, in this case, I would like to propose modifying [matchRow [3]].

When these points are reflected in your script, how about the following modification?

From:

const searchValues = dsh.getRange("B2:B").getValues();
const matchingID = searchValues.map(searchRow => {
  const matchRow = mDB.find(r => r[0] == searchRow[0])
  return matchRow ? [matchRow[3]] : ["Không tìm thấy dữ liệu"];
})
dsh.getRange("C2:C").setValues(matchingID);

To:

const range = dsh.getRange("B2:B"   dsh.getLastRow())
const searchValues = range.getValues();
const matchingID = searchValues.map(searchRow => {
  const matchRow = mDB.find(r => r[0] == searchRow[0])
  return matchRow ? [matchRow[3].toString() != "" ? matchRow[3] : "Pending"] : ["Không tìm thấy dữ liệu"];
})
range.offset(0, 1).setValues(matchingID);

Reference:

Conditional (ternary) operator