Day: September 17, 2020

因為疫情，同事都係一齊訂外賣，之後係公司食，之前大家都係用Whatsapp group 到send 出黎，但成日出事，互overwrite 大家d 野。

所以之前有同事就google sheet 去入data，但我今日竟然無聊到去整靚佢…

有好多無聊functionsssss，睇黎我真係悶到傻了。

function sheetName() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

// update the title
function onOpen() {
  setTitle();
}

// reload the whatsapp message when any cell was updated
function onEdit(e) {
  var outputRange = SpreadsheetApp.getActiveSpreadsheet().getRange('B21');
  outputRange.setValue(generateWhatsappMsg('A3:D17', 'B21'));
}

function setTitle() {
  var titleRange = SpreadsheetApp.getActiveSpreadsheet().getRange('A1');
  var date = Utilities.formatDate(new Date(), "GMT+8:00", "yyyyMMdd");
  titleRange.setValue("今日終於食日本野 " + date);
}

function generateNewSheet() {
  var date = Utilities.formatDate(new Date(), "GMT+8:00", "yyyyMMdd");
  var todaySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(date);
  if (!todaySheet) {
    SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet();
    SpreadsheetApp.getActiveSpreadsheet().renameActiveSheet(date);
    todaySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(date);  
  } 
  SpreadsheetApp.setActiveSheet(todaySheet);
  setTitle();
}

function generateWhatsappMsg(range, outRange) {
  var inputRange = SpreadsheetApp.getActiveSpreadsheet().getRange(range);
  var rangeValues = inputRange.getValues();
  var lastRow = inputRange.getLastRow() - 3;
  var tel = SpreadsheetApp.getActiveSpreadsheet().getRange('B19').getValue();
  var msg = '送餐地點：XXXX\n送餐時間：12:00\n電話：'+tel+'\n\n';
  var errorMsg = '';
  var foodDict = {}; // key: set (sideDish); val: order count
  var drinkDict = {}; // key: drinkId; val: order count
  
  for ( i = 0; i < lastRow; i++){
    let name = rangeValues[i][0];
    let set = rangeValues[i][1];
    let sideDish = rangeValues[i][2];
    let drink = rangeValues[i][3];

    if (set) {
      var foodKey = sideDish? set + '(' + sideDish + ')': set;
      if (foodKey in foodDict) {
        foodDict[foodKey] = foodDict[foodKey] + 1;
      } else {
        foodDict[foodKey] = 1;
      }
      if (!drink) {
        errorMsg += name + ' 未選揀飲品\n';
      }
    }
    if (drink) { // if selected drink
      if (drink in drinkDict) {
        drinkDict[drink] = drinkDict[drink] + 1;
      } else {
        drinkDict[drink] = 1;
      }
      if (!set) {
        errorMsg += name + ' 未選揀飯餐\n';
      }
    }
  };
  
  // no one eat :(
  if (Object.keys(foodDict).length == 0) {
    errorMsg = "今日無人食 :(";
  }
  
  // looped all people, now generate the message
  for ( var foodKey in foodDict) {
    msg += foodKey + ": " + foodDict[foodKey] + "\n";
  }
  msg += '\n';
  for ( var drinkKey in drinkDict) {
    msg += drinkKey + ": " + drinkDict[drinkKey] + "\n";
  }
  
  msg += '\n唔要餐具/糖，謝謝';
  
  if (errorMsg.length>0) {
    SpreadsheetApp.getActiveSpreadsheet().getRange(outRange).setBackground('#E6B8AF');
    return errorMsg;
  } else {
    SpreadsheetApp.getActiveSpreadsheet().getRange(outRange).setBackground('white');
    return msg;
  }
}