Objective: I would like to eliminate the use of formulas (array formulas, importranges, and vlookups). Instead, I would like to use Google App Script to populate columns in the Child Database Spreadsheet. This is because of the current performance issues every time I open the sheet, and issues with Google Data Studio timing out when pulling the data.

I have 2 spreadsheets.

#1 - Master Database (~1,000,000 rows) - 100% Manual Input

#2 - Child Database (~10,000 rows)

Its purpose: Manually enter ID's in Col A, and the formula will auto-populate Col B:C (Name & Email)

• This is the expected results with GAS instead of the current formula.

• Col A - Manual Input of ID.
• Col B1 contains formula =ARRAYFORMULA(VLOOKUP(A2:A,IMPORTRANGE("URL","MasterDB!A2:C"),{2,3},FALSE)) which get's the ID's from Col A, searches the Master Database spreadsheet, and returns the Name, and Email.
  

What is the best solution?

Here is what I came up with, so far...

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

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

  [Some Code Here]
  - Return Col B,C from Master Sheet, if Col A matches in both Master & Child Sheet.

}

Thanks for any of your input, guidance, and help :)

CodePudding user response：

Modification points:

• In your script, const mDB = ssh.getRange("A2:A").getValues; and const cDB = dsh.getRange("A2:A").getValues; are required to be added () for executing the function of getValues.
• It seems that import of the function name is the reserved name. So please modify the function name. When V8 runtime is used.

When these points are reflected to the script, it becomes as follows.

Modified script:

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

• In order to achieve your goal, I modified the sample script at this thread.

Note:

• This script is used with V8 runtime. So when you disable V8 runtime, please enable it.
• If this was not the result you expect, can you provide the sample Spreadsheet? By this, I would like to modify the script.

References:

• reduce()
• map()

Added:

About your new 3 questions, I answered as follows.

[Question #1] I assume o is just a placeholder and can be any letter I want. Is that true? or does the letter o have some significant?

Yes. You can use other variable name except for o. In this script, the initial value of o is {}. Ref

[Question #2] What do the 3 dots do? [a, ...bc] ?

... is spread syntax. Ref

[Question #3] How would I skip a returned column? Currently it returns b,c. How would I return c,d instead?

In this case, the sample script is as follows.

function Q69818704_myFunction() {
  const sss = SpreadsheetApp.openById('ABC');
  const ssh = sss.getSheetByName("MasterDB");
  const mDB = ssh.getRange("A2:D"   ssh.getLastRow()).getValues();

  const dss = SpreadsheetApp.openById('XYZ');
  const dsh = dss.getSheetByName("ChildDB");
  const cDB = dsh.getRange("A2:A"   dsh.getLastRow()).getValues();

  const obj = mDB.reduce((o, [a,, ...cd]) => ((o[a] = cd), o), {});
  const values = cDB.map(([b]) => obj[b] || ["", ""]);
  dsh.getRange(2, 2, values.length, 2).setValues(values);
}