Google Apps脚本执行索引和放大两个独立的Google表格之间的匹配功能 [英] Google Apps Script performing Index & Match function between two separate Google Sheets

问题描述

我使用Google Apps脚本在两张独立的Google表格之间执行索引和匹配功能，并检索匹配的记录并填充指定的表单和列。虽然它运行得很好，但我希望脚本能够更高效地运行，因此使用时间更少。有人请看下面的代码示例，并为我提供建议/指针来做到这一点。

  function SiteIDLookup（）{
 var search_spreadsheet = SpreadsheetApp.openById（GoogleSheetId）; 
 SpreadsheetApp.setActiveSpreadsheet（search_spreadsheet）; 
 var find_spreadsheet = SpreadsheetApp.openById（GoogleSheetId）; 
 SpreadsheetApp.setActiveSpreadsheet（find_spreadsheet）; 
 var ssheet = search_spreadsheet.getSheetByName（schoolSiteID_Lookup）; 
 var fsheet = find_spreadsheet.getSheetByName（Info_Formatted）; 
 var FMaxR = fsheet.getMaxRows（）; 
 fsheet.getRange（2，16，FMaxR，1）.clear（{contentsOnly：true}）; 
 var findData = fsheet.getDataRange（）。getValues（）; 
 var searchData = ssheet.getDataRange（）。getValues（）; （var i = 0; i< findData.length; i ++）{
 for（var j = 0; j< searchData.length; j ++）{
（
 
） var find = findData [i] [14]; 
 var searchref = searchData [j] [0]; 
 
 if（find == searchref&&& find！=）{
 var found = ssheet.getRange（j + 1,2,1,1）.getDisplayValue（） ; 
 fsheet.getRange（i + 1,16,1,1）.setValue（found）; 
} 
} 
} 
} 
  

解决方案

对你的问题的评论是非常正确的。您正在执行不必要的操作，如

  SpreadsheetApp.setActiveSpreadsheet（search_spreadsheet）; 
 SpreadsheetApp.setActiveSpreadsheet（find_spreadsheet）; 
  

哪个服务器没有目的。然后在循环内执行不必要的操作。

$ $ $ $ $ $ $ $ $ $ $ var var found = ssheet.getRange（j + 1,2,1,1 ）.getDisplayValue（）;

考虑到您已经完成了 var searchData = ssheet.getDataRange（）。getValues ）; 该命令行是多余的，并在每次迭代中执行以引导。在数组中有2个数据集，您需要使用数组并使用 .setValues（array）来输出数据。一些事情（你必须检查索引等，因为我没有实际的工作表来测试它）

  function SiteIDLookup（）{
 // ----------------------------------- --------------------------------- 
 //这是纯粹的化妆品，我个人比较讨厌声明变量
 //循环内部或代码中间
 var i，j 
 var find 
 var searchref 
 var found = [] 
 //  - -------------------------------------------------- ----------------- 
 
 var search_spreadsheet = SpreadsheetApp.openById（GoogleSheetId）; 
 var find_spreadsheet = SpreadsheetApp.openById（GoogleSheetId）; 
 
 var ssheet = search_spreadsheet.getSheetByName（schoolSiteID_Lookup）; 
 var fsheet = find_spreadsheet.getSheetByName（Info_Formatted）; 
 var FMaxR = fsheet.getMaxRows（）; 
 
 fsheet.getRange（2，16，FMaxR，1）.clear（{contentsOnly：true}）; 
 
 var findData = fsheet.getDataRange（）。getValues（）; 
 var searchData = ssheet.getDataRange（）。getValues（）; （i = 0; i  for（j = 0; j< searchData.length; j ++）{
 $ b（
 
） $ b find = findData [i] [14]; 
 searchref = searchData [j] [0]; 
 
 if（find == searchref&& find！=）{
 found [i] = searchData [j] [1] 
} 
 else {
 found [i] = [''] 
} 
 // found = ssheet.getRange（j + 1,2,1,1）.getDisplayValue（）; 
 // fsheet.getRange（i + 1,16,1,1）.setValue（found）; 
} 
} 
 
 fsheet.getRange（2，16，found.length，1）.setValues（找到）
} 
  

I am using Google Apps Script to perform a Index and Match function between two separate Google Sheets and retrieve the matching records and populate the designated sheet and column. Although it works fairly well, I would like to get the script to run more efficiently and as such use less time. Could Someone please look at the code example below and provide me with advice/pointers to do so

function SiteIDLookup() {
var search_spreadsheet = SpreadsheetApp.openById("GoogleSheetId");
SpreadsheetApp.setActiveSpreadsheet(search_spreadsheet);
var find_spreadsheet = SpreadsheetApp.openById("GoogleSheetId");
SpreadsheetApp.setActiveSpreadsheet(find_spreadsheet);
var ssheet = search_spreadsheet.getSheetByName("schoolSiteID_Lookup");
var fsheet = find_spreadsheet.getSheetByName("Info_Formatted");
var FMaxR = fsheet.getMaxRows();
fsheet.getRange(2, 16, FMaxR, 1).clear({contentsOnly: true});
var findData = fsheet.getDataRange().getValues();
var searchData = ssheet.getDataRange().getValues();

for(var i=0; i < findData.length; i++) {
 for(var j=0; j < searchData.length; j++) {
  var find = findData[i][14];
  var searchref = searchData[j][0];

  if(find == searchref && find != "" ) {
   var found = ssheet.getRange(j+1,2,1,1).getDisplayValue();
   fsheet.getRange(i+1,16,1,1).setValue(found);
   }
  }
 }
}

解决方案

The comment on your question is pretty much correct. You are doing unnecessary actions like

SpreadsheetApp.setActiveSpreadsheet(search_spreadsheet);
SpreadsheetApp.setActiveSpreadsheet(find_spreadsheet);

which server no purpose. Then inside of the loops you perform an unnecessary action

var found = ssheet.getRange(j+1,2,1,1).getDisplayValue();

considering you already did var searchData = ssheet.getDataRange().getValues(); that command line is redundant and is performed at each iteration to boot. You have 2 data sets in arrays, you need to work with arrays and use .setValues(array) to output the data. Something along the lines of this (you would have to check the indexing etc, since I don't have the actual sheets to test this on)

function SiteIDLookup() {
//  --------------------------------------------------------------------
//  This bit is purely cosmetic, I personaly hate declaring variables
//  inside of loops or middle of the code
  var i, j
  var find
  var searchref
  var found = []
//  --------------------------------------------------------------------

  var search_spreadsheet = SpreadsheetApp.openById("GoogleSheetId");  
  var find_spreadsheet = SpreadsheetApp.openById("GoogleSheetId");

  var ssheet = search_spreadsheet.getSheetByName("schoolSiteID_Lookup");
  var fsheet = find_spreadsheet.getSheetByName("Info_Formatted");
  var FMaxR = fsheet.getMaxRows();

  fsheet.getRange(2, 16, FMaxR, 1).clear({contentsOnly: true});

  var findData = fsheet.getDataRange().getValues();
  var searchData = ssheet.getDataRange().getValues();

  for (i = 0; i < findData.length; i++) {
    for (j = 0; j < searchData.length; j++) {

      find = findData[i][14];
      searchref = searchData[j][0];

      if (find == searchref && find != "" ) {
        found[i] = searchData[j][1]
      }
      else {
        found[i] = ['']
      }
      //        found = ssheet.getRange(j+1,2,1,1).getDisplayValue();
      //        fsheet.getRange(i+1,16,1,1).setValue(found);
    }
  }

  fsheet.getRange(2, 16, found.length, 1).setValues(found)
}

这篇关于Google Apps脚本执行索引和放大两个独立的Google表格之间的匹配功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！