---
# System prepended metadata

title: Velly Friend Match

---

# Velly Friend Match

Goal: Friends put in their horoscope and get matched to someone in the database with their same horoscope

Google Sheet's native script

Prints out filtered list

```
function buildReport() {
data = SpreadsheetApp.getActive().getSheetByName('Form1').getRange("C2:D").getValues()
Logger.log (data)
var filtered = data.filter(function (el) {
  return (typeof el !== 'undefined' && el[0].length > 2);
  })
console.log(filtered); //prints out filtered lists so that it's generated
data=filtered //resetting the data
console.log("created filtered") //confirmation that filtered list has been generated
let matchBreakdown = "Matches: "//data.map(function(row) { //https://www.august.com.au/blog/
newPerson = data[data.length-1][0]
LastHoroscopeSign = data[data.length-1][1]
//compatability matrix
if (LastHoroscopeSign == "Aries"){
    var filterListName = ["Aquarius"]
} else if (LastHoroscopeSign == "Taurus"){
    var filterListName = ["Cancer"]
} else if (LastHoroscopeSign == "Gemini"){
    var filterListName = ["Aquarius"]
} else if (LastHoroscopeSign == "Cancer"){
    var filterListName = ["Piseces"]
} else if (LastHoroscopeSign == "Leo"){
    var filterListName = ["Sagittarius"]
} else if (LastHoroscopeSign == "Virgo"){
    var filterListName = ["Taurus"]
} else if (LastHoroscopeSign == "Libra"){
    var filterListName = ["Gemini"]
} else if (LastHoroscopeSign == "Scorpio"){
    var filterListName = ["Cancer"]
} else if (LastHoroscopeSign == "Sagittarius"){
    var filterListName = ["Aries"]
} else if (LastHoroscopeSign == "Capricorn"){
    var filterListName = ["Taurus"]
} else if (LastHoroscopeSign == "Aquarius"){
    var filterListName = ["Gemini"]
} else if (LastHoroscopeSign == "Pisces"){
    var filterListName = ["Scorpio"]
}
console.log (filterListName)
console.log (filterListName[0])
console.log (filterListName[0] == "Aquarius")
console.log (typeof filterListName[0])
for (var i = 0; i < data.length; i++) {
    Logger.log(data[i][1])
    console.log (filterListName.includes(data[i][1]))
    console.log ((typeof data[i][1]))
    console.log (filterListName.indexOf(data[i][1])+1)
    if (filterListName.includes(data[i][1])){
        newmatchstring = newPerson +" matched with "+data[i][0]
        matchBreakdown += newmatchstring
        Logger.log(newmatchstring)
    }
} 
//Logger.log(matchBreakdown)
let payload = {
    "blocks": [
      {
        "type": "section",
        "text": {
          "type": "mrkdwn",
          "text": ":rainbow: *Friend Matched!* :rainbow:"
        }
      },
      {
        "type": "section",
        "text": {
          "type": "mrkdwn",
          "text": matchBreakdown
        }
      }]} //copy line 7 - 13
  sendAlert(payload);
}
function sendAlert(payload) {
  const webhook = "https://hooks.slack.com/services/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"; //Paste your webhook URL here
  var options = {
    "method": "post", 
    "contentType": "application/json", 
    "muteHttpExceptions": true, 
    "payload": JSON.stringify(payload) 
  };
  
  try {
    UrlFetchApp.fetch(webhook, options);
  } catch(e) {
    Logger.log(e);
  }
}
```
---

![](https://i.imgur.com/7ZBxhIE.png)

> Must create a Trigger in the console so that when a user submits in Forms it creates a incoming webhook in slack

---

<H2>
 Results
</H2>

![](https://i.imgur.com/CCZ8uiY.png)


<h2>
Current Status
</h2>

02/02/2021 - Script is broken as I am testing matching the variables

working on payload

<h2>
Resources
</h2>

Resources


https://www.august.com.au/blog/how-to-send-slack-alerts-from-google-sheets-apps-script/

https://cloud.google.com/run/docs/triggering/webhooks

https://developers.google.com/apps-script/overview

https://www.actiondesk.io/blog/google-sheets-script-to-automatically-retrieve-sql-data

https://digitalfinancelearning.com/filtering-using-google-script/#:~:text=In%20google%20script%2C%20we%20have,F10%20(A1%3AF10).&text=Once%20we%20set%20the%20filter,the%20criteria%20for%20that%20column.

https://www.actiondesk.io/blog/google-sheets-script-to-automatically-retrieve-sql-data