How to modify arrayFormula and vlookup in Google Apps Script-CodePudding

Hi I'm just new in Google Apps Script. I want to modify this formula below to Google Apps Script

=arrayFORMULA(iferror(VLOOKUP(J3,{'WO SR 22/23'!P:P,'WO SR 22/23'!B:B},2,FALSE)))

The lookup value is in wsPetitionStatusReport sheet. I tried code below but it always return null. Can you help me with this? Thank you so much

function vLookUpVALUE() {

   const ss = SpreadsheetApp.getActiveSpreadsheet();
const wsWOSR = ss.getSheetByName("WO SR 22/23")
const wsPetitionStatusReport  = ss.getSheetByName("Petition Status Report ")
 
const searchVALUES = wsPetitionStatusReport.getRange("J3:J").getValues()


const wsWOSRDATcolO = wsWOSR.getRange("B3:P"   wsWOSR.getLastRow()).getValues();


const matchDATAcolO = searchVALUES.map(searchROWcolO => {
        const matchROWcolO = wsWOSRDATcolO.find (r => r[0] == searchROWcolO[0])
        return matchROWcolO ? [matchROWcolO[0]] : [null]
    
})
  
 console.log(matchDATAcolO)


}

CodePudding user response：

You can evaluate the array formula for the whole column by replacing J3 with an open-ended reference like J3:J:

=arrayformula(iferror(vlookup(J3:J, { 'WO SR 22/23'!P:P, 'WO SR 22/23'!B:B }, 2, false)))

To do the same with Apps Script, use something like this:

function vlookupLookAlike() {
  const ss = SpreadsheetApp.getActive();
  const source = {
    values: ss.getRange('WO SR 22/23!B3:P').getValues(),
    keyColumn: 14, // =column(P3) - column(B3)
    dataColumn: 0,
  };
  source.keys = source.values.map(row => row[source.keyColumn]);
  source.data = source.values.map(row => row[source.dataColumn]);
  const target = {
    range: ss.getRange('Petition Status Report !J3:J'), // trailing space
  };
  target.keys = target.range.getValues().flat();
  target.results = target.keys.map(key => [key && source.data[source.keys.indexOf(key)]]);
  target.range.offset(0, 5).setValues(target.results);
}