如何使用Google Apps脚本在Google电子表格中执行查找/过滤 [英] How to Perform a Lookup/Filter Across Google Spreadsheets Using Google Apps Script

问题描述

我希望使用Google Apps脚本来填充电子表格，其中包含来自其他电子表格的已过滤数据。
$ b 源文档是员工列表（列A，NAME ），他们报告的时间（B列，HOURS），他们正在进行的项目（C列，PROJECT）和他们的具体任务（D列，TASK）。

我想用相同的信息填充第二个电子表格，但是只有PROJECT等于Project X且Task等于Task 1或Task 2。

我知道如何在源文档中没有脚本的情况下做到这一点，但Google电子表格不允许VLookup或我知道的任何其他查找函数在单独的文档中查找值。在这种情况下，使用ImportRange将源数据导入到第二个电子表格不起作用，因为源数据太大（最终，由于一个电子表格数据太多，此脚本将不得不被更改为查看多个源文档）。

对不起，我没有代码。我自己解决这个问题的尝试让我无处可寻。

谢谢！

解决方案

这是一个稍微旧的线程，但是如何做到这一点的例子太少了，受伤了您可以将SQL语句传递到下面的querySpreadsheet（）函数中，如下所示：

  var querySQL =SELECT A，B， C，D WHERE C ='项目X'AND（D ='任务1'或D ='任务2'）; 
  

然后，可以将函数返回的数据数组放入目标工作表中，如下所示： p>

  var aryValues = querySpreadsheet（querySQL）; 
 SpreadsheetApp.getActiveSpreadsheet（）。getRangeByName（'OUTPUT_TOPLEFT_CELL'）。offset（0，0，aryValues.length，4）.setValues（aryValues）; 
  

这些功能几乎可以使用，只需将您的信息替换到指定位置即可。
这里的脚本的原始信贷给Mogsdad：
在发送到Google Apps脚本信息中心之前，使用QUERY URL对数据进行分组

 函数querySpreadsheet（querySQL）{
 
尝试{
 
 //您可以在电子表格的网址中找到这两个项目
 var ssKey ='长度码以&结尾的数据来源电子数据表内的电子文档密钥以key =开头; 
 var gId ='包含数据的纸张索引 - 以gid ='开头; 
 var query = encodeURIComponent（querySQL）; 
 
 var url ='http://spreadsheets.google.com/tq?key=%KEY%&gid=%GID%&tq=%QUERY%'
 .replace （'％KEY％'，ssKey）
 .replace（'％QUERY％'，query）
 .replace（'％GID％'，gId）; 
 
 var response = UrlFetchApp.fetch（url）; 
 var content = response.getContentText（）; 
 var jsonContent = getJSON（content）; 
 
 var numCols = jsonContent.table.cols.length; 
 var numRows = jsonContent.table.rows.length; 
 
 //将objectContent解码为一个二维数组。 
 var mydata = []; 
 //然后数据行
（var row = 0; row< numRows; row ++）{
 mydata [row] = []; （var col = 0; col< numCols; col ++）{
 mydata [row] .push（jsonContent.table.rows [row] .c [col] .v）; 
 
} 
} 
 
返回mydata; 
 
} catch（error）{
 Logger.log（'querySpreadsheet：ERROR  - '，error）; 
}; 
}; 
 
 //内容文本格式不统一;尝试2种方法返回一个JSON对象
 function getJSON（contentText）{
 
 try {
 
 var regex = /.*google.visualization.Query.setResponse \\（（。*）\）/ g 
 var ret = eval（'（'+ regex.exec（contentText）[1] +'）'）; 
 return ret; 
 
} catch（error）{
 
 var ret = regex.exec（contentText）[1]; 
 var ret = Utilities.jsonParse（ret）; 
 return ret; 
 
}; 
}; 
  

I would like to use Google Apps Script to populate a spreadsheet with filtered data from another spreadsheet.

The source document is a list of employees (column A, NAME), with their reported hours (column B, HOURS), the project they are working on (column C, PROJECT), and their specific task (column D, TASK).

I would like to populate the second spreadsheet with the same information, but only where the PROJECT equals "Project X" and the Task equals either "Task 1" or "Task 2."

I know how to do this without scripts in the source document, but Google spreadsheets does not allow VLookup, or any other lookup function that I am aware of, to lookup values across separate documents. Importing the source data to the second spreadsheet using ImportRange does not work in this case because the source data is too large (ultimately, this script will have to be altered to look at several source documents since there is too much data for one spreadsheet).

I am sorry I have no code. My attempts to solve this problem on my own have gotten me nowhere.

Thank you!

解决方案

This is a slightly old thread, but there are so few examples of how to do this that I thought one more couldn't hurt. You can pass an SQL statement into the querySpreadsheet() function below, something like:

var querySQL = "SELECT A, B, C, D WHERE C = 'Project X' AND (D = 'Task 1' OR D = 'Task 2')";

You can then drop the array of data returned by the function into your target sheet like so:

var aryValues = querySpreadsheet(querySQL);
SpreadsheetApp.getActiveSpreadsheet().getRangeByName('OUTPUT_TOPLEFT_CELL').offset(0, 0, aryValues.length, 4).setValues(aryValues);

These functions are almost good to go, just substitute your information where indicated. Credit to Mogsdad for the original of this script here: Using QUERY URL to group data before sending to Google Apps Script dashboard

function querySpreadsheet(querySQL){ 

try {  

  //you can find both these items in the url of your spreadsheet
  var ssKey = 'DOCUMENT KEY OF THE DATA SOURCE SPREADSHEET INSIDE THESE QUOTES - long code ends with "&" starts with "key="';
  var gId = 'INDEX OF THE SHEET CONTAINING THE DATA - starts with "gid="';
  var query = encodeURIComponent(querySQL);

  var url = 'http://spreadsheets.google.com/tq?key=%KEY%&gid=%GID%&tq=%QUERY%'
  .replace('%KEY%',ssKey)
  .replace('%QUERY%',query)
  .replace('%GID%',gId);

  var response = UrlFetchApp.fetch(url);
  var content = response.getContentText();    
  var jsonContent = getJSON(content);

  var numCols = jsonContent.table.cols.length;
  var numRows = jsonContent.table.rows.length;

  // Decode objectContent into a two-dimensional array.
  var mydata = [];
  // Then data rows
  for (var row = 0; row < numRows; row++) {
    mydata[row] = [];
    for (var col = 0; col < numCols; col++ ) {
      mydata[row].push(jsonContent.table.rows[row].c[col].v);
    }
  }

  return mydata;

}  catch(error) {
  Logger.log('querySpreadsheet: ERROR - ', error);
};    
};

//the content text format is not uniform; try 2 methods to return a JSON object 
function getJSON(contentText) {

try {  

  var regex = /.*google.visualization.Query.setResponse\((.*)\)/g    
  var ret = eval('('+ regex.exec(contentText)[1] +')');
  return ret;

}  catch(error) {

  var ret = regex.exec(contentText)[1];
  var ret = Utilities.jsonParse(ret);
  return ret;

}; 
};

这篇关于如何使用Google Apps脚本在Google电子表格中执行查找/过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！