Is there an option using Google App Scripts that works like VLOOKUP?

I am hoping for a solution that will replace the ??? on Spreadsheet 2, with the emails on Spreadsheet 1 on a scheduled bases using the GAS trigger feature.

Currently I am using formulas, but they are slowing down the performance of my sheets. I don't need them fetching data more than once every 24 hours, hence why I am wanting to use GAS instead with a trigger.

• IMPORTRANGE & QUERY to import Spreadsheet #1 (ID, Email 1, Email2) to Spreadsheet #2 =QUERY(IMPORTRANGE(spreadsheet_url, A1:E), "SELECT Col1,Col4,Col5 WHERE A Col1 is not null", 1)
• Then on Spreadsheet #2 using ARRAYFORMULA & VLOOKUP to populate all the emails. =ARRAYFORMULA(VLOOKUP(Sheet1!A2:A,ImportedData!A2:C},{2,3},0)

Here are what my 2 spreadsheets look like...

Spreadsheet #1 (Contains ~2 million cells)

Spreadsheet #2 (Contains ~200k cells)

UPDATE

I asked a similar question HERE and received an answer.

The answer was:

function myFunction() {
  const sss = SpreadsheetApp.openById('ABC');
  const ssh = sss.getSheetByName("MasterDB");
  const mDB = ssh.getRange("A2:C"   ssh.getLastRow()).getValues(); //Get's ID's from Master Spreadsheet

  const dss = SpreadsheetApp.openById('XYZ');
  const dsh = dss.getSheetByName("ChildDB");
  const cDB = dsh.getRange("A2:A"   dsh.getLastRow()).getValues(); //Get's ID's from Child Spreadsheet

  // Create an object for searching the values of column "A".
  const obj = mDB.reduce((o, [a, ...bc]) => ((o[a] = bc), o), {});
  
  // Create an array for putting to the Spreadsheet.
  const values = cDB.map(([b]) => obj[b] || ["", ""]);
  
  // Put the array to the Spreadsheet.
  dsh.getRange(2, 2, values.length, 2).setValues(values);
}

My New Question (for this thread)

How do I modify the script to exclude column B and C?

CodePudding user response：

In your situation, I think that your goal might be able to be achieved by the following 2 modification points in your script.

From:

const mDB = ssh.getRange("A2:C"   ssh.getLastRow()).getValues();

To:

const mDB = ssh.getRange("A2:E"   ssh.getLastRow()).getValues();

And also, please modify as follows.

From:

const obj = mDB.reduce((o, [a, ...bc]) => ((o[a] = bc), o), {});

To:

const obj = mDB.reduce((o, [a,,,...de]) => ((o[a] = de), o), {});