I have a google sheet with the following data google sheet "formatfruit"

Each user has a fruit and a vegetable associated, I want to know the percentage of similarity between each user in the google sheet "formatfruit" Today I can compare the first user kevin with all the others and return his percentage of similarity in another google sheet called "matchofruit".

I associated the value "1" when a user has a fruit or a vegetable in common with kevin and the value "0" if the user has no fruit or vegetable in common. The result that appears in the google sheet matchofruit is here google sheet matchofruit

The code I used is below

function myFunction() {
  var formafruit = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("fruit");
  var matchofruit = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("matchofruit");
  
  var n = formafruit.getLastRow(); 
  var user1 = formafruit.getRange(2,1).getValues();// name user 1 : kévin
  var user2 = formafruit.getRange(3,1).getValues();// name user 2 : mikael

    for (var i = 2;i<4;i  ) { // i<4 because we have 3 column in formafruit
      for (var z = 2;z<n 1;z  ) {
    matchofruit.getRange(z,1).setValue(user1); // Return the name of the users in the first column
    if(formafruit.getRange(2,i).getValue() === formafruit.getRange(z,i).getValue()){ // Compare the fruits and vegetables associated to kévin with the fruits and vegetables associated to each user
    matchofruit.getRange(z,i).setValue(1); // Returns 1 if kevin shares at least one fruit or vegetable in common with a user
      }
    else {
    matchofruit.getRange(z,i).setValue(0);
      } 
    }
  }
  
  // Calculate the % of common values
    for (var p = 0;p<n-1;p  ){}
    for (var s = 0;s<n-1;s  ) {
    var scoreforall = matchofruit.getRange(2,2,p,11).getValues()[s]// get the array of all the matches
    let sum = 0;
    for (let e = 0; e < scoreforall.length; e  ) {
    sum  = scoreforall[e]; // add each array together
    }
    var sumTotal= Math.round(sum*(100/2)); // convert in percentage each sum 
    matchofruit.getRange(s 2,4).setValue(sumTotal); //  send match values in column 4
   } 

  // Return the result in a sentence
    for (var a = 2;a<n 1;a  ) {
    var usern = formafruit.getRange(a,1).getValues(); //get all the users' emails in the formafruit
    var valeurmatch = matchofruit.getRange (a,4).getValues(); // get value % of matches
    matchofruit.getRange(a,5).setValue(user1 " " "have" " " valeurmatch "%" " " "of values in common with" " " usern);//Return the % of common value between Kevin and the other users
    }    
    
}

I would like to be able to do the same for mikael, gauthier, vanessa and mireille knowing that I only put 5 users to simplify the problem but that in truth there can be more than 100 users and that each user has more than 11 associated values(here we have only 2 different type of values, fruits and vegetables). It's been several weeks that I'm looking for a solution to my problem and I haven't found anything to solve it. Do you have an idea?

Thanks!

CodePudding user response：

I believe your goal is as follows.

• You want to achieve the following situations. (The following images are from OP's question.)

  • From

    

  • To

    

• In your situation, for example, when 5 users are used, you want to create 25 rows.

When I saw your script, the methods of setValues and getValues are used in the loop. I think that this becomes the high process cost. Ref So, I would like to propose the following modification.

Modified script:

function myFunction2() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [src, dst] = ["fruit", "matchofruit"].map(s => ss.getSheetByName(s));
  const [, ...values] = src.getDataRange().getValues();
  const res = values.flatMap(([a1, ...v1]) =>
    values.map(([a2, ...v2]) => {
      const temp = v1.map((e, i) => e == v2[i] ? 1 : 0);
      const sum = (temp.filter(f => f == 1).length / temp.length) * 100;
      const matchResult = `${a1} have ${sum}% of values in common with ${a2}`;
      return [a1, ...temp, sum, matchResult];
    })
  );
  dst.getRange(2, 1, res.length, res[0].length).setValues(res);
}

• In this modification, the values are retrieved from "fruit" sheet. And, an array for putting to "matchofruit" sheet is created. And then, the created array is put into "matchofruit" sheet.

Note:

• In this sample script, the header row of "matchofruit" has already been put. If you want to put the header row to "matchofruit" sheet, please add it to my proposed script.

References:

• map()
• filter()