Google App Scripts&amp；Google Sheets-相当于VLOOKUP/IMPORTRANGE-使用多个电子表格 [英] Google App Scripts & Google Sheets - Equivalent of VLOOKUP/IMPORTRANGE - Using multiple spreadsheets

问题描述

目标： 我想取消使用公式(数组公式、导入范围和vlookup)。相反，我想使用Google App脚本来填充子数据库电子表格中的列。这是因为我每次打开工作表时都会出现当前的性能问题，并且在拉取数据时Google Data Studio超时。

---

我有2个电子表格。

#1-主数据库(约1,000,000行)-100%手动输入

	A(手动输入)	B(手动输入)	C(手动输入)
1	X123456	无名氏	johndoe@examplecom
2	X987654	简·史密斯	JaneSmith@examplecom
3	X543210	莎拉·史密斯	sarahsmith@examplecom

---

#2-子数据库(约10,000行)

用途：在A列手工输入ID，公式会自动填入B列：C(名称&；Email)

• 这是使用GAS而不是当前公式的预期结果。

	A(手动输入)	B(自动填充)	C(自动填充)
1	X543210	莎拉·史密斯	sarahsmith@examplecom
2	X123456	无名氏	johndoe@examplecom

• A列-手工输入ID。
• B1列包含公式=ARRAYFORMULA(VLOOKUP(A2:A,IMPORTRANGE("URL","MasterDB!A2:C"),{2,3},FALSE))，该公式从A列获取ID，搜索Master Database电子表格，然后返回名称和电子邮件。
  

---

最佳解决方案是什么？

这是我到目前为止想出来的…

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.

}

感谢您的任何投入、指导和帮助：)

推荐答案

修改点：

• 在您的脚本中，需要添加const mDB = ssh.getRange("A2:A").getValues;和const cDB = dsh.getRange("A2:A").getValues;来执行getValues的功能。
• 函数名的import似乎是保留名。所以请修改函数名称。使用V8运行时。

当这些点反映到脚本中时，将如下所示。

修改后的脚本：

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

• 为了实现您的目标，我在this thread修改了示例脚本。

注意：

• 此脚本用于V8运行时。因此，当您禁用V8运行时时，请启用它。
• 如果这不是您预期的结果，您能提供示例电子表格吗？由此，我想修改脚本。

引用：

• reduce()
• map()

已添加：

关于您的新3个问题，我回答如下。

[问题1]我假设o只是一个占位符，可以是我想要的任何字母。这是真的吗？还是字母o有什么重要意义？

是的。您可以使用o以外的其他变量名。在此脚本中，o的初始值为{}。Ref

[问题2]这3个点是做什么的？[a，.BC]？

...是扩展语法。Ref

[问题3]如何跳过返回的列？当前它返回b，c。我如何改为返回c，d？

在本例中，示例脚本如下所示。

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

这篇关于Google App Scripts&amp；Google Sheets-相当于VLOOKUP/IMPORTRANGE-使用多个电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！