!投

讓觀眾自行投稿到Google Sheet 的功能

Nightbot

query和User前要記得加encodeURI 去轉譯成網址看得懂的編碼

$(urlfetch json https://script.google.com/macros/s/AKfycbyvKoTNgiOvgSPrI6Y5wb75fxj7WdqqRefSR-0orFzgkDq5YGprHZ6TiU3yIjjTh7yKHQ/exec?action=addRow&msg=$(eval encodeURI("$(query)"))&user=$(eval encodeURI("$(user)")))

GAS

const SpreadSheetID = "你的SheetID";
const SpreadSheet = SpreadsheetApp.openById(SpreadSheetID);

function getSheet(sheetName) {
  return SpreadSheet.getSheetByName(sheetName);
}

//投票
function appendSheetCellInNewRow(sheetName, userName ,cellData) {
  const Sheet = getSheet(sheetName);
  const newRow = Sheet.getLastRow() + 1;
  //把值做成Array，以空白分割
  const dataArray = cellData.split(" ");
  var userRow = 0;

  //如果有投稿過的，抓Username在哪一列
  for (let u=1; u<newRow; u++){
    if(Sheet.getRange(u, 1).getValue() == userName){
      userRow = u;
    }
  }

  //如果有抓到Username,修改列
  if(userRow != 0){
  
      //找到User那一列重新寫入
      Sheet.getRange(userRow, 1).setValue(userName);
      
      //按照Array長度計算欄位長度放進值(不超過3個欄位)
      if(dataArray.length<3){
        for(let i=0; i<dataArray.length; i++){
        Sheet.getRange(userRow, i+2).setValue(dataArray[i]);
        }
      }
      else{
        for(let i=0; i<3; i++){
        Sheet.getRange(userRow, i+2).setValue(dataArray[i]);
      }
    }
  }
  //沒抓到Username,新投稿者
  else{
      //新增一列
      Sheet.getRange(newRow, 1).setValue(userName);
      
      //按照Array長度計算欄位長度放進值
      if(dataArray.length<3){
        for(let i=0; i<dataArray.length; i++){
        Sheet.getRange(newRow, i+2).setValue(dataArray[i]);
        }
      }
      else{
        for(let i=0; i<3; i++){
        Sheet.getRange(newRow, i+2).setValue(dataArray[i]);
      }
    }
  }
}



function doGet(e) {
  //取得參數
  const params = e?.parameter;
  //沒有東西回報nodata
  if(!params){
    return ContentService.createTextOutput("no data");
  }
  
  if(params.msg==""){
    return ContentService.createTextOutput("no data");
  }

  switch(params.action){
    case 'addRow':
    appendSheetCellInNewRow('NYB_2023',params.user,params.msg);
    return ContentService.createTextOutput(params.user+'提交成功 , https://reurl.cc/qZAQ0E');
    break;

    default:
    return ContentService.createTextOutput(params.user+'提交失敗');
    break;
  }
}