Google Apps Script match and fill data-CodePudding

I have tried several ideas to check two different sheets and get data written in the firt sheet, but I couldn't reach the goal.

the scenario is like below:

I have sheet1 called "Persons", where I want data from the two other sheets to be added (basically if the user has one or two monitors, a headphone, or a phone) and I want to fill it with a serial number of that device, to be added, some of the users have two monitors for example.
I have sheet 2 "Devices" which has the user email, S/N, and the type of the device.
last sheet called "Phones" where S/n and the owner's email is there.

and what I need to achieve is, if the user has two monitors, then their S/N will be added to the corresponding column, if it has only one, then will be only in the "Monitor 1" column, and same for headphones and phones.

I would really appreciate it if someone could help me with that

Here is my code and the link to the sheet https://docs.google.com/spreadsheets/d/1_eBCSgD2Uzyh9pvB0v2Qu3Sl_T4NaW7HkpQ9iAtN3Ig/edit?usp=sharing

function myFunction() {

    var ss1 = SpreadsheetApp.openById('1_eBCSgD2Uzyh9pvB0v2Qu3Sl_T4NaW7HkpQ9iAtN3Ig');
    var sheet1 = ss1.getSheetByName("Persons");
    var sheet2 = ss1.getSheetByName("Devices");
    var sheet3 = ss1.getSheetByName("Phones");

    var range1 = sheet1.getRange(2, 1, sheet1.getLastRow() - 1);
    var data1 = range1.getValues(); //get the Persons Email from sheet persons

    var range2 = sheet1.getRange(2, 2, sheet1.getLastRow() - 1);
    var data2 = range2.getValues(); //get the col to put monitor 1 in 

    var range3 = sheet1.getRange(2, 3, sheet1.getLastRow() - 1);
    var data3 = range3.getValues(); //get the col to put monitor 2 in 

    var range4 = sheet1.getRange(2, 4, sheet1.getLastRow() - 1);
    var data4 = range4.getValues(); //get col to put headphone sn in

    var range5 = sheet1.getRange(2, 5, sheet1.getLastRow() - 1);
    var data5 = range5.getValues(); //get col to put phone in



    var range6 = sheet2.getRange(2, 2, sheet1.getLastRow() - 1);
    var data6 = range6.getValues(); //get the email of the monitor or headphone owner
    var range7 = sheet2.getRange(2, 3, sheet1.getLastRow() - 1);
    var data7 = range7.getValues(); //get headphone or monitor

    //Phones
    var range8 = sheet3.getRange(2, 2, sheet1.getLastRow() - 1);
    var data8 = range8.getValues(); //get phone's owner email
    var range9 = sheet3.getRange(2, 1, sheet1.getLastRow() - 1);
    var data9 = range9.getValues(); //get phone S/N

    Logger.log(data2);

    for (var i = 0; i < data8.length; i  ) {
        for (var j = 0; j < data1.length; j  ) {
            for (var k = 0; k < data9.length; k  ) {
                for (var l = 0; l < data5.length; l  ) {
                    if (data8[i] === data1[j]) {
                        data5[l] = data9[k]; // Modified
                    }
                }
            }
        }
    }


}

CodePudding user response：

It seems that in your showing script, data5 has only one column. And, in this case, getValues can be used only 3 times. By this, all values are retrieved from 3 sheets. By this, the process cost can be reduced a little.

In your situation, how about the following sample script?

Sample script:

function myFunction() {
  var ss1 = SpreadsheetApp.openById('1_eBCSgD2Uzyh9pvB0v2Qu3Sl_T4NaW7HkpQ9iAtN3Ig');
  var sheet1 = ss1.getSheetByName("Persons");
  var sheet2 = ss1.getSheetByName("Devices");
  var sheet3 = ss1.getSheetByName("Phones");

  // 1. Retrieve values from 3 sheets.
  var values1 = sheet1.getRange("A2:A"   sheet1.getLastRow()).getValues();
  var values2 = sheet2.getRange("A2:C"   sheet2.getLastRow()).getValues().reduce((o, r) => {
    if (o[r[1]]) {
      if (o[r[1]][r[2]]) {
        o[r[1]][r[2]] = [...o[r[1]][r[2]], r[0]];
      } else {
        o[r[1]][r[2]] = [r[0]];
      }
    } else {
      o[r[1]] = { [r[2]]: [r[0]] };
    }
    return o;
  }, {});
  var values3 = sheet3.getRange("A2:B"   sheet3.getLastRow()).getValues().reduce((o, r) => (o[r[1]] = r[0], o), {});

  // 2. Create an array for putting to the 1st sheet.
  var res = values1.map(([a]) => {
    var t1 = Array(3).fill(null);
    if (values2[a]) {
      t1 = [...values2[a]["Monitor"] ? [...values2[a]["Monitor"], ...Array(2 - values2[a]["Monitor"].length).fill(null)] : Array(2).fill(null), ...(values2[a]["Headphone"] || [null])];
    }
    var t2 = values3[a] || null;
    return [...t1, t2];
  });

  // 3. Put the array to the 1st sheet.
  sheet1.getRange(2, 2, res.length, res[0].length).setValues(res);
}

If my understanding of your goal is correct, I thought that this sample script obtains your goal. Please confirm it.

Note:

Please test this script with your provided Spreadsheet. Because this sample script is for your provided Spreadsheet. So, when you change the structure of your Spreadsheet, this script might not be able to be used. Please be careful about this.

References:

Added:

From thank you very much for that, it was working fine, I understood that my answser resolved your question.

About except I wanted to add more columns in there like, 2 more phones and one headphone, and I wanted to adjust the code, but somehow it failed, when I saw your new Spreadsheet, I think that it is required to largely change my proposed script. So, in this case, the sample script is as follows.

Sample script:

function myFunction() {
  var ss1 = SpreadsheetApp.openById('1_eBCSgD2Uzyh9pvB0v2Qu3Sl_T4NaW7HkpQ9iAtN3Ig');
  var sheet1 = ss1.getSheetByName("Persons");
  var sheet2 = ss1.getSheetByName("Devices");
  var sheet3 = ss1.getSheetByName("Phones");

  // 1. Retrieve values from 3 sheets.
  var values1 = sheet1.getRange("A2:A"   sheet1.getLastRow()).getValues();
  var values2 = sheet2.getRange("A2:C"   sheet2.getLastRow()).getValues().reduce((o, r) => {
    if (o[r[1]]) {
      if (o[r[1]][r[2]]) {
        o[r[1]][r[2]] = [...o[r[1]][r[2]], r[0]];
      } else {
        o[r[1]][r[2]] = [r[0]];
      }
    } else {
      o[r[1]] = { [r[2]]: [r[0]] };
    }
    return o;
  }, {});
  var values3 = sheet3.getRange("A2:B"   sheet3.getLastRow()).getValues().reduce((o, r) => (o[r[1]] = o[r[1]] ? [...o[r[1]], r[0]] : [r[0]], o), {});

  // 2. Create an array for putting to the 1st sheet.
  var res = values1.map(([a]) => {
    var t1 = Array(3).fill(null);
    if (values2[a]) {
      t1 = [...values2[a]["Monitor"] ? [...values2[a]["Monitor"], ...Array(2 - values2[a]["Monitor"].length).fill(null)] : Array(2).fill(null), ...(values2[a]["Headphone"] ? [...values2[a]["Headphone"], ...Array(2 - values2[a]["Headphone"].length).fill(null)] : Array(2).fill(null))];
    }
    var t2 = [...values3[a], ...Array(3 - values3[a].length).fill(null)] || Array(3).fill(null);
    return [...t1, ...t2];
  });

  // 3. Put the array to the 1st sheet.
  sheet1.getRange(2, 2, res.length, res[0].length).setValues(res);
}