Блог

Я работаю в Google Sheets под управлением chrome V8

У меня есть два массива, созданные из данных диапазона на двух разных листах

в реальной ситуации я заранее знаю ширину или длину arr1 или arr2. Все, что я знаю, это то, что они имеют разные размеры

Оба массива имеют Id столбец. arr1 всегда будет иметь Id столбец в качестве первого столбца. Я не знаю заранее положение столбца Id столбца в arr2

arr1 при печати на листе будет выглядеть

 Id  |Label  |SAH1   |SAH2    |SAH3
---------------------------------------
aa1 |Bob    |stuff1 |stuff10 |stuff19
aa2 |Eggbert|stuff2 |stuff11 |stuff20
aa3 |Juan   |stuff3 |stuff12 |stuff21
aa4 |Alice  |stuff4 |stuff13 |stuff22
aa5 |Fraya  |stuff5 |stuff14 |stuff23
aa6 |Ted    |stuff6 |stuff15 |stuff24
aa7 |Velma  |stuff7 |stuff16 |stuff25
aa8 |Carol  |stuff8 |stuff17 |stuff26
aa9 |LeRoy  |stuff9 |stuff18 |stuff27
 

arr2 при печати на листе будет выглядеть

 Name             |SXH1      |Id     |SXH2
--------------------------------------------
Maya Townsend    |thing1    |mm1    |thing20
David Collste    |thing2    |mm2    |thing21
Bob              |thing3    |aa1    |thing22
Jennifer Menke   |thing4    |mm4    |thing23
Aissata Camara   |thing5    |mm5    |thing24
Marta Arranz     |thing6    |mm6    |thing25
Michael  Lennon  |thing7    |mm7    |thing26
Jonny Norton     |thing8    |mm8    |thing27
Carol            |thing9    |aa8    |thing28
Ted              |thing10   |aa6    |thing29
Katie Boone      |thing11   |mm11   |thing30
Adeline Sibanda  |thing12   |mm12   |thing31
Brian Blankinship|thing13   |mm13   |thing32
Christine Lai    |thing14   |mm14   |thing33
Jennifer Pierce  |thing15   |mm15   |thing34
Federico Bellone |thing16   |mm16   |thing35
Jeffrey Su       |thing17   |mm17   |thing36
Alice            |thing18   |aa4    |thing37
Sarah Ditton     |thing19   |mm19   |thing38
 

результат arr при печати на листе должен выглядеть следующим образом

 Id  |Label  |SAH1   |SAH2    |SAH3    |Name |SXH1   |SXH2  
------------------------------------------------------------
aa1 |Bob    |stuff1 |stuff10 |stuff19 |Bob  |thing3 |thing22 
aa2 |Eggbert|stuff2 |stuff11 |stuff20 |""   |""     |""
aa3 |Juan   |stuff3 |stuff12 |stuff21 |""   |""     |""
aa4 |Alice  |stuff4 |stuff13 |stuff22 |Alice|thing18|thing37
aa5 |Fraya  |stuff5 |stuff14 |stuff23 |""   |""     |""
aa6 |Ted    |stuff6 |stuff15 |stuff24 |Ted  |thing10|thing29
aa7 |Velma  |stuff7 |stuff16 |stuff25 |""   |""     |""
aa8 |Carol  |stuff8 |stuff17 |stuff26 |Carol|thing9 |thing28
aa9 |LeRoy  |stuff9 |stuff18 |stuff27 |""   |""     |""
 

Я искал способ сделать это и не смог найти ничего, что я мог бы изменить

Вот таблица Google с тестовыми данными https://docs.google.com/spreadsheets/d/1y0CFUEdpZ77IgKGVYXUinepg7we2F0311U4D5fAP17Y/edit?usp=sharing

Спасибо

Я считаю, что ваша цель и ситуация заключаются в следующем.

• Вы хотите добиться преобразования result arr из arr1 и arr2 в свой вопрос с помощью Google Apps Script.
• Вы не знаете столбец Id в arr2 таблице.

Для достижения вашей цели я хотел бы предложить следующий поток.

1. Извлеките значения из 2 исходных листов.
2. Создайте объект из значений листа «arr2», используя значения «Id».
3. Создайте массив для добавления в таблицу результатов, используя значения листов «arr1» и «arr2».
4. Поместите значения в таблицу результатов в таблицу «Результат».

Когда приведенный выше поток отражается в скрипте, он становится следующим.

Пример сценария:

Пожалуйста, задайте имя каждого листа.

 function myFunction() {
  const arr1SheetName = "arr1";
  const arr2SheetName = "arr2";
  const resultSheetName = "Result";

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // 1. Retrieve values from 2 source sheets.
  const [[header1, ...values1], [header2, ...values2]] = [arr1SheetName, arr2SheetName].map(s => ss.getSheetByName(s).getDataRange().getValues());

  // 2. Create an object from the values of "arr2" sheet using the values of "Id".
  const indexId = header2.indexOf("Id");
  const obj = values2.reduce((o, r) => {
    const id = r[indexId];
    r.splice(indexId, 1);
    return Object.assign(o, {[id]: r});
  }, {});

  // 3. Create an array for putting to the result sheet using the values of "arr1" and "arr2" sheets.
  const array = values1.map(r => obj[r[0]] ? r.concat(obj[r[0]]) : r.concat(Array(3).fill("")));
  header2.splice(indexId, 1);
  array.unshift([...header1, ...header2]);
  
  // 4. Put values to the result sheet to "Result" sheet.
  const sheet = ss.getSheetByName(resultSheetName);
  sheet.clearContents();
  sheet.getRange(1, 1, array.length, array[0].length).setValues(array);
}
 

• В этом случае из вашей общей электронной таблицы имена листов arr1 , arr2 и Result . Пожалуйста, будьте осторожны с этим.
• И в этом скрипте, даже когда столбец Id in arr2 sheet перемещается, скрипт работает.

Результат:

При использовании этого скрипта получается следующий результат.

От

• arr1 лист

  

• arr2 лист

  

Для:

• Result лист

  

Ссылки:

• уменьшить ()
• Назначение деструктурирования
• Синтаксис распространения (…)

Объединение совпадающих строк

 function combineMatchingRows() {
  const ss=SpreadsheetApp.getActive();
  const sh1=ss.getSheetByName('Sheet1');
  const sh2=ss.getSheetByName('Sheet2');
  const osh=ss.getSheetByName('Sheet3');//output sheet
  const vA1=sh1.getRange(2,1,sh1.getLastRow()-1,sh1.getLastColumn()).getDisplayValues();
  const hA1=sh1.getRange(1,1,1,sh1.getLastColumn()).getDisplayValues()[0];
  var hObj1={};//columns in sheet1
  hA1.forEach((h,i)=>{hObj1[h]=i 1;});
  const vA2=sh2.getRange(2,1,sh2.getLastRow()-1,sh2.getLastColumn()).getDisplayValues();
  const hA2=sh2.getRange(1,1,1,sh2.getLastColumn()).getDisplayValues()[0];
  var hObj2={};//columns in sheet2
  hA2.forEach((h,i)=>{hObj2[h]=i 1;});
  const id2A=sh2.getRange(2,hObj2['Id'],sh2.getLastRow()-1,1).getValues().flat();//array of ids in sheet2
  var vO=[];
  vA1.forEach((r,i)=>{
    let idx=id2A.indexOf(r[hObj1['Id']-1]);
    if(idx!=-1) {
      vA2[idx].splice(hObj2['Id']-1,1);//remove second Id column
      var row=r.concat(vA2[idx]);
      vO.push(row);
    }
  });
  osh.clearContents();
  osh.getRange(1,1,vO.length,vO[0].length).setValues(vO);
}
 

Лист1:

 Id,Label,SAH1,SAH2,SAH3
aa1,Bob,stuff1,stuff10,stuff19
aa2,Eggbert,stuff2,stuff11,stuff20
aa3,Juan,stuff3,stuff12,stuff21
aa4,Alice,stuff4,stuff13,stuff22
aa5,Fraya,stuff5,stuff14,stuff23
aa6,Ted,stuff6,stuff15,stuff24
aa7,Velma,stuff7,stuff16,stuff25
aa8,Carol,stuff8,stuff17,stuff26
aa9,LeRoy,stuff9,stuff18,stuff27
 

Лист2:

 Name,SXH1,Id,SXH2
Maya Townsend,thing1,mm1,thing20
David Collste,thing2,mm2,thing21
Bob,thing3,aa1,thing22
Jennifer Menke,thing4,mm4,thing23
Aissata Camara,thing5,mm5,thing24
Marta Arranz,thing6,mm6,thing25
Michael  Lennon,thing7,mm7,thing26
Jonny Norton,thing8,mm8,thing27
Carol,thing9,aa8,thing28
Ted,thing10,aa6,thing29
Katie Boone,thing11,mm11,thing30
Adeline Sibanda,thing12,mm12,thing31
Brian Blankinship,thing13,mm13,thing32
Christine Lai,thing14,mm14,thing33
Jennifer Pierce,thing15,mm15,thing34
Federico Bellone,thing16,mm16,thing35
Jeffrey Su,thing17,mm17,thing36
Alice,thing18,aa4,thing37
Sarah Ditton,thing19,mm19,thing38
 

Лист3 (вывод):

 aa1,Bob,stuff1,stuff10,stuff19,Bob,thing3,thing22
aa4,Alice,stuff4,stuff13,stuff22,Alice,thing18,thing37
aa6,Ted,stuff6,stuff15,stuff24,Ted,thing10,thing29
aa8,Carol,stuff8,stuff17,stuff26,Carol,thing9,thing28