Google sheets slack bot

index.gs

// Put your Slack webhook here, make sure its connected to the correct channel
var SLACK_WEBHOOK_POST_URL = "make one from slack integration web hoooks" //general

function dice() {
  
  if(Utilities.formatDate(new Date(), "GMT", "u") < 6){
  
    var personal = 0; // Satt til null siden jeg tenkte vi kjører gruppepreik 100%. Men kan endres her om man vil. Da må man sikkert endre logikken litt i preik() og separere spørsmålene på et vis.
    
    var dice = Math.random();
    
    if (dice < personal){
      preik()
    }else{
      gruppepreik()
    }
  }
}

function preik() {
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Users");
  
  var dataRange = sheet.getRange('A2:C14');
  var data = dataRange.getValues();
  
  var restOfTheUsers = data.filter(function(row){
    return row[2]!='x'
  })
  
  if(restOfTheUsers.length < 1) {
    clearAllMarks(data);
    preik();
    return;
  }
  
  var users = restOfTheUsers.length;
  var index = getRandomInt(users)-1
  var row = restOfTheUsers[index];
  
  var user = "<@".concat(row[1], ">");
  
  Logger.log(user);
  
  var question = getQuestion();
  
  Logger.log(question);
  
  if(Utilities.formatDate(new Date(), "GMT", "u") < 6){
    dispatch(user, question)
    markMessageSent(data, row);
  }
  
}

function gruppepreik() {
  var rss = 0.33;
  
  var dice = Math.random();
  
  if (dice <= rss){ //Hvis vi skal druse en reddit-post
    var feeds = [['news', 0], ['politics', 2], ['interestingasfuck', 1], ['todayilearned', 0], ['oddlysatisfying', 0], ['technology', 2], ['science', 1]];
    var feeds_num = feeds.length;
    
    var index = getRandomInt(1, feeds_num)-1;
    var sub = feeds[index][0];
    var pinned = feeds[index][1];
    
    var response = UrlFetchApp.fetch('https://www.reddit.com/r/' + sub + '/.rss').getContentText();
    var doc = XmlService.parse(response);
    var root = doc.getRootElement();
    var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
    
    var entries = root.getChildren('entry', atom);
    
    var title = entries[pinned].getChild('title', atom).getText();
    var url = entries[pinned].getChild('link', atom).getAttribute('href').getValue();
   
    Logger.log(title); 
    Logger.log(url);
    
    reddit_dispatch(sub, title, url)
    
  }else { //Om vi skal stille gruppa et spørsmål
    
    var question = getQuestion();
    
    if(Utilities.formatDate(new Date(), "GMT", "u") < 6){
      group_dispatch(question);
      markQuestion(question);
      Logger.log('Q for sending: ' + question);
    }
  
  }
}
  

function dispatch(user, question){
    
  // Stringify payload
  var payload = {
    payload: '{"text": "' + user +", "+ question + '"}'
  };

  // Build request
  var options = {
    method: "post",
    payload: payload
  };
  
  Logger.log(payload)

  // Send to Slack
  UrlFetchApp.fetch(SLACK_WEBHOOK_POST_URL, options);
};

function getRandomInt(max) {
  return Math.floor(Math.random() * Math.floor(max)) + 1;
}

function reddit_dispatch(sub, title, url){
  
    
  // Stringify payload
  var payload = {
    payload: '{"text": "' + '<!here> ' +'Todays best from *r/' + sub + '*: ' + '<' + url +'|' + title + '>' + '\nDiscuss! 😄' + '"}'
  };

  // Build request
  var options = {
    method: "post",
    payload: payload
  };
  
  Logger.log(payload)

  // Send to Slack
  UrlFetchApp.fetch(SLACK_WEBHOOK_POST_URL, options);
};

function group_dispatch(question){
  
  var question_text = question[0];
    
  // Stringify payload
  var payload = {
    payload: '{"text": "' + '<!here> ' + question_text + '"}'
  };

  // Build request
  var options = {
    method: "post",
    payload: payload
  };
  
  Logger.log(payload)

  // Send to Slack
  UrlFetchApp.fetch(SLACK_WEBHOOK_POST_URL, options);
};

function getRandomInt(min, max) {
  min = Math.ceil(min);
  max = Math.floor(max);
  return Math.floor(Math.random() * (max - min + 1)) + min;
}

function getQuestion() {
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Questions");
  var data = sheet.getDataRange().getValues();
  
  var restOfTheQuestions = data.filter(function(row){
    return row[1]!='x'
  })
  
  if(restOfTheQuestions.length < 2) {
    clearAllQs(data);
    getQuestion();
    //return;
  }
  
  var max = data.length;
  //Logger.log(max + data);
  
  //dette ble rotete og ineffektivt. Men greit. Sorry, Snorre, for at jeg rotet til koden din.
  
  var found = false;
  
  while (!found){
    
    var index = getRandomInt(2, max)-1;
    
    if (data[index][1] != 'x'){
      found = true;
    }  
  }
    
  return [data[index][0], index]

}


function markMessageSent(users, selectedUser) {
  var userId = selectedUser[1];
  var indexForMarking = users.map(function(row, index){
    if(row[1] === userId) return index;
  }).filter(function(row){
    return row != undefined;
  })
  var box = 1+1+indexForMarking[0];
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Users");
  var cell = sheet.getRange("C"+box);
  cell.setValue('x');
}

function clearAllMarks(users){
  var usersIndex = users.length+1;
  var array = []
  for(var i = 0; i < usersIndex-1; i++) {
    array.push([""])
  }
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Users");
  var cells = sheet.getRange("C2:C"+usersIndex);
  cells.setValues(array);
}

function clearAllQs(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Questions");
  var data = sheet.getDataRange().getValues();
  
  var questionIndex = data.length;
  
  var array = []
  for(var i = 0; i < questionIndex; i++) {
    array.push([""])
  }
  var cells = sheet.getRange("B2:B"+(questionIndex+1));
  cells.setValues(array);
}

function markQuestion(question) {
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Questions");
  var data = sheet.getDataRange().getValues();
  
  var row = question[1]+1;
  
  var cell = sheet.getRange("B"+row);
  cell.setValue('x');
}