I want to send a email with a formatted list of data (datalist1, datalist2, datalist3) on it. The list of data has a format that I want to make, but when I merge the 3 datalist using "push" into 1 array (merge) and send by email, the format only works for the first data (datalist1). If you guys know the problem or show the other way to achive it, It can be really helpful. Thank you

The sample input value of variable 'data':

The Expected Result that I want:

The Result that I got now: (The Problem)

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('SheetName');

  var [_, ...data] = sheet.getDataRange().getValues();
  var arr1 = [];
  var arr2 = [];
  var arr3 = [];
  for (var i = 0; i < data.length; i  ) {
    if (data[i][0] == 'First') {
      arr1.push([data[i][1], data[i][2], data[i][0], data[i][3]]);
    }
    if (data[i][0] == 'Second') {
      arr2.push([data[i][1], data[i][2], data[i][0], data[i][3]]);
    }
    if (data[i][0] == 'Third') {
      arr3.push([data[i][1], data[i][2], data[i][0], data[i][3]]);
    }
  }

  var merge = [];
  var datalist1;
  var datalist2;
  var datalist3;

  for (var y = 0; y < arr1.length; y  ) {
    if (arr1[y][2] == 'First') {
      if (y == 0) {
        datalist1 = "First"   "\n"   "- "   arr1[y];
      } else {
        datalist1 = datalist1   "\n"   "- "   arr1[y]
      }

    }
  }

  for (var y = 0; y < arr2.length; y  ) {
    if (arr2[y][2] == 'Second') {
      if (y == 0) {
        datalist2 = "Second"   "\n"   "- "   arr2[y];
      } else {
        datalist2 = datalist2   "\n"   "- "   arr2[y]
      }

    }
  }

  for (var y = 0; y < arr3.length; y  ) {
    if (arr3[y][2] == 'Third') {
      if (y == 0) {
        datalist3 = "Third"   "\n"   "- "   arr3[y];
      } else {
        datalist3 = datalist3   "\n"   "- "   arr3[y]
      }

    }
  }
  console.log(datalist1);
  console.log(datalist2);
  console.log(datalist3);


  merge.push(datalist1, datalist2, datalist3);
  //merge.sort();
  console.log(merge);
  var email = '@gmail.com'
  var Subject = "data";
  var Message = "Hello, \n"   "\n"  
    "This is a data message to let you know\n"   "\n"  
    "Here is the list of data: \n"   datalist1   
    "\n"   datalist2   "\n"   datalist3   "\n";

  MailApp.sendEmail(email, Subject, Message);

}

CodePudding user response：

I believe your goal is as follows.

• You want to achieve the following situation. (Following images are from your question.)

  • From

    

  • To

    

Modification points:

• From your showing an image of the sample Spreadsheet, it supposes that data is [["First", "low", "medium", "high"], ["Second", "medium", "low", "high"], ["First", "low", "high", "medium"], ["Second", "medium", "low", "high"], ["First", "low", "high", "medium"], ["Third", "low", "high", "medium"]].
• When data is used for your script, arr1, arr2, and arr3 are [["low","medium","high"],["low","high","medium"],["low","high","medium"]], [["medium","low","high"],["medium","low","high"]] and [["low","high","medium"]], respectively. In this case, arr1[i][0] == 'First', arr2[i][0] == 'Second' and arr3[i][0] == 'Third' are always false. By this, all values of datalist1, datalist2, datalist3 are null.
  • From this situation and your question, I'm worried that you might have miscopied your current script.

From the above situation, in this answer, I would like to propose a sample script using the above value of data and your expected result of ``.

Sample script:

const data = [["First", "low", "medium", "high"], ["Second", "medium", "low", "high"], ["First", "low", "high", "medium"], ["Second", "medium", "low", "high"], ["First", "low", "high", "medium"], ["Third", "low", "high", "medium"]]; // This is from your showing image of sample Spreadsheet.
const order = ['First', 'Second', 'Third']; // This is from your script.

const sorted = data.sort((a, b) => {
  const i1 = order.indexOf(a[0]);
  const i2 = order.indexOf(b[0]);
  const len = data.length;
  return 1 * ((i1 > -1 ? i1 : len) - (i2 > -1 ? i2 : len));
});
const merged = sorted.reduce((o, [a, ...b]) => {
  if (o.temp != a) {
    o.merged.push(a);
    o.temp = a;
  }
  o.merged.push(`- ${b.join(",")}`);
  return o;
}, { merged: [], temp: "" }).merged.join("\n");
console.log(merged)

Testing:

When the above script is run, the following result is obtained.

First
- low,medium,high
- low,high,medium
- low,high,medium
Second
- medium,low,high
- medium,low,high
Third
- low,high,medium

Note:

• If you want a script for retrieving values from Spreadsheet, you can also use the following script.

    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    const data = sheet.getRange("A2:D"   sheet.getLastRow()).getValues();
  

References:

• sort()
• reduce()
• map()