Email the filter results from Google Apps Scripts

0
votes

I have the following function (btw I am new to Google Apps Scripts).

function testing(){
  let thisSpreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(thirtyMinuteWorksheet).getDataRange().getValues()

  let filterData = thisSpreadsheet.filter(function (row,index) {
      return row[11] >= 30
  });


if(filterData.length > 0){
      MailApp.sendEmail({
      to: '[email protected]',
      subject: 'test',
      body: filterData,
      noReply: true
      });
  }

the filter data filters the sheet and returns all the rows that match the logic. Id like to be able to email those rows. if I run this, i get and email with this. (plus more, shorten it for simplicity).

{10=[Ljava.lang.Object;@7f7f43f6, 179=[Ljava.lang.Object;@d387470, 
83=[Ljava.lang.Object;@5daa185f, 54=[Ljava.lang.Object;@39ca2a17, }

i can see this is an object but I couldnt find anything on their API that pointed me in the right direction as to how to covert this results into the actual values

ps

Yes, if i console.log filterData i do get the correct objects. any ideas?

2
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api

2 Answers

1
votes

I figured out a way, since this is an array of objects, I just converted that into and HTML function. as below.

I passed in my array results and added some headers as well.

function makeTableHTML(myArray,tableHeaders) {
    let result = "<table border=1>";
    result += "<tr>";
        for(var j=0; j<tableHeaders.length; j++){
            result += "<td>"+tableHeaders[j]+"</td>";
        }
        result += "</tr>";
    for(var i=0; i<myArray.length; i++) {
        result += "<tr>";
        for(var j=0; j<myArray[i].length; j++){
            result += "<td>"+myArray[i][j]+"</td>";
        }
        result += "</tr>";
    }
    result += "</table>";

    return result;
}

That gave me a way to add the return results into the sendmail function from Google Scripts.

0
votes

You can try using join instead since the data you are sending is an array. Convert it to string to properly show the data.

Sample data:

sample data

Code:

  let filterData = thisSpreadsheet.filter(function (row, index) {
    return row[11] >= 30;
  });

  // Preprocesses filterData before sending mail
  filterData.forEach(function (row, index) {
    filterData[index] = filterData[index].join(",");
  });
  filterData = filterData.join("\n");


  if (filterData.length > 0) {
    MailApp.sendEmail({
      to: '[email protected]',
      subject: 'test',
      body: filterData,
      noReply: true
    });
  }

Sample output:

sample output

One quick answer is to try and wrap filterData with JSON.stringify(), but you can't modify how it shows in the mail.

Code:

body: JSON.stringify(filterData),

Sample output:

sample output2