#google-apps-script #google-sheets
Вопрос:
Я чувствую, что этот сценарий работает почти так, как задумывалось, но я не уверен, где разместить определенные компоненты процедуры в цикле for для достижения желаемого результата. Туннельное зрение сейчас очень эффективно, вполне возможно, что я переосмысливаю простую задачу. Пожалуйста, дайте мне знать, если я смогу более четко описать проблему. Любые предложения или указания на существующий ресурс полезны.
Настройка: Первый лист содержит динамический вертикальный список текстовых значений, начинающихся в ячейке I3 и идущих вниз. Второй лист содержит динамический вертикальный список текстовых значений, начинающихся в диапазоне A2 и идущих вниз, и содержит аналогичный набор текстовых значений в тех же строках столбца B.
Цель:
- Получите значение каждой используемой ячейки в столбце I листа 1 (диапазон один)
- Получите значение каждой используемой ячейки в столбце A листа 2 (диапазон два)
- Для каждой используемой ячейки в первом диапазоне проверьте значение каждой ячейки первого диапазона, чтобы узнать, существует ли значение во втором диапазоне
- Если совпадение существует, получите номер строки ячейки во втором диапазоне, содержащей значение (если совпадение существует, то будет только одно совпадение).
- Получите значение ячейки в той же строке в столбце B листа 2
- Установите указанное выше значение в качестве значения ячейки в строке, содержащей значение, найденное в обоих диапазонах в столбце M листа 1
Ниже приведено то, что мне удалось придумать. Я могу заставить его функционировать до последнего шага. Похоже, что соответствующая переменная номера строки не обновляется с каждым циклом for.
// Get values of range one
var lastRowRangeOne = sheetone.getRange('I3').getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
var rangeOneValues = sheetone.getRange('I3:I' lastRowClientColumn).getValues();
// Get values of range two
var sheettwo = spreadsheet.getSheetByName('Sheet2');
var sheettwoLastRow = sheettwo.getLastRow();
var sheettwoList = sheettwo.getRange('A2:A' sheettwoLastRow).getValues();
var sheettwoListFlat = sheettwoList.map(function(row) {return row[0];});
for (var i = 0; i< rangeOneValues.length ; i ){ // for each row in range one
if (sheettwoListFlat[i] == rangeOneValues[i]) { // if the range one value exists in range two
var rowNumber = sheettwoListFlat.indexOf(rangeOneValues[i]) 3; // get the row number of the matching value found in range two
var sheetOneColumnMValue = sheettwo.getRange('B' rowNumber).getValue(); // get the cell value of the same row in sheet two column B
var sheetOneRowNumber = i 3; // get the row number of the range one value
sheetone.getRange('M' sheetOneRowNumber).setValue(sheetOneColumnMValue); // set the cell value of sheet one column M row x, where x is sheetOneRowNumber
}
}
Комментарии:
1. Пожалуйста, предоставьте образец электронной таблицы и желаемый результат
2. Спасибо! Желаемый результат, безусловно, проще, чем кажется. Пожалуйста, ознакомьтесь с примером рабочей книги здесь: docs.google.com/spreadsheets/d/… Моя цель с помощью приведенного выше сценария состоит в том, чтобы для каждого значения в столбце I проверить, существует ли это значение в столбце A на втором листе. Если он существует, извлеките значение эквивалентной строки в столбце B на листе 2 и вставьте это значение в строку, соответствующую исходному значению в столбце M на листе 1.
Ответ №1:
Если вы напечатаете значение sheettwoListFlat
, оно будет содержать только 5 элементов. Как только итератор цикла for достигнет 5 или более, он автоматически установит if (sheettwoListFlat[i] == rangeOneValues[i]) {
значение false. Также оператор будет работать только в том случае, если элементы одного и того же индекса обоих массивов равны.
Также было бы более эффективно, если бы вы искали Лист1, используя значения листа 2, так как лист 2 имеет меньшее значение.
Здесь я использовал TextFinder, чтобы найти текст в пределах диапазона.
Использование значений листа 2 в качестве ключа поиска
Код:
function myFunction() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheetone = spreadsheet.getSheetByName('Sheet1');
var lastRowRangeOne = sheetone.getRange('I2').getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
var rangeOneValues = sheetone.getRange('I2:I' lastRowRangeOne);
var sheettwo = spreadsheet.getSheetByName('Sheet2');
var sheettwoLastRow = sheettwo.getLastRow();
var sheettwoList = sheettwo.getRange('A2:B' sheettwoLastRow).getValues();
for (var i = 0; i < sheettwoList.length; i ){
var find = rangeOneValues.createTextFinder(sheettwoList[i][0]).findNext()
if(find){
var sheetOneRowNumber = find.getRow();
sheetone.getRange('M' sheetOneRowNumber).setValue(sheettwoList[i][1]);
}
}
}
Использование значений листа 1 в качестве ключа поиска
Код:
function myFunction() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheetone = spreadsheet.getSheetByName('Sheet1');
var lastRowRangeOne = sheetone.getRange('I2').getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
var rangeOneValues = sheetone.getRange('I2:I' lastRowRangeOne).getValues();
var sheettwo = spreadsheet.getSheetByName('Sheet2');
var sheettwoLastRow = sheettwo.getLastRow();
var sheettwoList = sheettwo.getRange('A2:B' sheettwoLastRow);
for (var i = 0; i< rangeOneValues.length ; i ){ // for each row in range one
var find = sheettwoList.createTextFinder(rangeOneValues[i][0]).findNext();
if(find){
var rowNumber = find.getRow()
var sheetOneColumnMValue = sheettwo.getRange('B' rowNumber).getValue(); // get the cell value of the same row in sheet two column B
var sheetOneRowNumber = i 2;
sheetone.getRange('M' sheetOneRowNumber).setValue(sheetOneColumnMValue);
}
}
}
Выход:
Примечание: Оба выводят один и тот же результат, но используют значения листа 2 в качестве ключа поиска быстрее, чем другой.
Комментарии:
1. Это бесконечно проще, чем я думал, и гораздо разумнее искать значения в меньшем диапазоне. Спасибо.
Ответ №2:
function compareTwoCols() {
const c1 = 'COL1';//column names
const c2 = 'COL3';
const ss1 = SpreadsheetApp.openById(gobj.globals.datagenid);//data set 1
const sh1 = ss1.getSheetByName('Sheet1');
const [hd1, ...vs1] = sh1.getDataRange().getValues();
let col1 = {};
hd1.forEach((h, i) => { col1[h] = i });
const ds1 = vs1.map((r, i) => {
return r[col1[c1]];
});
const ss2 = SpreadsheetApp.openById(gobj.globals.ssid);//data set 2
const sh2 = ss2.getSheetByName('Sheet0');
const [hd2, ...vs2] = sh2.getDataRange().getValues();
let col2 = {};
hd2.forEach((h, i) => { col2[h] = i });
const ds2 = vs2.map((r, i) => {
return r[col2[c2]]
});
let matches = { pA: [] };
let idx = -1;
ds1.forEach((e, i) => {
let from = '';
do {
idx = ds2.indexOf(e, from);
if (~idx) {
if (!matches.hasOwnProperty(e)) {
matches[e] = [];
matches[e].push({ val1: e, row1: i 2, col1: col1[c1] 1, row2: idx 2, col2: col2[c2] 1 });
matches.pA.push(e);
} else {
matches[e].push({ val1: e, row1: i 2, col1: col1[c1] 1, row2: idx 2, col2: col2[c2] 1});
}
from = idx 1;
}
} while (~idx);
});
Logger.log(JSON.stringify(matches));
}
Электронная таблица1 Лист1:
COL1 | COL2 | COL3 | COL4 | COL5 |
---|---|---|---|---|
3 | 4 | 2 | 3 | 4 |
2 | 6 | 6 | 1 | 4 |
5 | 1 | 7 | 5 | 5 |
9 | 8 | 7 | 9 | 5 |
7 | 9 | 0 | 8 | 1 |
8 | 2 | 8 | 7 | 9 |
5 | 8 | 7 | 9 | 9 |
1 | 2 | 0 | 8 | 6 |
2 | 7 | 4 | 0 | 3 |
8 | 2 | 0 | 2 | 6 |
Электронная таблица2 Лист0:
COL1 | COL2 | COL3 | COL4 | COL5 |
---|---|---|---|---|
5 | 1 | 2 | 7 | 6 |
4 | 5 | 7 | 8 | 2 |
6 | 3 | 8 | 1 | 5 |
0 | 7 | 6 | 3 | 6 |
4 | 7 | 6 | 1 | 7 |
5 | 6 | 9 | 2 | 1 |
3 | 0 | 2 | 2 | 8 |
4 | 5 | 0 | 8 | 1 |
1 | 3 | 9 | 2 | 2 |
3 | 6 | 7 | 0 | 3 |
Соответствует Объекту:
{
"2":[
{
"val1":2,
"row1":3,//ds1 row 3
"col1":1,
"row2":2,//ds2 row 4
"col2":3
},
{
"val1":2,
"row1":3,//ds1 row 3
"col1":1,
"row2":8,//ds2 row 8
"col2":3
},
{
"val1":2,
"row1":10,
"col1":1,
"row2":2,
"col2":3
},
{
"val1":2,
"row1":10,
"col1":1,
"row2":8,
"col2":3
}
],
"7":[
{
"val1":7,
"row1":6,
"col1":1,
"row2":3,
"col2":3
},
{
"val1":7,
"row1":6,
"col1":1,
"row2":11,
"col2":3
}
],
"8":[
{
"val1":8,
"row1":7,
"col1":1,
"row2":4,
"col2":3
},
{
"val1":8,
"row1":11,
"col1":1,
"row2":4,
"col2":3
}
],
"9":[
{
"val1":9,
"row1":5,
"col1":1,
"row2":7,
"col2":3
},
{
"val1":9,
"row1":5,
"col1":1,
"row2":10,
"col2":3
}
],
"pA":[//just an array of all of the matches
2,
9,
7,
8
]
}