Как выполнить поиск / фильтр по таблицам Google с помощью скрипта Google Apps

Question

Я хотел бы использовать Google Apps Script для заполнения электронной таблицы отфильтрованными данными из другой электронной таблицы.

Исходным документом является список сотрудников (столбец A, ИМЯ), с указанием их часов работы (столбец B, ЧАСЫ), проекта, над которым они работают (столбец C, ПРОЕКТ), и их конкретной задачи (столбец D, ЗАДАЧА).

Я хотел бы заполнить вторую электронную таблицу той же информацией, но только там, где ПРОЕКТ равен "Проект X", а Задача равна "Задаче 1" или "Задаче 2".

Я знаю, как сделать это без сценариев в исходном документе, но электронные таблицы Google не позволяют VLookup или любой другой известной мне функции поиска искать значения в разных документах. Импорт исходных данных во вторую электронную таблицу с помощью ImportRange в этом случае не работает, поскольку исходные данные слишком велики (в конечном счете, этот сценарий придется изменить, чтобы просмотреть несколько исходных документов, поскольку для одной электронной таблицы слишком много данных).

Извините, у меня нет кода. Мои попытки решить эту проблему самостоятельно ни к чему не привели.

Спасибо!

Answer 1

Это немного старая тема, но примеров того, как это сделать, так мало, что я подумал, что еще один не повредит. Вы можете передать оператор SQL в функцию querySpreadsheet() ниже, что-то вроде:

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

Затем вы можете перетащить массив данных, возвращаемых функцией, на целевой лист следующим образом:

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

Эти функции практически удобны, просто подставьте свою информацию там, где это указано. Благодарим Mogsdad за оригинал этого скрипта здесь: использование URL-адреса QUERY для группировки данных перед отправкой на панель инструментов скрипта Google Apps

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;

}; 
};

Answer 2

Короткий ответ: вы не можете, особенно если данные большие. Вы могли бы сделать это, написав формулу с помощью скрипта (используя vlookup или запрос), но у этого есть ограничения, например, вы должны по очереди, если более одного скрипта выполняется одновременно. Другой более надежный способ - использовать urlfetch с таблицами API. Используйте список строк api с фильтром "q". Это хорошо работает даже с одновременными сценариями.